MS-SQL / SQL Server
「TRUNCATE」と「DELETE」でデータ削除時にIDENTITYの変化について
データを削除する時に使うのが「DELETE」と「TRUNCATE TABLE」ですよね。
「DELETE」はトランザクションログを残しながら削除していて大量のデータを削除する場合は速度が遅いですが、
特定のデータのみ指定して削除することができます。
「TRUNCATE TABLE」はトランザクションログを残さないので大量のデータでも早く削除することができますが、
特定のデータのみ指定して削除することはできません。
テーブルの全データを削除する時に使います。
では、テーブルの全データを削除した時にテーブルに指定したIDENTITYの変化について
テスト用のSQLを実行しながら説明したいと思います。
下記のSQLを実行してテーブルを作成しましょう。
-- #### テーブル作成
CREATE TABLE dbo.TempData
(
Idx int IDENTITY(1,1) NOT NULL
, Data1 varchar(10) NOT NULL
)
「Idx」列にIdentityを設定して基本値が「1」で、「1」ずつ増加するように設定しています。
では、テータを登録して結果を確認してみましょう。
-- #### データ登録
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_1')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_2')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_3')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_4')
-- #### データ確認
SELECT * FROM dbo.TempData
実行結果
Idx Data1
----------- ----------
1 aaaa_1
2 aaaa_2
3 aaaa_3
4 aaaa_4
予想通り「Idx」列には「1」から順番に登録されました。
では、テーブルの全データを「DELETE」を使って削除してから再度データを登録してみましょう。
-- #### データ削除
DELETE dbo.TempData
-- #### データ登録
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_1')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_2')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_3')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_4')
-- #### データ確認
SELECT * FROM dbo.TempData
実行結果
Idx Data1
----------- ----------
5 aaaa_1
6 aaaa_2
7 aaaa_3
8 aaaa_4
そうすると「Idx」は「5」から増加しています。
「DELETE」の場合、データのみ削除していてIDENTITY値は初期化しないのです。
削除する前のIDENTITYの最大値「4」を記憶していて
次のデータを登録する時に「5」として登録するのです。
では、「TRUNCATE TABLE」の場合はどう違うのかテストしてみましょう。
まず、テーブルを作り直しましょう。
-- #### テーブル削除
DROP TABLE dbo.TempData
-- #### テーブル作成
CREATE TABLE dbo.TempData
(
Idx int IDENTITY(1,1) NOT NULL
, Data1 varchar(10) NOT NULL
)
全く同じテーブルですが、一応削除してから再度作りました。
では、データを登録してみましょう。
-- #### データ登録
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_1')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_2')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_3')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_4')
-- #### データ確認
SELECT * FROM dbo.TempData
実行結果
Idx Data1
----------- ----------
1 aaaa_1
2 aaaa_2
3 aaaa_3
4 aaaa_4
どうですか？
ここまでは同じです。
ここからが違うのです。
「TRUNCATE TABLE」を使ってテーブルの全データを削除してまたデータを登録してみましょう。
-- #### データ削除
TRUNCATE TABLE dbo.TempData
-- #### データ登録
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_1')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_2')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_3')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_4')
-- #### データ確認
SELECT * FROM dbo.TempData
実行結果
Idx Data1
----------- ----------
1 aaaa_1
2 aaaa_2
3 aaaa_3
4 aaaa_4
どうですか？
「DELETE」で削除してデータを登録した時は Idx値が「5」から登録されましたが、
「TRUNCATE TABLE」の場合は「1」から登録されています。
そうです。
「TRUNCATE TABLE」の場合は「IDENTITY」値まで初期化するのです。
うん？？
では、「TRUNCATE TABLE」を使っても「IDENTITY」値を維持させたい！！！と言う方もいますよね。
こういう場合は、まず「TRUNCATE TABLE」する前に下記のSQLを利用してIDENTITY値を確認します。
DBCC CHECKIDENT('dbo.TempData', NORESEED)
実行すると。。。
ID 情報を調べています。現在の ID 値 '4'、現在の列値 '4'。
DBCC の実行が完了しました。DBCC がエラー メッセージを出力した場合は、システム管理者に相談してください。
こういう結果が表示されます。
現在値は「4」!
では、再度「TRUNCATE TABLE」してデータを削除しましょう。
-- #### データ削除
TRUNCATE TABLE dbo.TempData
削除したら下記のSQLを利用して IDENTITY値を設定します。
元々IDENTITY値が「4」だったので、下記のSQLでは「5」を設定します。
-- #### Identity初期化及び手動指定
DBCC CHECKIDENT('dbo.TempData', RESEED, 5)
正常に実行されたら下記のSQLを利用してデータを登録してみましょう。
-- #### データ登録
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_1')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_2')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_3')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_4')
-- #### データ確認
SELECT * FROM dbo.TempData
実行結果
Idx Data1
----------- ----------
5 aaaa_1
6 aaaa_2
7 aaaa_3
8 aaaa_4
この通り、IDENTITY値が「5」から登録されています。
ここで。。。
あぁ～～ そうなんだ。。。っと言う人もいれば、
あれ？？おかしいぞ！！！っと言う人もいると思います。
どこがおかしいかと言いますと！
IDENTITY値を初期化する時に使うSQLで知られている下記のSQL!!
-- #### Identity初期化及び手動指定
DBCC CHECKIDENT('dbo.TempData', RESEED, 5)
先ほど「IDENTITY」値を「5」に設定する為に使いました！！
今まで知られているのは。。。
上記のSQLのように「5」に設定した場合、
次から登録されるデータには「1」が増加された「6」から登録されるのが
正しいと思っていますよね。
だけど、上記のテストでは「5」に設定したら「5」からデータが登録されました。
では、現在の「dbo.TempData」テーブルにはIDENTITYの最大値が「8」です。
ここでIDENTITY値を「10」に変更してみましょう。
-- #### Identity初期化及び手動指定
DBCC CHECKIDENT('dbo.TempData', RESEED, 10)
正常に実行されたらまた下記のSQLを利用してデータを登録してみましょう。
-- #### データ登録
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_1')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_2')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_3')
INSERT INTO dbo.TempData (Data1) VALUES ('aaaa_4')
-- #### データ確認
SELECT * FROM dbo.TempData
実行結果
Idx Data1
----------- ----------
5 aaaa_1
6 aaaa_2
7 aaaa_3
8 aaaa_4
11 aaaa_1
12 aaaa_2
13 aaaa_3
14 aaaa_4
あれ？？
ここでは設定した「10」からではなく、
「11」から登録されています。
では、まとめます！！！
テーブルを作成(CREATE TABLE)してまだ1件も登録してない場合、
TRUNCATE TABLEをしてテーブルを初期化した場合、
「DBCC CHECKIDENT」を利用して「IDENTITY」値を設定した場合、
設定した番号からIDENTITY値が登録されます。
だが、1件でもデータが登録されたことがあるテーブルの場合、
「DBCC CHECKIDENT」を利用して「IDENTITY」値を設定した時は
設定したIDENTITY値に増加値「1」を増加した値が次のデータが登録される時に登録されます。
少し差があるので、知っておけばいい情報だと思います。