この記事を読むのに必要な時間は約 8 分です。
テーブルを消すこととテーブルをからにする事の違い
説明
データを登録したり削除したり消したりと、いろいろ試しているなかで、
- テーブルを消したい(drop)
- テーブルの中身のデータを消したい(delete)
- テーブルの中身のデータを消したい(truncate)
と、この様な内容で行ったり来たりすることは無いでしょうか?
知らなかった人はそれぞれの意味が分からないと思いますが、知ってしまうちゃんと意識して使い分けができるようになると思います。
ぜひぜひ違いを覚えて適宜使い分けてくださいね。
図解
分かりやすくするために、図解してみます。
dropのイメージ図
drop、存在を消してしまう SQL文です。
怖ろしいですねー 怖いですねー
いいえ、怖くないです。
消してしまった方が良い事もありますし、消さなければならない事もあります。
使い分けが大事なだけです。
deleteのイメージ図
delete、何かがあった・存在自体は許してあげる そんなSQL文です。
何か優しい、イメージになりますね。
いいえ、別に優しくともなんともないです。
dropの時と一緒ですが、消してしまった方が良い事もありますし、消さなければならない事もあります。
truncateのイメージ図
delete とほぼ一緒ですね。
ただし、こちらの方がきれいさっぱりになってくれます。
deleteもdropもtruncateもどれも使い分けが必要なだけです。それぞれに上下関係などありません。
必要なコマンドを必要なシチュエーションで実行できれば良いです。
それではテスト用のテーブルに対して実際にコマンドを実行してみて結果を確認してみたいと思います。
因みにテスト用のテーブルは全く同じデータが入ったテーブルを複製して作っています。
テーブルの状態を確認するコマンドはこれです。
EXEC sp_spaceused
EXEC sp_spaceused A -- 何もしない EXEC sp_spaceused B -- delete table EXEC sp_spaceused C -- drop table EXEC sp_spaceused D -- truncate table
コマンド
delete
コマンドは以下となります。
delete [B]
drop
コマンドは以下となります。
drop table [C]
truncate
コマンドは以下となります。
truncate table [D]
それでは実行結果を確認してみましょう。
表示結果が一個減りました。
実はこの減った一個は隣のタブの メッセージ に出ています。
メッセージ 15009、レベル 16、状態 1、プロシージャ sp_spaceused、行 118 [バッチ開始行 0]
オブジェクト ‘C’ がデータベース ‘sampleDB’ に存在しないか、この操作では無効です。
しっかりエラーになっていますね。
表にまとめるとこんな感じです。
実行前 | name | rows | reserved | data | index_size | unused | 備考 |
A | 124189 | 24456 KB | 20448 KB | 8 KB | 4000 KB | 何もしないテーブル | |
B | 124189 | 24456 KB | 20448 KB | 8 KB | 4000 KB | deleteするテーブル | |
C | 124189 | 24456 KB | 20448 KB | 8 KB | 4000 KB | drop するテーブル | |
D | 124189 | 24456 KB | 20448 KB | 8 KB | 4000 KB | truncateするテーブル | |
実行前 | name | rows | reserved | data | index_size | unused | 備考 |
A | 124189 | 24456 KB | 20448 KB | 8 KB | 4000 KB | 何もしないテーブル | |
B | 0 | 1224 KB | 984 KB | 8 KB | 232 KB | deleteしたテーブル | |
C | drop したテーブル | ||||||
D | 0 | 0 KB | 0 KB | 0 KB | 0 KB | truncateしたテーブル |
解説
delete
deleteの実行結果を見てみてください。
dropとtruncateと比較して明らかに違うのが
(124189 行処理されました)
と記載されているところです。
drop、truncateは
コマンドは正常に完了しました。
と、表記されています。
つまり、deleteは一行一行に対して処理を行っているという事になります。
この、一行一行に対して処理を行っているため、
ログの記録がされており、ロールバック処理などが可能と言う事になります。
これがメリットにもなる点なのですがデメリットになる場合もあります。
3個のSQL文を実行しましたが、一番遅いのがこのdeleteです。 今回実験した結果では
全て1秒以内に終わったのですが、それでも遅さを体感できたのはこのdelete文となります。
drop
実行後の結果を確認しましたが、見事にエラーとなりました。
dropはそのテーブルの存在を消してしまうので、定義の確認なども出来なくなります。
truncate
状態を確認すると中身がすべて 0 になっていますね。
でも、定義情報は残っているため、EXEC sp_spaceusedの実行結果ではエラーにはなっていません。
如何でしょうか?それぞれの違いはわかりましたか?
それぞれ 削除する と言う事に違いはありませんが、挙動がこの様に変わってきますので
使い分けが非常に大事になります。