Lekcja 4. Typy danych i funkcje wbudowane języka T-SQL

Definiując kolumny w tabelach, parametry w procedurach i funkcjach oraz zmienne w partiach kodu T-SQL trzeba wybrać dla nich typ danych. Typ danych ogranicza dane jakich możemy użyć, dodatkowo enkapsuluje pewne zachowania odnośnie danych oraz ich użycia a także udostępnia zestaw operatorów jakich możemy użyć. Ponieważ typ danych jest podstawowym elementem danych to wybór odpowiedniego typu ma ogromny wpływ na aplikacje w wielu różnych warstwach. Dlatego wybór typu danych powinien być traktowany z dużą uwagą.

Wybór odpowiedniego typu danych

Wybór typu danych dla atrybutów jest prawdopodobnie jedną z najważniejszych decyzji dotyczących danych. SQL Serwer wspiera wiele typów danych z różnych kategorii. Oto lista kategorii i typów danych:

numeryczne dokładne (INT, NUMERIC)
numeryczne przybliżone (FLOAT, REAL)
ciągi znaków (CHAR, VARCHAR)
ciągi znaków UNICODE (NCHAR, NVARCHAR)
ciągi binarne (BINARY, VARBINARY)
data i czas (DATE, TIME, DATETIME2, SMALLDATETIME, DATETIME, DATETIMEOFFSET)

Jak widać istnieje wiele opcji typów danych co czyni wybór odpowiedniego trudnym zadaniem. Wybierając typ powinno się kierować pewnymi zasadami. Jedną z największych zalet modelu relacyjnego jest jest znaczenie, jakie daje do egzekwowania integralności danych w ramach tego samego modelu, na wielu poziomach. Ważnym aspektem przy wyborze odpowiedniego typu dla danych jest, aby pamiętać, że typ jest ograniczeniem. Oznacza to, że ma on pewien zakres obsługiwanych wartości i nie pozwala na użycie wartości z poza tego zakresu. Przykładowo typ DATE pozwala na użycie tylko poprawnych dat. Próba wstawienia danych które nie są datą zostanie odrzucona. jeżeli chcesz użyć atrybutu który ma reprezentować datę jak np datę urodzenia i użyjesz typu INT lub CHAR to nie skorzystasz z wbudowanej walidacji dat. Podobnie jak typ danych jest ograniczeniem to NOT NULL jest nim również. Jeżeli atrybut nie może zawierać NULL to należy wymusić ograniczenie NOT NULL. Kolejną rzeczą jest to, że nie chcemy zastanawiać się nad formatowaniem wartości danego typu. Czasami ludzie używają ciągu znaków do przechowywania dat ponieważ myślą, że przechowują datę w konkretnym formacie. Formatowanie wartości tak aby była odpowiednio wyświetlana jest obowiązkiem aplikacji. Typ jest właściwością wartości przechowywanej w bazie danych i wewnętrzny format nie powinien być zmartwieniem użytkownika. Aspekt ten ma do czynienia z ważną zasadą w modelu relacyjnym zwaną "fizyczną niezależnością danych". Typ danych enkapsuluje zachowania. Używając nieodpowiedniego typu tracimy zawarte w nim zachowania, operatory i funkcje które wspiera. Jako prosty przykład operator (+) oznacza dodawanie dla liczb lecz dla ciągów znaków oznacza złączanie. jeżeli wybierzemy nieodpowiedni typ dla naszych danych musimy konwertować typ danych. Kolejnym ważnym aspektem przy wyborze typu danych jest rozmiar. Często jednym z najważniejszych czynników mających wpływ na wydajność wykonywania zapytania jest udział I/O. Zapytanie które mniej odczytuje jest zwykle szybsze. W dzisiejszych czasach mamy do czynienia z tabelami posiadającymi miliony a nawet miliardy wierszy. Kiedy pomnożymy rozmiar typu przez ilość wierszy to liczba szybko może stać się bardzo duża. Przykładowo mając atrybut przechowujący wynik testu z zakresu od 0 do 100 użycie typu INT to przesada. Używa on 4 bajty na wartość a typ TINYINT używa tylko 1 bajt i jest bardziej odpowiedni dla tego zadania. Podobnie jest z datami, ludzie mają tendencję do używania DATETIME który używa 8 bajtów. Jeżeli zależy nam jedynie na dacie to powinniśmy użyć typu DATE który zużywa tylko 3 bajty. Dla wartośći przechowujących datę i czas powinniśmy rozważyć DATETIME2 i SMALLDATETIME. DATETIME2 w zależności od precyzji zajmuje od 6 do 8 bajtów i zapewnia szerszy zakres dat i lepszą regulowaną precyzję. SMALLDATETIME używa 4 bajty. Krótko mówiąc, należy użyć najmniejszego typu, który nadaje się do potrzeb. Należy uważać z typami numerycznymi przybliżonymi FLOAT i REAL. Są to typy zmiennoprzecinkowe przybliżone, dlatego nie wszystkie wartości w zakresie mogą być w nich bezpośrednio reprezentowane. Zaletą tych typów jest to, że mogą reprezentować bardzo małe i bardzo duże wartości. typy te są również oszczędne (4 bajty dla REAL i 8 bajtów dla FLOAT). Nie należy używać ich do przechowywania precyzyjnych wartości. Poniżej znajduje się przykład błędnej konwersji FLOAT do NUMERIC:

