Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jonny1409
Posting Yak Master
133 Posts |
 Posted - 2007-12-18 : 05:01:50
|
Lets say I have a table called 'tbl_ABC', and in there the ID field has identity set to yes.If I have rows with ID's numbered 1-10, the following SQL gives me the next free ID, which in this case would be 11.SELECT MAX(ID+ 1) AS NextIDFROM tbl_ABC However, if I was to delete numbers 8-10 - the SQL above would shwo me 9, even though the next available number is actually 11 as 9 and 10 have been used.Is it possible for me to get the actual next number i.e in this case 11 ? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-12-18 : 05:22:32
|
SELECT IDENT_CURRENT('tbl_ABC') + 1
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
Jonny1409
Posting Yak Master
133 Posts |
Posted - 2007-12-18 : 05:44:55
|
| Hi Peso,Thanks for that, but It doesn't seem to work.If I do it in a view in Enterprise Manager it shows me 10 rows, all with <NULL> in them.The same happens when I use Query Analyser. |
|
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-12-18 : 05:50:07
|
| Which means you don't have identity column in tbl_ABC.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
|
|
|
Jonny1409
Posting Yak Master
133 Posts |
Posted - 2007-12-18 : 05:55:06
|
| Apologies, I was being a tool.All sorted now - thanks for your help. |
|
|
|
|
|
|
Identity Key Problem