Jakie są dobre praktyki programowania w TSQL?

Najlepsze praktyki programistyczne oraz konwencje kodowania

WAŻNE

Gdy podejmie się decyzje o stosowaniu danej praktyki / konwencji, ważne jest, by stosować się do tego konsekwentnie, czyli we wszystkich swoich programach / skryptach itp. Bardzo ważne jest by w zespołach kilkuosobowych wszyscy stosowali te same praktyki oraz konwencje. Dzięki temu kod oraz architektura stają się bardziej zrozumiałe oraz uporządkowane.

---

Myśl o zbiorach

Wielu programistów, zwłaszcza tych mających doświadczenie w programowaniu proceduralnym ma problem z przestawieniem się na myślenie 'relacyjne'.
Większość przypadków, w których użyto pętli, kursorów oraz innych sposobów przetwarzania danych wiersz po wierszu da się zastąpić bardziej skomplikowanymi instrukcjami (SELECT, INSERT, UPDATE), które będą operować na zbiorach i wykonają się znacznie szybciej.

---

Jawne określanie kolumn w instrukcji SELECT

Symbol gwiazdki (*) użyty wraz z instrukcją SELECT informuje SQL Server, że zapytanie powinno zwrócić wszystkie kolumny tabeli.

SELECT * FROM SomeTable

To nie jest dobra praktyka nawet gdy chcemy w odpowiedzi uzyskać wszystkie kolumny. W przypadku gdy do tabeli zostanie dodana nowa kolumna, zostanie ona dodana to zbioru danych otrzymanych w odpowiedzi na zapytanie. Jeśli dany moduł / aplikacja potrafi obsłużyć jedynie określoną ilość kolumn, wystąpią błędy podczas przetwarzania. Jeśli jawnie określimy nazwy kolumn, dane otrzymane w odpowiedzi będą takie same niezależnie od ilości dodanych kolumn.

SELECT Id, Name, UserId, TimeStamp FROM SomeTable

W przypadku, gdy w wyniku nie potrzebujemy wszystkich kolumn z tabeli, jawne ograniczenie ich w instrukcji SELECT redukuje odczyt danych z dysku i także zmniejsza ilość danych wysyłanych przez sieć.

---

Jawne określanie kolumn w instrukcji INSERT

Podobnie jak w poprzednim przypadku jawne określane kolumn uchroni nas przed błędami w przypadku dodania nowej kolumny bądź zamiany kolejności kolumn w tabeli.

Zamiast pisać:

INSERT INTO SomeTable VALUES('1, 'John', 34)

bezpieczniej będzie:

INSERT INTO SomeTable (Id, Name, UserId) VALUES('1, 'John', 34)

---

Komentarze

Pisz komentarze wszędzie tam gdzie kontekst nie jest oczywisty.

Długość komentarzy nie ma wpływu na wydajność, ale nie powinniśmy przesadzać z ich długością. Powinny zawierać informację dzięki którym zrozumienie kodu będzie prostsze. Na początku procedur, funkcji, trgierr'ów lub rozbudowanych skryptów staraj się umieszczać krótki opis działania.

---

Aliasy

Stosuj aliasy jeśli w instrukcji występują więcej niż dwa źródła danych, zwiększy to czytelność kodu

---

Czytelność kodu

Używaj zmiennych o znaczących nazwach i staraj się nimi zastapić nic nie znaczące zmienne numeryczne, dzięki temu kod stanie się czytelniejszy.

SELECT Id, Name
FROM Users
WHERE StatusId IN (2, 4) -- ciężko wywynioskować znaczenie dyfr 2 oraz 4

DECLARE @statusBanned INT = 2
DECLARE @statusWarned INT = 4
SELECT Id, Name
FROM Users
WHERE StatusId IN (@statusWarned , @statusBanned ) -- o wiele łatwiej wywnioskować czego szukamy

---

Kursory

Kursory w niektórych przypadkach są niezbędne, jednak w większości przypadków to samo można wykonać stosując nieco bardziej skomplikowaną instrukcję (SELECT, INSERT, UPDATE). Kursory zużywają dużo zasobów oraz mogą powodować zakleszczenia dlatego są mało wydajne.

---

Tabele tymczasowe

Tabele tymczasowe oznaczają większa ilość odczytu / zapisu danych z dysku. Zamiast tabel tymczasowych, powinniśmy wszędzie tam gdzie to możliwe używać zmiennych tablicowych, widoków bądź CTE.

---

Klauzula DISTINCT

DISTINICT powoduje, że SQL Server wykonuje dodatkową operacją sortowania danych w celu znalezienia duplikatów.
Gdy wiesz, że dany zbiór danych nie zawiera duplikatów nie umieszczaj klauzuli DISTINCT w instrukcji.

---

UNION vs UNION ALL

Unia w SQL to po prostu łączenie (sumowanie) dwóch zbiorów. Klauzula UNION działa jednak wolniej od UNION ALL, ponieważ usuwa z połączonych zbirów duplikaty. Jeśli wiemy, że w zbiorach nie ma duplikatów bądź jeśli nie jest ważne by dane były unikatowe powinniśmy stosować UNION ALL.

---

IF EXISTS

Jeśli istnieje potrzeba sprawdzenia czy istnieją określone przez nas dane powinno używać się klauzuli IF EXISTS.
Jeśli instrukcja natrafi na pierwszy rekord spełniający dany warunek zakańcza działania i zwraca true.

Przykład:
Następuje zliczenie wszystkich wierszy, sprawdzenie czy ilość jest większa niż 1, jeśli tak zwracane jest true.

IF ( (SELECT count(*) 
FROM USERS
WHERE Id > 20) > 0)
BEGIN
     --do something
END

W momencie napotkania pierwszego rekordu spełniającego warunek zwracane jest true

IF EXISTS (SELECT *
FROM USERS
WHERE Id > 20) 
BEGIN
     --do something
END

---

Sortowanie

W klauzuli ORDER BY stosuj jawne nazwy kolumn zamiast numerów kolumny. W przypadku zmiany ilości kolumn bądź zmiany kolejności kolumn zapytanie może generować niepoprawne wyniki.
Zamiast stosować poniższy zapis

SELECT Id, Name, TimeStamp
FROM Users
ORDER BY 1 -- źle widziane

bezpieczniej będzie napisać

SELECT Id, Name, TimeStamp
FROM Users
ORDER BY Id -- dobra praktyka

---

Klauzula LIKE

Korzystając z klauzuli LIKE podczas wyszukiwania należy starać się unikać umieszczania operatorów wieloznacznych na początku ciągu tekstowego, powoduje to bowiem skanowanie całej tabeli. Wszędzie tam gdzie to możliwe należy określić pierwszą literę a następnie stosować operator wieloznaczności w zależności od potrzeb.

SELECT Name
FROM Users
WHERE Name LIKE '%m' -- skanowanie całej tabeli 

SELECT Name
FROM Users
WHERE Name LIKE 'A%m' -- wykorzystanie indeksu

---

Średniki

Stosuj średniki do oddzielania instrukcji. Co prawda SQL Server nie wymaga tego, lecz standard SQL już tak.
Na stronie Microsoftu można znaleźć informację o tym, że brak stosowania średnika na końcu instrukcji jest sposobem przestarzałym i w przyszłych wersjach będzie uznawane to jako błąd.

---

Procedury składowane - dobre praktyki

Nazwy procedur składowanych

Nie powinno używać się przedrostka _sp jako pierwszych liter w nazwie procedury. Procedury systemowe SQL Server mają przedrostek _sp dlatego jeśli podczas działania SQL Server napotka procedurę z takim przedrostkiem, najpierw wyszuka jej w bazie master, dopiero gdy jej nie odszuka przejdzie do bazy docelowej, dlatego wywołanie może trwać dłużej.

SET NOCOUNT ON

W procedurach składowanych warto jako pierwszą instrukcję ustawić SET NOCOUNT ON. Domyślnie opcja jest wyłączona, co oznacza, że SQL Server wyśle do klienta informację o ilości przetworzonych rekordów dla każdej instrukcji wywołanej w procedurze. Skutkuje to obniżeniem wydajności (nadmiarowe obliczenia) oraz większą ilością informacji wysyłanych przez sieć.

Zwracanie wartości

Instrukcja RETURN powinna służyć jedynie do zwracania statusu wykonania operacji.
Przyjęło się, że

• 0 - operacja wykonana bez błędów
• wartość powyżej 0 - operacja wykonana niepoprawnie, cyfra może oznaczać jeden z kodów błędu stosowanych przez firmę/zespół/programistę

Do zwracania wartości pożądanych powinniśmy używać parametrów z oznaczeniem OUTPUT

.