DECLARE @f AS FLOAT = '29545428.022495';
SELECT CAST(@f AS NUMERIC(28, 14)) AS value;

Wynik:

Value --------------------------------------- 29545428.02249500200000

Kolejnym ważnym czynnikiem jest wybór pomiędzy ciągami znaków stałymi (CHAR, NCHAR, BINARY) i dynamicznymi (VARCHAR, NVARCHAR, VARBINARY). typy o stałej długości używają zawsze takiej samej ilości w pamięci np CHAR(30) używa zawsze pamięci dla 30 znaków, nieważne jak długa jest wartość. Oznacza to, że aktualizacja wartości nie wymaga poszerzenia fizycznego a zatem przesunięcie danych nie jest wymagane. Dlatego dla często aktualizowanych atrybutów powinno się uwzględniać raczej typy o stałej szerokości. Typy o zmiennej szerokości zawsze zajmują tyle ile wpisana do nich wartość plus kilka bajtów z informacją o przesunięciu. Więc dla bardzo różnych rozmiarów ciągów używając typów o zmiennej szerokości można zaoszczędzić dużo miejsca na dysku. Typy o zmiennej szerokości powinny być preferowane w przypadkach kiedy wydajność odczytów jest priorytetem. Z ciągami znaków istnieje jeszcze pytanie czy użyć typów regularnych (CHAR, VARCHAR) lub typów UNICODE (NCHAR, NVARCHAR). Pierwsze z nich używają 1 bajtu na znak i wspierają tylko znaki jednego języka (bazują na właściwości collation). Typy UNICODE używają natomiast 2 bajty na znak (oprócz skompresowanych) i wspierają wiele języków. Większe wymagania w zakresie przechowywania danych UNICODE są łagodzone począwszy od SQL Server 2008 R2 z kompresją UNICODE . Definiując atrybuty prezentujące tę samą rzecz w różnych tabelach - szczególnie gdy będą używane później w celu łączenia tabel (jak klucze główne i obce) - ważną rzeczą jest aby typy były zgodne. W przeciwnym wypadku SQL Serwer w czasie porównywania będzie dokonywał niejawną konwersję co może mieć negatywny wpływ na wydajność oraz spowodować nie użycie indeksów. Używając literału konkretnego typu należy być pewnym, że jest on w odpowiednim formacie. Przykładowo literał regularnego ciągu znaków jest ograniczony pojedynczymi cudzysłowami np: 'abc'. Natomiast literały UNICODE są ograniczone pojedynczymi cudzysłowami oraz poprzedzony znakiem N np: N'abc'. Jeżeli wyrażenie zawiera elementy o różnych typach to SQL Serwer musi stosować niejawną konwersję co może mieć negatywny wpływ na wydajność. Czasami interpretacja literału może nie być tym co wydaje się intuicyjnie. Aby wymusić konkretny typ literałów można zastosować bezpośrednią konwersję z użyciem funkcji CAST, CONVERT, PARSE, TRY_CAST, TRY_CONCERT lub TRY_PARSE. Przykładowo literał 1 jest zawsze rozpatrywany przez SQL Serwer jako typ INT. Jeżeli chcemy aby był rozpatrywany np jako BIT to należy użyć konwersji np. CAST(1 AS BIT). Podobnie 4000000000 jest rozpatrywane jako NUMERIC a nie BIGINT. Różnica pomiędzy funkcjami z przedrostkiem TRY i bez jest taka, że te bez przedrostka zgłaszają błąd jeżeli wartość nie może być skonwertowana natomiast te z przedrostkiem zwracają w takim wypadku NULL. Przykładowo poniższy kod zwróci błąd:

