The Best Ways to Use SQL Delete Statement in a SQL Table

W relacyjnych bazach danych, tworzymy tabele do przechowywania danych w różnych formatach. SQL Server przechowuje dane w formacie wierszy i kolumn, które zawierają wartości związane z każdym typem danych. Kiedy projektujemy tabele SQL, definiujemy typy danych, takie jak integer, float, decimal, varchar i bit. Na przykład, tabela przechowująca dane klientów może zawierać pola takie jak: nazwa klienta, email, adres, stan, kraj itd. Różne polecenia SQL są wykonywane na tabeli SQL i mogą być podzielone na następujące kategorie:

• Język definicji danych (DDL): Polecenia te są używane do tworzenia i modyfikowania obiektów bazy danych w bazie danych.
  • Create: Tworzy obiekty
  • Alter: Modyfikuje obiekty
  • Drop: Usuwa obiekty
  • Truncate: Usuwa wszystkie dane z tabeli
• Język manipulacji danymi (Data Manipulation Language, DML): Polecenia te wstawiają, pobierają, modyfikują, usuwają i aktualizują dane w bazie danych.
  • Select: Pobiera dane z pojedynczej lub wielu tabel
  • Insert: Dodaje nowe dane w tabeli
  • Update: Modyfikuje istniejące dane
  • Delete: Usuwa istniejące rekordy w tabeli

• Język sterowania danymi (DCL): Polecenia te są związane z kontrolkami praw lub uprawnień w bazie danych.
  • Grant: Przypisuje uprawnienia do użytkownika
  • Revoke: Cofa uprawnienia użytkownikowi
• Transaction Control Language (TCL): Polecenia te sterują transakcjami w bazie danych.
  • Commit: Zapisuje zmiany wprowadzone przez zapytanie
  • Rollback: Cofa transakcję jawną lub niejawną do początku transakcji lub do punktu zapisu wewnątrz transakcji
  • Zapisz transakcje: Ustawia punkt zapisu lub znacznik wewnątrz transakcji

Załóżmy, że masz dane zamówienia klienta przechowywane w tabeli SQL. Jeśli wstawiałbyś dane do tej tabeli w sposób ciągły, tabela mogłaby zawierać miliony rekordów, co spowodowałoby problemy z wydajnością w twoich aplikacjach. Konserwacja indeksu może również stać się bardzo czasochłonna. Często nie trzeba przechowywać zamówień starszych niż trzy lata. W takich przypadkach, możesz usunąć te rekordy z tabeli. Pozwoliłoby to zaoszczędzić miejsce w pamięci, a także zmniejszyć wysiłek związany z konserwacją.

Dane z tabeli SQL można usunąć na dwa sposoby:

• Używając polecenia SQL delete
• Używając polecenia truncate

Sprawdzimy różnicę między tymi poleceniami SQL później. Poznajmy najpierw instrukcję SQL delete.

Konstrukcja SQL delete bez żadnych warunków

W języku manipulacji danymi (DML), instrukcja SQL delete usuwa wiersze z tabeli. Możesz usunąć określony wiersz lub wszystkie wiersze. Podstawowa instrukcja delete nie wymaga żadnych argumentów.

Utwórzmy tabelę SQL Orders za pomocą poniższego skryptu. Ta tabela ma trzy kolumny , i .

Create Table Orders ( OrderID int, ProductName varchar(50), ProductQuantity int )

Wstaw kilka rekordów do tej tabeli.

Insert into Orders values (1,'ABC books',10), (2,'XYZ',100), (3,'SQL book',50)

Teraz, załóżmy, że chcemy usunąć dane z tabeli. Możesz określić nazwę tabeli, aby usunąć dane za pomocą polecenia delete. Obie instrukcje SQL są takie same. Możemy określić nazwę tabeli z (opcjonalnego) słowa kluczowego lub określić nazwę tabeli bezpośrednio po delete.

Delete Orders Go Delete from Orders GO

Konstrukcja SQL delete z przefiltrowanymi danymi

Te instrukcje SQL delete usuwają wszystkie dane tabeli. Zazwyczaj, nie usuwamy wszystkich wierszy z tabeli SQL. Aby usunąć konkretny wiersz, możemy dodać klauzulę where do instrukcji delete. Klauzula where zawiera kryteria filtrowania i ostatecznie określa wiersz(y) do usunięcia.

Na przykład, załóżmy, że chcemy usunąć id zamówienia 1. Po dodaniu klauzuli where, SQL Server najpierw sprawdza odpowiednie wiersze i usuwa te konkretne wiersze.

Delete Orders where orderid=1

Jeśli warunek klauzuli where jest fałszywy, nie usuwa żadnych wierszy. Na przykład, usunęliśmy zamówione 1 z tabeli zamówienia. Jeśli wykonamy instrukcję ponownie, nie znajdzie ona żadnych wierszy, które spełniałyby warunek klauzuli where. W tym przypadku zwraca 0 wierszy, których to dotyczy.

Konstrukcja polecenia usunięcia i klauzula TOP

Do usuwania wierszy można również użyć instrukcji TOP. Na przykład, poniższe zapytanie usuwa 100 najlepszych wierszy z tabeli Orders.

Delete top (100)  from Orders

Ponieważ nie określiliśmy żadnego 'ORDER BY’, zapytanie wybiera losowe wiersze i usuwa je. Możemy wykorzystać klauzulę Order by do posortowania danych i usunięcia górnych wierszy. W poniższym zapytaniu, sortuje wiersze w kolejności malejącej, a następnie usuwa je z tabeli.

Delete from Orders where In ( Select top 100 FROM Orders order by Desc )

Usuwanie wierszy na podstawie innej tabeli

Czasami musimy usunąć wiersze na podstawie innej tabeli. Ta tabela może istnieć w tej samej bazie danych lub nie.

• Table lookup

Możemy użyć metody table lookup lub SQL join do usunięcia tych wierszy. Na przykład, chcemy usunąć wiersze z tabeli, które spełniają następujący warunek:

Powinny one mieć odpowiadające im wiersze w tabeli ..

Patrząc na poniższe zapytanie, mamy tutaj instrukcję select w klauzuli where instrukcji delete. SQL Server najpierw pobiera wiersze, które spełniają warunki polecenia select, a następnie usuwa te wiersze z tabeli za pomocą polecenia SQL delete.

Delete Orders where orderid in (Select orderid  from Customer)

• SQL Join

Alternatywnie, możemy użyć SQL joins pomiędzy tymi tabelami i usunąć wiersze. W poniższym zapytaniu, dołączamy tabele ] do tabeli. Złączenie SQL zawsze działa na wspólnej kolumnie pomiędzy tabelami. Mamy kolumnę, która łączy obie tabele razem.

DELETE Orders FROM Orders o INNER JOIN Customer c ON o.orderid=c.orderid

Aby zrozumieć powyższe polecenie usunięcia, spójrzmy na rzeczywisty plan wykonania.

Zgodnie z planem wykonania, wykonuje skanowanie tabeli na obu tabelach, pobiera pasujące dane i usuwa je z tabeli Zamówienia.

• Wspólne wyrażenie tabelaryczne (CTE)

Możemy użyć wspólnego wyrażenia tabelarycznego (CTE) do usunięcia wierszy z tabeli SQL. Najpierw definiujemy CTE, aby znaleźć wiersz, który chcemy usunąć.

Następnie dołączamy CTE do tabeli SQL Orders i usuwamy wiersze.

WITH cteOrders AS (SELECT OrderID FROM Customer WHERE CustomerID = 1 ) DELETE Orders FROM cteOrders sp INNER JOIN dbo.Orders o ON o.orderid = sp.orderid;

Wpływ na zakres tożsamości

Kolumny tożsamości w SQL Server generują unikalne, sekwencyjne wartości dla twojej kolumny. Są one używane przede wszystkim do unikalnej identyfikacji wiersza w tabeli SQL. Kolumna klucza podstawowego jest również dobrym wyborem dla indeksu klastrowego w SQL Server.

W poniższym skrypcie mamy tabelę. Tabela ta ma kolumnę tożsamości id.

Create Table Employee ( id int identity(1,1),  varchar(50) )

Wstawiliśmy 50 rekordów do tej tabeli, które wygenerowały wartości tożsamości dla kolumny id.

Declare @id int=1 While(@id<=50) BEGIN Insert into Employee() values('Test'+CONVERT(VARCHAR,@ID)) Set @id=@id+1 END

Jeśli usuniemy kilka wierszy z tabeli SQL, nie zresetuje to wartości tożsamości dla kolejnych wartości. Na przykład usuńmy kilka wierszy, które mają wartości tożsamości od 20 do 25.

Delete from employee where id between 20 and 25

Teraz przejrzyj rekordy tabeli.

Select * from employee where id>15

Wykazuje ona lukę w zakresie wartości tożsamości.

Konstrukcja polecenia delete SQL i dziennik transakcji

Konstrukcja polecenia delete SQL rejestruje każde usunięcie wiersza w dzienniku transakcji. Załóżmy, że musisz usunąć miliony rekordów z tabeli SQL. Nie chcesz usuwać dużej liczby rekordów w pojedynczej transakcji, ponieważ może to spowodować, że Twój plik dziennika transakcji będzie rósł wykładniczo, a Twoja baza danych może być niedostępna. Jeśli anulujesz transakcję w środku, może to zająć godziny, aby cofnąć instrukcję usuwania.

W tym przypadku powinieneś zawsze usuwać wiersze w małych fragmentach i regularnie je zatwierdzać. Na przykład, możesz usunąć partię 10 000 wierszy naraz, zatwierdzić ją i przejść do następnej partii. Gdy SQL Server zatwierdzi chunk, można kontrolować wzrost dziennika transakcji.

Najlepsze praktyki

• Powinno się zawsze wykonać kopię zapasową przed usunięciem danych.
• Domyślnie SQL Server używa transakcji niejawnych i zatwierdza rekordy bez pytania użytkownika. Jako najlepszą praktykę, powinieneś rozpocząć jawną transakcję za pomocą Begin Transaction. Daje ci to kontrolę nad popełnieniem lub cofnięciem transakcji. Powinieneś również uruchamiać częste kopie zapasowe dziennika transakcji, jeśli twoja baza danych jest w trybie pełnego odzyskiwania.
• Chcesz usuwać dane w małych kawałkach, aby uniknąć nadmiernego użycia dziennika transakcji. Pozwala to również uniknąć blokowania innych transakcji SQL.
• Powinieneś ograniczyć uprawnienia, aby użytkownicy nie mogli usuwać danych. Tylko autoryzowani użytkownicy powinni mieć dostęp do usuwania danych z tabeli SQL.
• Chcesz uruchomić instrukcję delete z klauzulą where. Usuwa ona przefiltrowane dane z tabeli SQL. Jeśli Twoja aplikacja wymaga częstego usuwania danych, dobrym pomysłem jest okresowe resetowanie wartości tożsamości. W przeciwnym razie możesz napotkać problemy związane z wyczerpaniem wartości tożsamości.
• W przypadku, gdy chcesz opróżnić tabelę, zaleca się użycie instrukcji truncate. Polecenie truncate usuwa wszystkie dane z tabeli, używa minimalnego logowania transakcji, resetuje zakres wartości tożsamości i jest szybsze niż polecenie SQL delete, ponieważ natychmiastowo deallocates wszystkie strony dla tabeli.
• W przypadku korzystania z ograniczeń klucza obcego (relacja rodzic-dziecko) dla swoich tabel, powinieneś usunąć wiersz z wiersza dziecka, a następnie z tabeli nadrzędnej. Jeśli usuwasz wiersz z wiersza nadrzędnego, możesz również użyć opcji kaskady przy usuwaniu, aby automatycznie usunąć wiersz z tabeli dziecka. Możesz odnieść się do artykułu: Kaskada usuwania i kaskada aktualizacji w SQL Server klucz obcy, aby uzyskać dalsze spostrzeżenia.
• Jeśli używasz klauzuli top do usuwania wierszy, SQL Server usuwa wiersze losowo. Powinieneś zawsze używać klauzuli top z odpowiednią klauzulą Order by i Group by.
• A delete statement nabywa wyłączną blokadę intencyjną na tabeli referencyjnej; dlatego w tym czasie żadne inne transakcje nie mogą modyfikować danych. Możesz użyć podpowiedzi NOLOCK, aby odczytać dane.
• Należy unikać używania podpowiedzi table hint w celu nadpisania domyślnego zachowania blokady instrukcji SQL delete; powinna być ona używana tylko przez doświadczonych DBA i programistów.

Ważne uwagi

Istnieje wiele korzyści z używania instrukcji SQL delete do usuwania danych z tabeli SQL, ale jak widać, wymaga to metodycznego podejścia. Ważne jest, aby zawsze usuwać dane małymi partiami i postępować ostrożnie przy usuwaniu danych z instancji produkcyjnej. Posiadanie strategii tworzenia kopii zapasowych w celu odzyskania danych w minimalnym czasie jest koniecznością, aby uniknąć przestoju lub przyszłego wpływu na wydajność.