Solved

retrieving next identity values

Posted on 2000-05-14
18
795 Views
Last Modified: 2008-03-10
This is not a very practical question, but I thought would be a good theoretical exercise.

What I want to do is retrieve the next available identity value for a given table, BEFORE AN INSERT OCCURS.  I was trying to write a generic stored procedure that you could supply the table name and the rest would be taken care of.

This idea first came to me while I was trying to find a work around, for a bit of code already written.  I have taken a different approach to the problem as this is not practical in a multi-user environment, but I would like to know if it is actually possible...

Looking forward to hearing a correct answer.
0
Comment
Question by:barx
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
  • 3
  • +4
18 Comments
 
LVL 7

Expert Comment

by:spiridonov
ID: 2809492
The only way to do it is to insert value into the table, use @@IDENTITY variable to retrive inserted value, then delete inserted record and run DBCC CHECKINDENT to reset identity value to retrieved-1.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2809605
If you do this:

select max(rowid) from LogFile
select autoval from syscolumns where id = object_id('LogFile') and name = 'rowid'
           
-----------
24

(1 row(s) affected)

autoval                                
-----------------------------
0x18000000010000000100000082

(1 row(s) affected)

This leads to the conclusion that autoval contains the necessary information to find out the next value:
0x18 is the hex value for 24,
the following 00001 and 00001 correspond to the Seed and Interval settings. 82 indicates that it is an IDENTITY field.

Hope this helps

0
 
LVL 1

Author Comment

by:barx
ID: 2809627
I need to retrieve the value before the insert occurs.

Thanx anyway
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2810073
That is what my information gives the possibility too:
add 0x18 and 0x1 and this gives the result 0x19 (25)
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2810076
This solution will need "some" code to do the computations, but it should work
0
 
LVL 1

Author Comment

by:barx
ID: 2812639
angelIII, thanx for explaining the autoval field, I had actually looked at that before but I had no idea what it was representing.

The only problem I have with your answer is that it appeared to work the 1st time I tried it, but after I inserted a few more records the '0x18' part of it didn't change.  From what you were saying I thought that the beginning of of that hex field should change as I insert more records but it appears that it doesn't.

Am I missing something you have said in your response, or does this happen to you aswell
0
 
LVL 1

Author Comment

by:barx
ID: 2812644
angelIII, thanx for explaining the autoval field, I had actually looked at that before but I had no idea what it was representing.

The only problem I have with your answer is that it appeared to work the 1st time I tried it, but after I inserted a few more records the '0x18' part of it didn't change.  From what you were saying I thought that the beginning of of that hex field should change as I insert more records but it appears that it doesn't.

Am I missing something you have said in your response, or does this happen to you aswell
0
 
LVL 6

Expert Comment

by:crsankar
ID: 2813308
I am not sure wether I understood your question correctly. Is it not possible for you to use a query like this to find out the next identity value?

select max(identitycol) + 1 from products

Here "1" should be substituted with the value of your identity increment

you can use the ident_incr function to find out the increment value.

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2814148
crsankar:
unfortunately, your idea will not work in the following situations:
everytime the "last" rows are deleted, their ID's are not released to the Identity Counter!

So adding 3 row, deleting them (even inside a transaction) will increase the identity value of 3!

The proposal of spirinodov (DBCC CheckIdent) will not work neither, because DBCC does not take care of transactions...


0
 
LVL 1

Author Comment

by:barx
ID: 2815487
Adjusted points from 50 to 100
0
 
LVL 1

Author Comment

by:barx
ID: 2815488
I checked back at the same example I was toying with the other day and the '0x18' part has changed now.....

It appears to change periodically which is quite strange indeed.  Maybe there is another field that tracks the amount of inserts.  

If anyone can solve this mystery I will increase the points to 100.
0
 
LVL 4

Accepted Solution

by:
kpkp earned 50 total points
ID: 2817949
I think I found your answer, angelIII's soloution does work - If you run a DBCC CHECKIDENT before you check the AutoVal column.

I did this (a few times) and it all add up:

use pubs
select max(job_id) from jobs
select autoval from syscolumns where name = 'job_id'
insert into jobs values  ('test2',10,10)
dbcc checkident(jobs)
select autoval from syscolumns where name = 'job_id'
0
 
LVL 1

Author Comment

by:barx
ID: 2819455
Thank you angelIII and kpkp, I would like to split the points 50/50 between you guys, but I am not sure how to do it.

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2820086
Ask community support to split points, they will guide you
0
 
LVL 3

Expert Comment

by:darinw
ID: 2824339
Community Support has reduced points from 100 to 50
0
 
LVL 3

Expert Comment

by:darinw
ID: 2824340
Hello everyone,

Reducing points to one half to allow for split.

You can now accept one of the comments in this thread as an answer. To award the other Expert, you can create a new question in this topic area with a title of 'For ExpertName -- 10352216' using that Experts username.

Remember, the Accept Comment as Answer button is in the header of the comment.

For your convenience, you can use this link to create the new question:
http://www.experts-exchange.com/bin/NewQForm?ta=90

darinw
Customer Service
0
 
LVL 3

Expert Comment

by:darinw
ID: 2824345
Oops. Made a mistake on the title of the new question - the number should be 10350313

darinw
Customer Service
0
 

Expert Comment

by:Srinibhatla
ID: 4105594
If while a user is adding a new record, with this autoVal, what if another user adds a new record to the DB?
0

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question