SELECT CAST('abc' AS INT);

Natomiast poniższy kod zwróci NULL:

SELECT TRY_CAST('abc' AS INT);

W funkcji CAST używamy wyrażenia i typu na jaki ma być wykonana konwersja. W funkcji CONVERT występuje jeszcze trzeci argument reprezentujący styl konwersji który jest wspierany przez niektóre konwersje jak np ciągi znaków konwertowane na datę i czas. Przykładowo CONVERT(DATE, '1/2/2012', 101) konwertuje ciąg znaków na datę używając stylu 101 który oznacza standard daty Stanów Zjednoczonych. Używając finkcji PARSE można wybrać jedną z obsługiwanych przez .NET Framework kultur. Przykładowo PARSE('1/2/2012' AS DATE USING 'en-US') konwertuje literał na datę używaną w kulturze United States English. W wyrażeniach które zawierają operacje na różnych typach, SQL Serwer zazwyczaj konwertuje takie które mają mniejszy priorytet. Rozważmy przykładowo wyrażenie 1 + '1'. Pierwszy operand jest typu INT a drugi typu VARCHAR. Na stronie online books można zobaczyć, że INT poprzedza VARCHAR co oznacza, że SQL Serwer niejawnie skonwertuje wartość '1' typu VARCHAR na wartość 1 typu INT i dlatego wynikiem działania będzie 2 a nie '11'. Aby mieć pewność wyniku należy używać jawnej konwersji. Jeżeli wszystkie operandy wyrażenia są tego samego typu to wynik również będzie tego samego typu. Przykładowo wynikiem działania 5/2 w T-SQL będzie wartość 2 typu INT a nie 2.5 typu NUMERIC. Jeżeli chcemy otrzymać wyniku typ NUMERIC to musimy skonwertować operandy typu INT na NUMERIC np: CAST(col1 AS NUMERIC(12, 2)) / CAST(col2 AS NUMERIC(12, 2)).

Wybór typu dla klucza

Kiedy definiujemy klucz w tabeli na istniejącym atrybucie pochodzącym z aplikacji to nie ma potrzeby wybierania typu, ponieważ atrybut już istnieje. Inaczej jest kiedy definiujemy klucz zastępczy, taki który ma pełnić tylko funkcję klucza. Dla takiego atrybutu musimy wybrać odpowiedni typ danych oraz mechanizm generowania wartości klucza. W rzeczywistości można usłyszeć różne opinie co do tego który mechanizm jest najlepszy, niektóre z nich oparte są na teorii a inne na doświadczeniach. Jednakże różne systemy z różnymi obciążeniami mogą mieć inne optymalne rozwiązania. Czasami priorytetem może być wydajność zapisu a innym razem odczytu. Jedne rozwiązania pozwalają na szybszy zapis a wolniejszy odczyt a inne rozwiązania w sposób odwrotny. Typowymi opcjami wyboru kluczy zastępczych są:

IDENTITY - właściwość kolumny generująca automatycznie wartość klucza typu numerycznego ze skalą 0, mianowicie typy całkowite (TINYIT, SMALLINT, INT, BIGINT) lub NUMERIC/DECIMAL ze skalą 0.
SEQUENCE - obiekt bazy danych dzięki któremu można pobrać nową wartość sekwencyjną. Tak jak IDENTITY używa wszystkich typów numerycznych ze skalą 0. W przeciwieństwie do IDENTITY nie jest powiązany z konkretną kolumną lecz jest niezależnym obiektem bazy danych. Można pobrać nową wartość sekwencji przed jej użyciem.
Niesekwencyjny GUID - niesekwencyjny, globalny i unikalny identyfikator typu UNIQUEIDENTIFIER. Jego nową wartość można wygenerować za pomocą funkcji NEWID(). Identyfikatory GUID gwarantują unikalność w przestrzeni i czasie.
Sekwencyjny GUID - sekwencyjny identyfikator można wygenerować za pomocą funkcji NEWSEQUENTIALID().

