What is the best way to Find and reuse deleted identities |
By: Dinesh Priyankara |
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. |
OBJECT_ID('OrderHeader') IS NOT NULL TABLE OrderHeader IDENTITY(1,1) PRIMARY KEY, NULL, |
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 CONVERT(varchar(3), @A), -- Adding something for Order Number CONVERT(varchar(3), @A)) -- Adding something for Description |
OK. Let’s delete some randomly selected records from the table. |
DELETE OrderHeader WHERE intID = 9212 2210 |
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 + IDENT_INCR('OrderHeader') BETWEEN IDENT_SEED('OrderHeader') AND 32766 t2 = t1.IDENTITYCOL + IDENT_INCR('OrderHeader')) @NextIdentityValue AS NextIdentityValue |
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 OrderHeader + CONVERT(varchar(3), @A), CONVERT(varchar(3), @A)) 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 tb_Numbers 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 VALUES (@A1) 1 |
Now let’s query the gaps (or first deleted identity value) in the OrderHeader table |
SELECT TOP 1 @NextIdentityValue = intNumber tb_Numbers OrderHeader.intID MAX(intID) FROM OrderHeader) NextIdentityValue 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 ‘method1:’ getdate() IDENT_INCR('OrderHeader') BETWEEN IDENT_SEED('OrderHeader') AND 32766 t2 = t1.IDENTITYCOL + IDENT_INCR('OrderHeader')) getdate() @StartingTime, @EndingTime ) AS ExecTimeInMS ‘method2:’ getdate() tb_Numbers OrderHeader.intID MAX(intID) FROM OrderHeader) STATISTICS IO OFF AS ExecTimeInMS 'OrderHeader'. Scan count 9998, logical reads 20086, physical reads 0, read-ahead reads 0. logical reads 5, physical reads 0, read-ahead reads 0. 'OrderHeader'. Scan count 2, logical reads 14, physical reads 0, read-ahead reads 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 . |
Adding Years, Months and Days
Adding Years, Months and Days to Years, Months and Days, with Bonus UK Public Holiday calculation.
2017-03-14
3,139 reads