T-SQL Hare Formatting

Formatowanie kodu jest ważne. Estetyczny i czytelny styl kodowania ułatwia zrozumienie znaczenia kodu, a co za tym idzie ułatwia jego rozbudowę oraz utrzymanie. Programiści poświęcają dużo więcej czasu na czytanie kodu niż na jego pisanie. Wprowadzenie jednolitych zasad formatowania kodu dla całego zespołu developerów jest opłacalną i korzystną praktyką. Jednolite formatowanie kodu przyśpiesza pracę zespołową, ponieważ członkowie zespołu mogą szybciej zorientować się w kodzie napisanym przez innych członków zespołu.

Poniższy poradnik jest propozycją sposobów formatowania kodu w T-SQL. Być może taki styl formatowania będzie odpowiedni dla Ciebie lub dla Twojego teamu.

Przewodnik

Przyjęto, że dobrą praktyką będzie unikanie polskich nazw w kodzie. Dobrą praktyką jest również do każdej tabeli dodanie standardowego przedrostka, który może być skojarzony z np. z nazwą firmy.

W niniejszym przykładzie jako przedrostka można użyć pochodzącego od tytułu artykułu przedrostka "hf" (hare formatting). Dlatego też tabela z użytkownikami może zostać nazwana np.: dbo.hfUsers, gdzie:

• dbo to schemat
• hf to skrót od “hare formatting”
• users to właściwe słowo opisujące tabelę.

Przyjęto, że spacji należy używać zawsze pomiędzy wszystkimi operatorami.
Nie powinno stosować się spacji po nawiasie otwierającym i przed nawiasem zamykającym.

Klauzule

W konwencji Hare Formatting klauzule pisane powinny być małymi literami.

-- good
select * from dbo.hfCustomers c;

-- bad
SELECT * FROM dbo.hfCustomers c;

-- bad
Select * From dbo.hfCustomers c;

Aliasy

Zawsze należy korzystać z aliasów. Konwencja nie korzysta ze słowa kluczowego "as" aby nadawać aliasy. Dopuszczalne jest użycie aliasu przed gwiazdką.

-- good
select * from dbo.hfCustomers c;

-- good
select c.* from dbo.hfCustomers c;

-- bad
select * from dbo.hfCustomers as c;

Tabele i Schematy

Nazwy tabel i schematów powinny być zapisywane w konwencji camelCase. Konwencja ta polega na zapisywaniu słów bez spacji, gdzie każde kolejne słowo z wyjątkiem pierwszego rozpoczyna się wielką literą.

Nazewnictwo tabel powinno opierać się na liczbie mnogiej, kolumny natomiast powinny być nazywane w liczbie pojedyńczej np. dla tabeli "dbo.hfCustomers" kluczem głównym może być kolumna "hfCustomerId".

--good
select * from dbo.hfReceiptHeaders soh;

--bad
select * from Dbo.HfReceiptHeaders soh;

--bad
select * from dbo.HfReceiptHeaders soh;

Znaczące nazwy obiektów

Dobrą praktyką jest nadawanie znaczących i jednoznacznych nazw dla obiektów, tak aby przyszli, jak i obecni odbiorcy w przyszłości mogli łatwo zrozumieć w jakim celu obiekt został stworzony.

-- bad
declare @x table ( /*no meaningful name*/
  hfDocumentId int,
  amountNet decimal(18,6),
  amountGross decimal(18,6)
);

-- good
declare @invoices table (
  hfDocumentId int,
  amountNet decimal(18,6),
  amountGross decimal(18,6)
);

-- bad
with cte as ( /*no meaningful name*/
  select
    soh.hfSalesOrderHeaderId,
    soh.amountNet,
    soh.amountGross
  from dbo.hfSalesOrderHeaders soh
    inner join dbo.hfCustomers c on c.hfCustomerId = soh.hfCustomerId
  where c.hfCustomerName = 'Apple'
)
select
  *
from cte c;

--good
with appleSalesOrderHeadersCte as (
  select
    soh.hfSalesOrderHeaderId,
    soh.amountNet,
    soh.amountGross
  from dbo.hfSalesOrderHeaders soh
    inner join dbo.hfCustomers c on c.hfCustomerId = soh.hfCustomerId
  where c.hfCustomerName = 'Apple'
)
select
  *
from appleSalesOrderHeadersCte c;

Zapytania w jednej linii kodu

