Reusing Identities

By Dinesh Priyankara, 2003/02/18

What is the best way to Find and reuse deleted identities

By: Dinesh Priyankara

In most table designs, Identity columns are used to maintain the uniqueness of records. There is no problem with insertion and modification of data using an identity column. With deletions though, gaps can occur between identity values. There are several ways to reuse these deleted (removed) identity values.

You can find a good solution in Books Online but I wanted to find a new way and my research ended up with a good solution. After several comparisons, I decided to continue with my solution. So, I'd like to share my method with you all and let you decide what solution to use.

First of all, let’s create a table called ‘'OrderHeader'’ that has three columns. Note that the first column intID is identity type column.

IF OBJECT_ID('OrderHeader') IS NOT NULL
DROP TABLE OrderHeader
GO
CREATE TABLE OrderHeader
(intID int IDENTITY(1,1) PRIMARY KEY,
strOrderNumber varchar(10) NOT NULL,
strDescription varchar(100))

Now let’s add some records to table. If you want, you can add small amount of records but I added 10000 records because most tables have more than 10000 records and we must always try to make our testing environment real.

DECLARE @A smallint
SET @A = 1
WHILE (@A <> 10001)
BEGIN
     INSERT INTO OrderHeader
          (strOrderNumber,
          strDescription)
          VALUES
          (‘OD-' + CONVERT(varchar(3), @A), -- Adding something for Order Number
          'Description' + CONVERT(varchar(3), @A)) -- Adding something for Description
     SET @A = @A + 1
END

OK. Let’s delete some randomly selected records from the table.

DELETE OrderHeader WHERE intID = 9212
DELETE OrderHeader WHERE intID = 2210
DELETE OrderHeader WHERE intID = 3200

If you run now a simple select query against the table, you will see some gaps between the column intID values.

Now it is time to find these gaps and reuse. As I mentioned above there are two methods (or more methods if you have already done in some other way). First let’s see the BOL example.

Method 1

DECLARE @NextIdentityValue int

SELECT @NextIdentityValue = MIN(IDENTITYCOL) + IDENT_INCR('OrderHeader')
FROM OrderHeader t1
WHERE IDENTITYCOL BETWEEN IDENT_SEED('OrderHeader') AND 32766
AND NOT EXISTS (SELECT * FROM OrderHeader t2
WHERE t2.IDENTITYCOL = t1.IDENTITYCOL + IDENT_INCR('OrderHeader'))

SELECT @NextIdentityValue AS NextIdentityValue

Output:
NextIdentityValue
--------------------
2210

This is very simple query. You can find the first deleted identity value and can reuse it. But remember you have to set the IDENTITY_INSERT ON that is allowed to explicit values to be inserted into identity column.

SET IDENTITY_INSERT OrderHeader ON

INSERT INTO OrderHeader
    (intID,
    strOrderNumber,
    strDescription)
VALUES
    (@NextIdentityValue,
    ‘OD-' + CONVERT(varchar(3), @A),
    'Description' + CONVERT(varchar(3), @A))

SET IDENTITY_INSERT OrderHeader OFF

Now let’s see the method 2.

Method 2

Now I am going to create another table that is called “tb_Numbers” and has only one column that contains numbers in sequence. In my most databases, I have created and used this table for many tasks. Let me come with those in my future articles.

IF OBJECT_ID('tb_Numbers') IS NOT NULL
DROP TABLE tb_Numbers
GO
CREATE TABLE tb_Numbers
(intNumber int PRIMARY KEY)

Note that I have inserted 30000 records (numbers) into the table. The range is depending on the usage of this table. In my some of databases, this range was 1 to 1000000.

DECLARE @A1 int
SET @A1 = 1

WHILE (@A1 <> 30000)
BEGIN
INSERT INTO tb_Numbers (intNumber) VALUES (@A1)
SET @A1 = @A1 + 1
END

Now let’s query the gaps (or first deleted identity value) in the OrderHeader table

SELECT TOP 1 @NextIdentityValue = intNumber
FROM OrderHeader
RIGHT OUTER JOIN tb_Numbers
ON tb_Numbers.intNumber = OrderHeader.intID
WHERE intID IS NULL AND intNumber < = (SELECT MAX(intID) FROM OrderHeader)

SELECT @NextIdentityValue AS NextIdentityValue
Output:
NextIdentityValue
--------------------
2210

This is very simple query too. I have used RIGHT OUTER JOIN to join the OrderHeader table with tb_Numbers. This join causes to return all rows (numbers) from tb_Numbers table. Then I have used some search conditions (WHERE clauses) to get the correct result set. This result set contains all missing values in intID column. By using TOP 1, we can get the desired result.

You can do the insertion same way as I have done in method 1.

Now it is time to compare these two methods. I simply used STATISTICS IO and the EXECUTION TIME to get the evaluation.

Comparison

DECLARE @StartingTime datetime, @EndingTime datetime

Print ‘method1:’

SET STATISTICS IO ON
SET @StartingTime = getdate()

SELECT MIN(IDENTITYCOL) + IDENT_INCR('OrderHeader')
FROM OrderHeader t1
WHERE IDENTITYCOL BETWEEN IDENT_SEED('OrderHeader') AND 32766
    AND NOT EXISTS (SELECT * FROM OrderHeader t2
        WHERE t2.IDENTITYCOL = t1.IDENTITYCOL + IDENT_INCR('OrderHeader'))

SET @EndingTime = getdate()
SET STATISTICS IO OFF

SELECT DATEDIFF(ms, @StartingTime, @EndingTime ) AS ExecTimeInMS

Print ‘method2:’

SET STATISTICS IO ON
SET @StartingTime = getdate()

SELECT TOP 1 intNumber
FROM OrderHeader
    RIGHT OUTER JOIN tb_Numbers
    ON tb_Numbers.intNumber = OrderHeader.intID
WHERE intID IS NULL AND intNumber < = (SELECT MAX(intID) FROM OrderHeader)

SET @EndingTime = getdate()
SET STATISTICS IO OFF

SELECT DATEDIFF(ms, @StartingTime, @EndingTime ) AS ExecTimeInMS
Output:
Method1:

2210

Table 'OrderHeader'. Scan count 9998, logical reads 20086, physical reads 0, read-ahead reads 0.

ExecTimeInMS
------------
200

Method2:
2210

Table 'tb_Numbers'. Scan count 1, logical reads 5, physical reads 0, read-ahead reads 0.
Table 'OrderHeader'. Scan count 2, logical reads 14, physical reads 0, read-ahead reads 0.

ExecTimeInMS
------------
0

As per the output, there are 20086 logical reads and it has taken 200 ms for the first method. But in second method there are only 19 logical reads and the execution time is less considerable.

That’s why I selected to continue in my way. But there may be a side that I have not seen but you can see. So, try on this and see whether how this T-SQL solution suit for you.

I highly appreciate your comments and suggestion.

You can reach me through dinesh@dineshpriyankara.com .

Total article views: 7912 | Views in the last 30 days: 7

FORUM

Reusing Identities

IDENTITY(1,1) lost numbers

SELECT @@IDENTITY - sqljdbc driver

help me to select ROW_NUMBER in sql server 2000

Select just three octets from IP number

identity column

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

You make SSC a better place

Reusing Identities

IDENTITY(1,1) lost numbers

SELECT @@IDENTITY - sqljdbc driver

help me to select ROW_NUMBER in sql server 2000

Select just three octets from IP number

identity column

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

You make SSC a better place

Already a member? Jump in: