Category Archives: SQL Server

Polecenie TOP z argumentem WITH TIES

2014-09-15

W dzisiejszym wpisie pokażę dosyć mało znaną konstrukcję TOP…WITH TIES. Muszę przyznać, że sam trafiłem na nią całkiem niedawno mimo, iż była ona dostępna już w SQL Server 2000. Załóżmy, że mamy pewien zbiór danych:

ties1

Teraz chcemy wybrać trzy firmy o największej sprzedaży. Company C i Company D mają taką samą wartość więc oczekujemy, że obie firmy znajdą się w wynikach zapytania. Niestety standardowe polecenie TOP 3 zwróci nam dokładnie trzy rekordy, pomijając wyniki ex-aequo.

Do tej pory w celu rozwiązania powyższego problemu stosowałem funkcję RANK(). Najpierw tworzyłem zapytanie z dodatkową kolumną rankingową:

select
  Company,
  TotalSales,
  rank() over(order by TotalSales desc) r
from Sales

Dzięki czemu otrzymywałem taki oto zbiór:

ties2

Następnie tworzyłem ostateczne zapytanie:

select
  Company,
  TotalSales
from
(
  select
    Company,
    TotalSales,
    rank() over(order by TotalSales desc) r
  from Sales
) Sales
where r <= 3

Otrzymując oczekiwane dane:

ties3

Okazuje się jednak, że dzięki poleceniu TOP z argumentem WITH TIES otrzymamy dokładnie ten sam wynik:

select top 3 with ties
  Company,
  TotalSales
from Sales
order by TotalSales desc

Dodanie do polecenia TOP klauzuli WITH TIES powoduje dołączenie do wynikowego zbioru danych wszystkich rekordów posiadających takie same wartości w kolumnach ORDER BY jak ostatni zwrócony rekord.

Reklamy

T-SQL – kilka przykładów zastosowania tabeli liczb całkowitych

2013-05-09

W poprzednim wpisie pokazałem kilka sposobów na wygenerowanie zbioru kolejnych liczb całkowitych. Dzisiaj zaprezentuję wykorzystanie tych danych w kilku przypadkach. Zacznijmy od przygotowania tabeli zawierającej 100 liczb, będzie ona używana w każdym z prezentowanych przykładów:

declare @Numbers table (N int);
with Numbers
as
(
    select 1 as Number
    union all
    select Number + 1 from Numbers where Number < 100
)
insert into @Numbers(N)
select Number from Numbers option (maxrecursion 100)

Generowanie dat

Poniższe zapytanie zwraca daty oraz informację o dniu tygodnia dla wszystkich dni znajdujących się pomiędzy dwoma datami:

set datefirst 1
declare @beginDate date = '20130501', @endDate date = '20130731'

select
	DateAdd(dd, N - 1, @beginDate) as [Date],
	case DatePart(dw, DateAdd(dd, N - 1, @beginDate))
		when 1 then 'Pn'
		when 2 then 'Wt'
		when 3 then 'Śr'
		when 4 then 'Cz'
		when 5 then 'Pi'
		when 6 then 'So'
		when 7 then 'Ni'
	end as [WeekDay]
from @Numbers
where DateAdd(dd, N - 1, @beginDate) <= @endDate

tally1

Luki w numeracji

Załóżmy, że mamy tabelę zawierającą określone wartości liczbowe:

declare @Values table (value int)
insert into @Values (value)
values (1),(3),(4),(5),(8),(10)

select * from @Values

tally2

Poniżej znajduje się zapytanie zwracające luki w numeracji:

declare @minValue int, @maxValue int
select @minValue = min(value), @maxValue = max(value)
from @Values

select N as Value
from @Numbers n
	left join @Values v on v.value = n.N
where n.N between @minValue and @maxValue
	and v.value is null

tally3

Pozycje występowania danego znaku w tekście

Zapytanie zwraca numery pozycji w tekście, na których znajduje się określony znak:

declare @text varchar(100) = 'to jest przykładowy tekst'
declare @char char(1) = 't'

select N as [CharIndex]
from @Numbers
where SubString(@text, N, 1) = @char and N <= Len(@text)

tally4

Litery występujące w tekście

Poniższe zapytanie zwraca wszystkie litery z podanego tekstu wraz z kodami Ascii oraz liczbą wystąpień:

declare @text varchar(100) = 'to jest przykładowy tekst'

select
	SubString(@text, N, 1) as [Char],
	Ascii(SubString(@text, N, 1)) as [Ascii],
	Count(*) as [Count]
from @Numbers
where N <= Len(@text)
group by SubString(@text, N, 1)

tally5

Podział tekstu na wyrazy

Zapytanie zwraca wszystkie fragmenty danego tekstu rozdzielone określonym znakiem:

declare @text varchar(100) = 'to jest przykładowy tekst'
declare @char char(1) = ' '

set @text = @char + @text + @char

select SubString(@text, N + 1,
        CharIndex(@char, @text, N + 1) - N - 1) as [Words]
from @Numbers
where SubString(@text, N, 1) = @char and N < Len(@text)

tally6

T-SQL – generowanie zbioru kolejnych liczb całkowitych

2013-05-08

