リレーショナルデータベースでは、さまざまな形式でデータを格納するためのテーブルを作成します。 SQL Server は、各データ型に関連する値を保持する行と列の形式でデータを格納します。 SQL テーブルを設計するとき、integer、float、decimal、varchar、bit などのデータ型を定義します。 例えば、顧客データを格納するテーブルでは、顧客名、電子メール、住所、州、国などのフィールドを持つことができます。 SQLテーブルに対して様々なSQLコマンドが実行され、以下のカテゴリーに分類されます：
- データ定義言語（DDL）。 これらのコマンドは、データベース内のデータベースオブジェクトを作成および変更するために使用されます。
- Create: オブジェクトを作成します
- Alter: オブジェクトを変更する
- Drop: オブジェクトを削除する
- Truncate: テーブルからすべてのデータを削除します
- Data Manipulation Language (DML)。 これらのコマンドは、データベース内のデータを挿入、取得、変更、削除、および更新します。
- Select: 単一または複数のテーブルからデータを取得します
- Insert。 テーブルに新しいデータを追加する
- Update：既存のデータを修正する
- Delete：削除する。 テーブルの既存のレコードを削除する
- Data Control Language (DCL)。 これらのコマンドは、データベース内の権利または許可制御に関連付けられます。
- Grant: ユーザーに権限を割り当てる
- Revoke: ユーザーからパーミッションを取り消す
- Transaction Control Language (TCL)。 これらのコマンドは、データベース内のトランザクションを制御します。
- コミット。 クエリによって行われた変更を保存します
- Rollback。 明示的または暗黙的なトランザクションをトランザクションの開始点まで、またはトランザクション内のセーブポイントまでロールバックします
- トランザクションを保存します。 トランザクション内のセーブポイントまたはマーカーを設定します
SQL テーブルに顧客注文データが格納されているとします。 このテーブルに継続的にデータを挿入し続けると、テーブルには何百万ものレコードが含まれることになり、アプリケーションにパフォーマンスの問題を引き起こすことになります。 また、インデックスのメンテナンスに非常に時間がかかるようになるかもしれません。 多くの場合、3年以上前の注文を保持する必要はありません。 このような場合、テーブルからこれらのレコードを削除することができます。
SQL テーブルからデータを削除するには、2 つの方法があります。
- SQL delete 文を使用する
- Truncate
これらの SQL コマンドの違いについては、後で見てみましょう。 まずはSQL delete文について調べてみましょう。
条件なしのSQL delete文
データ操作言語（DML）文の中で、SQL delete文はテーブルから行を削除します。 特定の行を削除することも、すべての行を削除することもできます。 基本的なdelete文は引数を必要としません。
以下のスクリプトを使用して、Orders SQLテーブルを作成しましょう。 このテーブルには3つのカラム , と .
Create Table Orders( OrderID int,ProductName varchar(50),ProductQuantity int)Insert a few records in this table.
Insert into Orders values (1,'ABC books',10),(2,'XYZ',100),(3,'SQL book',50)Now, suppose we want to delete the table data.このテーブルには3つのカラムがあります。 データを削除するには、deleteステートメントを使ってテーブル名を指定します。 どちらのSQL文も同じです。 オプション）キーワードからテーブル名を指定するか、deleteの後に直接テーブル名を指定します。
Delete OrdersGoDelete from OrdersGOA SQL delete statement with filtered data
これらのSQL deleteステートメントは、テーブルのデータをすべて削除します。 通常、SQLテーブルからすべての行を削除することはありません。 特定の行を削除するには、deleteステートメントでwhere句を追加することができます。 WHERE句はフィルタ条件を含み、最終的にどの行を削除するかを決定します。
例えば、注文ID 1を削除したいとします。 WHERE句を追加すると、SQL Serverはまず該当行をチェックし、それらの特定の行を削除します。 たとえば、orders テーブルから ordered 1 を削除しました。 もう一度この文を実行すると、Where句の条件を満たす行は見つかりませんでした。 この場合、影響を受けた0行を返します。
SQL delete文とTOP句
TOP文も同様に行を削除するために使用することができます。 例えば、以下のクエリはOrdersテーブルから上位100行を削除します。
Delete top (100)from Orders「ORDER BY」を指定していないため、ランダムに行を選択して削除します。 Order by句を利用して、データをソートし、上位行を削除することができます。
Delete from Orders where In(Select top 100 FROM Ordersorder by Desc)別のテーブルに基づいて行を削除する
時には、別のテーブルに基づいて行を削除する必要があります。
- Table lookup
これらの行を削除するには、テーブル検索法または SQL ジョインを使用できます。 たとえば、次の条件を満たすテーブルから行を削除したいとします。
それは.テーブルで対応する行を持っている必要があります
以下のクエリを見てください。 SQL Server はまず select 文を満たす行を取得し、次に SQL delete 文を使用してテーブルからこれらの行を削除します。
Delete Orders where orderid in(Select orderidfrom Customer)- SQL Join
あるいは、これらのテーブル間で SQL ジョインを使用して行を削除することも可能です。 以下のクエリでは、[ テーブル ] とテーブルを結合しています。 SQL結合は常にテーブル間の共通カラムで動作します。
DELETE OrdersFROM Orders oINNER JOIN Customer c ON o.orderid=c.orderid上記の削除ステートメントを理解するために、実際の実行プランを表示してみましょう。
実行プランに従って、両方のテーブル上でテーブルスキャンを実行し、一致するデータを取得して、Ordersテーブルからそれらを削除します。
- Common table expression (CTE)
SQL テーブルから行を削除するために、同様に Common table expression (CTE) を使用することができます。 まず、削除したい行を見つけるために CTE を定義します。
次に、CTE を SQL テーブルの Orders と結合して行を削除します。
WITH cteOrders AS(SELECT OrderIDFROM CustomerWHERE CustomerID = 1 )DELETE OrdersFROM cteOrders spINNER JOIN dbo.Orders o ON o.orderid = sp.orderid;ID 範囲への影響
SQL Server の ID 列は列に対してユニークで連続した価値を発生させます。 これらは主に、SQL テーブル内の行を一意に識別するために使用されます。 主キー列は、SQL Server のクラスタ化インデックスにも適しています。
以下のスクリプトでは、あるテーブルがあります。 このテーブルにはID列があります。
Create Table Employee(id int identity(1,1),varchar(50))このテーブルに50レコードを挿入し、ID列のID値を生成しました。
Declare @id int=1While(@id<=50)BEGINInsert into Employee() values('Test'+CONVERT(VARCHAR,@ID))Set @id=@id+1ENDSQLテーブルから数行削除しても、その後の値のID値がリセットされるわけではありません。 たとえば、ID値20から25を持つ数行を削除してみましょう。
Delete from employeewhere id between 20 and 25ここで、テーブルレコードを表示します。
Select * from employee where id>15ID値の範囲のギャップを表示します。
SQL delete文とトランザクションログ
SQL deleteは各行の削除をトランザクションログで記録しています。 SQL テーブルから何百万ものレコードを削除する必要があるとします。 1 つのトランザクションで大量のレコードを削除すると、ログ ファイルが指数関数的に増大し、データベースも使用できなくなる可能性があるため、このようなことはしたくありません。 途中でトランザクションをキャンセルすると、delete 文をロールバックするのに何時間もかかることがあります。
この場合、常に行を小さな塊で削除し、それらの塊を定期的にコミットする必要があります。 たとえば、一度に 10,000 行のバッチを削除し、それをコミットして次のバッチに移行することができます。 SQL Server がチャンクをコミットするとき、トランザクション ログの成長を制御することができます。
ベスト プラクティス
- データを削除する前に常にバックアップを実行するべきです。
- デフォルトでは、SQL Server は暗黙トランザクションを使用し、ユーザーに尋ねずにレコードをコミットします。 ベスト プラクティスとして、Begin Transaction を使用して明示的なトランザクションを開始する必要があります。 これにより、トランザクションをコミットまたはロールバックするための制御が可能になります。 また、データベースが完全復旧モードの場合、トランザクション ログのバックアップを頻繁に実行する必要があります。
- トランザクション ログの過剰使用を避けるために、データを小さな塊で削除することをお勧めします。 また、他の SQL トランザクションのブロックも回避できます。
- ユーザーがデータを削除できないように、アクセス権を制限する必要があります。 許可されたユーザーのみがSQLテーブルからデータを削除するアクセス権を持つべきです。
- あなたは、where句を使用してdelete文を実行したいです。 これは、SQL テーブルからフィルタリングされたデータを削除します。 アプリケーションで頻繁にデータを削除する必要がある場合、ID 値を定期的にリセットすることをお勧めします。 さもなければ、ID 値の枯渇問題に直面する可能性があります。
- テーブルを空にしたい場合は、truncate ステートメントを使用することをお勧めします。 truncate ステートメントは、テーブルからすべてのデータを削除し、トランザクション ログを最小限に抑え、ID 値の範囲をリセットし、テーブルのすべてのページをすぐに割り当てるので、SQL delete ステートメントよりも高速です。
- テーブルに外部キー制約（親子関係）を使用している場合、子行から、そして親表から行を削除する必要があります。 親行から行を削除する場合、cascade on delete オプションを使用すると、子テーブルから行を自動的に削除することもできます。 記事を参照するとよいでしょう。 SQL Server の外部キーにおける削除カスケードおよび更新カスケードを参照してください。
- 行の削除に top ステートメントを使用すると、SQL Server は行をランダムに削除してしまいます。 常に、対応する Order by および Group by 句で top 句を使用する必要があります。
- Delete 文は、参照テーブルの排他的インテント ロックを取得します。 NOLOCKヒントを使用してデータを読み取ることができます。
- SQL delete ステートメントのデフォルトのロック動作を上書きするためにテーブル ヒントを使用することは避けるべきで、経験豊富な DBA および開発者のみが使用すべきです。 常に少量のバッチでデータを削除し、本番インスタンスからデータを削除するときは慎重に進めることが重要です。 ダウンタイムや将来のパフォーマンスへの影響を避けるために、最小限の時間でデータを回復するバックアップ戦略を持つことは必須です。