Numeracja wierszy w SQL Server – DENSE_RANK

W poprzednim poście zajęliśmy się funkcją RANK. Dla przypomnienia umożliwia ona numerację wierszy z rozróżnieniem wartości.  Dla dokładnego opisu odsyłam tutaj. Jedną z cech RANK jest fakt, że w przypadku remisów (takich samych wartości) licznik wewnętrzny jest zwiększany, czego rezultatem są przerwy w numeracji. W przypadku DENSE_RANK opisane przerwy nie istnieją. Rozważmy poniższe zapytanie:

select DENSE_RANK() over(order BY ProductSubCategoryID),ProductSubcategoryID from Production.Product;

Możliwy wynik to:

 

DENSE_RANK ProductSubCategoryId
1 5
1 5
1 5
2 6
2 6
2 6

W przypadku RANK byłoby to:

RANK ProductSubCategoryId
1 5
1 5
1 5
4 6
4 6
4 6

 

Funkcję różnią się wyłącznie obsługa tzw. remisów. DENSE_RANK nie powoduje przerw w numeracji, z kolei w przypadku RANK takowe przerwy powstają.

Numeracja wierszy w SQL Server – RANK

W poprzednim poście przedstawiłem funkcję ROW_NUMBER. RANK działa analogicznie z tym, że wierszom o tej samej wartości nadawane są takie same liczby porządkowe. Rozważmy poniższe zapytanie:

select RANK() over(order BY ProductSubCategoryID),ProductSubcategoryID from Production.Product;

Wiersze o takim samym ProductSubCategoryId otrzymają równe wartości a nie kolejne liczby jak w przypadku ROW_NUMBER. Przykładowo zwrócone wiersze:

 

RANK ProductSubCategoryId
1 5
1 5
1 5
4 6
4 6
4 6

 

Warto zwrócić uwagę, że RANK nie zwraca kolejnych liczb. W przypadku gdy występuje kilka takich samych wartości, wewnętrzny licznik jest zwiększany i powstaje potem luka– na przykładzie jest to 1 a 4 (zamiast 2).

Numeracja wierszy w SQL Server – ROW_NUMBER

Czasami wykonując zapytania T-SQL potrzebujemy ponumerować wiersze. Baza SQL Server dostarcza nam kilka różnych funkcji.

Podstawową funkcją jest ROW_NUMBER, która zwraca dla danego zbioru wartości od 1 do n. W przypadku gdy dwa zwrócone wiersze zawierają identyczne dane, ROW_NUMBER traktuje je jako różne i nadaje im kolejne numery. Przykład:

select ROW_NUMBER() over(order BY NAME),ProductSubcategoryID from Production.Product;

W klauzuli over określa się m.in. sortowanie. Za pomocą tego ROW_NUMBER wie, jaka wartość jest tak  naprawdę pierwsza w zbiorze.

Można również rozbić zbiór na kilka podzbiorów za pomocą PATRITION BY:

select ROW_NUMBER() over(PARTITION BY ProductSubcategoryID order BY NAME ),ProductSubcategoryID from Production.Product;

W tej chwili wiersze o różnych ProductSubcategoryID będą numerowane osobno. Wynik będzie zatem wyglądać mniej więcej tak:

 

ROW_NUMBER ProductSubcategoryID
1 1
2 1
3 1
4 1
1 2
2 2
3 2
1 5
2 5
3 5

 

W następnym poście zajmiemy się funkcją RANK.

Do czego służy atrybut SCHEMABINDING?

W wielu DDL można ustawiać atrybut SCHEMABINDING. Do czego on służy?

Ustawienie SCHEMABINDING powoduje, że dany obiekt (widok, funkcja itp.) jest powiązany z wszystkimi innymi obiektami do których się odwołuje. Jeśli zatem widok korzysta z jakieś tabeli to nie może zostać ona zmieniona lub usunięta ponieważ naruszyłoby to strukturę widoku. Użycie SCHEMABINDING jest sztywnym powiązaniem i zapobiega przypadkowemu usunięcia powiązanych obiektów.