Często podczas programowania w T-SQL pojawia się potrzeba skorzystania ze zbioru kolejnych liczb całkowitych z określonego zakresu. SQL Server nie posiada gotowej tabeli zawierającej takie dane, pozostaje opracowanie własnego rozwiązania. Poniżej znajdują się trzy przykłady kodu generującego zbiór kolejnych liczb całkowitych z zakresu od 1 do 195. Skrypty nie korzystają z żadnych obiektów bazy danych (tabele systemowe, widoki), nie opierają się również na pętli while co jest częstym ale mało wydajnym podejściem.

Skrypt 1:

with Numbers
as
(
	select 1 as Number
	union all
	select Number + 1 from Numbers where Number < 195
)
select Number from Numbers option (maxrecursion 195)

Skrypt 2:

select top 195 (a * 10 + b) as Number
from
(
	values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),
		(11),(12),(13),(14),(15),(16),(17),(18),(19)
) as A(a),
(
	values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
) as B(b)
order by 1

Skrypt 3:

with Numbers
as
(
	select a
	from
	(
		values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
	) as A(a)
)
select top 195 row_number() over(order by @@spid) as Number
from Numbers as a, Numbers as b, Numbers as c

W kolejnym wpisie pokażę kilka przykładów zastosowania wygenerowanego w ten sposób zbioru liczb do rozwiązania określonych problemów.

Grupowanie danych przy użyciu poleceń ROLLUP, CUBE oraz GROUPING SETS

2012-10-03

W dzisiejszym temacie zajmę się omówieniem poleceń ROLLUP, CUBE oraz GROUPING SETS. Polecenia te umieszczane są w klauzuli GROUP BY i pozwalają na wygenerowanie odpowiednich grupowań w celu agregacji danych. Warto zaznaczyć, iż omawiane polecenia zastępują instrukcje COMPUTE, COMPUTE BY, WITH ROLLUP oraz WITH CUBE. Dwie pierwsze zostały wycofane z języka T-SQL w SQL Server 2012, pozostałe dwie zostaną wycofane w kolejnej wersji. Aby zaprezentować działanie omawianych instrukcji stworzyłem tabelę zawierającą informacje o wartości sprzedaży uzyskanej przez pracowników w poszczególnych latach:

create table #Sales
(
	EmployeeId int,
	[Year] int,
	Sales money
)

insert into #Sales values(1, 2005, 120000)
insert into #Sales values(1, 2006, 180000)
insert into #Sales values(1, 2007, 250000)
insert into #Sales values(2, 2005, 150000)
insert into #Sales values(2, 2006, 110000)
insert into #Sales values(3, 2006, 200000)
insert into #Sales values(3, 2007, 240000)

ROLLUP

Polecenie ROLLUP grupuje dane według wskazanych kolumn w sposób hierarchiczny. Użycie ROLLUP(a, b, c) spowoduje wygenerowanie następujących grupowań: (a, b, c), (a, b), (a) oraz rekordu agregującego cały zbiór. Inaczej mówiąc, użycie w zapytaniu konstrukcji ROLLUP(a, b, c) jest równoważne z wykonaniem czterech zapytań połączonych klauzulą UNION ALL, gdzie każde zapytanie posiada odpowiednią klauzulę GROUP BY. Poniższy przykład pokazuje działanie polecenia ROLLUP:

select
    [Year],
    EmployeeId,
    sum(Sales) as Sales
from #Sales
group by rollup([Year], EmployeeId)

Jak widać dzięki przekazaniu do polecenia ROLLUP kolumn Year i EmployeeId w wynikowym zbiorze uzyskaliśmy następujące grupowania: (Year, EmployeeId), (Year) oraz rekord ogólny dla całego zbioru.

CUBE

Polecenie CUBE działa w sposób podobny, z tą różnicą, iż przy grupowaniu danych uwzględniane są wszystkie kombinacje wskazanych kolumn. Użycie CUBE(a, b, c) spowoduje więc wygenerowanie następujących grupowań: (a, b, c), (a, b), (a, c), (b, c), (a), (b), (c) oraz rekordu agregującego cały zbiór. Poniższy przykład pokazuje działanie polecenia CUBE:

select
    [Year],
    EmployeeId,
    sum(Sales) as Sales
from #Sales
group by cube([Year], EmployeeId)

Użycie polecenia CUBE dla kolumn Year i EmployeeId spowodowało wygenerowanie następujących grupowań: (Year, EmployeeId), (Year), (EmployeeId) oraz rekordu ogólnego dla całego zbioru.

GROUPING SETS

Polecenie GROUPING SETS działa analogicznie do powyższych, ale daje nam możliwość jawnego określenia wszystkich grupowań jakie mają być uwzględnione w wynikowym zbiorze. Poszczególne grupy wymieniane są po przecinku, przy czym jeżeli w ich skład wchodzi kilka kolumn muszą być ujęte w nawiasy. Puste nawiasy () oznaczają rekord ogólny dla całego zbioru. Poniżej przykład użycia polecenia GROUPING SETS:

select
    [Year],
    EmployeeId,
    sum(Sales) as Sales
from #Sales
group by grouping sets((EmployeeId, [Year]), EmployeeId, ())

W powyższym zapytaniu jawnie określone zostały następujące grupowania: (EmployeeId, Year), (EmployeeId) oraz rekord ogólny dla całego zbioru.

Funkcja GROUPING

Podczas korzystania z omawianych poleceń bardzo użyteczna może okazać się funkcja GROUPING. Pozwala ona określić dla każdego wiersza wynikowego zbioru czy dana kolumna jest zagregowana (posiada wartość NULL) czy nie. W przypadku kolumny zagregowanej funkcja zwraca 1, w przeciwnym wypadku 0. Oto przykład użycia:

select
	[Year],
	EmployeeId,
	sum(Sales) as Sales,
	grouping([Year]) as AgrYear,
	grouping(EmployeeId) as AgrEmployeeId
from #Sales
group by cube([Year], EmployeeId)

Jak widać kolumny AgrYear oraz AgrEmployeeId wskazują czy w danym wierszu kolumny Year i EmployeeId posiadają wartość NULL wynikającą z określonego grupowania (wartość 0 zwrócona przez funkcję GROUPING oznacza, że dana kolumna bierze udział w grupowaniu).

Funkcja GROUPING_ID

Inną metodą na uzyskanie informacji o zagregowanych kolumnach jest użycie funkcji GROUPING_ID. Zastępuje ona wielokrotne użycie funkcji GROUPING (dla każdej kolumny). Dla wskazanego zestawu kolumn generowana jest postać binarna na podstawie poszczególnych wyników funkcji GROUPING, następnie wynik zwracany jest w postaci wartości typu int. Załóżmy, że uzyskaliśmy następujące wyniki w danym wierszu: GROUPING(a) = 1, GROUPING(b) = 0, GROUPING(c) = 1. Wywołanie funkcji GROUPING_ID(a, b, c) na podstawie wartości binarnej 101 (GROUPING(a) + GROUPING(b) + GROUPING(c)) da wynik 5. Przykład użycia:

select
	[Year],
	EmployeeId,
	sum(Sales) as Sales,
	grouping([Year]) as AgrYear,
	grouping(EmployeeId) as AgrEmployeeId,
	grouping_id([Year], EmployeeId) Agr
from #Sales
group by cube([Year], EmployeeId)

Więcej informacji na temat omawianych poleceń można znaleźć na MSDN: GROUP BY, Using GROUP BY with ROLLUP, CUBE, and GROUPING SETS, GROUPING SETS Equivalents.

Polecenie output – odczyt informacji o przetworzonych rekordach

2012-09-01

Od wersji SQL Server 2005 w języku T-SQL dostępne jest polecenie output. Możemy dołączyć je do instrukcji insert, update, delete lub merge w celu uzyskania informacji o rekordach przetworzonych w wyniku ich działania. Informacje te udostępniane są przez dwa zbiory danych: inserted oraz deleted – ich struktura odpowiada tabeli, na której wykonywana jest dana operacja. Zbiór inserted dostępny jest przy operacjach insert, update oraz merge i zawiera nową (zaktualizowaną) postać rekordów. Z kolei zbiór deleted udostępniany jest przy poleceniach update, delete oraz merge i przechowuje oryginalną postać rekordów. Mamy dwie możliwości wykorzystania polecenia output, możemy jedynie zwrócić wybrane informacje lub poprzez klauzulę into umieścić je w określonej tabeli lub zmiennej tabelarycznej.
Aby zaprezentować działanie omawianego polecenia stworzyłem dwie tabele: #Test, na której przeprowadzane będą określone operacje oraz #Log, w której zapisywana będzie ich historia:

create table #Test
(
	Id int Identity(1, 1),
	Name varchar(50)
)

create table #Log
(
	Id int,
	NameOld varchar(50),
	NameNew varchar(50),
	OperationType varchar(6),
	OperationTime datetime
)

Poniższy kod wstawia do tabeli #Test cztery rekordy i przy użyciu polecenia output informacje o nich umieszcza w tabeli #Log (jawne specyfikowanie pól tabeli docelowej nie jest konieczne):

insert into #Test(Name)
    output inserted.Id, inserted.Name, 'INSERT', GetDate()
    into #Log(Id, NameNew, OperationType, OperationTime)
values ('Name_1'), ('Name_2'), ('Name_3'), ('Name_4')

Oto zawartość tabel #Test oraz #Log:

Jak widać instrukcja output umożliwia odczyt wartości z pól zdefiniowanych jako identity.
Kolejny przykład dotyczy modyfikacji rekordów:

update #Test set Name = 'Name_44'
	output inserted.Id, inserted.Name, deleted.Name, 'UPDATE', GetDate()
	into #Log(Id, NameNew, NameOld, OperationType, OperationTime)
where Name = 'Name_4'

Zawartość tabel #Test oraz #Log:

Usuwanie rekordów:

delete from #Test
	output deleted.Id, deleted.Name, 'DELETE', GetDate()
	into #Log(Id, NameOld, OperationType, OperationTime)
where Name in ('Name_2', 'Name_3')

Aktualna zawartość tabel #Test oraz #Log:

W przypadku polecenia merge klauzula output dysponuje dodatkowym argumentem $action typu nvarchar(10). Argument ten udostępnia informacje o rodzaju wykonanej na danym rekordzie operacji (‚INSERT’, ‚UPDATE’ lub ‚DELETE’):

merge #Test as t
using
(
	select v.Id, v.Name
	from (values (1, 'Name_11'), (2, 'Name_55')) v(Id, Name)
) as s on s.Id = t.Id
when matched and t.Name <> s.Name then update set t.Name = s.Name
when not matched by target then insert (Name) values (s.Name)
when not matched by source then delete
output $action, deleted.Id as deletedId, deleted.Name as deteledName,
	inserted.Id as insertedId, inserted.Name as insertedName;

Oto zbiór zwrócony przez polecenie output oraz ostateczna postać tabeli #Test:

Więcej informacji na temat polecenia output: MSDN

