wtorek, 28 kwietnia 2015

Synonimy

Jeżeli zdarza się wam używać połączonych serwerów i to w dodatku w dużej ilości zapytań to zdajecie sobie sprawę z tego, że odwoływanie sie do obiektów, korzystając z pełnej czteroczłonowej nazwy jest mało wygodne. Jeżeli dodać do tego fakt, że te same zapytania, czy to procedury składowane muszą wykonywać się zarówno na środowiskach programistycznych, testowych a później produkcyjnych to jest to po prostu proszenie się o telefony czy maile do osób odpowiedzialnych za wsparcie aplikacji.
 
Tworzenie kilku wersji procedury składowanej, dla poszczególnych środowisk nie jest optymalnym podejściem. Z pomocą przychodzą jednakże synonimy. Jest to jedna z funkcjonalności, które nie są prawdopodobnie bardzo powrzechnie wykorzystywane.

Zasadniczo synonimy upraszczają zapytania odwołujące się do serwerów połaczonych, ale także mogą być pomocne w przypadku gdy nazwa tabeli się zmienia a jest ona referencjonowana przez aplikację. Utworzenie synonimu ze starą nazwą tabeli a odwołującego się do nowej nazwy sprawi, że dla aplikacji korzystającej z tego typu obiektu zmiana będzie niezauważalna.

W przypadku serwerów połączonych używanie synonimów ma tą zaletę, że w przypadku gdy są one wykorzystywane np. w procedurach składowanych, to ta sama wersja może być wykorzystywana na różnych środowiskach. Jedyne co musi być specyficzne dla poszczególnych środowisk to obiekt synonimu.

Tworzenie synonimu jest bardzo proste. Założmy, że mamy serwer połączony o nazwie MYSERVER oraz instancji SQL2012. Na serwerze znajduje się baza danych MyRemoteDatabase i zawiera ona tabelę Products. Synonim dla tego typu obiektu wyglądałby następująco.
CREATE SYNONYM dbo.LinkedServerProducts

FOR [MYSERVER\SQL2012].MyRemoteDatabase.dbo.Products

GO


Aby odwołać się teraz do obiektu Products na bazie danych MyRemoteDatabase wystarczy następujące zapytanie.


SELECT * FROM dbo.LinkedServerProducts



Powyższe zapytanie to w dużej mierze uproszczenie. O tym dlaczego nie warto używać SELECT * FROM pisałem w tym miejscu.

Synonimy, choć niezbyt często używane mogą być bardzo przydatne. Szczególnie, gdy zachodzi konieczność odwoływania się do obiektów na serwerach połączonych.

czwartek, 2 kwietnia 2015

Sortowanie wyników w operacjach złączenia pionowego

Jakiś czas temu pisałem o indeksach (część 1, część 2) klastrowych oraz o ich wpływie na sortowanie wyników. Pomimo tego, że dwa wspomniane wpisy dotyczyły pewnych niuansów, na które warto zwrócić uwagę w czasie pracy indeksami klastrowymi, to pokazałem także, że poleganie na indeksach w celu uzyskania posortowanych wyników, może być bardzo zawodne. Przywołałem ten temat jako wstęp do sortowania wyników w przypadku operacji złączenia pionowego (UNION/UNION ALL). A wspomniałem o tym dlatego, że jest spora grupa osób, które myśli, że mając określony indeks w bazie danych i wykorzystując go w zapytaniu uzyskamy wyniki posortowane wg klucza tego indeksu. To jest oczywiście mitem i jedynym sposobem na uzyskanie wyników posortowanych według jakiejś określonej kolejności jest użycie klauzuli ORDER BY.
W przypadku unii sortowanie wyników wg pożądanego schematu może przysporzyć trochę problemów.

W kolejnych akapitach przedstawię kilka różnych klas problemów z jakimi możecie mieć do czynienia w przypadku sortowania zapytań korzystających z UNION ALL. Wszystkie przykłady będą się opierać na dwóch niewielkich zmiennych tabelarycznych.
DECLARE @TABLE1 TABLE (ID INT, TITLE VARCHAR(100))

INSERT INTO @TABLE1 VALUES

(1, 'Item 1'),

(2, 'Item 2'),

(3, 'Item 3')

 

DECLARE @TABLE2 TABLE (ID INT, TITLE VARCHAR(100))

INSERT INTO @TABLE2 VALUES

(1, 'Item 4'),

(2, 'Item 5'),

(3, 'Item 6'),

(4, 'Item 7')

Sortowanie całego zbioru wyników

Posortowanie całego zbioru wyników jest operacją najprostszą do wykonania. Po prostu wystarczy dodać klauzulę ORDER BY na końcu zapytania.

Poniższe zapytanie posortuje rekordy z obu tabel w kolejności ID

SELECT ID, TITLE FROM @TABLE1 

UNION ALL

SELECT ID, TITLE FROM @TABLE2

ORDER BY ID

Inna kolejność sortowania pierwszego i drugiego zapytania

Posortowanie wyników poszczególnych zapytań w innej kolejności niestety ale nie może być rozwiązane poprzez dodanie ORDER BY do pierwszego zapytania (tuż przed UNION ALL). Operacja sortowania jest ostatnią operacją, jaka jest wykonywane przez SQL Server w momencie wykonywania zapytań.

W przypadku gdyby wymaganiem było wyciągnięcie tylko części wyników z pierwszej i drugiej tabeli z pomocą przychodzą podzapytania z klauzulą TOP.

Poniższe zapytanie wyciąga po dwa rekordy z każdej tabel, przy czym wyniki z pierwszej tabeli są sortowanie rosnąco a z drugiej malejąco.

SELECT ID, TITLE FROM (SELECT TOP 2 ID, TITLE FROM @TABLE1 ORDER BY ID ASC) AS Q1

UNION ALL

SELECT ID, TITLE FROM (SELECT TOP 2 ID, TITLE FROM @TABLE2 ORDER BY ID DESC) AS Q2

Czasami taki rezultat może okazać się pożądany, ale zapewne częściej bardziej oczekiwanym wynikiem będą wszystkie rekordy pochodzące z tabeli pierwszej i drugiej.

Skoro w powyższym przykładzie zadziałał TOP to wystarczy go trochę zmodyfikować.

DECLARE @TOP1 INT

SELECT @TOP1 = COUNT(*) FROM @TABLE1 

 

DECLARE @TOP2 INT

SELECT @TOP2 = COUNT(*) FROM @TABLE2

 

SELECT ID, TITLE FROM (SELECT TOP (@TOP1) ID, TITLE FROM @TABLE1 ORDER BY ID ASC) AS Q1

UNION ALL

SELECT ID, TITLE FROM (SELECT TOP (@TOP2) ID, TITLE FROM @TABLE2 ORDER BY ID DESC) AS Q

Tym razem zamiast stałej, użyta została zmienna, zawierająca ilość rekordów z poszczególnych tabel. Rozwiązanie to ma jeden mały minus. Jest bardzo nieefektywne. Po prostu aby osiągnąć zamierzony efekt potrzeba przeskanować dodatkowo każdą z tabel aby uzyskać ilość rekordów. Czyli w sumie wychodzą 4 operacje skanowania. Nie jest to nic dobrego w przypadku dużych tabel.

Kolejną opcją jest skorzystanie z TOP x PERCENT.

SELECT ID, TITLE FROM (SELECT TOP 66.7 PERCENT ID, TITLE FROM @TABLE1 ORDER BY ID DESC) DATA

UNION ALL

SELECT ID, TITLE FROM (SELECT TOP 100 PERCENT ID, TITLE FROM @TABLE2 ORDER BY ID DESC) DATA

Co ciekawe w powyższym przykładzie pierwsze zapytanie zwraca wyniki poprawnie posortowane, natomiast w przypadku drugiego operacja sortowanie nie miała miejsca. TOP 100 PERCENT był swego czasu defektem w SQL Serverze, jednakże problem ten został naprawiony w wersji SQL 2005. Tak naprawdę gdy SQL widzi TOP 100 PERCENT to ignoruje to zupełnie podczas tworzenia planu wykonania. Podejrzenie właściwego planu wykonania tylko to potwierdza.

unionTop100Percent

Jeżeli chodzi o pierwszą część zapytania, która zwróciła swoje wyniki w odpowiedniej kolejności użyłem 66.7%. Dlatego akurat takiej wartości? Ano dlatego, że 0.667 * 3 (ilość rekordów w tabeli) = 2.001. Po zaokrągleniu w górę daje to 3, czyli sumę wszystkich rekordów. Takie rozwiązanie może być skuteczne tylko w przypadku dobrania odpowiedniej liczby. Czyli znowu jest to nieefektywne a w dodatku także może być nieefektowne rozwiązanie.

