Creation of auto Increment row

Question

Creation of auto Increment row

IT researcher

SSCertifiable

Points: 7600
More actions
March 26, 2013 at 12:42 am

#275670

Hi,
I am using Sql server 2008 R2 express.I want a column id with auto incrementing value in my table.But i don't want to use built in Auto increment property because I want to delete a row and then insert row in middle etc and Built in Auto increment property will cause some restriction on operations.So i want to increment that value during inserting a row manually.I thought i can do like this
declare @root int
select @root=max(id)from [AdventureWorks].dbo.seh_test
insert into AdventureWorks.dbo.seh_test values(@root+1,'seh')
Is there any way to do same thing using single query? Or any optimizations for my query?
Thank you

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Steve JP Ten Centuries Points: 1070 More actions · Answer 1

I'm aware that this might not be answering your question but you can still insert rows into the table which has an incremental column:

drop table [Test auto inc]

create table [Test auto inc]

( ID int not null identity(10,2) primary key

, [Text column?] varchar(100) null

)

insert [Test auto inc] ( [Text column?] ) values ( 'The' )

insert [Test auto inc] ( [Text column?] ) values ( 'brown' )

insert [Test auto inc] ( [Text column?] ) values ( 'fox' )

select * from [Test auto inc]order by ID

set identity_insert [Test auto inc] on

insert [Test auto inc] ( ID, [Text column?] ) values ( 11 , 'quick' )

insert [Test auto inc] ( ID, [Text column?] ) values ( 1 , 'Old school:' )

set identity_insert [Test auto inc]off

select * from [Test auto inc] order by ID

The identity_insert is the key to this and is not a problem within a stored procedure.

Or if you still want to use a single query then you can use the code below or use a trigger. Either of these methods can raise issues in there own right or cause duplicates. Note that this for a single row insert at a time.

insert [Test auto inc] ( ID, [Text column?] )

select NuID = isnull( ( Select MAX(ID) + 1 from [Test auto inc] ) , 1 )

, 'jumped'

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

I would strongly recommend if you want an auto-incrementing column, use the identity property. If gaps are such a big problem (which they shouldn't be, the identity is a meaningless number), you can use identity insert and put a row into the gap.

The problem is that the code you posted has no concurrency control in it, it's possible for 2 or more people to insert the same value with it. Home-built autoincrement is easy to get wrong (duplicate values or sever performance impact). Rather use identity.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Edit: duplicate post.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

winmansoft (3/26/2013)
Hi,
I am using Sql server 2008 R2 express.I want a column id with auto incrementing value in my table.But i don't want to use built in Auto increment property because I want to delete a row and then insert row in middle etc and Built in Auto increment property will cause some restriction on operations.So i want to increment that value during inserting a row manually.I thought i can do like this
declare @root int
select @root=max(id)from [AdventureWorks].dbo.seh_test
insert into AdventureWorks.dbo.seh_test values(@root+1,'seh')
Is there any way to do same thing using single query? Or any optimizations for my query?
Thank you

Is this new column to be used by the database as a key of some kind or by the business as, say, an order or invoice number?

If it's the former, follow Gail's advice. It really doesn't matter if there are gaps in a pk sequence and any method designed to fill the gaps or create a gapless sequence will cost you far more than you may think.

If it's the latter, how many rows do you intend to generate at a time?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

timwell SSCertifiable Points: 5315 More actions · Answer 5

I have also implemented my own auto-increment (for reasons I won't go into here) that may be totally loony.

I would welcome any feedback/criticism...

I have a table that keeps the next row ID to assign for each table

CREATE TABLE [dbo].[NextRowId](

[NidTableName] [varchar](255) NULL,

[NidCurrentId] [int] NULL

)

and return the next value from a stored proc and avoiding conflict by putting it into a transaction.

CREATE PROCEDURE [dbo].[GetNextRowId]

@Table VarChar(255)

AS

BEGIN

Declare @result int

BEGIN TRY

BEGIN TRANSACTION

Update NextRowId Set @result = NidCurrentId = NidCurrentId + 1 Where NidTableName = @Table

COMMIT TRANSACTION

END TRY

BEGIN CATCH

ROLLBACK TRANSACTION

END CATCH

RETURN @result

END

I have not tested what happens if (for example) the transaction fails and is rolled back...

aaron.reese SSChampion Points: 13498 More actions · Answer 6

I use ID fields to provide me with an absolutely unique (to the table) value that can be used as the Foriegn key to other tables. Its value has no meaning except to be a pointer to this particular record. If this is what you are using it for then use identity. If you are using it for something else and you must not have breaks (e.g. document number and they must be sequential with no gaps for audit purposes) then you will have to use something else, but you would probably be better off adding both the Identity field and use that as the FK AND adding the document reference ID using your method.

Where's Joe when you need him, I am sure he would have lots to say on the subject.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

aaron.reese (3/26/2013)
I use ID fields to provide me with an absolutely unique (to the table) value that can be used as the Foriegn key to other tables. Its value has no meaning except to be a pointer to this particular record. If this is what you are using it for then use identity. If you are using it for something else and you must not have breaks (e.g. document number and they must be sequential with no gaps for audit purposes) then you will have to use something else, but you would probably be better off adding both the Identity field and use that as the FK AND adding the document reference ID using your method.
Where's Joe when you need him, I am sure he would have lots to say on the subject.

Joe hates surrogate keys 😛

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

IT researcher SSCertifiable Points: 7600 More actions · Answer 8

Thanks for replies

I think we will use identity property only