SELECT Explanation, Example FROM Pro.Knowledge
FacebookRSS

Querying MS SQL Server 2012 70-461 – start do MCSA

Microsoft wraz z nowym produktem – SQL Server 2012 – wprowadził kolejną generację ścieżek certyfikujących. Poniżej garść moich wrażenia na gorąco, z pierwszego egzaminu (70-461) „nowej” ścieżki, który miałem przyjemność zdać. Znaczącym przystankiem na tej drodze jest tytuł MCSA (w sumie 3 egzaminy dotyczące zapytań, administracji oraz BI).

Moja aktywność w tej materii jest podyktowana tym, że zbyt długo odkładałem już aktualizację transkryptu. By móc formalnie z czystym sumieniem :), prowadzić nowe, oficjalne kursy Microsoft związane z SQL Server 2012, potrzebuję przejść tą drogę !

Muszę przyznać, że był to chyba najprostszy z egzaminów MS do których podchodziłem do tej pory. Może dlatego, że jest w zasadzie czwartym egzaminem z serii związanym mniej lub bardziej z pisaniem zapytań. Zaliczyłem ścieżki developerskie SQL Server 2000, 2005 i 2008, których zakres tematyczny w znacznym stopniu się tutaj pokrywa. 70-461 obejmował dodatkowo kilka nowych feature’ów dostępnych w 11 wersji SQL Servera, poza tym w niektórych przypadkach miałem wrażenie wszechograniającego deja vu :)

Oczywiście za dużo na temat samych pytań pisać nie mogę, ale fakt jest taki, że ogarnia podstawowy zakres materiału i jest dobrym startem dla osób rozpoczynających przygodę z certyfikacją Microsoft.

Myślę, że jeśli pracujesz z SQL Server 2008 i znasz dobrze T-SQL, to w zasadzie z marszu powinieneś dać sobie radę bo z nowinek (ku mojemu zdziwieniu) wprowadzonych do SQL 2012 na tym teście zbyt wiele nie ma.

Odnośnie mojego przygotowania. Ostatnie dwa tygodnie, spędziłem na zabawie i testowaniu nowych funkcjonalności T-SQL wprowadzonych w SQL Server 2012. Dodatkowo, specjalnie na tą okazję zakupiłem książkę – oficjalny Trainning Kit 70-461 – Querying Microsoft SQL Server 2012, która w bardzo usystematyzowany sposób stara się ogarnąć zakres materiału tego egzaminu.

Książka (abstrahując od ceny) jest niezła, szczególnie dla osób początkujących (w końcu Itzik Ben Gan się pod nią podpisał). Poza tym, razem z nią na dołączonym CD, dostarczany jest Trainning Kit Exam Preparation (symulacja testu) – oficjalna aplikacja do sprawdzenia swoich sił przed egzaminem.

Pomimo swojej kosmicznej ceny, spełnia swój cel – potwierdzam, że jest w stanie dobrze przygotować do tego testu. Myślę, że jest jakąś alternatywą (głównie cenową) do kursu 10774A: Querying Microsoft SQL Server 2012. Jeśli czujesz potrzebę skorzystania z dodatkowych pomocy i cena Cię nie odstraszy – polecam.

Więcej na temat samego egzaminu, zakres zagadnień, znajdziesz na oficjalnej stronie MS – egzamin 70-461 Querying SQL Server 2012.

Możliwości funkcji okna OVER()

Zastosowanie i możliwości funkcji OVER()

Funkcja okna OVER() stosowana jest zazwyczaj razem z funkcjami szeregującymi. Jest ich nierozłącznym elementem i służy do określania zakresu i sposobu w jaki będą nadawane numery wierszy. Opisuję jej zastosowanie w tym zakresie w artykule dotyczącym funkcji rankingowych.

W wersjach SQL Server 2005-2008 R2, możemy ją również używać razem z funkcjami agregującymi (SUM, AVG, MAX etc). Oferuje, więc połączenie możliwości działania na grupach (bo na nich operują agregaty) i na części nie grupowanej – czyli dowolnych kolumnach, w jednym zapytaniu. Stosowanie funkcji agregujących jak widać, nie jest ograniczone tylko i wyłącznie do kwerend grupujących z jawnie określoną klauzulą GROUP BY.

USE Northwind;
 
