MSSQL 2005 - Get next increment id from an index column

[tahirk]

Hi,

I am currently developing a C#/ASP.NET web application that hooks into an MSSQL 2005 Express database (will be using 2005 enterprise in the live environment).

The database uses several tables with simple index columns which auto increment (the reason I went for simple integer index values is due to a requirement by the user whereby we need to display the records and their corresponding index number for referencing later on).

I am using the following SQL to return the current maximum value:

SELECT MAX(indexcolumn) FROM mydbtable

I am then adding 1 to the returned value to get the next record id. I need to obtain this value prior to doing any inserts.. which I seem to think is not so easy with MSSQL unless you do a insert first!

I want to be able to find out what the increment value is either by querying the MSSQL database or using some code logic to determine what the correct next id is.

I also have the problem of multiple inserts getting the same index value however I was hoping this is simply a case of letting the database handle the auto increment for each insert and then just updating the posting page with whatever id has been assigned to the record that was just inserted.

Is there a better way of doing this (and simpler as I may be over complicating th matter)?

Thanks in advance,

Fz

 

[AlexCuse]

I think you should just use an identity column and use SCOPE_IDENTITY() to return the identity of the row inserted to your app.

An identity column handles the incrementing for you so you don't have to worry about such things.

Hope this helps,

Alex

[small]----signature below----[/small]
I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[mrdenny]

It's much better to simply get the new value after the insert, rather than before. By doing it before there is no way to be sure that you are getting the value which will actually be assigned to the record.

That warning said, the IDENT_CURRENT system function can be used to retreive that last identity value that was used for a table by any user.

declare @ident_current as int
set @ident_current = IDENT_CURRENT('YourTable')
...

But again, you would be much better off doing the insert then getting back the actual value which was assigned with the scope_identity function.

declare @IdentityValue int
insert into YourTable
...
set @IdentityValue = scope_identity()
...

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[tahirk]

So there is no easy way of finding out what the next increment value is before doing an insert?

I will try out the suggestions and see how I get on.

Cheers,

Fz

 

[ESquared]

What if you find out the next increment value, then someone else grabs it? Getting a "next" value at all, one per customer, is a function of the database's atomicity for each action. the moment you split things into two steps, you don't have an atomic action anymore and someone can come in between the steps.

The only other way to do it is to get an exclusive, held table lock on the table in question, so no one can do anything at all with it between your statements. But that's a performance killer.

Can you insert dummy values and then fill in correct ones with an update?

Can you put the identity generation into another very narrow table and then use that value for the insertion to the main table?

In fact, why do you need the ID before insertion? Why is it so important?

[COLOR=black #e0e0e0]For SQL and technical ideas, visit my blog, Squared Thoughts.[/color]