Czy istnieje więc sposób by pierwsze i drugie zapytanie mogło być sortowane wg innej kolejności bez korzystania uprzednio np. z tabel tymczasowych?

Tak się składa, że coś takiego, można w dość prosty sposób osiągnąć za pomocą starego dobrego przyjaciela czyli ROW_NUMBER.

SELECT ID, TITLE FROM (

SELECT 1 AS TABLESEQUENCE, ROW_NUMBER() OVER(ORDER BY ID ASC) AS RN, ID, TITLE FROM @TABLE1

UNION ALL

SELECT 2 AS TABLESEQUENCE, ROW_NUMBER() OVER(ORDER BY ID DESC) AS RN, ID, TITLE FROM @TABLE2 

) AS ORDEREDUNION

ORDER BY TABLESEQUENCE, RN

W powyższym przykładzie złączenie jest realizowane w ramach podzapytania. Każde z zapytań poza właściwymi danymi zwraca także odpowiednią kolejność i później w zapytaniu zewnętrznym wyniki są odpowiednio sortowane. Jest to jednakże dość kosztowne zapytanie i w przypadku dużych tabel może zająć relatywnie dużo czasu. Wynika to z faktu, że występują tutaj 3 operacje sortowania, które są dość kosztowne.

Korzystając z powyższej techniki można także rozwiązać problem generowania odpowiedzi w formacie CSV, gdzie pierwszy wiersz ma być nagłówkiem zwierającym listę kolumn.


SELECT DATA FROM (

SELECT 'ID|TITLE' AS HEADER, 'A' AS RN

UNION ALL

SELECT CAST(ID AS VARCHAR) + '|' + TITLE AS DATA, CAST(ROW_NUMBER() OVER(ORDER BY ID) AS VARCHAR) AS RN FROM @TABLE1 

) AS RESULTS(DATA, RN)

ORDER BY RN DESC


W powyższym przykładzie właściwe dane będą posortowane malejąco. Natomiast gdyby sortowanie miało być rosnąco to wystarczy zmienić RN w nagłówku na 0.


SELECT DATA FROM (

SELECT 'ID|TITLE' AS HEADER, '0' AS RN

UNION ALL

SELECT CAST(ID AS VARCHAR) + '|' + TITLE AS DATA, CAST(ROW_NUMBER() OVER(ORDER BY ID) AS VARCHAR) AS RN FROM @TABLE1 

) AS RESULTS(DATA, RN)

ORDER BY RN ASC

Powyższe przykłady pokazały w jaki sposób, można posortować wyniki biorące udział w złączeniu pionowym według indywidualnej kolejności sortowania dla każdego z zapytań.

Zapraszam do eksperymentowania. Polecam testowanie z wykorzystaniem sys.dm_db_index_physical_stats oraz sys.partitions. Zarówno DMF jak i widok katalogowy zawierają informacje na temat ilości rekordów. Jednakże nie zawsze wyniki te będą zgodne z tym co zwróci funkcja COUNT.

poniedziałek, 30 marca 2015

7 powodów dlaczego nie powinno się używać SELECT * FROM

Nie tak dawno temu, ktoś zadał pytanie dlaczego nie powinno się używać konstrukcji SELECT * FROM . Większość osób po prostu zwraca uwagę na to, że nie powinno się tego robić, nie wspominając dlaczego tak jest.

Mam nadzieję, że te kilka poniższych punktów pozwoli Wam lepiej zrozumieć dlaczego po prostu tego typu zapytania nie powinny być tworzone,

1. SQL Server przed wykonaniem zapytania musi ustalić nazwy kolumn jakie pojawią się jako rezultat zapytania. Można to dość łatwo zweryfikować używając Profilera a w nim zdarzenia RPC:Completed.

2. Zwracanie wszystkich kolumn, szczególnie tych, które nie będą później wykorzystane jest tylko stratą zasobów. Jeżeli tego typu zapytanie pochodzi z aplikacji, to bardzo prawdopodobne jest to, że dane te będą przesyłane po sieci. W zależności od ilości kolumn zwróconych a nie używanych w aplikacji a także od wolumenu danych, może się okazać ze ilość danych nikomu nie potrzebnych a przesłanych może być przeliczana w MB lub GB.

3. W przypadku gdy tylko podzbiór danych jest potrzebny i jest on zawarty w indeksie nieklastrowym, zapytanie SELECT * FROM spowoduje, że indeks ten nie będzie wykorzystywany, lub co gorsza będzie wykorzystany w połączeniu z operacją key lookup lub bookmark lookup.