Nowości w SQL Server 2012 – funkcje analityczne

2012-06-23

W najnowszej wersji SQL Server język T-SQL wzbogacony został o kilka nowych funkcji analitycznych. Są to tzw. funkcje okienkowe operujące na podzbiorach (oknach danych tworzonych klauzulą OVER) głównego zbioru zwróconego przez zapytanie. Zanim przejdę do omówienia wspomnianych funkcji pokażę co zmieniło się w samym poleceniu OVER. Polecenie umożliwiało tworzenie podzbiorów danych poprzez umieszczane w nim instrukcje PARTITION BY i ORDER BY. Obecnie na potrzeby danej funkcji każdy taki podzbiór możemy podzielić na jeszcze mniejsze okna danych. Służy do tego instrukcja ROWS zawężająca dany podzbiór do określonych rekordów. Po słowie ROWS definiujemy zakres rekordów jaki wejdzie w skład danego okna, odbywa się to poprzez polecenia:

  • CURRENT ROW – bieżący rekord;
  • PRECEDING – liczba rekordów poprzedzających bieżący (UNBOUNDED PRECEDING oznacza początek podzbioru);
  • FOLLOWING – liczba kolejnych rekordów po bieżącym (UNBOUNDED FOLLOWING oznacza koniec podzbioru);

Polecenia 0 PRECEDING oraz 0 FOLLOWING są jednoznaczne z CURRENT ROW.
Zamiast ROWS operującego na fizycznych pozycjach względem bieżącego rekordu, możemy użyć polecenia RANGE, które odnosi się do rekordów poprzez ich wartości. I tak np. CURRENT ROW w przypadku RANGE to wszystkie rekordy posiadające w polach z klauzuli ORDER BY taką samą wartość jak rekord bieżący.

Aby pokazać działanie omawianych mechanizmów przygotowałem tabelę tymczasową zawierającą średnie miesięczne ceny paliw w okresie czerwiec – wrzesień 2011 roku (a wówczas narzekaliśmy, że jest drogo ;)):

create table #CenyPaliw
(
    Rok int,
    Miesiac int,
    Paliwo varchar(10),
    Cena money
)

insert into #CenyPaliw(Rok, Miesiac, Paliwo, Cena)
values
    (2011, 6, 'PB95', 5.11), (2011, 6, 'ON', 4.95), (2011, 6, 'LPG', 2.49),
    (2011, 7, 'PB95', 5.13), (2011, 7, 'ON', 4.94), (2011, 7, 'LPG', 2.48),
    (2011, 8, 'PB95', 5.21), (2011, 8, 'ON', 5.05), (2011, 8, 'LPG', 2.47),
    (2011, 9, 'PB95', 5.10), (2011, 9, 'ON', 5.08), (2011, 9, 'LPG', 2.54)

select * from #CenyPaliw

Poniższe zapytanie pokazuje zastosowanie nowych możliwości polecenia OVER:

select Paliwo, Rok, Miesiac, Cena,
    max(cena) over(partition by paliwo) as [Max],
    max(cena) over(partition by paliwo order by rok, miesiac
        rows between unbounded preceding and current row) as [MaxDoTeraz],
    max(cena) over(partition by paliwo order by rok, miesiac
        rows between 2 preceding and 0 following) as [MaxOd3Mcy],
    min(cena) over(partition by paliwo order by rok, miesiac
        rows between current row and 2 following) as [MinDo3Mcy],
    min(cena) over(partition by paliwo order by rok, miesiac
        rows between current row and unbounded following) as [MinOdTeraz]
from #CenyPaliw
order by Paliwo, Rok, Miesiac

W poszczególnych kolumnach znajdują się następujące informacje w odniesieniu do każdego rodzaju paliwa:

  • Max – maksymalna cena paliwa w całym badanym okresie;
  • MaxDoTeraz – maksymalna cena paliwa do danego miesiąca (rekordu);
  • MaxOd3Mcy – maksymalna cena paliwa z ostatnich trzech miesięcy (względem danego rekordu);
  • MinDo3Mcy – minimalna cena paliwa w trzech kolejnych miesiącach (względem danego rekordu);
  • MinOdTeraz – minimalna cena paliwa od danego miesiąca do końca okresu;

Po zapoznaniu się z rozszerzonymi możliwościami klauzuli OVER czas przejść do prezentacji nowych funkcji analitycznych. Są to funkcje: LAG, LEAD, FIRST_VALUE, LAST_VALUE, PERCENT_RANK, CUME_DIST, PERCENTILE_CONT oraz PERCENTILE_DISC. Ich działanie w odniesieniu do ceny każdego rodzaju paliwa pokazuje poniższe zapytanie:

select Paliwo, Rok, Miesiac, Cena,
    lag(cena, 1, 0) over(partition by paliwo order by rok, miesiac) as [Poprzednia],
    lead(cena, 1, 0) over(partition by paliwo order by rok, miesiac) as [Nastepna],
    first_value(cena) over(partition by paliwo order by rok, miesiac
        rows unbounded preceding) as [Pierwsza],
    last_value(cena) over(partition by paliwo order by rok, miesiac
        rows between current row and unbounded following) as [Ostatnia],
    percent_rank() over(partition by paliwo order by rok, miesiac) as [Rank1],
    cume_dist() over(partition by paliwo order by rok, miesiac) as [Rank2],
    percentile_cont(0.5) within group (order by cena)
        over (partition by paliwo) as [Med1],
    percentile_disc(0.5) within group (order by cena)
        over (partition by paliwo) as [Med2]