Wybierając typ dla kluczy zastępczych należy zwrócić uwagę na sposób ich generowania oraz rozmiar typów danych jakie używają. Im większy jest typ danych tym więcej pamięci zajmuje i wolniejsze są odczyty. Rozwiązanie używające typu INT potrzebuje 4 bajty dla każdej wartości klucza, BIGINT zajmuje 8 bajtów a UNIQUEIDENTIFIER aż 16 bajtów. Wymagania dotyczące składowania klucza zastępczego mogą mieć kaskadowy wpływ jeżeli klucz klastrowany jest zdefiniowany na tej samej kolumnie. Kolumny indeksu klastrowanego są użyte we wszystkich indeksach nieklastrowanych w celu zlokalizowania wiersza w tabeli. Jeżeli zdefiniujemy indeks klastrowany na kolumnie X i nieklastrowane indeksy - pierwszy na kolumnie A, drugi na B i trzeci na C to indeksy nieklastrowane będą wewnętrznie zawierały kolumny (A,X), (B,X) i (C,X). W sprawie wyboru kluczy sekwencyjnych i niesekwencyjnych istnieje kilka aspektów do rozważenia. Używając klucza sekwencyjnego wszystkie wiersze idą zgodnie w prawą stronę indeksu. Kiedy strona jest pełna to SQL Serwer alokuje nową stronę i ją wypełnia. W wyniku otrzymujemy małą fragmentację indeksu co sprawia, że odczyt jest wydajniejszy. Ponadto wstawianie może być również szybsze o ile odbywa się w jednej sesji a dane zapisywane są na jednym dysku twardym lub niewielkiej ilości dysków. Jednakże z podsystemami pamięci masowej klasy wyższej sytuacja może być inna. Kiedy czytamy dane w wielu sesjach w końcu zaistnieje sytuacja w której powstaną zatrzaski (ang. latch) na stronach poziomu liści. To gardło uniemożliwia wykorzystanie pełnej przepustowości podsystemu pamięci. Kiedy stosujemy klucze niesekwencyjne jak NEWID lub własne rozwiązanie to gdy próbujemy wstawić dane do pełnej strony to SQL Serwer wykonuje podział strony, alokuje nową stronę do której trafia połowa wierszy ze strony oryginalnej. Podział strony ma swój koszt oraz powoduje fragmentacje indeksu. Fragmentacja indeksu ma negatywny wpływ na wydajność odczytów. Jednakże w przypadku wydajności wstawiania w systemach posiadających wiele biegunów i dane są ładowane z wielu sesji to losowa kolejność może być lepszym rozwiązaniem. Dzieje się tak dlatego, że nie ma słabych punktów po prawej stronie indeksu. Dobry przykład takiej strategii jest opisany na stronie http://blog.kejser.org/2011/10/05/boosting-insert-speed-by-generating-scalable-keys/. Podziały i fragmentacja indeksów może być złagodzona poprzez okresową przebudowę indeksów.

Funkcje daty i czasu

T-SQL udostępnia zestaw funkcji pozwalających na manipulację datą i czasem. W tej sekcji omówione zostaną najważniejsze funkcje. Pełna lista i opis funkcji można znaleźć na stronie Books Online for SQL Server 2012.

Aktualny czas i data

Ważną kategorią funkcji daty i czasu są funkcje zwracające aktualną datę i czas. Do funkcji w tej kategorii należą:

GETDATE - funkcja specyficzna dla T-SQL zwracająca aktualną datę i czas instancji SQL Serwera do której jesteśmy podłączeni. Zwracana typ to DATETIME.
CURRENT_TIMESTAMP - jest to standard języka SQL, zwraca taki sam wynik jak funkcja GETDATE.
SYSDATETIME - zwraca aktualną datę i czas instancji SQL Serwera. Zwracany typ to DATETIME2.
SYSDATETIMEOFFSET - zwraca aktualną datę i czas instancji SQL Serwera. Zwracany typ to DATETIMEOFFSET.
GETUTCDATE - zwraca aktualną datę i czas w formacie UTC. Zwracana typ to DATETIME.
SYSUTCDATETIME - zwraca aktualną datę i czas w formacie UTC. Zwracana typ to DATETIME2.

Nie istnieją funkcje wbudowane które zwracają samą aktualną datę lub sam aktualny czas. Aby pobrać te informacje należy rzutować funkcję SYSDATETIME na typ DATE lub TIME np: CAST(SYSDATETIME() AS DATE).

Części daty i czasu

Ta sekcja zawiera funkcje które wyodrębniają części z daty i czasu lub budują datę i czas z części. Do funkcji wyodrębniających części daty i czasu należą:

DATEPART - funkcja wyodrębnia część daty lub czasu (np: rok, minuta itp) i zwraca ją jako wynik typu INT. Przykładowo DATEPART(month, '20120212') zwraca w wyniku wartość 2.
YEAR - skrót funkcji DATEPART zwracający rok.
MONTH - skrót funkcji DATEPART zwracający miesiąc.
DAY - skrót funkcji DATEPART zwracający dzień.
DATENAME - funkcja podobna do DATEPART lecz jako wynik zwraca nazwę części daty jako ciąg znaków. Wynik tej funkcji zależy od języka. Przykładowo dla języka sesji us_english wyrażenie DATENAME(month, '20120212') zwróci jako wynik 'February', natomiast dla języka sesji italian wynikiem będzie 'febbraio'.

T-SQL posiada funkcje konstruujące datę i czas z części numerycznych dla każdego typu danych daty i czasu. Są to:

DATEFROMPARTS - konstruuje datę o typie DATE z części numerycznych np: DATEFROMPARTS(2012, 02, 12).
DATETIME2FROMPARTS - konstruuje datę o typie DATETIME2 z części numerycznych.
DATETIMEFROMPARTS - konstruuje datę o typie DATETIME z części numerycznych.
DATETIMEOFFSETFROMPARTS - konstruuje datę o typie DATETIMEOFFSET z części numerycznych.
SMALLDATETIMEFROMPARTS - konstruuje datę o typie SMALLDATETIME z części numerycznych.
TIMEFROMPARTS - konstruuje datę o typie TIME z części numerycznych.

Funkcja EOMONTH oblicza ostatni dzień miesiąca dla daty którą wstawimy na wejściu. Zakładając, że dzisiejsza data to 5 luty 2014 to EOMONTH(SYSDATETIME()) da w wyniku '2014-02-28'.

Dodawanie i różnica

T-SQL posiada funkcje służące do dodawania i obliczania różnicy między datami i czasami. Są to funkcje:

DATEADD - funkcja ta umożliwia dodanie pewnej liczby jednostek do konkretnej części czasu lub daty. Przykładowo wyrażenie DATEADD(year, 1, '20120212') dodaje rok do daty 12 luty 2012.
DATEDIFF - funkcja ta zwraca różnicę między datami w żądanych częściach daty lub czasu. Przykładowo wyrażenie DATEDIFF(day,'20110212', '20120212') oblicza ilość dni pomiędzy datami 12 luty 2011 i 12 luty 2012 i zwraca w wyniku 365.

Offset

T-SQL obsługuje dwie funkcje daty i czasu z przesunięciem. Są to funkcje:

SWITCHOFFSET - funkcja zwraca wejściową wartość DATETIMEOFFSET w żądanym przesunięciu. Przykładowo wyrażenie SWITCHOFFSET(SYSDATETIMEOFFSET(), '-08:00') zwróci datę i czas przesunięty o 9 godzin od polskiego ponieważ czas polski ma przesunięcie '+01:00'.
TODATETIMEOFFSET - funkcja służy do konstrukcji wartości DATETIMEOFFSET z dwuch parametrów wejściowych. Pierwszy parametr to data i czas bez przesunięcia a drugi to przesunięcie.

Poniższy kod demonstruje użycie obu funkcji:

SELECT
SWITCHOFFSET('20130212 14:00:00.0000000 -08:00', '-05:00') AS [SWITCHOFFSET],
TODATETIMEOFFSET('20130212 14:00:00.0000000', '-08:00') AS [TODATETIMEOFFSET];

Wynik:

SWITCHOFFSET TODATETIMEOFFSET ---------------------------------- ---------------------------------- 2013-02-12 17:00:00.0000000 -05:00 2013-02-12 14:00:00.0000000 -08:00

Funkcje ciągów znakowych

T-SQL nie został stworzony aby używać bardzo skomplikowanych manipulacji na ciągach znaków więc nie posiada dużego zbioru funkcji do tego służących. Funkcje służące do manipulacji ciągami znaków są podzielone na podkategorie:

Łączenie

Łączenie ciągów znaków jest używane bardzo często. T-SQL pozwala na łączenie ciągów znakowych na dwa sposoby - pierwszy z użyciem operatora (+) oraz drugi za pomocą funkcji CONCAT. Poniżej znajduje się przykład łączenia ciągów za pomocą operatora (+):

SELECT empid, country, region, city,
country + N',' + region + N',' + city AS location
FROM HR.Employees;