4. Większa ilość fizycznych i logicznych operacji odczytu. SQL Server zapisuje dane czy to z tabeli czy indeksu na stronach, które mogą zawierać nie więcej niż 8096 bajtów. W przypadku gdy wszystkie kolumny, które rzeczywiście są potrzebne znajdują się w indeksie nieklastrowym oraz indeks ten nie zawiera wszystkich kolumn z tabeli, każda ze stron indeksu będzie zawierać więcej danych aniżeli analogiczna strona indeksu klastrowego lub też tabeli.

5. SQL Server przechowuje wyniki zapytań w pamięci podręcznej, tak by w przypadku ponownego wykonania tego samego zapytania odpowiedź mogła być zwrócona szybciej i przy wykorzystaniu mniejszej ilości zasobów. Jeżeli zapytanie zwraca niepotrzebnie dane, które nie będą wykorzystane to będą one mimo wszystko zapisane w pamięci podręcznej. Pamięć podręczna jest ograniczona i w przypadku gdy jej brakuje SQL Server uruchomi mechanizm czyszczenia. Może to skutkować tym, że dane, które rzeczywiście są potrzebne w pamięci podręcznej zostaną usunięte.

6. Jeżeli SELECT * FROM jest używane by wypełnić tabelę danymi, może się okazać, że w przyszłości liczba kolumn się zmieni i wcześniej stworzone zapytanie po prostu zwróci błąd.

7. Czytelność kodu. Zapytanie bez wylistowanych kolumn jest po prostu mniej czytelne. Lenistwo nie powinno usprawiedliwiać pogarszania jakości tworzonego kodu.

Mam nadzieję, że powyższe 7 powodów dlaczego nie powinno się używać zapytania w stylu SELECT * FROM przekonało Was aby w przyszłości pisać lepszej jakości kod.

wtorek, 24 marca 2015

Optymalizacja baz danych z wykorzystaniem magazynu danych zarządzania.

SQL Server oferuje bardzo ciekawe narzędzie, pozwalające monitorować różne aspekty pracy serwera. Magazyn danych zarządzania (Management Data Warehouse) jest bazą danych, która zawiera informacje miedzy innymi na temat zapytań, które były wykonywane na poszczególnych bazach danych.

Magazyn danych zarządzania może być stworzony przy pomocy kreatora w kilka minut.

MDW

Jeżeli baza danych nie istnieje trzeba ją najpierw utworzyć. Drugim krokiem jest skonfigurowanie zbierania danych z serwera, bądź też serwerów, jako że można użyć jednej bazy danych do przechowywania informacji z wielu instancji SQL Servera.

Magazyn danych zarządzania ma wbudowane 3 raporty. Jeżeli chodzi o optymalizację zapytań to Query Statistics History na początek będzie zdecydowanie wystarczający.

Raport zwraca zapytania, które można sortować wg zużycia procesora, czasu wykonania, operacji wejścia/wyjścia, fizycznych operacji odczytu i logicznych operacji odczytu.

Duża ilość fizycznych operacji odczytu może być rezultatem brakującego warunku w zapytaniu. Warto zwrócić uwagę na ten raport, gdyż może on podpowiedzieć gdzie przydałaby się optymalizacja, lub też które z zapytań zawiera błąd logicznych. Brak warunku w zapytaniu może zwyczajnie być błędem, na który ktoś nie zwrócił wcześniej uwagi.

Podumowując magazyn danych zarządzania oferuje podstawową funkcjonalność pozwalającą monitorować serwer bazodanowy. Ciekaw jestem ilu z was wykorzystuje tą funkcjonalność, gdyż prawdopodobnie nie jest to coś o czym często można usłyszeć.

czwartek, 12 marca 2015

Schemat bazy danych

Wszystkie obiekty w bazie danych są pogrupowane w zbiorze zwanym schematem. Schemat to kontener zawierający obiekty i do którego można w prosty sposób nadac uparwnienia. Zamiast nadawać uprawnienia na poszczególne obiekty, można to zrobić na wyższym czyli na schemacie.
Nazwa schematu jest częścią nazwy obiektu. Pełna kwalifikowana nazwa obiektu składa się z 4 części. Są to
  • Nazwa serwera
  • Nazwa bazy danych
  • Nazwa schematu
  • Nazwa obiektu
W momencie kiedy obiekt jest tworzony bez podania nazwy schematu, to jest on tworzony w schemacie domyślnym użytkownika tworzącego obiekt.

