identity 속성인 컬럼의 경우 일반적으로 데이터를 지워도 지운 값을 비워두고 계속 사용하게 되는데 이 기사는 그 빈값을 재사용하는 방법에 대한 내용이다.
출처 : http://www.sqlservercentral.com/columnists/dPriyankara/reuseidentities.asp
---------------------------------------------------------------
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 .
[방문히트이벤트]1000히트를 잡아라!