--Tradycyjne używanie funkcji agregujących z GROUP BY 
select OrderID , SUM(UnitPrice*Quantity) as TotWartosc
from dbo.[Order Details]
Group by OrderID;
 
--używanie funkcji agregującej razem z OVER() – nie musimy stosować GROUP BY, 
--zatem mamy dostęp do wszystkich kolumn, nie tylko do atrybutów grupujących
 
select OrderID, ProductID , 
	SUM(UnitPrice*Quantity) OVER(Partition by OrderID ) as TotWartosc
from dbo.[Order Details];

OVER_01
Podsumowując, w SQL Server 2005-2008 R2 funkcja okna OVER(), jest używana tylko razem z funkcjami szeregującymi lub agregujacymi. Wydziela przedział (podzbiór) z tabeli wynikowej i pozwala wykonywać na nim odpowiednie kalkulacje.


Rozszerzone możliwości OVER() w SQL Server 2012

SQL Server 2012 wprowadza szereg nowych możliwości, również w już istniejących, dobrze znanych funkcjach. Funkcja OVER(), oferuje teraz znacznie więcej w stosunku do poprzednich implementacji.

Podzbiór z podzbioru czyli frames (ramki)

Jedną z nowinek, jest możliwość działania na podzbiorach „ramkach” (widow frames) w kontekście zdefiniowanego okna dla funkcji agregujących. Ramki to kolejny poziom abstrakcji podzbiorów – w ramach definicji okna – partycji. W prostych słowach, możemy wydzielić podzbiór (ramkę) z podzbioru (okna).

Dzięki temu możemy wyznaczać sumy narastające lub inne agregacje dotychczas realizowane za pomocą podzapytań lub zapytań skorelowanych. W praktyce znacznie uprasza nam to kod SQL.

Wyznaczanie ramki za pomocą ORDER BY

Załóżmy, że chcemy pokazać zamówienia, wraz z ich detalami oraz informacją o wartości pozycji w ramach danego zamówienia jako sumy narastającej.

W tej sytuacji potrzebujemy określić za pomocą funkcji okna przedziały, obejmujące wszystkie elementy każdego zamówienia. Następnie, właśnie za pomocą ramek, możemy dla każdego elementu zamówienia (wiersza) w ramach podzbioru, wykonać dodatkowe kalkulacje.

USE AdventureWorks2008
GO
 
select SalesOrderId, ProductID, SalesOrderDetailId as DetailId, LineTotal ,
 
-- określamy okna – wszystkie elementy danego zamówienia – PARTITION BY 
-- oraz ramkę. Dla każdego elementu będą to wiersze od początku przedziału 
-- do current row, biorąc pod uwagę wartość sortowaną SalesOrderDetailId
 
SUM(LineTotal) OVER(partition by SalesOrderId order by SalesOrderDetailId) as RunningSUM,
COUNT(LineTotal) OVER(partition by SalesOrderId order by SalesOrderDetailId) as QtyFrameEl,
 
-- w SQL Server 2005-2008 R2 brak możliwości stosowania ORDER BY w OVER() 
-- w przypadku uzycia z funkcjami agregującymi np. SUM, AVG, MAX, MIN etc
-- mogliśmy wyznaczyć agregat tylko w ramach całej partycji np. TotalValue
 
	SUM(LineTotal) OVER(partition by SalesOrderId ) as TotalValue,
	COUNT(LineTotal) OVER(partition by SalesOrderId ) as QtyWindowEl
 
from Sales.SalesOrderDetail 
where SalesOrderId IN (43666,43664)

OVER_02
W tym przykładzie wyznaczmy RunningSUM czyli sumę narastającą w oparciu o dwa poziomy podzbiorów. Pierwszy – definiujemy za pomocą PARTITION BY – zakres zamówienia. W ramach niego, będziemy określali wycinek rekordów (ramkę) spełniających warunek sortowania ORDER BY. Ramka jest wyznaczana dla każdego wiersza osobno. W jej ramach, będą brane pod uwagę wszystkie rekordy dla których wartość sortująca – SalesOrderDetailId będzie mniejsza lub równa bieżącemu elementowi.

Zauważ, że sortuję po kolumnie unikalnej, zatem dla każdego n-tego wiersza, ramka będzie zawierała dokładnie n-elementów. Są to zawsze wszystkie wiersze w ramach okna, posiadające wartość atrybutu po którym sortujemy mniejszą lub równą. W powyższym przykładzie, kolumna QtyFrameElements obrazuje liczbę elementów branych pod uwagę w ramach ramki.

