Solved

Current identity value

Posted on 1998-08-26
9
261 Views
Last Modified: 2010-03-19
I have a table with a identity column. Sometime I need to delete all the data in the table. How can I get the current identity value (next identity value) with no data in the table? (I am using SQL Server 6.5)?
0
Comment
Question by:chunhsin
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 3

Expert Comment

by:gmoriak
ID: 1089765
This was not easy.  I have never seen it documented and you're probably not supposed to know it exists.  But here's the answer.

It is stored inside (it's binary - you'll have to pull it out) the updatestamp column in the sysindexes table for the row with your table name.  

select updatestamp from sysindexes where name = 'mytable'

then you'll have to use bitwise operators to get the value, because this has the next value and the increment seed in a single column.
0
 

Author Comment

by:chunhsin
ID: 1089766
In the SQL Server Books on line, the updatestamp is the count of change to row or page. When I delete som row in the table, the updatestamp increment too. I don´t know how can I do what you said!
0
 
LVL 5

Expert Comment

by:mayhew
ID: 1089767
Out of curiosity, why would you need to know the next number if the table is empty?

You can start entering new data without knowing what the next number should be.

If you supply more information, maybe we can figure out a way around your problem.  :)

0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 1

Expert Comment

by:kdimmock
ID: 1089768
You might be better getting the value back after an INSERT through the @@IDENTITY variable rather than predicting it beforehand (what if some other user gets in first? - who wins?)
0
 

Expert Comment

by:manish77
ID: 1089769
This answer is really secret stuff but I am offering it to you free.

Try
dbcc checkident (tablename)

manish77
0
 

Author Comment

by:chunhsin
ID: 1089770
Sorry for the delay.

My situation is the following:
  I have a application that insert user login information into SQL Server. Everyday, I download the user login information to a log file, and delete from SQL Server. When I start my application I need to calculate an offset to be sum with a control number, and the offset is evaluate from the currente identity value that need to be get using ODBC.

  I can´t  use "dbcc checkident" because this command may reset the current identity value.
0
 
LVL 3

Expert Comment

by:gmoriak
ID: 1089771
Food for thought - When something is this difficult to resolve, the solution often is to rethink your process, not to find a force fit workaround.

0
 
LVL 5

Expert Comment

by:mayhew
ID: 1089772
When you start your application, could you add a dummy row, select the id value, add 1 to it, delete the dummy row and then do the calc?

It's not the most elegant solution, but I think it would work.  :)

Either that, or you're going to have to store the ID number in another table and have an after insert trigger on the first table that updates that value in the second table.

Don
0
 
LVL 2

Accepted Solution

by:
Ezhil earned 100 total points
ID: 1089773
use @@IDENTITY  global variable to get the last identity value
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server - Copy database from one server to another 3 32
Run Stored Procedure uisng ADO 5 21
MS SQL Server select from Sub Table 14 25
SQLCMD Syntax 2 15
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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how the fundamental information of how to create a table.

829 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