from #CenyPaliw
order by Paliwo, Rok, Miesiac

Poszczególne funkcje oraz kolumny, w których zostały użyte:

  • LAG [Poprzednia] – domyślnie funkcja zwraca wartość określonego pola z poprzedniego rekordu podzbioru, jeżeli jest to pierwszy rekord zwraca NULL, opcjonalnie można podać większy offset i wartość domyślną (w tym przypadku odpowiednio 1 i 0);
  • LEAD [Nastepna] – domyślnie funkcja zwraca wartość określonego pola z następnego rekordu podzbioru, jeżeli jest to ostatni rekord zwraca NULL, opcjonalnie można podać większy offset i wartość domyślną (w tym przypadku odpowiednio 1 i 0);
  • FIRST_VALUE [Pierwsza] – funkcja zwraca wartość pola z pierwszego rekordu w określonym oknie danych;
  • LAST_VALUE [Ostatnia] – funkcja zwraca wartość pola z ostatniego rekordu w określonym oknie danych;
  • PERCENT_RANK [Rank1] – ranking rekordu w danym podzbiorze obliczany wg wzoru: (RN – 1) / (RC – 1), gdzie RN to kolejny numer rekordu (funkcja Rank), RC to liczba rekordów w danym podzbiorze;
  • CUME_DIST [Rank2] – ranking rekordu w danym podzbiorze obliczany wg wzoru: RS / RC, gdzie RS to liczba rekordów o wartości mniejszej bądź równej od bieżącego (dotyczy klauzuli ORDER BY), RC to liczba rekordów w danym podzbiorze;
  • PERCENTILE_CONT [Med1] – funkcja zwraca percentyl dla przekazanego procentu (0.0 – 1.0). Jest to wielkość, poniżej której padają wartości zadanego procentu rekordów z danego podzbioru, np. percentyl 50% to mediana. Do określenia kolumny, której wartości będą analizowane służy dodatkowa klauzula WITHIN GROUP. Zwrócony wynik nie musi pokrywać się z jakąkolwiek wartością w danym podzbiorze;
  • PERCENTILE_DISC [Med2] – funkcja działa analogicznie do powyższej, z tą różnicą, iż zwrócony wynik zawsze pokrywa się z wartością występującą w danym podzbiorze;

Jak widać nowe funkcje znacznie upraszczają proces analizy, eliminując konieczność tworzenia szeregu skomplikowanych podzapytań w celu uzyskania interesujących nas danych.

Więcej informacji o nowościach w SQL Server 2012: MSDN

Nowości w SQL Server 2012 – odczyt struktury zwracanych danych

2012-06-10

W SQL Server 2012 pojawiło się kilka obiektów systemowych pozwalających na uzyskanie szczegółowych informacji odnośnie struktury danych zwracanych przez określone zapytanie, bez konieczności jego uruchamiania. Do tego celu służy procedura sp_describe_first_result_set oraz funkcje sys.dm_exec_describe_first_result_set i sys.dm_exec_describe_first_result_set_for_object. W wyniku ich działania otrzymujemy zestaw danych opisujących pierwszy zbiór zwrócony przez dane zapytanie lub obiekt (procedura, funkcja, widok, itp.). Jeżeli dane polecenie nie zwraca zbioru danych otrzymamy pusty wynik.

W celu prezentacji działania powyższych obiektów stworzyłem tabelę i procedurę składowaną:

create table Test
(
    Id int not null primary key,
    Name varchar(50)
)

create procedure pTest
as
    select * from Test

sp_describe_first_result_set

Procedura sp_describe_first_result_set przyjmuje trzy parametry, z czego dwa ostatnie są opcjonalne. Jako pierwszy parametr przekazywane jest polecenie SQL, dla którego chcemy uzyskać informacje na temat struktury danych wynikowych. Drugi parametr pozwala na określenie argumentów polecenia SQL. Trzeci z parametrów przyjmuje wartości 0, 1 lub 2 i służy do wyboru rodzaju prezentowanych informacji. W wyniku działania procedury otrzymujemy szczegółowy opis struktury pierwszego zbioru danych zwróconych przez podane polecenie SQL. W poszczególnych wierszach znajdują się opisy kolumn zwracanego zbioru. Najważniejsze informacje to:

  • is_hidden – czy kolumna jest ukryta
  • name – nazwa kolumny
  • column_ordinal – pozycja kolumny
  • is_nullable – czy kolumna zezwala na wartości NULL
  • system_type_id – identyfikator typu danych
  • system_type_name – nazwa typu danych
  • max_length – maksymalny rozmiar danych
  • collation_name – collation danych tekstowych w kolumnie

Pełna lista wraz z opisami znajduje się na MSDN.

Przykład użycia:

exec sp_describe_first_result_set N'select * from Test'

Zwrócenie informacji tylko o widocznych kolumnach (domyślnie):

exec sp_describe_first_result_set N'select Name from Test', null, 0

Zwrócenie informacji także o kolumnach niewidocznych (uwzględniona została kolumna Id będąca kluczem głównym tabeli, is_hidden = 1):

exec sp_describe_first_result_set N'select Name from Test', null, 1


W przypadku gdy określone polecenie zwraca więcej niż jeden zbiór danych, udostępnione zostaną informacje tylko o pierwszym z nich:

