SQL Tips

 

[SQL Server] 「DELETE」と「TRUNCATE」でデータ削除時にIDENTITYの変化について

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」を増加した値が次のデータが登録される時に登録されます。

少し差があるので、知っておけばいい情報だと思います。
このエントリーをはてなブックマークに追加
2014-12-07 17:09:52   4558

コメント

[SQL Server] Transact-SQLを使ってSQL Server ユーザーを作成する方法

-.日付:2015-01-16   カテゴリ : -   閲覧数:1452

[SQL Server] SET IDENTITY_INSERT で IDENTITY を設定した列に値を登録する方法

-.日付:2014-11-30   カテゴリ : -   閲覧数:3315

[SQL Server] 月末の日付(月末日)を求めたい / EOMONTH関数 / 月末日を取得

-.日付:2014-11-23   カテゴリ : -   閲覧数:12132

[SQL Server] LIKEを利用して検索する時パフォーマンスに注意事項

-.日付:2014-11-22   カテゴリ : -   閲覧数:4944

[SQL Server] IPアドレスを数字に変更する方法

-.日付:2014-10-26   カテゴリ : -   閲覧数:1717

[SQL Server] GeoIPを利用してIPアドレスの国情報を取得する方法

-.日付:2014-10-25   カテゴリ : -   閲覧数:6209

Copyright © 2015 INNOYA.COM All rights reserved. RSS