Zastosowanie a także konsekwensje używania uproszczonej nazy obiektów, bez podania nazwy schematu zaprezentuje na krótkim przykładzie.

Poniższy skrypt tworzy testową bazę danych, loginy, użytkowników, schemat oraz nadaje odpowiednie uprawnienia.
USE [master]

GO

 

--Create Database

CREATE DATABASE [SchemaTest]

GO

 

USE [SchemaTest]

GO

 

--Create logins, users and schema

CREATE LOGIN [MySchemaLogin] WITH PASSWORD='T0PSecret', DEFAULT_DATABASE=[SchemaTest], DEFAULT_LANGUAGE=[us_english]

GO

 

CREATE USER [MySchemaUser] FOR LOGIN [MySchemaLogin]

GO

 

CREATE SCHEMA [MySchema] AUTHORIZATION [MySchemaUser]

GO

ALTER USER [MySchemaUser] WITH DEFAULT_SCHEMA=[MySchema]

GO

 

CREATE LOGIN [dboSchemaLogin] WITH PASSWORD='T0PSecret', DEFAULT_DATABASE=[SchemaTest], DEFAULT_LANGUAGE=[us_english]

GO

 

CREATE USER [dboSchemaUser] FOR LOGIN [dboSchemaLogin]

GO

 

ALTER USER [dboSchemaUser] WITH DEFAULT_SCHEMA=[dbo]

GO

 

--Grant Permissions

GRANT CREATE TABLE TO [MySchemaUser]

GO

GRANT INSERT TO [MySchemaUser]

GO

GRANT SELECT TO [MySchemaUser]

GO

 

GRANT ALTER ON SCHEMA::[dbo] TO [MySchemaUser]

GO

GRANT INSERT ON SCHEMA::[dbo] TO [MySchemaUser]

GO

GRANT SELECT ON SCHEMA::[dbo] TO [MySchemaUser]

GO

 

GRANT ALTER ON SCHEMA::[dbo] TO [dboSchemaUser]

GO

GRANT INSERT ON SCHEMA::[dbo] TO [dboSchemaUser]

GO

GRANT SELECT ON SCHEMA::[dbo] TO [dboSchemaUser]

GO


Teraz loguje się do serwera korzystając z loginu MySchemaLogin.

Poniższy skrypt tworzy dwie tabele MyTable w dwóch różnych schematach a także dodaje po jednym wierszu do każdej z tabel.


CREATE TABLE MyTable (

    Greeting VARCHAR(100)

)

GO

 

INSERT INTO MyTable (Greeting) VALUES ('Hello World from MyTable in MySchema schema')

GO

 

CREATE TABLE dbo.MyTable (

    Greeting VARCHAR(100)

)

GO

 

INSERT INTO dbo.MyTable (Greeting) VALUES ('Hello World from MyTable in dbo schema')

GO


Pomimo tego, że tabele ozornie nazywają się tak samo, zapytanie poprawnie się wykonało.

Poniższa kwerenda weryfikuje istnienie dwóch tabel o nazwie MyTable w bazie danych.


SELECT SCHEMA_NAME(schema_id) AS SchemaName, name AS TableName FROM sys.tables WHERE name ='MyTable'


SchemaNameTableName
dboMyTable
MySchemaMyTable


Teraz czas sprawdzić co się stanie w przypadku gdy będąc zalogowanym na loginie MySchemaLogin odpytana zostanie tabela MyTable.


SELECT 1 AS QueryID, Greeting FROM MyTable UNION ALL

SELECT 2 AS QueryID, Greeting FROM dbo.MyTable 



Pierwsze z zapytań odwołuje się do tabeli bez jawnie podanego schematu. Jak już wcześniej wspomniałem w przypadku kiedy schemat nie zostanie uwzględniony w zapytaniu, SQL Server będzie próbował znaleść obiekt MyTable w domyślnym schemacie użytkownika.

Wynik zapytanie jest następujący

QueryIDGreeting
1Hello World from MyTable in MySchema schema
2Hello World from MyTable in dbo schema


W przypadku drugiego zapytania nazwa obiektu została podana razem ze schematem.

No to jeszcze zobaczmy co się stanie w przypadku kiedy do zerwera zalogujemy się na loginie dboSchemaLogin i wywołamy te same zapytanie co powyżej.
QueryIDGreeting
1Hello World from MyTable in dbo schema
2Hello World from MyTable in dbo schema