exec sp_describe_first_result_set N'select Id from Test; select Id, Name from Test'


Jeżeli polecenie w zależności od warunku logicznego zwraca zbiory danych o różnej strukturze wygenerowany zostanie błąd:

exec sp_describe_first_result_set N'if (1=1) select Id from Test else select Name from Test'

sys.dm_exec_describe_first_result_set

Funkcja dm_exec_describe_first_result_set działa w identyczny sposób jak omówiona wcześniej procedura, jednak w jej przypadku podanie wszystkich trzech parametrów jest obowiązkowe:

select * from sys.dm_exec_describe_first_result_set(N'select * from Test', null, 0)

select name, system_type_name
from sys.dm_exec_describe_first_result_set(N'pTest', null, 0)
order by column_ordinal

Istnieje jeszcze jedna różnica w stosunku do procedury sp_describe_first_result_set. Jeżeli przekazane w parametrze polecenie będzie błędne procedura zgłosi wyjątek. W przypadku funkcji wynik zostanie zwrócony, a informacje o błędach zostaną umieszczone w odpowiednich kolumnach:

  • error_number – numer błędu
  • error_severity – poziom błędu
  • error_state – stan błędu
  • error_message – komunikat błędu
  • error_type – typ błędu
  • error_type_desc – opis typu błędu
--Odwołanie do nieistniejącego obiektu Test1
select * from sys.dm_exec_describe_first_result_set(N'select * from Test1', null, 0)

sys.dm_exec_describe_first_result_set_for_object

Funkcja dm_exec_describe_first_result_set_for_object działa analogicznie do powyższej, jednak jako pierwszy parametr przyjmuje ID obiektu procedury składowanej lub triggera (w przypadku innych obiektów zgłoszony zostanie błąd). W jej przypadku nie podaje się parametru z listą argumentów:

select * from sys.dm_exec_describe_first_result_set_for_object(Object_Id('pTest'), 0)

Więcej informacji na ten temat można znaleźć na MSDN: sp_describe_first_result_set
sys.dm_exec_describe_first_result_set
, sys.dm_exec_describe_first_result_set_for_object

Nowości w SQL Server 2012 – WITH RESULT SETS

2012-05-30 

W SQL Server 2012 polecenie EXECUTE rozszerzone zostało o dodatkową opcję WITH RESULT SETS. Pozwala ona na określenie oczekiwanej struktury danych zwracanych przez zapytanie lub procedurę składowaną. Jeżeli zwrócony wynik nie będzie zgodny z określoną definicją wygenerowany zostanie wyjątek. Omawiana opcja umożliwia także nadanie własnych nazw kolumnom wynikowego zbioru danych. Klauzula WITH RESULT SETS może przyjąć trzy opcje:

  • WITH RESULT SETS UNDEFINED – nieokreślona struktura danych wynikowych, jest to ustawienie domyślne
  • WITH RESULT SETS NONE – brak wynikowego zbioru danych, jeżeli operacja zwróci jakikolwiek zbiór wygenerowany zostanie wyjątek
  • WITH RESULT SETS <result_sets_definition> – definicja struktury wynikowego zbioru danych

Definicja wyniku (<result_sets_definition>) może być określona na cztery sposoby:

  • AS OBJECT – struktura danych wynikowych musi pokrywać się z danym obiektem (tabelą, widokiem, funkcją zwracającą tabelę)
  • AS TYPE – struktura danych wynikowych musi pokrywać się z danym typem tabelarycznym
  • AS FOR XML – dane zwracane przez polecenie muszą mieć format XML
  • <custom_definition> – własna struktura danych wynikowych (lista kolumn wraz z typami oraz dodatkowymi informacjami: null/not null, collate)

W wyniku wykonania polecenia EXECUTE może zostać zwróconych kilka zbiorów danych. Za pomocą WITH RESULT SETS możemy określić ich liczbę oraz strukturę każdego z nich. Co więcej, możemy w takim przypadku stosować różne typy definicji. Można na przykład określić, że dana procedura składowana musi zwrócić dwa zbiory danych, z czego pierwszy będzie miał strukturę zgodną z typem tabelarycznym, a drugi będzie w formacie XML.

W celu zaprezentowania działania opcji WITH RESULT SETS stworzyłem tabelę Obiekty, typ tabelaryczny tObiekty oraz procedurę składowaną PobierzObiekty. Procedura przyjmuje parametr typu int i w zależności od jego wartości zwraca różne wyniki (0 – brak zbioru wynikowego, 1 – jeden zbiór danych, 2 – dwa zbiory danych, 3 – wynik w formacie XML):

--Utworzenie tabeli Obiekty
create table Obiekty
(
    Id int not null,
    Nazwa nvarchar(512)
)

--Wstawienie 10-ciu rekordów
insert into Obiekty (Id, Nazwa)
select top 10 object_id, name
from sys.objects
order by object_id

--Utworzenie typu tabelarycznego tObiekty
create type tObiekty
as table
(
    Id int not null,
    Nazwa nvarchar(512)
)

--Utworzenie procedury PobierzObiekty
create procedure PobierzObiekty
    @wynik int
as
begin
    --0 - brak wyników
    if @wynik = 0
        return
    --1 - jeden zbiór wynikowy, 2 - dwa zbiory wynikowe
    if @wynik in (1, 2)
    begin
        select Id, Nazwa
        from Obiekty

        if @wynik  = 2
            select object_id, name, create_date
            from sys.objects

        return
    end
    --3 - wynik w formacie XML
    if @wynik = 3
    begin
        select Id, Nazwa
        from Obiekty as Obiekt
        for xml auto, root('Obiekty')

        return
    end