Jedynym przypadkiem, aby zapisać query w jednej linii kodu jest przypadek, gdy w zapytaniu wykorzystano jedynie gwiazdkę, lub wyciągnięto tylko jedną kolumnę.
Taka decyzja jest podyktowana tym, że w przypadku edycji kodu, programiści często wykorzystują funkcję multikursora w swoich edytorach kodu. Wykorzystanie multikursora jest trudniejsze, gdy nazwy kolumn w zapytaniu znajdują się w jednej linii.

--  good
select * from dbo.hfUsers u;

-- bad
select u.name, u.email from dbo.hfUsers u;

Zapytania w wielu liniach kodu

W przypadku występowania więcej niż jednej kolumny w zapytaniu, powinno zostać one zapisane w kilku liniach.

Przecinki

Przecinki oddzielające definicje kolumn powinny zostać umiejscowione na końcu linii.

select
  u.hfUserId,
  u.name,
  u.email
from dbo.hfUsers u
where u.name = 'John';

Złączenia

Dla każdej tabeli należy użyć aliasu. Złączenia (instrukcje join) powinny być zapisywane przy pomocy pełnych nazw i poiwnny zostać zapisane z jednym wcięciem w stosunku do klauzul from w swoich zapytaniach. Warunki złączeń powinny być w tej samej lini w której znajduje się instrukcja złączenia. W warunkach złączenia należy najpierw zapisywać tabele, z których następuje odwołanie, a następnie tabele, do których odwołanie nastepuje.

-- good
select
  c.*
from dbo.hfCustomers c
  inner join dbo.hfCustomersAddresses ca on ca.hfCustomerId = c.hfCustomerId
  inner join dbo.hfAddresses a on a.hfAddressId = ca.hfAddressId;

-- bad
select
  c.*
from dbo.hfCustomers c
inner join dbo.hfCustomersAddresses ca on ca.hfCustomerId = c.hfCustomerId
inner join dbo.hfAddresses on hfAddressId = ca.hfAddressId; /*There should be an indent before the inner join, there is no alias in join condition*/

-- bad
select
  c.*
from dbo.hfCustomers c
  inner join dbo.hfCustomersAddresses ca on c.hfCustomerId = ca.hfCustomerId
  inner join dbo.hfAddresses a on ca.hfAddressId = a.hfAddressId; /*the order of the tables aliases in the joins are incorrect*/

-- bad
select
  c.*
from dbo.hfCustomers c
  join dbo.hfCustomersAddresses ca /*The full join name should be used here*/
  on c.hfCustomerId = ca.hfCustomerId /*Join conditions should be written on the same line as the join*/
  inner join dbo.hfAddresses a
  on ca.hfAddressId = a.hfAddressId;

Podzapytania, Wcięcia, Nawiasy

Wcięcia w kodzie realizowane są przy pomocy dwóch spacji. Główne klauzule powinny być wyrównane do lewej strony. Podzapytania powinny być wcięte dwiema spacjami w prawo względem fragmentu nadrzędnego.

Nawiasy otwierające powinny być umiejscowione w tej samej linii, w której umiejscowiono klauzulę, funkcję lub element nadrzędny.

Nawiasy zamykające powinny być umiejscowione na tej samej głębokości, na której umiejscowiony został element, przy którym zastosowano nawias otwierający.

-- good
select
  *
from (
  select
    soh.hfCustomerId,
    avg(soh.amountNet) avgAmountNet
  from dbo.hfSalesOrderHeaders soh
  group by soh.hfCustomerId
) soh
  inner join dbo.hfCustomers e on e.hfCustomerId = soh.hfCustomerId
where soh.avgAmountNet <= 5000;

--bad
select
  *
from (
  select
    soh.hfCustomerId,
    avg(soh.amountNet) avgAmountNet
  from dbo.hfSalesOrderHeaders soh
  group by soh.hfCustomerId) soh /* the parenthesis should be on the next line */
  inner join dbo.hfCustomers e on e.hfCustomerId = soh.hfCustomerId
where soh.avgAmountNet <= 5000;

-- bad
select
  *
from (
/* unnecessary empty line */
  select
    soh.hfCustomerId,
    avg(soh.amountNet) avgAmountNet
  from dbo.hfSalesOrderHeaders soh
  group by soh.hfCustomerId
/* unnecessary empty line */
) soh
  inner join dbo.hfCustomers e on e.hfCustomerId = soh.hfCustomerId
where soh.avgAmountNet <= 5000;

Apostrofy zamiast cudzysłowów, użycie prefiksu "N"

• Przy definiowaniu wartości tekstowych preferowane są apostrofy.
• W celach optymalizacyjnych, przy porównywaniu wartości tekstowej do kolumny typu nvarchar należy zawsze stosować prefiks "N".
• Cudzysłów jest dozwolony przy nadawaniu aliasów dla kolumn, gdzie nazwa kolumny koniecznie musi składać się z kilku wyrazów oddzielonych spacją.
• Należy podkreślić, że nazwy kolumn składające się z kilku wyrazów oddzielonych spacją mogą być używane, jednak tylko w przypadku, gdy jest to niezbędne.

