;WITH CTECommon Table Expression zostało przedstawione w SQL Server 2005. Wyrażenie ze słowem kluczowym WITH tworzy tymczasowy rezultat wynikowy, który przedstawia dane z pojedynczego polecenia “SELECT,INSERT,UPDATE,DELETE” albo z widoku “View”.

 

Na pierwszy rzut oka wydawało mi się ,że CTE działa podobnie jak tymczasowa tabela ,ale istnieje wiele różnic.

CTE (Common Table Expression)

  • Są nieindeksowane ,ale mogą używać istniejących indeksów do obiektowych, do których się referują.
  • W esencją są widokami, które są jednorazowe.
  • Istnieją, dopóki następne zapytanie nie zostanie uruchomione
  • Może być rekursywne

Tabele Tymczasowe

  • Są zmaterializowanymi prawdziwymi tabelami, które istnieją w tempdb
  • Mogą być zaindeksowane.
  • Istnieją na czas połączenia z bazą
  • Inne procedury i zapytania mogą referować się do tej samej tymczasowej tabeli.
  • Mają określone statystki wygenerowane przez silnik DB.

Tymczasowa tabela, jak i CTE są używane do zapamiętywania wyników, do których będziemy się odwoływać wiele razy w trakcie jednego zapytania. Oczywiście CTE, jak i tymczasowa tabela z powodu swoich różnic przydają się w różnych scenariuszach.

Jeśli chcemy by wynik był łatwo usuwalny oraz rekurencyjny wtedy korzystamy z CTE. Natomiast gdy mamy duży zbiór danych i chcemy aby one istniały na przestrzeni wielu zapytań wtedy korzystam z tabeli tymczasowej.

W tym wpisie użyjemy CTE ,aby skorzystać z jego największej zalety, czyli samo odwołaniu.

Rekurencja

Zacznijmy od koncepcji rekurencji. Oto prosty kod w C#.

public static string Rec(int level)
{
    if (level == 0)
        return "0";
    else return level.ToString() + "_" + Rec(level - 1);
}

Ten kod będzie się powtarzał, dopóki nie osiągnie poziomu 0. Wywołanie metody Rec(6) zwróci napis:

6_5_4_3_2_1_0

Oto co się zdarzyło w trakcie wywołania.

Rec(6) = 
    "6_" + Rec(5) = 
        "6_5_" + Rec(4) = 
            "6_5_4_" + Rec(3) = 
                "6_5_4_3_" + Rec(2) = 
                    "6_5_4_3_2_" + Rec (1) = 
                        "6_5_4_3_2_1_" + Rec(0) = 
                                "6_5_4_3_2_1_0"

Za każdym razem, gdy funkcja jest wywoływana poziom rekursji zmniejsza się o jeden od poprzedniego wywoływania. Gdy dojdzie do zera rekurencja się zakończy.

Trudniejszy przykład

image

Ten prosty przykład można by było przełożyć na zapytania do baz danych ,ale byłoby to zbyt proste i niepraktyczne.

O praktyczny przykład z rekurencją nie trudno. Ten przykład istnieje w wielu wpisach blogowych, jak i na MSDN.

System hierarchii pracowników.

Na obrazku po prawej widzisz pracowników ,którzy mają swoich podległy z wyjątkiem oczywiście głównego szefa. Możesz też zwrócić uwagę na poziomy hierarchii. Przykładowo Denis Kolokowski jest na poziomie 3, ponieważ jego szefowe też podlega komuś poziom wyżej.

Jak zobrazować taki związek w tabeli relacyjne?

image

Wystarczy do tego jedna tabela, która w kolumnie ManagerId referuje się do innego pracownika, czyli do tej samej tabeli ,ale do innego wiersza.

No, chyba że ktoś może być swoim własnym szefem.

Gdzie tutaj potrzebna jest rekurencja?

image

 

Załóżmy ,że rekordy w naszej tabeli pracownicy są pomieszane losowo i dostaliśmy zadanie wyświetlenia tych rekordów w kolejności od najwyższej postawionej osoby do najniższej.

Tak jak jest to ukazana na obrazku po lewej.

Ten problem nie może być rozwiązany słowem ORDER BY.

Musimy rekurencyjnie zaczynają od szefa wszystkich szefów spacerować po rekordach ,aby odnaleźć pracownika podległego pod nim ,a potem postępować analogicznie ,aż dojdziemy do pracowników na najniższym szczeblu.

W SQL Serwerze rekurencje można wykonać z CTE i bez. Pokaże ci obie metody, chociaż metoda używająca CTE jest dużo wygodniejsza.

Przykład z CTE

Na początku tworze tabelkę Employees ,a potem uzupełniam ją danymi.

CREATE TABLE #Employees
(
	EmployeeID INT PRIMARY KEY NOT NULL,
	ManagerID INT NULL,
	Name VARCHAR(50) NOT NULL,
	SurName VARCHAR(50) NOT NULL, 
	BirthDate DATETIME NULL
)

INSERT INTO #Employees (EmployeeID,ManagerID ,Name,SurName,BirthDate)
VALUES
(1,NULL,'Adam','Adamiecki','1954-12-12'),
(2,1,'Alicja','Kazikowska','1974-02-26'),
(3,2,'Denis','Kolokowski','1974-04-16'),
(4,3,'Marcin','Zdziłowski','1974-06-11'),
(5,3, 'Zdzislaw','BohaterGalaktyki','1988-11-12'),
(6,1,'Kamila','Krukowska','1973-02-23')