Pierwsza ramka zawiera tylko element SalesOrderDetailId = 53. Sumowanie dla tego podzbioru to oczywiście tylko wartość 2039.994000.

Druga ramka zawierać będzie wszystkie elementy poprzedzające (wartości <54) + element danego wiersza – czyli będą to pozycje numer 53 oraz 54.Funkcja agregująca, będzie zatem sumowała wartości tych dwóch elementów. Każda następna będzie działać dokładnie tak samo. Dla n-tego wiersza, agregacja odbywać będzie się w ramach n-tej ramki zawierającej n-elementów, mniejszych lub równych wartości SalesOrderId dla danego rekordu . Żeby dobrze zrozumieć działanie ramek, proponuję jeszcze jeden przykład z sortowaniem (określaniem ramek) po kolumnie nieunikalnej w ramach przedziału (partycji). Określmy więc podzbiór (ramkę) elementów według ilości danej pozycji w zamówieniu:

select SalesOrderId, ProductID, OrderQty, 
 
SUM(OrderQty) OVER(partition by SalesOrderId order by OrderQty) as RunningQty,
COUNT(OrderQty) OVER(partition by SalesOrderId order by OrderQty) as QtyFrameElements
 
from Sales.SalesOrderDetail 
where SalesOrderId IN (43666,43664)

OVER_03
Tym razem pierwsza ramka (podobnie jak cztery kolejne) zawierać będzie 5 elementów – ponieważ zgodnie z jej definicją, jest 5 wierszy spełniających założenia ORDER BY. Dla wszystkich pierwszych 5 wierszy (czyli tych z ilością danych produktów =1), ramka będzie tak samo zdefiniowana. Dlatego wynik funkcji agregującej będzie dla nich identyczny. Różnica pojawi się dopiero w 6 wierszu – w ramce znajdą się wtedy wszystkie elementy <= 2. Określanie ramek za pomocą ORDER BY jest pierwszym i najprostszym sposobem w kontekście partycji elementów. Wyznaczany jest zawsze dla każdego wiersza tak samo. Funkcja agregująca, działająca w jej ramach, bierze pod uwagę wszystkie elementy podzbioru, które są mniejsze lub równe wartości wiersza po której sortujemy.

Precyzyjne wyznaczanie wielkości ramki za pomocą RANGE i ROWS

Ramkę możemy wyznaczyć bardziej precyzyjnie niż tylko poprzez elementy poprzedzające plus bieżący wiersz jak ma to miejsce w sytuacji stosowania samego ORDER BY. W SQL Server 2012 pojawiły się rozszerzenia klauzuli OVER() – RANGE oraz ROWS, za pomocą których definiujemy rozmiar ramki. Może być on przedstawiony zawsze w formie przedziału, ze ściśle określonymi granicami. Składnia jest prosta i w ogólności bazuje na schemacie :

{ROWS | RANGE} BETWEEN  <początek przedziału ramki> AND <koniec przedziału ramki>

ROWS

Pozwala na określenie rozmiaru ramki z dokładnością do pojedynczego wiersza. Początek i koniec przedziału, definiowane mogą być za pomocą :

  • UNBOUNDED PRECEDING – wszystkie rekordy od początku ramki (poprzedzające wiersz, dla którego ramka jest wyznaczana)
  • <unsigned integer> PRECEDING – konkretna liczba wierszy poprzedzających
  • CURRENT ROW – reprezentuje konkretny, bieżący wiersz dla którego wyznaczana jest ramka
  • <unsigned integer> FOLLOWING – konkretna liczba wierszy następujących po danym elemencie.
  • UNBOUNDED FOLLOWING – wszystkie wiersze do końca podzbioru okna.

Przykład – zwróć uwagę na rozmiar ramki – kolumna QtyFrEle

select SalesOrderId, ProductID, UnitPrice, OrderQty, 
 
SUM(OrderQty) OVER(partition by SalesOrderId order by SalesOrderDetailId
	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 
 ) as RunningQty,
 
-- liczba elementów w ramce, dla każdego wiersza
COUNT(OrderQty) OVER(partition by SalesOrderId order by SalesOrderDetailId
	ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 
) as QtyFrEle
 
from Sales.SalesOrderDetail 
where SalesOrderId IN (43666,43664)

