Thank this author by sharing:
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
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.
of all, let’s create a table called
‘'OrderHeader'’ that has three columns. Note that the first column
intID is identity type column.
OBJECT_ID('OrderHeader') IS NOT NULL DROP
TABLE OrderHeaderGOCREATE TABLE OrderHeader(intID int
IDENTITY(1,1) PRIMARY KEY,strOrderNumber varchar(10) NOT
you run now a simple select query against the table, you will see some
gaps between the column intID values.
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.
see the method 2.
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.
do the insertion same way as I have done in method 1.
it is time to compare these two methods. I simply used STATISTICS IO and
the EXECUTION TIME to get the evaluation.
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.
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.
highly appreciate your comments and suggestion.
IDENTITY(1,1) lost numbers
SELECT @@IDENTITY. SQLException has raised The statement did not return a result set
help me to select ROW_NUMBER in sql server 2000
Select just three octets from IP number
As a member of SQLServerCentral, you get free access to loads of fresh content: thousands
of articles and SQL scripts, a library of free eBooks, a weekly database news roundup,
a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals
that makes it such a success.