Tym razem oba zapytanie zwrócily ten sam wynik. Stało się tak dlatego, że domyślnym schematem dla użytkownika powiązanego z loginem dboSchemaLogin jest dbo.

Podsumowując, dobrą praktyką jest używanie nazw dwuczłonowych. Pozwala to uniknąć niespodzianek zwiazanych z tym, gdy użytkownik tworzący obiekty, nie ma domyślnego schematu ustawionego na taki na którym większośc zapytań do bazy dancyh jest wykonywanych. W niektórych sytuacjach użycie nazw dwuczłonowych jest wręcz wymagane. Dwuczłonowe nazwy są jednym z wymagań podczas tworzenia materializowanych widoków.

Na koniec jeszcze skrypt czyszczący obiekty wykorzystane w powyższych przykładach.


USE [master]

GO

 

DECLARE @SQL VARCHAR(MAX)

DECLARE @SESSION_ID INT

DECLARE SESSIONDROP CURSOR FAST_FORWARD FOR SELECT session_id FROM sys.dm_exec_sessions WHERE login_name IN ('MySchemaLogin', 'dboSchemaLogin')

OPEN SESSIONDROP

FETCH NEXT FROM SESSIONDROP INTO @SESSION_ID

WHILE @@FETCH_STATUS = 0

BEGIN

    SET @SQL = 'KILL ' + CONVERT(VARCHAR(10), @SESSION_ID)

    EXEC (@SQL)

 

    FETCH NEXT FROM SESSIONDROP INTO @SESSION_ID    

END

 

CLOSE SESSIONDROP

DEALLOCATE SESSIONDROP

 

DROP LOGIN MySchemaLogin 

GO

 

DROP LOGIN dboSchemaLogin 

GO

 

ALTER DATABASE [SchemaTest] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

 

DROP DATABASE [SchemaTest]

GO

środa, 11 marca 2015

Hierarchie i CTE

Do napisania tego posta zainspirował mnie temat na forum wss.geekclub.pl. Czasami zachodzi konieczność przechowywania w bazie danych hierarchi. Najprostszym przykładem tego typu danych może byc lista pracowników wraz z ich wzajemnymi powiązaniami.

Dzięki wykorzystaniu rekursywnych CTE w bardzo prosty sposób można stworzyć zapytanie, które zwróci wyniki w kolejności hierarchi organizacyjnej.

Załóżmy, że mamy następującą tabelę.

EmployeeId JobTitle ManagerId
15 General Manager 16
16 CEO NULL
25 Development Manager 15
10 Team Leader 25
11 Software Developer 10
declare @employees table (

    EmployeeId int,

    JobTitle varchar(255),

    ManagerId int

)

 

insert into @employees values 

(15,'General Manager', 16), (16, 'CEO', null), (25,'Development Manager',15), 

(10, 'Team Leader', 25), (11, 'Software Developer', 10)


Numery identyfikacyjne celowo są w losowej kolejności i nie są one bezpośrednio powiązne z hierarchią organizacyjną.
Założmy, że potrzebujemy pełną strukturę ogranizacyjna w kolejności poczynając od najważniejszej osoby w organizacji.

EmployeeIdJobTitle
16CEO
15General Manager
25Development Manager
10Team Leader
11Software Developer

Powyzszy problem może być rozwiązany za pomocą następującego zapytania


;with hierarchy as

(

select EmployeeId, JobTitle, 1 as level from @employees where ManagerId is null

 

union all

 

select e.EmployeeId, e.JobTitle, level + 1 from @employees e

inner join hierarchy e2 on e.ManagerId = e2.EmployeeId

)

 

select EmployeeId, JobTitle From  hierarchy order by level asc


Założmy teraz, że jesteśmy zainteresowani tylko szefem działu wraz ze wszystkimi osobami które do niego raportują.
EmployeeIdJobTitle
25Development Manager
10Team Leader
11Software Developer

Aby osiągnąć powyższy wynik wystarczy zmodyfikować predykat w pierwszym zapytaniu.


;with hierarchy as

(

select EmployeeId, JobTitle, 1 as level from @employees where EmployeeId = 25

 

union all

 

select e.EmployeeId, e.JobTitle, level + 1 from @employees e

inner join hierarchy e2 on e.ManagerId = e2.EmployeeId

)

 

select EmployeeId, JobTitle From  hierarchy order by level asc


Podsumowując, za pomocą CTE można w bardzo łatwy sposób napisać zapytania, które będą zaróno eleganckie jak i skuteczne.