OVER_04
Przy określaniu rozmiaru za pomocą konkretnej liczby wierszy poprzedzających / następujących, trzeba zwrócić uwagę na potencjalnie różną liczbę elementów ramek (początkowych i końcowych).
Ramka, może być też określona, w uproszczony sposób, bez jawnego przedziału (BETWEEN … AND …). Możemy określić tylko liczbę wierszy poprzedzających, które mają być brane pod uwagę. Jest to skrócona, wygodna forma zapisu określający przedział ramki do bieżącego rekordu.

select SalesOrderId, ProductID,UnitPrice,OrderQty,SalesOrderDetailId, LineTotal ,
 
SUM(LineTotal) OVER(partition by SalesOrderId order by SalesOrderDetailId
 	ROWS 1 PRECEDING   
 ) as RunningQty,
 
-- liczba elementów w ramce, dla każdego wiersza
COUNT(OrderQty) OVER(partition by SalesOrderId order by SalesOrderDetailId
ROWS 1 PRECEDING      
) as QtyFrEle
 
from Sales.SalesOrderDetail 
where SalesOrderId IN (43666,43664)

OVER_05

RANGE

Różnica pomiędzy ROWS a RANGE polega na innej interpretacji granic przedziałów. W szczególności dotyczy to bieżącego wiersza (current row). W ramce określanej przez ROWS, jest on dokładnie tym wierszem, dla którego wyznaczana jest ramka. Podobnie początek jak i koniec przedziału może być w ROWS określony do konkretnego numeru, fizycznego wiersza.
Z kolei CURRENT ROW w przypadku RANGE, oznacza zarówno bieżący wiersz, jak i wszystkie, których wartości sortowane w ORDER BY (wyznaczających, domyślnie ramkę) są równe. Dlatego też, nie możemy stosować w RANGE – konkretnej liczby wierszy poprzedzających jak i następujących, bo w zbiorze mogą pojawić się wartości równe i zapytanie nie byłoby jednoznacznie określone.

Range przyjmuje zatem tylko 3 konkretne granice przedziałów :

  • RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW – wszystko od początku przedziału do danego zakresu określonego przez wartość elementu sortującego danego wiersza.
  • RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING – analogicznie do poprzedniego tylko w drugą stronę.
  • RANGE CURRENT ROW – wszystkie wiersze, dla których atrybut sortowany jest równy bieżącemu rekordowi w ramach okna.

Sposób działania RANGE, został już pokazany (niejawnie) w pierwszym przykładzie, najprostszego definiowania ramek czyli z ORDER BY. Samo ODRER BY określa ramkę na zasadzie – wszystkie mniejsze lub równe wiersze według wartości sortującej. Jest to zatem równoważnik RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

select SalesOrderId, ProductID,UnitPrice,OrderQty,
 
SUM(OrderQty) OVER(partition by SalesOrderId order by OrderQty) as RunningQty,
SUM(OrderQty) OVER(partition by SalesOrderId order by OrderQty
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) as RANGE_OrdQty
 
from Sales.SalesOrderDetail 
where SalesOrderId IN (43666,43664)

OVER_06

Ramki w funkcjach analitycznych

W SQL Server 2012, wprowadzono kilka nowych funkcji analitycznych. Koncepcja ramek w zakresie definiowanym funkcją okna OVER, jest również dostępna w niektórych z nich.
Sposób określania jest analogiczny do wyżej wymienionego z pewnymi ograniczeniami, wynikającymi już z charakteru samych funkcji. Można ją spotkać np. w FIRST_VALUE czy LAST_VALUE. 

Sortowanie wyniku ORDER BY

Ostatnim krokiem logicznego przetwarzania zapytań jest operacja sortowania, którą możemy określić właśnie w ORDER BY.

(5)     SELECT
(1)	FROM
(2)	WHERE
(3)	GROUP BY
(4)     HAVING
(6)     ORDER BY

W tym miejscu, silnik relacyjny, ma już w pełni uformowany zbiór wynikowy. Pozostało tylko posortowanie go zgodnie z określonymi w ORDER BY wymogami.


Po czym możemy sortować

Zazwyczaj sortujemy zbiór wynikowy według określonych atrybutów (kolumn) elementów, znajdujących się w klauzuli SELECT. Ponieważ jest to kolejny krok po SELECT, silnik relacyjny wykonujący zapytaniem zna także nazwy aliasów które tam mogliśmy użyć.

