Next Identity Field
-
Hey gurus,
Quick question, does anyone know a way to get the next identity field that will be used in a table. At first thought I used the following query.
SELECT TOP 1 family_key + 1 AS nfamily_key FROM t_family ORDER BY family_key DESC
As you know this really isn't practicle. Since if some records were deleted from the database, I wouldn't be getting an accurate identity. The only reason I want this is so I can show the user what the next "key" is when they are entering in the data.
Thanks for the help
Shane Stecher
-
If I would do this, I'd use MAX() instead od TOP.
However, I wouldn't do this. SQL Server is not Access. Consider two users inserting data. One started a little bit earlier and is shown number 100, and then enters some data, but instead of saving, leaves and goes out to lunch. Next, user 2 is inserting data in the meantime. What is he shown? 100? 101? Which number will he get when he saves, before user 1 comes back from lunch? If you really want this what about showing this number right after successfully inserting the data?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Select IDENT_CURRENT will return the last identity value generated for a specific table. I agree with Frank that you should show the newly generated ID right after the row is inserted.
Greg
Greg
-
SELECT IDENT_CURRENT()+IDENT_INCR() will return the next one. Still I wouldn't do it.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
agree with Frank. Don't do it. Why do you need to know?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply