Reusing Identities

,

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

.

Rate

Share

Share

Rate