Lekcja 25. Usuwanie danych

W tej lekcji użyte zostaną tabele i dane które były używane w lekcji dotyczącej aktualizacji danych. Są to tabele Sales.MyCustomers, Sales.MyOrders i Sales.MyOrderDetails które są kopiami tabel z bazy TSQL2012. Poniższy kod służy do ich utworzenia:

IF OBJECT_ID('Sales.MyOrderDetails', 'U') IS NOT NULL
DROP TABLE Sales.MyOrderDetails;
IF OBJECT_ID('Sales.MyOrders', 'U') IS NOT NULL
DROP TABLE Sales.MyOrders;
IF OBJECT_ID('Sales.MyCustomers', 'U') IS NOT NULL
DROP TABLE Sales.MyCustomers;
SELECT * INTO Sales.MyCustomers FROM Sales.Customers;
ALTER TABLE Sales.MyCustomers
ADD CONSTRAINT PK_MyCustomers PRIMARY KEY(custid);
SELECT * INTO Sales.MyOrders FROM Sales.Orders;
ALTER TABLE Sales.MyOrders
ADD CONSTRAINT PK_MyOrders PRIMARY KEY(orderid);
SELECT * INTO Sales.MyOrderDetails FROM Sales.OrderDetails;
ALTER TABLE Sales.MyOrderDetails
ADD CONSTRAINT PK_MyOrderDetails PRIMARY KEY(orderid, productid);

Instrukcja DELETE

Instrukcja DELETE służy do usuwania wierszy z bazy danych. Pozwala ona na użycie predykatu filtrującego który wyznacza wiersze do usunięcia. Podstawowa forma instrukcji wygląda następująco:

DELETE FROM <table>
WHERE <predicate>;

Jeżeli nie wyznaczymy predykatu to wszystkie wiersze zostaną usunięte. Poniższy przykładowy kod usuwa produkty o identyfikatorze 11 z tabeli Sles.MyOrderDetails:

DELETE FROM Sales.MyOrderDetails
WHERE productid = 11;

Wykonanie tego kodu przez SQL Serwer zwróci komunikat informujący o tym, że zostało usuniętych 38 wierszy:

(38 row(s) affected)

Tabele użyte w tej lekcji są bardzo małe lecz w realnym środowisku zawierają dużo więcej danych. Instrukcja DELETE jest całkowicie zapisywana w logu co w rezultacie dla dużych ilości usuwanych danych może trwać jakiś czas. Takie duże operacje usuwania mogą spowodować rozrośnięcie się logu transakcji do bardzo dużych rozmiarów. Mogą powodować również powodować eskalację blokad (lock escalation), co oznacza, że SQL Serwer z blokad nałożonych na poszczególne wiersze zamienie blokowanie na takie które obejmuje całą tabelę. Taka eskalacja może spowodować całkowity brak dostępu do danych tabeli, aż do końca procesu. Aby zapobiec wyżej wymienionego problemu można dzielić duże operacje usuwania na mniejsze np za pomocą opcji TOP która ogranicza liczbę użytych wierszy w pętli. Poniżej przykład takiego rozwiązania:

WHILE 1 = 1
BEGIN
DELETE TOP (1000) FROM Sales.MyOrderDetails
WHERE productid = 12;
IF @@rowcount < 1000 BREAK;
END

Powyższa pętla używa nieskończonej pętli (WHERE 1=1 jest zawsze prawdziwe). W każdej iteracji instrukcja DELETE usuwa nie więcej niż 1000 wierszy na raz. Instrukcja IF sprawdza czy liczba użytych wierszy jest mniejsza od 1000, jeżeli tak to przerywa działanie pętli. W powyższym przykładzie jest tylko 14 wierszy więc pętla uruchomi się tylko raz.

Instrukcja TRUNCATE

Instrukcja TRUNCATE usuwa wszystkie wiersze z tabeli. W przeciwieństwie do instrukcji DELETE nie posiada opcjonalnej filtracji. Jako przykład poniższa instrukcja usuwa wszystkie wiersze z tabeli Sales.MyOrderDetails:

TRUNCATE TABLE Sales.MyOrderDetails;

Po wykonaniu tej operacji, tabela wynikowa jest pusta. Różnice pomiędzy instrukcjami DELETE i TRUNCATE:

DELETE zapisuje znacznie więcej do logu transakcyjnego. Dla instrukcji DELETE, SQL Serwer loguje aktualne dane które zostały usunięte. Dla instrukcji TRUNCATE, SQL Serwer zapisuje jedynie informacje o zwolnionych stronach w pamięci. W rezultacie instrukcja TRUNCATE jest zwykle o wiele szybsza.
Instrukcja DELETE nie resetuje właściwości IDENTITY jeżeli taka istnieje w tabeli. TRUNCATE resetuje. Aby TRUNCATE zachowało aktualną wartość dla pola z IDENTITY należy przechować aktualną wartość plus jeden za pomocą funkcji IDENT_CURRENT, i ustawić właściwość po usunięciu danych.
Instrukcja DELETE dopuszcza sytuację w której tabela docelowa posiada klucz obcy, dopóki nie istnieją wiersze powiązane z tymi które mają być usunięte. Instrukcja TRUNCATE może być wykonana tylko na tabelach bez klucza obcego.
Instrukcja DELETE może być użyta dla tabeli która jest użyta w widoku indeksowanym, instrukcja TRUNCATE nie.
Instrukcja DELETE wymaga uprawnień DELETE na tabeli docelowej, instrukcja TRUNCATE wymaga uprawnień ALTER.