Używając tego operatora, zawsze gdy wśród łączonych wartości pojawi się wartość NULL to wynikiem też będzie NULL. Jest to zachowanie wywodzące się ze standardu i można je zmienić ustawiając opcję sessji CONCAT_NULL_YIELDS_NULL_INPUT na wartość OFF. Nie jest to rekomendowane. Aby zastąpić NULL przez pusty ciąg znaków istnieje wiele rozwiązań programistycznych. Jedną z opcji jest użycie funkcji COALESCE(,'') np:

SELECT empid, country, region, city,
country + COALESCE( N',' + region, N'') + N',' + city AS location
FROM HR.Employees;

Inną opcją jest użycie funkcji CONCAT która w przeciwieństwie do operatora (+) zamienia wartość NULL na pusty ciąg znaków. Oto przykład zapytania:

SELECT empid, country, region, city,
CONCAT(country, N',' + region, N',' + city) AS location
FROM HR.Employees;

Wycinanie i pozycja podciągów

Funkcja SUBSTRING wycina podciąg z ciągu znaków podanego jako pierwszy argument zaczynając od pozycji podanej jako drugi argument o długości podanej jako trzeci argument. Przykładowo funkcja SUBSTRING ('abcd',1,3) zwraca 'abc'. Jeżeli trzeci argument jest większy niż możliwa ilość znaków które pozostały to funkcja zwraca podciąg do końca ciągu wejściowego. Funkcje LEFT i RIGHT wycinają żądaną liczbę znaków z lewej lub prawej strony ciągu. Przykładowo LEFT('abcde', 3) zwraca 'abc' a RIGHT('abcde', 3) zwraca 'cde'. Funkcja CHARINDEX zwraca pozycję pierwszego wystąpienia podciągu użytego jako pierwszy argument w ciągu użytym jako drugi argument. Przykładowo CHARINDEX(' ','Itzik Ben-Gan') zwraca 6. Jeżeli podciąg nie jest odnaleziony to zwraca o. Można również użyć trzeciego argumentu którym jest pozycja od której wyszukiwanie ma być zaczęte. W praktyce często używa się kombinacji funkcji. Przykładowo z pola fullname formatowanego jako '' chcemy wyodrębnić pierwsze imię. Możemy tego dokonać za pomocą wyrażenia:

LEFT(fullname, CHARINDEX(' ', fullname) - 1)

T-SQL pozwala również na użycie funkcji PATINDEX która podobnie jak CHARINDEX zwraca pierwszą pozycję wystąpienia podciągu w ciągu. Lecz używając funkcji PATINDEX możemy wyszukiwać wzorców. Wzorce są formatowane podobnie do wzorców instrukcji LIKE. Używa znaków % dla dowolnego ciągu, _ dla dowolnego znaku i [] dla wyszukania znaku z listy znaków w zakresie. Przykładowo funkcja PATINDEX('%[0-9]%', 'abcd123efgh') wyszukuje pierwsze wystąpienie cyfry z zakresu 0-9 i zwraca 5.

Długość ciągu

Funkcja LEN zwraca długość ciągu znakowego w odniesieniu do ilości znaków. Niezależnie czy ciąg jest regularny czy też UNICODE funkcja ta zwraca ilość użytych znaków. Przykładowo funkcja LEN(N'xyz') zwraca 3. Jeżeli ciąg posiada jakiekolwiek spacje na końcu to funkcja LEN ich nie liczy. Funkcja DATALENGTH zwraca ilość bajtów ciągu znaków. Dla ciągów UNICODE są to dwa bajty na znak. Przykładowo funkcja DATALENGTH('xyz') zwraca 3 a funkcja DATALENGTH(N'xyz') zwraca 6. Funkcja DATALENGTH zlicza również końcowe spacje.

Modyfikacja ciągów

Funkcja REPLACE służy do zastąpienia wszystkich wystąpień podciągu przekazanego jako drugi argument w ciągu przekazanym jako pierwszy argument przez podciąg przekazany jako trzeci argument. Przykładowo funkcja REPLACE('.1.2.3.', '.', '/') zastępuje wszystkie wystąpienia kropki przez ukośnik i zwraca ciąg '/1/2/3/'. Funkcja REPLICATE pozwala na replikację ciągu znaków żądaną ilość razy. Przykładowo funkcja REPLICATE('0', 10) zwraca '0000000000'. Funkcja STUFF operuje na ciągu wejściowym przekazanym jako pierwszy argument. Od pozycji przekazanej w drugim argumencie usuwa żądaną liczbę znaków podaną w trzecim argumencie i zastępuje ten podciąg podciągiem przekazanym w czwartym argumencie. Przykładowo funkcja STUFF(',x,y,z', 1, 1, '') zwraca 'x,y,z'.