;WITH Emp_CTE AS (
		SELECT EmployeeID,ManagerID,Name,SurName, BirthDate
		FROM #Employees
		WHERE ManagerID IS NULL
		UNION ALL
		SELECT e.EmployeeID,e.ManagerID, e.Name,e.SurName,  e.BirthDate
		FROM #Employees e
		INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)

SELECT *
FROM Emp_CTE

DROP TABLE #Employees

Wyrażenie :WITH robi następującą rzecz. Na początku wykonuje się SELECT, który wybiera rekord o najwyższym szczeblu. Nie ma on swojego menadżera więc wiemy ,że ten pracownik stoi najwyżej w hierarchii.

Na tym etapie zmienna CTE Emp_CTE ma jeden rekord.

Później są wykonywane następne SELECT-y, które dodają następne rekordy do Emp_CTE, tylko jeżeli wystąpiło JOIN-owanie pomiędzy Manager.ID  a pracownikiem z poprzedniego rekordu.

W ten sposób w kroku drugim CTE Emp_CTE ma 3 rekordy, ponieważ Adamowi podlega Alicja i .Kamila.

Postępując dalej w taki sam sposób będziemy mieli listę pracowników zgodnie z ich poziomem w hierarchii.

image

Na końcu zapytania tabela tymczasowa jest likwidowana.

Jak widać rekurencja z CTE jest banalnie prosta ponieważ możemy odwołać się do jej samie wewnątrz zapytania które je tworzy.

Symulowanie CTE w stary sposób

Symulowanie zachowania bez użycia CTE wygląda następująco.

--CREATE TABLE #Employees
--Tak samo jak wczesniej

DECLARE  @EmployeesOLD  TABLE(
	EmployeeID INT PRIMARY KEY NOT NULL,
	ManagerID INT NULL,
	Name VARCHAR(50) NOT NULL,
	SurName VARCHAR(50) NOT NULL, 
	BirthDate DATETIME NULL,
	Pass int
)

DECLARE @Pass int
SET	@Pass = 0

INSERT INTO	@EmployeesOLD (EmployeeID,ManagerID,Name,SurName,BirthDate, Pass)
SELECT EmployeeID,ManagerID,Name,SurName, BirthDate,@Pass
		FROM #Employees
		WHERE ManagerID IS NULL

WHILE	 @@ROWCOUNT > 0
BEGIN
	-- increment pass
	SET @Pass = @Pass + 1
	
	-- insert next set
	INSERT INTO	@EmployeesOLD (EmployeeID,ManagerID,Name,SurName,BirthDate, Pass)
	SELECT	C.employeeID, C.ManagerID,C.Name,C.SurName,C.BirthDate, @Pass
	FROM	#Employees C
			inner join @EmployeesOLD  P 
				on C.ManagerID = P.EmployeeID
				and P.Pass = @Pass - 1 -- only look at rows from the previous pass
END

SELECT * FROM @EmployeesOLD ORDER BY Pass

--DROP TABLE #Employees

Symulacja wymaga użycia zmiennej typu TABLE albo tabeli tymczasowej, która będzie przechowywać te same kolumny co oryginalna tablica plus kolumnę “Pass”, która będzie określać poziom danego pracownika.

Analogicznie do ostatniego przykładu najpierw do zmiennej dodawany jest rekord najwyższego rzędu ma on wartość Pass na poziomie zerowym.

Następnie wykonujemy pętle “WHILE @@ROWCOUNT < 0” tak długo ,aż nie będzie więcej rekordów do zebrania. Czyli jeśli INSERT nie dodał nowych rekordów w poprzednim kroku wtedy pętla się zakończy. Zmienna “@@ROWCOUNT” określa liczbę dodanych wcześniej rekordów.

Po kroku pierwszym zmienna “@@ROWCOUNT” równa się jeden, ponieważ istnieje tylko jeden główny szef.

Pętla się wykonuje.

W wewnątrz pętli inkrementujemy zmienną @Pass o jeden. A następnie wykonuję insert-A do zmiennej typu TABLE. Insert ten dodaje tylko rekordy niższe od poprzedniego.

Ostatecznie aby otrzymać listę pracowników posortowaną po ich poziomach musimy wykonać polencie ORDER BY na kolumnie “Pass”.

image

Jak widać wyniki są identyczne jak w przypadku użycia CTE.

Symulowanie starego sposobu z CTE

Poprzedni przykład z CTE nie używał kolumny Pass. Jednak jej dodanie nie jest trudne. Uśmiech

;WITH Emp_CTE2 as (
    SELECT    EmployeeID,ManagerID,Name,SurName, BirthDate, Pass = 0
    FROM #Employees
	WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID,e.ManagerID, e.Name,e.SurName,  e.BirthDate, P.Pass + 1
		FROM #Employees  AS e
        INNER JOIN Emp_CTE2 P on e.ManagerID = P.EmployeeID    
)
SELECT * FROM Emp_CTE2

Zaczynamy od znalezienia głównego szefa, który ma pass równy 0. Potem ten rekord i dalsze rekordy są łączone tak samo jak wcześniej tylko dodajemy do nich Pass zwiększony o jeden.