-- bad
select
  *
from dbo.hfUsers u
where u.name = N"Elsa"; /*Incorrect syntax, "N" doesn't work with "*/

-- good
select
  *
from dbo.hfUsers u
where u.name = N'Elsa'; /*used "N" because e.name is nvarchar*/

Operator nierówności

Ze względu na to, że operator != jest powszechny w większości języków programowania, preferowane jest używanie != zamiast <>.

select
  *
from dbo.hfUsers u
where u.email != 'admin@admin.com';

Zapis daty i godziny

Datę zawsze należy zapisywać w jednoznacznym dla komputera formacie. Należy zauważyć, że w zależnośći od konfiguracji data zapisana w formacie tekstowym może być interpretowana na różne sposoby. Istnieje kilka formatów daty, które są jednoznaczne dla komputera, poniżej kilka przykładów:

1. "YYYY-MM-DD" (np. 2006-06-10) - format powszechnie używany zgodny z normą ISO 8601. Pierwsze cztery cyfry oznaczają rok, następnie dwie cyfry oznaczają miesiąc, a kolejne dwie cyfry oznaczają dzień.
2. "YYYYMMDD" (np. 20060610) - format podobny do formatu pierwszego, ale bez myślników. Nadal jest jednoznaczny dla komputera, a używanie samych cyfr ułatwia ewentualną konwersję typów np. date to int.
3. "YYYY-MM-DDThh:mm:ss" (np. 2006-06-10T12:34:56) - format datetime, czyli przechowujący oprócz daty informacje o czasie. Znak "T" wykorzystany został jako separator daty od czasu. Po separatorze podano godziny, minuty i sekundy w formacie hh:mm:ss.
4. "YYYYMMDD hh:mm:ss" (np. 20060610 12:34:56) - format podobny do poprzedniego, zamiast "T" zastosowano spację jako separator.

Standard Data Hare Formatting poleca korzystać w kodzie z formatów 1 i 4, czyli najbardziej intuicyjnych dla człowieka.

Indentacje w klauzuli where

select
  *
from dbo.hfUsers u
where u.hfUserTypeId between 1 and 3
  and u.registrationDate <= '2022-01-05';

Warunki or oraz nawiasy w klazuzuli where

Dla zapytań z wieloma warunkami, gdzie znajduje się operator "lub" (or) rezultat zostanie zwrócony, gdy którykolwiek z warunków "or" zostanie spełniony. Aby upewnić się, że kod działa poprawnie, każdy warunek "or" dotyczący tego samego kontekstu należy umieścić w nawiasach. Trzeba również zwrócić uwagę na kolejność wykonywania się warunków w predykacie.

W poniższym kodzie intencją było zwrócenie każdego pracownika, który ma na imię Mark lub Jack oraz zarabia więcej niż 3000. Kolejność wykonywania operatorów logicznych w T-SQL spowoduje, że najpierw zostanie wykonany warunek "and" a dopiero później warunek "or". Rezultatem będzie pokazanie wszystkich pracowników o imieniu "Jack", którzy zarabiają więcej niż 3000 a dodatkowo wszystkich pracowników o imieniu "Mark" niezależnie od zarobków.

-- bad
select
  *
from dbo.hfEmployees e
where
  e.name = N'Mark' or
  e.name = N'Jack'
  and e.grossSalary >= 3000;

-- good
select
  *
from dbo.hfEmployees e
where (
  e.name = N'Mark' or
  e.name = N'Jack'
)
  and e.grossSalary >= 3000;

operator IN

Celem ułatwienia obsługi multikursora w edytorach kodu, każdy warunek w klazuli in należy zapisać w osobnej linii. Listę wartości należy wciąć na dwie spacje względem linii poprzedzającej.

-- good
select
  *
from dbo.hfUsers u
where u.name in (
  N'Adam',
  N'Elon',
  N'Wayne',
  N'Evo'
);

grupowanie, sortowanie

• Nazwy kolumn zawsze w należy podawać jawnie.
• Nie należy sortować i grupować wyników używając numerów kolumn. W przypadku grupowania/sortowania przy pomocy numerów kolumn, można bardzo łatwo przeoczyć ewentualną zmianę kolejności w klauzuli select, co zwróci nieprawidłowe wyniki.
• Po funkcji agregującej należy podać nazwę zwracanej kolumny.
• Jeśli intencją jest sortowanie rosnące, slowo kluczowe "asc" można pominąć.