end

Przykłady użycia WITH RESULT SETS dla zapytania oraz procedury składowanej:

--Definicja wyniku dla zapytania
exec ('select * from Obiekty')
with result sets
(
    (
        Id int not null,
        Nazwa nvarchar(50)
    )
)

--Definicja wyniku dla procedury
exec PobierzObiekty 1
with result sets
(
    (
        Id int not null,
        Nazwa nvarchar(50)
    )
)

--Procedura nie zwracająca danych
exec PobierzObiekty 0
with result sets none

--Dowolny rodzaj wyniku lub jego brak
exec PobierzObiekty 1
with result sets undefined

--Definicja wyniku na podstawie tabeli
exec PobierzObiekty 1
with result sets
(
    as object Obiekty
)

--Definicja wyniku na podstawie typu tabelarycznego
exec PobierzObiekty 1
with result sets
(
    as type tObiekty
)

--Wynik w formacie XML
exec PobierzObiekty 3
with result sets
(
    as for xml
)

Przykłady użycia WITH RESULT SETS dla kilku zbiorów danych zwracanych przez procedurę składowaną z jednoczesną zmianą nazw kolumn tych zbiorów:

--Własne definicje dwóch wynikowych zbiorów danych
exec PobierzObiekty 2
with result sets
(
    (
        Identyfikator int not null,
        MojaNazwa nvarchar(50)
    ),
    (
        Identyfikator int not null,
        MojaNazwa nvarchar(50),
        DataUtworzenia datetime
    )
)

--Definicja na podstawie typu oraz własna
exec PobierzObiekty 2
with result sets
(
    as type tObiekty,
    (
        Identyfikator int not null,
        MojaNazwa nvarchar(50),
        DataUtworzenia datetime
    )
)

Jeżeli struktura zwracanych danych nie będzie zgodna z określoną definicją wygenerowany zostanie wyjątek (sprawdzenie nie dotyczy nazw kolumn):

--EXECUTE statement failed because its WITH RESULT SETS clause specified a non-nullable type for column #1 in result set #1, and the corresponding value sent at run time was null.
exec ('select null as Id, Nazwa from Obiekty')
with result sets
(
    (
        Id int not null,
        Nazwa nvarchar(50)
    )
)

--EXECUTE statement failed because its WITH RESULT SETS clause specified 0 result set(s), and the statement tried to send more result sets than this.
exec ('select * from Obiekty')
with result sets none

W pierwszym przypadku problemem są wartości NULL w kolumnie Id, w drugim zapytanie zwraca zbiór danych mimo, iż definicja na to nie pozwala.

Więcej informacji o poleceniu EXECUTE oraz opcji WITH RESULT SETS: MSDN

Nowości w SQL Server 2012 – sekwencje

2012-05-23 

W najnowszej wersji SQL Server pojawił się mechanizm sekwencji. Sekwencje przypominają Identity, z tą różnicą, że ich działanie nie ogranicza się do pojedynczej tabeli. Utworzona sekwencja jest globalnym obiektem bazy danych i może być wykorzystywana w wielu tabelach, ponadto można w łatwy sposób zarządzać nią z poziomu języka T-SQL. Na poszczególnych przykładach pokażę w jaki sposób korzystać z obiektów sekwencji.

Tworzenie/usuwanie

Najprostszy sposób utworzenia sekwencji:

create sequence dbo.Numerator

Przy tworzeniu sekwencji możemy określić dodatkowe parametry:

create sequence dbo.Numerator
    as int
    start with 1
    increment by 1
    minvalue 1
    maxvalue 100
    cycle
    cache 10
  • AS – typ wartości (tinyint, smallint, int, bigint, numeric), domyślnym typem jest bigint
  • START WITH – wartość początkowa, domyślnie minimalna/maksymalna wartość wybranego typu danych
  • INCREMENT BY – wartość o jaką sekwencja jest zwiększana/zmniejszana przy pobieraniu kolejnej wartości, domyślnie 1
  • MINVALUE – minimalna wartość sekwencji
  • MAXVALUE – maksymalna wartość sekwencji
  • CYCLE – opcja określa, czy po osiągnieciu wartości MAXVALUE/MINVALUE licznik sekwencji jest „przekręcany”
  • CACHE – liczba kolejnych wartości, które przy pobieraniu są buforowane w pamięci w celu ograniczenia operacji odczytu/zapisu z dysku, zwiększa to wydajność ale w przypadku wyłączenia serwera dochodzi do powstania luk w numeracji (dane z cache zostają utracone)

Usunięcie sekwencji:

drop sequence dbo.Numerator

Sekwencjami można również zarządzać z poziomu Management Studio, są one widoczne w gałęzi Programmability -> Sequences:

Resetowanie wartości

Istnieje możliwość zresetowania sekwencji do danej wartości początkowej:

alter sequence dbo.Numerator
restart with 3

Podgląd aktualnej wartości

W celu sprawdzenia aktualnej wartości sekwencji bez jej zwiększania/zmniejszania należy posłużyć się widokiem sys.sequences (kolumna current_value):

select current_value
from sys.sequences
where name = 'Numerator'

Pobieranie wartości

Do pobrania i jednoczesnej zmiany wartości sekwencji służy polecenie NEXT VALUE FOR:

select next value for dbo.Numerator

Przykłady zastosowania:

create table #Test (Id int, Nazwa nvarchar(128))

--Bezpośrednie pobranie wartości
insert into #Test (Id, Nazwa)
values (next value for dbo.Numerator, 'Nazwa_1')

--Pobranie wartości do zmiennej
declare @Numer int = next value for dbo.Numerator
insert into #Test (Id, Nazwa)
values (@Numer, 'Nazwa_2')

--Pobranie wartości dla każdego rekordu zapytania zgodnie z porządkiem w opcjonalnej klauzuli OVER
insert into #Test(Id, Nazwa)
select next value for dbo.Numerator over(order by name), name
from sys.objects

Możliwe jest również pobranie zakresu wartości za pomocą procedury sp_sequence_get_range:

declare
    @firstValue sql_variant,
    @lastValue sql_variant

--Pobranie 10 wartości
exec sp_sequence_get_range
    @sequence_name = 'dbo.Numerator',
    @range_size = 10,
    @range_first_value = @firstValue output,
    @range_last_value = @lastValue output

select @firstValue as PierwszyNumer, @lastValue as OstatniNumer

Transakcje

W przypadku pobrania wartości sekwencji w ramach rozpoczętej transakcji, a następnie odwołaniu tej transakcji, wartość sekwencji nie jest przywracana do stanu początkowego:

select current_value
from sys.sequences
where name = 'Numerator'

--Rozpoczęcie transakcji
begin tran
select next value for dbo.Numerator
--Wycofanie transakcji
rollback tran

select current_value
from sys.sequences
where name = 'Numerator'

Parametry MINVALUE, MAXVALUE, CYCLE

Poniższy kod tworzy sekwencję rozpoczynającą się od 4 z minimalną wartością 1 i maksymalną 5. Następnie trzykrotnie pobierana jest jej wartość:

if (object_id('dbo.Numerator ') is not null)
    drop sequence dbo.Numerator

create sequence dbo.Numerator
    as int
    start with 4
    increment by 1
    minvalue 1
    maxvalue 5

select next value for dbo.Numerator
select next value for dbo.Numerator
select next value for dbo.Numerator

Ostatnia próba pobrania zakończyła się błędem, ponieważ sekwencja osiągnęła maksymalną wartość. Jeżeli przy tworzeniu sekwencji dodamy parametr CYCLE, po osiągnięciu MAXVALUE jej wartość zostanie ustawiona na MINVALUE:

if (object_id('dbo.Numerator ') is not null)
    drop sequence dbo.Numerator

create sequence dbo.Numerator
    as int
    start with 4
    increment by 1
    minvalue 1
    maxvalue 5
    cycle

select next value for dbo.Numerator
select next value for dbo.Numerator
select next value for dbo.Numerator

Warto zauważyć, że w powyższej sytuacji wartość sekwencji nie jest ustawiana na parametr START WITH tylko na MINVALUE.

Jeżeli parametr INCREMENT BY miałby wartość ujemną, sekwencja po osiągnięciu MINVALUE zostałaby ustawiona na MAXVALUE.

Default kolumny

Sekwencji można użyć jako wartości DEFAULT dla kolumny tabeli:

create table Test
(
    Id int default(next value for dbo.Numerator),
    Nazwa nvarchar(128)
)

Więcej informacji o sekwencjach w SQL Server 2012: MSDN

Nowości w SQL Server 2012 – obsługa błędów i polecenie THROW

2012-05-14 

Jedną z nowości w SQL Server 2012 jest polecenie THROW służące do wywoływania wyjątków. Działa ono podobnie do znanej wcześniej funkcji RAISERROR. Zgodnie z MSDN w najnowszej wersji języka T-SQL zalecane jest stosowanie THROW zamiast RAISERROR. Omawiane polecenie ma dwa zastosowania. Pierwsze z nich polega na wywołaniu THROW z określonymi parametrami w celu wygenerowania własnego wyjątku. Drugim zastosowaniem jest wywołanie THROW bez argumentów w sekcji CATCH (tylko w tym miejscu jest to dozwolone) co pozwala na ponowne zgłoszenie przechwyconego wyjątku (propagowanie wyjątku).

THROW error_number, error_message, error_state

error_number – numer błędu (wartość INT nie mniejsza od 50000), w odróżnieniu od RAISERROR nie musi znajdować się w sys.messages
error_message – komunikat błędu
error_state – stan, wartość z przedziału 0 – 255

W przypadku polecenia THROW nie mamy wpływu na parametr severity (Level), zawsze jest on ustawiony na 16.

Przykład użycia:

THROW 50001, 'Własny komunikat błędu', 3;

Dotychczas propagowanie wyjątku z użyciem polecenia RAISERROR realizowane było w następujący sposób:

begin try
    select 1 / 0
end try
begin catch
    --Obsługa błędów

    --Propagowanie wyjątku
    declare
        @ErrorMessage nvarchar(4000) = ERROR_MESSAGE(),
        @ErrorSeverity int = ERROR_SEVERITY(),
        @ErrorState int = ERROR_STATE()

    RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)
end catch

Dzięki poleceniu THROW operacja ta staje się znacznie prostsza:

begin try
    select 1 / 0
end try
begin catch
    --Obsługa błędów

    --Propagowanie wyjątku
    THROW
end catch

Więcej informacji o nowościach w SQL Server 2012: MSDN