Znaczenie atrybutu XACT_ABORT

Aby wyjaśnić znaczenie atrybutu stwórzmy najpierw tabelę składają się wyłącznie z jednej kolumny – klucza głównego. Ponadto nie ustawiajmy IDENTITY dla tej kolumny. Następnie spróbujmy wykonać 2 poniższe  insert’y:

begin transaction
insert into TestSet (ID) values(1);
insert into TestSet (ID) values(1);
commit transaction;

Przy drugim insercie wyskoczy błąd. To jest oczywiste ponieważ klucz główny musi być unikalny. Jeśli jednak odpalimy select’a przekonamy się, że wartości z pierwszego insert’a zostały wstawione (1). Dzieje się tak ponieważ domyślnie transakcja nie jest anulowana w przypadku wystąpienia błędu. Aby temu zaradzić należy ustawić właśnie zmienną XACT_ABORT na true:

SET XACT_ABORT on
go
begin transaction
insert into TestSet (ID) values(1);
insert into TestSet (ID) values(1);
commit transaction;

XACT_ABORT powoduje, że w przypadku wystąpienia błędu cała transakcja jest anulowana (rollback). Do bazy nie zostanie dodany żaden wpis – nawet pierwszy insert, który nie powoduje błędu.

Liczba miejsc po przecinku w decimal

Pisząc kod odpowiedzialny za walidację liczb potrzebowałem sprawdzić ile miejsc po przecinku ma dany decimal. Użyłem rozwiązania może mało eleganckiego ale przynajmniej działającego:) :

decimal decimalNumber = 21.235;
int length = (decimalNumber % 1).ToString().Length - 2;

Reszta z dzielenia przez jeden zawsze zwraca to co jest po przecinku. Dla 21.235 będzie to 0.235. Zatem długość string’a minus dwa ( jeden dla przecinka, jeden dla zera) stanowi liczbę miejsc po przecinku.

Może komuś się to przyda w przyszłości :).

Egzamin 70-433 zdany

Wczoraj przystąpiłem do 70-433 – SQL Server 2008 dla programisty. Polecam wszystkim zapoznanie się z training kitem przygotowującym do tego egzaminu – na pewno dowiecie się czegoś nowego o MSSQL. Jeśli chodzi o sam egzamin, pytania standardowo bardzo zróżnicowane. Jak zwykle nie zabrakło pytań, na które aby odpowiedzieć trzeba znać po prostu dokumentację – dla mnie jest to bezsensowne. Większość jednak dotyczyła zapytań SQL (przeróżnych, nie tylko tych “klasycznych”) oraz optymalizacji. Jak łatwo zauważyć od jakiegoś czasu sporo postów dotyczy SQL Server’a. W najbliższym czasie postaram się przedstawić kolejne ciekawe aspekty SQL Server, które nie są moim zdaniem zbyt popularne (np. query hints).

Hierarchyid, ciąg dalszy

W poprzednim poście zaprezentowałem sposób tworzenia drzewa w tabeli wykorzystując typ hierarchyid. W dzisiejszym poście z kolei chciałbym pokazać kilka ułatwień jakie daje nam ten nowy typ.

Każdy węzeł jest reprezentowany za pomocą stringu. Dla korzenia jest to “/”, dla potomstwa “/1”, “/2” itd. Schodząc w dół drzewa otrzymujemy “/1/1”, “/1/1/1” itd. Bardzo łatwo wiec dostać się do konkretnego węzła:

select * from Tree where Node=cast('/1/' as hierarchyid);

Typ hierarchyid posiada wiele metod. Jedną z ciekawszych jest Getlevel:

select Node.GetLevel(),Node from Tree ;

image

Wyświetlenie więc wszystkich węzłów na wskazanym poziomie nie jest już problemem:

select Node.GetLevel(),Node from Tree where Node.GetLevel()=1;

Ponadto do dyspozycji mamy metody typu GetAncestor lub IsDescedantOf.