Makale Özeti

SQL Server 2005 ile beraber gelen, view'lere benzerliği ile dikkat çeken bir yapı olan Common Table Expression(CTE)'yi inceliyor olacağız bu yazımızda...

Makale

SQL Server 2005 ile beraber gelen, view'lere benzerliği ile dikkat çeken bir yapı olan Common Table Expression(CTE)'yi inceliyor olacağız bu yazımızda.

View'lere çok benzeyen yapılarıyla karşımızda olan CTE'ler türetilmiş tablolara göre çok daha gelişmiş özelliklere sahiptir. Oluşturulduktan sonra aynen view ve türetilmiş tablolarda olduğu gibi FROM anahtar kelimesiyle bir tabloymuşcasına kullanılabilmenin yanında artı bir özellik olrak CTE'ler sadece bir defa tanımlanmak suretiyle SQL uygulaması içerisinde defalarca kullanılabilirler. Bu özelliği sayesinde sürekli karşısında olduğumuz bir kavram olan kod tekrarının önüne geçmeyi başarabilirler.

CTE'lerin bir diğer avantajı olarak veritabanı üzerinde sürekli tutulmayan, anlık olarak yaratılıp uygulama çalışmasının tamamlanmasının ardından yok olmaları sayılabilir. Bu sayede özellikle türetilmiş tablo ve view'lerde karşımıza çıkan unique isimlendirme, veritabanı performansında eksi yönde gelişmeler ve kod tekrarı engellenmiş olacaktır.

CTE tanımlama şekli :

[WITH <common_table_expression> [,...n] ]
<common_table_expression>::=
  expression_name
  [(column_name [,...n])]
  AS
  (<CTE_query_expression>)

Bu syntax'da WITH anahtar kelimesi önem taşıyor. WITH anahtar kelimesi sayesinde yarattığımız CTE'nin takma adını(alias) ve içerisinde barındıracağı alan isim bilgilerini belirtebiliyoruz.

CTE'lerin kendi kendini tekrar etmemesi yani kod tekrarı yapmaması özelliğini Recursive Olmayan tanımıyla niteleyebiliriz.
 

ÖRNEK 1 :

USE
AdventureWorks;
GO

WITH
Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
AS

(
     SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
     FROM Sales.SalesOrderHeader
     GROUP BY SalesPersonID
)

SELECT
E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
     E
.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM
HumanResources.Employee AS E
     JOIN Sales_CTE AS OS
     ON E.EmployeeID = OS.SalesPersonID
     LEFT OUTER JOIN Sales_CTE AS OM
     ON E.ManagerID = OM.SalesPersonID
ORDER
BY E.EmployeeID;

GO

Sonuç :



ÖRNEK 2 :

WITH DirReps (Manager, DirectReports) AS
(

    SELECT
ManagerID, COUNT(*) AS DirectReports
    FROM
HumanResources.Employee
    GROUP
BY ManagerID
)

SELECT
AVG(DirectReports) AS [Average Number of Direct Reports]
FROM
DirReps
WHERE
DirectReports>= 2 ;
GO

Sonuç :



With anhtar kelimesinin yanında birden fazla CTE tanımlanabilir. Tanımlanan her yeni CTE kendisinden önce tanımlanan CTE yi de kullanabilecek durumda yaratılacaktır. Birden fazla CTE'nin aynı WITH anahtarını kullanması durumuda CTE'ler virgül (,) karakteri ile birbirinden ayrılabilir.

ÖRNEK 3 :

WITH
Employee(EmployeeID, Cnt)
AS

(
SELECT EmployeeID, COUNT(*)
FROM Purchasing.PurchaseOrderHeader
GROUP BY EmployeeID
),


Cost
(MN, MX, Diff)
AS

(
SELECT MIN(Cnt), MAX(Cnt), MAX(Cnt)-MIN(Cnt)
FROM Employee
)


SELECT
* FROM
Cost

Sonuç :




Evren AYAN
Microsoft MVP - Connected System Developer
http://www.evrenayan.net