Formatowanie ciągów

Funkcje UPPER i LOWER zmieniają wszystkie znaki w ciągu na odpowiednio małe lub duże litery. Funkcje LTRIM i RTRIM usuwają odpowiednio spacje z lewej lub prawej strony ciągu. Aby usunąć spacje z lewej i prawej strony ciągu należy użyć kombinacji RTRIM(LTRIM()). Funkcja FORMAT pozwala na formatowanie ciągu bazując na ciągu formatującym i opcjonalnie kulturze przekazanej jako trzeci argument. Do formatowania można użyć ciągów formatujących wspieranych przez .NET Framework (więcej można znaleźć na stronie Books Online). Przykładowo funkcja FORMAT(1759, '000000000') zwraca '0000001759'.

Wyrażenie CASE

T-SQL pozwala na użycie instrukcji CASE oraz kilku powiązanych funkcji służących do stosowania logiki warunkowej. Wyrażenie CASE ma dwie formy - prostą (ang. simple) i przeszukiwaną (ang. searched). Poniżej znajduje się przykład wykorzystania prostej formy :

SELECT productid, productname, unitprice, discontinued,
CASE discontinued
WHEN 0 THEN 'No'
WHEN 1 THEN 'Yes'
ELSE 'Unknown'
END AS discontinued_desc
FROM Production.Products;

Prosta forma porównuje wyrażenie wejściowe (kolumna discontinued w przykładzie) do wielu wyliczonych wartości skalarnych w wyrażeniu when (0 i 1 w przykładzie) i zwraca określony powiązany z dopasowaniem ('yes' lub 'no' w przykładzie). Jeżeli nie ma dopasowania i klauzula ELSE jest określona ('Unknown' w przykładzie) to wartość wyrażenia ELSE jest zwracana. Jeżeli nie ma klauzuli ELSE to zwracane jest NULL. Forma przeszukiwana wyrażenia CASE jest bardziej skomplikowana. Zamiast porównywania wyrażenia wejściowego do wielu możliwych wyrażeń, używa predykatów w klauzuli WHEN, i wartość pierwszego predykatu który zwraca wynik prawdziwy jest zwracana. Jeżeli żaden predykat nie jest prawdziwy to zwracana jest wartość z klauzuli ELSE lub NULL. Oto przykład:

SELECT productid, productname, unitprice,
CASE
WHEN unitprice < 20.00 THEN 'Low'
WHEN unitprice < 40.00 THEN 'Medium'
WHEN unitprice >= 40.00 THEN 'High'
ELSE 'Unknown'
END AS pricerange
FROM Production.Products;

T-SQL wspiera kilka funkcji które mogą być rozważane jako formy skrócone wyrażenia CASE. Są to funkcje standardu SQL: COALESCE i NULLIF oraz niestandardowe funkcje ISNULL, IFF i CHOOSE. Funkcja COALESCE przyjmuje listę wyrażeń wejściowych i zwraca pierwsze którego wartość nie jest NULL lub NULL jeżeli wszystkie są NULL. Przykładowo:

COALESCE(, , …, )

Może być rozważane jako skrót od:

CASE
WHEN  IS NOT NULL THEN 
WHEN  IS NOT NULL THEN 
…
WHEN  IS NOT NULL THEN 
ELSE NULL
END

Typowym zastosowaniem COALESCE jest zastępowanie NULL przez inną wartość. Przykładowo wyrażenie COALESCE(region, '') zwraca region lub pusty ciąg znaków jeżeli region jest pusty. T-SQL pozwala na użycie funkcji ISNULL która przyjmuje dwa parametry wejściowe. Podobnie jak COALESCE zwraca pierwszą niepustą wartość. Zamiast COALESCE(region, '') można użyć ISNULL(region, ''). Istnieje kilka różnic pomiędzy użyciem COALESCE i ISNULL. Jedną z różnic jest zwracany typ danych. Przeanalizujmy kod:

DECLARE
@x AS VARCHAR(3) = NULL,
@y AS VARCHAR(10) = '1234567890';
SELECT COALESCE(@x, @y) AS [COALESCE], ISNULL(@x, @y) AS [ISNULL];

Wynik:

COALESCE ISNULL ---------- ------ 1234567890 123