Możemy zatem odwoływać się zarówno do nazw kolumn, tabel (zbiorów) z których pobieramy dane (wyszczególnione w klauzuli FROM) jak również aliasów które nadaliśmy w SELECT.

USE AdventureWorks2008
GO
-- sortowanie po aliasie i nazwie kolumny źródłowej
select FirstName as Imie, 
	   LastName as Nazwisko,
	   PersonType
from Person.Person
order by Nazwisko, FirstName

ORDER_BY_01
Kolejną możliwością jest sortowanie po numerach kolumn tabeli wynikowej. Poniższy zapis jest tożsamy z poprzednim przykładem – zwróci identyczny wynik.

select FirstName as Imie, 
	   LastName as Nazwisko,
	   PersonType
from Person.Person
order by 2, 1

Nie jest to jednak zalecany sposób z uwagi na możliwość zmiany kolejności kolumn, zatem ich numerów w zbiorze wynikowym. Wystarczy że dopiszemy dodatkową kolumnę, lub zamienimy kolejność w SELECT i wynik sortowania będzie niezgodny z pierwotnym zamierzeniem. Sposób wygodny, ale polecany tylko do szybkich kwerend adhoc, w których nie chce nam się przepisywać nazw kolumn.

W standardzie ANSI SQL:1999 wprowadzono możliwość sortowania nie tylko po kolumnach, które określamy w SELECT. Możemy sortować po dowolnych atrybutach tabel (zbiorów) do których odwołujemy się w klauzuli FROM zapytania, pod warunkiem, że nie używamy DISTINCTa.

-- zauważ, że BusinessEntityID nie występuje w SELECT
select FirstName as Imie, 
	   LastName as Nazwisko,
	   PersonType
from Person.Person
order by Nazwisko, FirstName, BusinessEntityID

ORDER_BY_03
Zauważ, że kolejność rekordów dla osób nazywających się Kim Abercombie, różni się od poprzedniego przykładu – widać to po kolumnie PersonType. Jest zgodna z sortowaniem wewnątrz tej grupy po BusinessEntityID, której w tym zapytaniu nie wyciągam w SELECT.

Pewnym wyjątkiem od tej zasady (oprócz sytuacji z DISINCT), są zapytania w których grupujemy dane. W kwerendach grupujących w wyrażeniu SELECT jak i ORDER BY, możemy odwoływać się tylko do kolumn lub ich aliasów, tworzących grupy oraz do pozostałych kolumn ale tylko poprzez funkcje agregujące. Nie można odwoływać się bezpośrednio do części surowej (RAW data) w ramach grupy – jest to szerzej wyjaśnione w rozdziale tego kursu, dotyczącym grupowania danych.

Podsumowując, możemy sortować po

  • dowolnych kolumnach tabel źródłowych użytych we FROM (pod warunkiem że nie korzystamy z DISTINCT lub w przypadku grupowania – sortujemy po kolumnach tworzących grupy)
  • wyrażeniach tworzących nowe kolumny zbioru wynikowego
  • aliasach użytych w SELECT
  • numerach kolumn zbioru wynikowego

Sortowanie po wartościach losowych

Składnia klauzuli ORDER BY pozwala nadać losowości elementom zbioru. Trochę to może być mylące, ponieważ by definition, każdy zbiór, który nie jest jawnie posortowany jest losowy. Jest to podstawa rozważań teorii zbiorów i tak powinniśmy w ogólności do tematu podchodzić. Jednak owa losowość – jest zawsze w pewien sposób uwarunkowana np. fizycznym składowaniem rekordów (lub sposobem przechowywania danych w pamięci podręcznej). Pamiętajmy, że jeśli tabela posiada indeks klastrowy – jej elementy będą już zawsze fizycznie posortowane według tego indeksu.

Dlatego czasem potrzebujemy nadać dodatkowej losowości np. nowym produktom wyświetlanym na stronie głównej sklepu (żeby przy każdej wizycie był to jakiś losowy podzbiór).

Nadanie losowości elementom w zbiorze możemy wykonać np. z zastosowaniem funkcji NEWID(). Obydwa poniższe zapytania są równoważne w sensie wydajnościowym (plany wykonania będą identyczne) i obrazują one skrót, jaki możemy wykonać sortując wyniki po wartości losowej :

select NEWID() as RandomValue ,FirstName as Imie, 
	   LastName as Nazwisko,
	   PersonType
from Person.Person
order by 1
 
-- obydwa zapytania zwracają ten sam rezultat. W drugim sortujemy po NEWID()
select FirstName as Imie, 
	   LastName as Nazwisko,
	   PersonType
from Person.Person
order by NEWID()

ORDER_BY_04

Sposób sortowania

Operacja sortowania odbywa się zawsze w określony sposób – malejąco lub rosnąco. Możemy sortować po jednej lub kilku kolumnach. Operacja taka wykonywana jest zawsze od lewej do prawej. W naszym przypadku było to najpierw sortowanie po Nazwisku, następnie w ramach podzbiorów (gdzie wartość nazwiska jest taka sama) – sortowanie po kolumnie FirstName.
Domyślnie, sortowanie odbywa się w sposób rosnący (ASC ang. ascending). Dla każdej z kolumn możemy określić właściwy kierunek sortowania rekordów :

  • rosnąco ASC ascending – jest to domyślny sposób i nie trzeba go jawnie określać.
  • malejąco DESC descending – konieczne jest jawne określenie przy atrybucie, po którym chcemy w ten sposób sortować.
-- sortujemy najpierw po nazwisku (rosnąco), następnie po imieniu (malejąco)
select FirstName as Imie, 
	   LastName as Nazwisko,
	   PersonType
from Person.Person
where LastName like 'SUL%'
order by Nazwisko, FirstName DESC

ORDER_BY_05


COLLATION – czyli sposób porównania wartości

Każde sortowanie sprowadza się do wykonania operacji porównania wartości. Porównywanie dwóch liczb jest zawsze jednoznaczne – matematyka nie daje nam w tej materii żadnej interpretacji. Albo coś jest większe, równe ew. mniejsze.

Inaczej sprawa ma się z wartościami tekstowymi – stringami. Istnieją przecież różne alfabety i dialekty, w których panują różne reguły. SQL Server dostarcza możliwości ustawienia reguł porównywania wartości tekstowych na różnych poziomach – np. bazy danych, tabeli czy nawet konkretnej kolumny.

W skryptach, zapytaniach SQL, wszędzie tam gdzie wykonujemy operacje porównywania wartości znakowych, możemy jawnie określić sposób w jaki chcemy to czynić. Do tego celu stosujemy słowo kluczowe COLLATE wraz z nazwą reguły według której będą wykonywane porównania.

Porównywanie wartości tekstowych sprowadza się do zasad ogólnych panujących w danym języku (alfabecie) – a także do określenia w jaki sposób traktowane będą małe / wielkie litery CS lub CI (case sensitivity) oraz czy będziemy rozróżnili znaki akcentowane występujące w niektórych językach AS / AI (accent sensitivity). Istnieją jeszcze dwie inne, ale rzadko stosowane w praktyce własności – KS / KI (kana sensitivity) dotyczy tylko języka japońskiego oraz WS / WI (width sensitivity) – porównywanie tych samych znaków kodowanych na jednym lub dwóch bajtach (UNICODE).

Funkcja systemowa fn_helpcollations(),pokaże nam informacje o wszystkich dostępnych sposobach porównywania (collations) ciągów znakowych.

SELECT Name, Description FROM fn_helpcollations()
where name like '%pol%'

ORDER_BY_06
Sprawdźmy zatem wynik porównywania stringów na przykładzie zawierającym polskie znaki. W tym celu posłużymy stwórzmy tabelkę testową :

create table dbo.collate_test
(
	id int identity(1,1),
	opis nvarchar(10)
)
 
insert into dbo.collate_test(opis)
values (N'Pączek'), (N'Paczek'), (N'pączek'),
(N'paczek'), (N'paCzek'), (N'PACZEK'), (N'PĄCZEK')

Nowo utworzona tabela posiada collation dziedziczone po ustawieniach bazy. Aby sprawdzić jakie faktycznie ma collation, zerknijmy do jej definicji (poprzez widok systemowy sys.columns) :

-- wynik zależny od ustawień bazy danych w której utworzyliśmy tabelę
SELECT c.name, c.collation_name 
FROM sys.columns c 
where OBJECT_ID = OBJECT_ID('dbo.collate_test')
and c.name = 'opis'

ORDER_BY_07

Przykład zastosowania COLLATION – polski alfabet, z uwzględnieniem małych i wielkich liter (a<A) oraz bez wrażliwości na akcent (w polskim języku i tak nie ma znaczenia).
Sortowanie ze wskazanym collation :

select * from collate_test
order by opis collate Polish_CS_AI

ORDER_BY_08
Zauważ, że pomimo wybrania kolacji z accent insensitive, wciąż są rozróżniane polskie znaczki (w sortowaniu a < ą). Nadal działamy w ramach polskiego alfabetu – litery ą, ę itd. nie są akcentami tylko są regularnymi znakami, stąd ustawienia AI/AS nie ma znaczenia. Jeśli chcesz spłaszczyć polski język – pozbyć się ogonków, trzeba użyć specjalnej kolacji Azerskiej, która jest idealna do tego zadania :

select * from collate_test
order by opis collate Azeri_Cyrillic_90_CI_AI

ORDER_BY_08c
Wszystkie rekordy traktowane są płasko jako ‘paczek’ – tak więc zwrócone są w kolejności odpowiadającej fizycznemu zapisowi w tabeli (widać to po ID).

Sprawdźmy szerzej, co wyprawia collation azerskie z polskim alfabetem :

declare @alfabet varchar(100) = 'ĄąĆćĘꣳŃńÓ󌜯żŹź'
 
select @alfabet as Polskie,
@alfabet COLLATE Azeri_Cyrillic_90_CI_AI as Azerskie

POL_AZER


Rozszerzone możliwości ORDER BY

Implementacja ORDER BY w T-SQL do wersji 10.5 (SQL Server 2008R2), obejmuje w zasadzie tylko określenie kolejności w jakiej mają być posortowane kolumny + sposób porównywania (collation). W SQL Server 2012 klauzula ta jest rozszerzona o obsługę stronnicowania wyników – operatory OFFSET i FETCH. W celach demonstracyjnych użyłem funkcji ROW_NUMBER do ponumerowanie rekordów, aby pokazać, które faktycznie ze zbioru wynikowe są zwracane.

OFFSET – czyli przesunięcie o n rekordów – zwraca zbiór wynikowy z pominięciem pierwszych n wierszy :

-- zwracamy wiersze, startując od pozycji 1001 (1000 pierwszych pomijamy zgodnie z zastosowanym
-- sposobem sortowania - po RN)
select ROW_NUMBER() OVER(order by BusinessEntityId) as RN, * 
from [Person].[Person]
ORDER BY RN OFFSET 1000 ROWS

ORDER_BY_11

OFFSET + FETCH – czyli stronnicowanie wyniku. Podajemy ile rekordów pomijamy za pomocą OFFSET a w FETCH określamy rozmiar strony. Chcemy np. wyświetlić czwartą stronę wyników, w ramach której pokazujemy 10 rekordów :

select ROW_NUMBER() OVER(order by BusinessEntityId) as RN, * from [Person].[Person]
 
ORDER BY RN OFFSET 30 ROWS
    --FETCH FIRST 10 ROWS ONLY -– FIRST/NEXT oraz ROW/ROWS
                               -- są synonimami zgodnymi ze standardem ANSI
	                       -- możemy je stosować wymiennie
     FETCH NEXT 10 ROWS ONLY

ORDER_BY_12

Pierwszy / ostatni dzień tygodnia, miesiąca

Początek i koniec tygodnia

Zdarza się, że na podstawie podanej daty, chcemy wyznaczyc np. pierwszy dzień tygodnia w którym ta data się mieści. Aby to wykonać, możemy posłużyć się podstawowymi funkcjami związanymi z datą i czasem :

-- początek tygodnia - zawsze poniedziałek - niezależne od datefirst 
SELECT DATEADD(wk, DATEDIFF(d, 0, GETDATE()) / 7, 0)
 
-- pierwszy dzień bieżącego tygodnia - zależne od ustawień @@Datefirst 
SELECT DATEADD(DAY, 1-DATEPART(WEEKDAY, GETDATE()), GETDATE());

W pierwszym przypadku wyznaczamy bezwzględną liczbę tygodni od daty 0 (1900-01-01) do dziś. Następnie dodajemy, znów do daty 0, zminiejszając dokładność do jednego tygodnia (czyli do jednostki na której nam zależy). Żadna z użytych funkcji nie jest wrażliwa od ustawień zmiennej @@DateFirst dlatego wynik zawsze będzie wyznaczał nam poniedzałek.