Instrukcja DELETE i złączenia JOIN

T-SQL pozwala na użycie składnie DELETE bazującej na użyciu złączeń JOIN, analogicznie jak w aktualizacji danych za pomocą instrukcji UPDATE. Idea jest taka, że można usunąć wiersze z jednej tabeli bazując na informacjach wyliczonych na podstawie złączenia z innymi tabelami. Przykładowo chcemy usunąć zamówienia klientów z USA. Kraj klienta należy do taeli klienta a nie zamówienia. Aby usunąć dane z tabeli Sales.MyOrders należy ją złączyć z tabelą Sales.MyCustomers:

DELETE FROM O
FROM Sales.MyOrders AS O
INNER JOIN Sales.MyCustomers AS C
ON O.custid = C.custid
WHERE C.country = N'USA';

Klauzula FROM definiuje złączenie tabel JOIN które jest przeanalizowane w pierwszej kolejności. Klauzula WHERE filtruje wiersze które należą do klientów z USA. Na koniec wykonywana jest instrukcja DELETE używająca aliasu z klauzuli FROM który wskazuje na tabelę z której mają być usunięte rekordy. Powyższe wyrażenie generuje wynik wskazujący, że zostało usuniętych 122 wiersze:

(122 row(s) affected)

Aby osiągnąć ten sam rezultat można użyć podzapytania jak poniżej:

DELETE FROM Sales.MyOrders
WHERE EXISTS
(SELECT *
FROM Sales.MyCustomers
WHERE MyCustomers.custid = MyOrders.custid
AND MyCustomers.country = N'USA');

Oba wyrażenia są wykonywane jednakowo przez SQL Serwer, oba mają również taką samą wydajność. Należy zapamiętać, że sposób z podzapytaniem jest uważany jako standard.

Instrukcja DELETE i wyrażenia tablicowe

Analogicznie jak w instrukcji UPDATE, T-SQL zezwala na użycie wyrażeń tablicowych do usuwania wierszy. Polega to na użyciu wyrażenia tablicowego jak np CTE do zdefiniowania wierszy które chcemy usunąć i uzycia instrukcji DELETE na wyrażeniu tablicowym. Wiersze są usuwane z wyznaczonej tabeli. Przykładowo chcemy usunąć 100 najstarszych zamówień. Instrukcja DELETE zezwala na użycie opcji TOP lecz nie pozwala na sortowanie za pomocą ORDER BY. Aby to obejść należy zdefiniować wyrażenie tablicowe bazujące na zapytaniu SELECT z opcją TOP i sortowaniem ORDER BY, po czym użyć instrukcji DELETE:

WITH OldestOrders AS
(
SELECT TOP (100) *
FROM Sales.MyOrders
ORDER BY orderdate, orderid
)
DELETE FROM OldestOrders;

Wykonanie tego kodu wygeneruje wynik:

(100 row(s) affected)

Ćwiczenia

Usuwanie z użyciem złączeń JOIN.

Utwórz od nowa strukturę tabel z początku lekcji za pomocą kodu:

IF OBJECT_ID('Sales.MyOrders', 'U') IS NOT NULL
DROP TABLE Sales.MyOrders;
IF OBJECT_ID('Sales.MyCustomers', 'U') IS NOT NULL
DROP TABLE Sales.MyCustomers;
SELECT * INTO Sales.MyCustomers FROM Sales.Customers;
ALTER TABLE Sales.MyCustomers
ADD CONSTRAINT PK_MyCustomers PRIMARY KEY(custid);
SELECT * INTO Sales.MyOrders FROM Sales.Orders;
ALTER TABLE Sales.MyOrders
ADD CONSTRAINT PK_MyOrders PRIMARY KEY(orderid);
ALTER TABLE Sales.MyOrders
ADD CONSTRAINT FK_MyOrders_MyCustomers
FOREIGN KEY(custid) REFERENCES Sales.MyCustomers(custid);

Napisz instrukcję usuwającą wiersze z tabeli Sales.MyCustomers dla klientów którzy nie posiadają powiązanych zamówień w tabeli Sales.MyOrders.
```
DELETE FROM TGT
FROM Sales.MyCustomers AS TGT
LEFT OUTER JOIN Sales.Orders AS SRC
ON TGT.custid = SRC.custid
WHERE SRC.orderid IS NULL;
```

Użycie instrukcji TRUNCATE.

Użyj instrukcji TRUNCATE do wyczyszczenia tabel Sales.MyOrders i Sqles.MyCustomers.
```
TRUNCATE TABLE Sales.MyOrders;
TRUNCATE TABLE Sales.MyCustomers;
```
Drugie wyrażenie zgłosi błąd:
Msg 4712, Level 16, State 1, Line 1 Cannot truncate table 'Sales.MyCustomers' because it is being referenced by a FOREIGN KEY constraint.

Podsumowanie

Instrukcja DELETE służy do usuwania wierszy z tabeli. Opcjonalnie można ograniczyć usuwane wiersze za pomocą filtracji w klauzuli WHERE i opcji TOP.
Instrukcja TRUNCATE usuwa wszystkie wiersze z tabeli. Zaletą tej instrukcji jest minimalizacja zapisu do logu transakcyjnego dzięki czemu może być ona dużo szybsza niż instrukcja DELETE.
T-SQL pozwala na użycie instrukcji DELETE bazującej na złączeniach JOIN.
T-SQL pozwala na użycie instrukcji DELETE bazującej na wyrażeniach tablicowych jak np CTE.