Typ zwracany przez COALESCE to typ zwracanego wyniku a typ ISNULL to typ pierwszego parametru wejściowego. Warto na to zwrócić uwagę np w sytuacji wstawiania danych poprzez SELECT INTO. COALESCE i ISNULL mogą mieć wpływ na wydajność podczas łączenia lub filtrowania zbiorów. Rozważmy przypadek kiedy mamy dwie tabele T1 i T2 i musimy połączyć je używając kolumn T1.col1 i T2.col2 które zezwalają na NULL. Porównanie między NULL jest nieznane co powoduje, że wiersze nie będą połączone. Chcemy aby wiersze NULL były traktowane jako równe. Niektórzy urzywają COALESCE lub ISNULL aby zastąpić NULL przez wartość w celu uzyskania równości porównywanych wartości. Przykładowo wiedząc, że kolumny są typu całkowitego i dane są większe od zera można próbować użyć predykatu COALESCE(T1.col1, -1) = COALESCE(T2.col1, -1) lub ISNULL(T1.col1, -1) = ISNULL(T2.col1, -1). Problemem tego rozwiązania jest to, że manipulujemy porównywanymi danymi i SQL Server nie jest w stanie użyć indeksu do połączenia tych kolumn. Rekomendowanym sposobem jest użycie dłuższej formy T1.col1 = T2.col1 OR (T1.col1 IS NULL AND T2.col1 IS NULL), którą SQL Serwer rozumie i potrafi wydajnie użyć indeksów. T-SQL pozwala na użycie funkcji NULLIF która jest standardem. Funkcja ta akceptuje dwa parametry i zwraca NULL jeżeli są równe lub wartość pierwszego jeżeli nie są równe. Przykładowo NULLIF(1, 1) zwróci NULL a NULLIF(1, 2) zwróci 1. Funkcja IFF ma postać:

IIF(, , )

Zwraca true_result jeżeli predykat jest prawdziwy i false_or_unknown_result w przeciwnym wypadku. Może być rozważana jako skrót wyrażenia:

CASE WHEN  THEN  ELSE  END

Przykładowo wyrażenie IIF(orderyear = 2012, qty, 0) zwraca wartość qty jeżeli rok zamówienia to 2012 i zero w przeciwnym wypadku. Funkcja CHOOSE pozwala na wybranie wyniku na wybranej pozycji przekazanej jako pierwszy parametr z listy wyrażeń przekazanych jako kolejne parametry. Funkcja ta przyjmuje formę:

CHOOSE(, , , …, )

Przykładowo wyrażenie CHOOSE(2, 'x', 'y', 'z') zwraca 'y'.

Ćwiczenia

I. Scalanie ciągów znaków i użycie funkcji daty i czasu

Otwórz nowe okno SSMS i ustaw kontekst bazy danych na bazę danych TSQL2012.
```
USE TSQL2012
GO
```
Napisz zapytanie zwracające identyfikator pracownika, jego imię i nazwisko jako pełną nazwę oraz rok urodzenia z tabeli HR.Employee:
```
SELECT empid,
firstname + N' ' + lastname AS fullname,
YEAR(birthdate) AS birthyear
FROM HR.Employees;
```

II. Użycie funkcji daty i czasu

Napisz wyrażenie obliczające ostatni dzień aktualnego miesiąca i ostatni dzień aktualnego roku.

SELECT EOMONTH(SYSDATETIME()) AS end_of_current_month;
SELECT DATEFROMPARTS(YEAR(SYSDATETIME()), 12, 31) AS end_of_current_year;

III. Użycie funkcji łańcuchów i konwersji

Napisz zapytanie zwracające identyfikator produktu formatowany jako ciąg o stałej szerokości 10 znaków z zerami na początku z tabeli Production.Products
```
SELECT productid,
RIGHT(REPLICATE('0', 10) + CAST(productid AS VARCHAR(10)), 10) AS str_productid
FROM Production.Products;
```

Użyj funkcji FORMAT aby otrzymać taki sam rezultat jak w poprzednim ćwiczeniu.

SELECT productid,
FORMAT(productid, 'd10') AS str_productid
FROM Production.Products;

Podsumowanie

Wybór typu danych dla atrybutów może mieć wielki wpływ na funkcjonalność i wydajność kodu T-SQL który operuje na danych.
T-SQL udostępnia wielu funkcji których można użyć do manipulacji danymi różnych typów.
T-SQL udostępnia wyrażenie CASE pozwalające na zwracanie wyników bazujących na logice warunkowej.