Drugi przypadek – wyznaczamy numer dnia tygodnia z zadanej daty (będzie to liczba 1-7) za pomocą funkcji DATEPART i odejmujemy (no dobrze, dodajemy z minusem :)) do zadanej danty+1. Ponieważ funkcja DATEPART z parametrem weekday jest wrażliwa na ustawienia zmiennej @@DateFirst – wynik funkcji będzie zależny od ustawień serwera. Aby zmienić sposób interpretacji pierwszego dnia tygodnia – set firstday n (n z <1-7>).

Początek i koniec miesiąca

Na podobnej, prostej zasadzie możemy wyznaczyc pierwszy dzień bieżącego miesiąca i ostani poprzedniego. Za pomocą Funkcji DAY(data) otrzymamy info o numerze dnia z danego miesiąca. Teraz pozostaje jedynie odjąć (dodać z minusem :)) tą wartość jako liczbę dni od zadanej daty :

 -- informacja o dniu miesiąca 
select DAY(getdate())
 
-- pierwszy dzień bieżącego miesiąca
select DATEADD(dd,-(DAY(getdate())-1), getdate()) 
 
 -- ostatni dzień poprzedniego miesiąca
select DATEADD(dd,-DAY(getdate()), getdate())

Koniec bieżącego miesiąca? Nic prostszego, wystarczy posłużyć się tym co już znamy i dodać do daty miesiąc, odejmowanie dni bez zmian :

-- ostatni dzień bieżącego miesiąca
SELECT DATEADD(mm,1,DATEADD(dd,-(DAY(getdate())-1), getdate()) )-1
 
-- i jeszcze pierwszy dzień poprzedniego miesiąca wersja sprostowana.
SELECT DATEADD(MONTH, DATEDIFF(MONTH, 0, getdate()) - 1, 0)

Wszędzie tam gdzie jest zastosowana funkcja getdate(), możemy umieścić nazwę kolumny, której dane chcemy przekształcać. Na podobnej zasadzie możemy wyznaczyć pierwszy lub ostatni dzień roku.
Na koniec może jezcze jeden przykład – jak wyznaczyć pierwszy poniedziałek danego miesiąca :

select DATEADD(wk,DATEDIFF(wk,0,DATEADD(dd,6-DATEPART(day,getdate()),getdate())),0)

Oczywiście jeśli chcemy wykorzystywać tego typu kalkulacje wiele razy, w różnych miejscach – najlepiej zapisać je w postaci funkcji skalarnych UDF co znacznie upraszcza nasze zapytanka.

Koniec miesiąca w SQL Server 2012 – nowa funkcja EOMONTH()

W SQL Server 2012 pojawia się nowa funkcja związana z datą i czasem – EOMONTH() która względem podanej daty zwraca ostatni dzień miesiąca w którym ta data się zawiera. Realizuje nam zatem zadanie, które wykonaliśmy za pomocą funkcji DATEADD.

-- nowa funkcja EOMONTH() - wprowadzona w SQL Server 2012
select EOMONTH(getdate()) LastDayOfMonth, getdate() CurrentDate
LastDayOfMonth CurrentDate
-------------- -----------------------
2013-01-31     2013-01-17 12:46:04.803

(1 row(s) affected)

Errata

W metodzie obliczania pierwszego dnia poprzedniego miesiąca, wkradł się błąd. Z pewnością nie byłoby go, gdybym przynajmniej przetestował tą metodę w pełnym zakresie (i nie ma co zwalać winy na przerwę świąteczną, w której pisałem tego posta :)). Poniżej prosty skrypcik, pokazujący błędne myślenie – różnica pomiedzy metodą błędną i tą, sprostowaną przez @paazur :

declare @Date smalldatetime  
 
create table #temp
(
	data smalldatetime,
	FirstDayLastMonth smalldatetime,
	FirstDayLastMonthWRONG smalldatetime,
)
 
set @Date = '2013-01-01'
 
while(YEAR(@Date)<'2014')
begin
 
	insert into #temp 
	values(@Date,
		-- correct method
		DATEADD(MONTH, DATEDIFF(MONTH, 0, @Date) - 1, 0),
		-- wrong method
		DATEADD(dd,-DAY(@Date)+1,DATEADD(mm,-1,@Date)))
 
	set  @Date = DATEADD(DD,1,@Date)
 
end
 
select * from #temp
where FirstDayLastMonth <> FirstDayLastMonthWRONG
 
drop table #temp