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))

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

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)

DECLARE

@A1 int
SET @A1 = 1

WHILE (@A1 <> 30000)

BEGIN
    INSERT INTO tb_Numbers (intNumber)

VALUES (@A1)
    SET @A1 = @A1 +

1
END

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

.