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 |
|
tripodal
Constraint Violating Yak Guru
259 Posts |
 Posted - 2009-06-03 : 15:20:41
|
| My goal is to arive at the next ID that will be inserted into a new table, BEFORE its inserted. I can pull IDENT_INCR(table_name) and know what to add to IDENT_CURRENT(table_name) to arive at the next ID that will be inserted. Problem is that this is broken* for a table that has never had any data in it.Example. 1. Create a table with an Identity column (1,1) and a VarChar column. DO NOT add any rows yet. 2. Query the IDENT_CURRENT. It will return 1 (one). IDENT_CURRENT+IDENT_INCR=2. **Not correct**.3. Now insert a row. The ID column will be 1 (one) because the seed is 1(one).4. Now query IDENT_CURRENT and it STILL says 1 (one). IDENT_CURRENT+IDENT_INCR=2. Correct.5. Now delete that initial row so you are back at an empty table. 6. Now query IDENT_CURRENT and it STILL says 1 (one). IDENT_CURRENT+IDENT_INCR=2. Correct.Now, we all know if you add another row to the table the ID will be 2. If you query IDENT_CURRENT at this point it still says 1 (one) and adding IDENT_INCR to IDENT_CURRENT yields 2 which is correct but there is no way to know there WAS a row 1 (one) or if the table is freshly created and never had any data in it. IDENT_CURRENT returns 1 for a new table with no data ever in it, it also returns 1 (one) for a table that has had 1 (one) row inserted and deleted. IDENT_CURRENT is correct for rows 2 and above.So, given the above info, how do I account for a freshly created table AND for a table that has had one row inserted and deleted when I need to know the next ACTUAL ID what will be inserted. I can't check for EOF because a freshly created table and one that has had all rows deleted both show EOF on a select.Inserting the row and then recovering the ID is not an option as I need it before I insert the row so I can present the info to a user in the form of a folder path so I need the NEXT ID to build the path from.So, for a table that has had at least one row added, irrelevant to the fact that it was deleted or not, IDENT_CURRENT + IDENT_INCR = NextID works but for a table that has had no rows ever in it, the formula incorrectly returns 2.There are 2 work arounds for this none of which we are happy with.1. When using this utility to generate the table, automatically create the first two rows.2. Modify the script that would add a new table to compare IDENT_CURRENT and the Seed and also check EOF. We are using this as the back end for the gallery and are trying to accomodate all the issues such as, 'why does the first gallery always have to be "name"' or 'why does it always make 2 galleries.' or why cant I create the first gallery myself'Because inevitably, the option you dont give someone, is the one they demand.threads and pages i have consulted.*http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74931*https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=297781 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-06-04 : 13:32:01
|
| at what point you want id to be given to user? even before they input data? or just before data gets inserted in table? |
 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-04 : 13:35:39
|
This is by behaviour.I've submitted this to Microsoft and they answered it "might" get fixed in following service packs.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2009-06-04 : 13:48:15
|
| Sounds like you only have one user working at a time, so you could select MAX (MAX + 1) and work with that. If there is ever the possibility that there will be more that one use on your system, then you had better redeign it now. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
tripodal
Constraint Violating Yak Guru
259 Posts |
Posted - 2009-06-05 : 16:54:36
|
| yes Peso I had seen that blog.Lost the link in my searches, so instead of displaying the infomation before hand. We decided it may well be easier to not display it at all until after the row is assigned.We really appreciate the help.Ill try to ask a much harder question next time.:) |
|
|
|
|
|
|
|
|
IDENT_CURRENT incorrect in a new table.