-- good
select
  e.name,
  count(*) namesCnt
from dbo.hfEmployees e
group by e.name
order by e.name;

-- good
select
  e.name,
  count(*) namesCnt
from dbo.hfEmployees e
group by e.name
order by e.name asc; /*"asc" is optional*/

-- bad
select
  e.name,
  count(*) /*there is no column name*/
from dbo.hfEmployees e
group by e.name
order by 1;  /*column name is preferred*/;

instrukcje warunkowe iif() oraz case when

Instrukcja "case" powinna być wcięta jeden raz w stosunku do klazuli w której się znajduje. Każda instrukcja "when" powinna znajdować się w osobnej linii i powinna być wcięta jeden raz w stosunku do instrukcji "case".

Słowo kluczowe "then" powinno znajdować się w tej samej linii, w której znajduje się instrukcja "when", jednak w przypadku długich warunków logicznych dopuszczalne jest przesunięcie słowa kluczowego "then" do kolejnej linii. W takim przypadku "then" powinno się znaleźć jedno wcięcie głębiej w stosunku do "when".

W języku T-SQL w przypadu podwójnego warunku preferowane jest przedkładanie funkcji "iif()" nad case when, aczkolwiek użycie instrukcji "case when" jest w takim przypadku również dopuszczalne. Należy zauważyć że wcięcie nawiasu zamykającego funkcji "iif()" jest na tej samej wysokości na której rozpoczyna się instrukcja.

-- good
select
  case
    when u.hfUserTypeId = 1 or u.hfUserTypeId = 2 or u.hfUserTypeId = 3
      then N'Email confirmed'
    else N'Email not confirmed'
  end emailConifrmationText
from hfUsers u;

-- good
select
  case
    when u.hfUserTypeId in (1, 2, 3) then N'Email confirmed'
    else N'Email not confirmed'
  end emailConifrmationText
from hfUsers u;

-- good
select
  case
    when u.hfUserId = 1 then N'Standard'
    when u.hfUserId = 2 then N'Premium'
    when u.hfUserId = 3 then N'Gold'
    else N'Email not confirmed'
  end userTypeName
 from dbo.hfUsers u;

-- good
select
  iif(
    u.hfUserTypeId not in (1, 2, 3),
    N'Email not confirmed',
    N'Email confirmed'
  ) emailConifrmationText
from dbo.hfUsers u;

Funkcje okienkowe

Element agregujący oraz "over", wraz z nawiasem otwierającym, powinny znajdować się w tej samej linii. Nawias zamykający powinien zostać umiejscowiony na tej samej głębokości, na której znajduje się funkcja agregująca. Instrukcje występujące wewnątrz funkcji okna powinny zostać wcięte jeden raz w stosunku do elementu nadrzednego.

Ze względu na to, jak bardzo skomplikowane i długie potrafią być funkcje okna, nieopisane elementy formatowania funkcji okien należy dostosować zgodnie ze swoimi preferencjami, jednocześnie dbając o czytelność dla innych programistów.

-- good
select
  soh.hfCustomerId,
  soh.hfOrderDate,
  sum(soh.amountNet) over (
    partition by soh.hfCustomerId order by soh.hfOrderDate desc
    rows between unbounded preceding and current row
  ) customerCumulativeSum
from dbo.hfSalesOrderHeaders soh
where soh.hfOrderDate between '2023-06-01' and '2023-06-30';

-- good
select
  soh.hfCustomerId,
  soh.hfOrderDate,
  sum(soh.amountNet) over (
    partition by soh.hfCustomerId
    order by soh.hfOrderDate desc
    rows between unbounded preceding and current row
  ) customerCumulativeSum
from dbo.hfSalesOrderHeaders soh
where soh.hfOrderDate between '2023-06-01' and '2023-06-30';

-- bad
select
  soh.hfCustomerId,
  soh.hfOrderDate,
  sum(soh.amountNet) over (
  partition by soh.hfCustomerId
  order by soh.hfOrderDate desc
  rows between unbounded preceding and current row
  ) customerCumulativeSum
from dbo.hfSalesOrderHeaders soh
where soh.hfOrderDate between '2023-06-01' and '2023-06-30';

-- bad
select
  soh.hfCustomerId,
  soh.hfOrderDate,
  sum(soh.amountNet) over (partition by soh.hfCustomerId
  order by soh.hfOrderDate desc rows between unbounded preceding and current row
  ) customerCumulativeSum
from dbo.hfSalesOrderHeaders soh
where soh.hfOrderDate between '2023-06-01' and '2023-06-30';