TechCodeview

Karmaşık birleştirmeleri ve alt sorguları kolaylaştırmak için SQL Server'ın Ortak Tablo İfadelerini veya CTE'leri kullanacağız. Ayrıca organizasyonel hiyerarşi gibi hiyerarşik verileri sorgulamanın bir yolunu da sağlar. Bu makalede CTE'ye, CTE türlerine, avantajlarına, dezavantajlarına ve bunların SQL Server'da nasıl kullanılacağına ilişkin eksiksiz bir genel bakış sunulmaktadır.

SQL Server'da CTE nedir?

CTE (Ortak Tablo İfadesi), yalnızca sorgu süresince var olan tek seferlik bir sonuç kümesidir . Tek bir SELECT, INSERT, UPDATE, DELETE, CREATE VIEW veya MERGE ifadesinin yürütme kapsamı içindeki verilere başvurmamızı sağlar. Geçicidir çünkü sonucu herhangi bir yerde saklanamaz ve sorgunun yürütülmesi tamamlanır tamamlanmaz kaybolacaktır. İlk olarak SQL Server 2005 sürümüyle geldi. Bir DBA her zaman Alt Sorgu/Görünüm'e alternatif olarak CTE'yi kullanmayı tercih etti. ANSI SQL 99 standardını takip ederler ve SQL uyumludurlar.

SQL Server'da CTE Sözdizimi

CTE sözdizimi bir CTE adı, isteğe bağlı bir sütun listesi ve ortak tablo ifadesini (CTE) tanımlayan bir ifade/sorgu içerir. CTE'yi tanımladıktan sonra SELECT, INSERT, UPDATE, DELETE ve MERGE sorgularında view olarak kullanabiliriz.

SQL Server'da CTE'nin temel sözdizimi aşağıdadır:

 WITH cte_name (column_names) AS (query) SELECT * FROM cte_name; 

Bu sözdiziminde:

• Daha sonra bir sorguda başvurulacak olan CTE adını ilk olarak belirledik.
• Bir sonraki adım virgülle ayrılmış sütunların bir listesini oluşturmaktır. CTE tanımı argümanlarındaki sütun sayısı ile sorgudaki sütun sayısının aynı olmasını sağlar. CTE argümanlarının sütunlarını tanımlamamışsak, CTE'yi tanımlayan sorgu sütunlarını kullanacaktır.
• Bundan sonra, ifade adından sonra AS anahtar sözcüğünü kullanacağız ve ardından sonuç kümesi CTE'yi dolduran bir SELECT ifadesi tanımlayacağız.
• Son olarak SELECT, INSERT, UPDATE, DELETE, MERGE ifadesi gibi bir sorguda CTE ismini kullanacağız.

CTE sorgu tanımı yazarken dikkat edilmesi gerekenler; aşağıdaki cümleleri kullanamayız:

1. TOP cümleciği olarak da kullanmadığınız sürece ORDER BY
2. İÇİNE
3. Sorgu ipuçları içeren OPTION yan tümcesi
4. GÖZAT İÇİN

Aşağıdaki resim CTE sorgu tanımının temsilidir.

Burada ilk bölüm, SQL'de bağımsız olarak çalıştırılabilen bir SQL sorgusu içeren bir CTE ifadesidir. İkinci kısım ise sonucu görüntülemek için CTE'yi kullanan sorgudur.

Örnek

Çeşitli örnekler kullanarak CTE'nin SQL Server'da nasıl çalıştığını anlayalım. Burada bir tablo kullanacağız' müşteri 'bir gösteri için. Bu tablonun aşağıdaki verileri içerdiğini varsayalım:

Bu örnekte CTE adı şöyledir: customer_in_newyork , CTE'yi tanımlayan alt sorgu üç sütunu döndürür müşteri adı, e-posta, Ve durum . Sonuç olarak CTE customer_in_newyork, New York Eyaleti'nde yaşayan tüm müşterileri iade edecektir.

CTE customer_in_newyork'u tanımladıktan sonra buna referansta bulunduk. SEÇME New York'ta bulunan müşterilerin ayrıntılarını almak için beyan.

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York') SELECT c_name, email, state FROM customers_in_NewYork; 

Yukarıdaki ifadeyi çalıştırdıktan sonra aşağıdaki çıktıyı verecektir. Burada sonucun yalnızca New York Eyaletinde bulunan müşteri bilgilerini döndürdüğünü görebiliriz.

Çoklu CTE

Bazı durumlarda sonuçları görmek için birden fazla CTE sorgusu oluşturmamız ve bunları birleştirmemiz gerekebilir. Bu senaryoda birden fazla CTE konseptini kullanabiliriz. Birden fazla CTE sorgusu oluşturmak ve bunları tek bir ifadede birleştirmek için virgül operatörünü kullanmamız gerekir. Birden fazla CTE'yi ayırt etmek için ',' virgül operatörünün önüne CTE adı gelmelidir.

Birden çok CTE, sonunda bir araya getirilen karmaşık sorguları basitleştirmemize yardımcı olur. Her karmaşık parçanın kendi CTE'si vardı ve bu daha sonra WHITE cümleciğinin dışında referans alınabiliyor ve birleştirilebiliyordu.

NOT: Çoklu CTE tanımı UNION, UNION ALL, JOIN, INTERSECT veya EXCEPT kullanılarak tanımlanabilir.

Aşağıdaki sözdizimi bunu daha açık bir şekilde açıklamaktadır:

 WITH cte_name1 (column_names) AS (query), cte_name2 (column_names) AS (query) SELECT * FROM cte_name UNION ALL SELECT * FROM cte_name; 

Örnek

SQL Server'da birden fazla CTE'nin nasıl çalıştığını anlayalım. Burada yukarıdakileri kullanacağız' müşteri ' gösteri için masa.

Bu örnekte iki CTE adını tanımladık. customer_in_newyork Ve customer_in_california . Daha sonra bu CTE'lerin alt sorgularının sonuç kümesi CTE'yi doldurur. Son olarak, CTE adlarını, içinde bulunan tüm müşterileri döndürecek bir sorguda kullanacağız. New York Ve Kaliforniya Eyaleti .

 WITH customers_in_NewYork AS (SELECT * FROM customer WHERE state = 'New York'), customers_in_California AS (SELECT * FROM customer WHERE state = 'California') SELECT c_name, email, state FROM customers_in_NewYork UNION ALL SELECT c_name, email, state FROM customers_in_California; 

New York ve Kaliforniya Eyaleti.

Neden CTE'ye ihtiyacımız var?

Veritabanı görünümleri ve türetilmiş tablolar gibi CTE'ler de karmaşık sorguları daha okunaklı ve basit hale getirerek yazmayı ve yönetmeyi kolaylaştırabilir. Bu özelliği, karmaşık sorguları, sorgunun yeniden yazılmasında yeniden kullanılabilecek basit bloklara bölerek başarabiliriz.

Kullanım durumlarından bazıları aşağıda verilmiştir:

• Türetilmiş bir tabloyu tek bir sorguda birden çok kez tanımlamamız gerektiğinde kullanışlıdır.
• Veritabanındaki bir görünüme alternatif oluşturmamız gerektiğinde kullanışlıdır.
• Aynı hesaplamayı birden çok sorgu bileşeninde aynı anda birden çok kez yapmamız gerektiğinde kullanışlıdır.
• ROW_NUMBER(), RANK() ve NTILE() gibi sıralama işlevlerini kullanmamız gerektiğinde kullanışlıdır.

Avantajlarından bazıları aşağıda verilmiştir:

15/100,00

• CTE kod bakımını kolaylaştırır.
• CTE kodun okunabilirliğini artırır.
• Sorgu performansını artırır.
• CTE, özyinelemeli sorguların kolayca uygulanmasını mümkün kılar.

SQL Server'daki CTE Türleri

SQL Server, CTE'yi (Ortak Tablo İfadeleri) iki geniş kategoriye ayırır:

1. Özyinelemeli CTE
2. Yinelemeli Olmayan CTE

Özyinelemeli CTE

Ortak bir tablo ifadesi, kendisine başvuran özyinelemeli CTE olarak bilinir. Konsepti ' olarak tanımlanan özyinelemeye dayanmaktadır. yinelenen bir sürecin veya tanımın tekrar tekrar uygulanması .' Özyinelemeli bir sorgu çalıştırdığımızda, verilerin bir alt kümesi üzerinde tekrar tekrar yinelenir. Basitçe kendisini çağıran bir sorgu olarak tanımlanır. Bir noktada bir bitiş koşulu vardır, dolayısıyla kendisini sonsuz olarak çağırmaz.

Özyinelemeli bir CTE'nin sahip olması gerekir BİRLİK TÜMÜ ifadesi ve özyinelemeli olması için CTE'nin kendisine başvuran ikinci bir sorgu tanımı.

Örnek

Özyinelemeli CTE'nin SQL Server'da nasıl çalıştığını anlayalım. Aşağıdaki ifadeyi göz önünde bulundurun; ilk beş tek sayıdan oluşan bir seri oluşturur:

 WITH odd_num_cte (id, n) AS ( SELECT 1, 1 UNION ALL SELECT id+1, n+2 from odd_num_cte where id <5 ) select * from odd_num_cte; < pre> <p>When we execute this recursive CTE, we will see the output as below:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-5.webp" alt="CTE in SQL Server"> <p>The below example is the more advanced recursive CTE. Here, we are going to use the &apos; <strong>jtp_employees</strong> &apos; table for a demonstration that contains the below data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-6.webp" alt="CTE in SQL Server"> <p>This example will display the hierarchy of employee data. Here table provides a reference to that person&apos;s manager for each employee. The reference is itself an employee id within the same table.</p> <pre> WITH cte_recursion (EmpID, FirstName, LastName, MgrID, EmpLevel) AS ( SELECT EmployeeID, FirstName, LastName, ManagerID, 1 FROM jtp_employees WHERE ManagerID IS NULL UNION ALL SELECT emp.EmployeeID, emp.FirstName, emp.LastName, emp.ManagerID, r.EmpLevel + 1 FROM jtp_employees emp INNER JOIN cte_recursion r ON emp.ManagerID = r.EmpID ) SELECT FirstName + &apos; &apos; + LastName AS FullName, EmpLevel, (SELECT FirstName + &apos; &apos; + LastName FROM jtp_employees WHERE EmployeeID = cte_recursion.MgrID) AS Manager FROM cte_recursion ORDER BY EmpLevel, MgrID </pre> <p>This CTE will give the following output where we can see the hierarchy of employee data:</p> <img src="//techcodeview.com/img/sql-server-tutorials/86/common-table-expression-sql-server-7.webp" alt="CTE in SQL Server"> <h3>Non-Recursive CTE</h3> <p>A common table expression that doesn&apos;t reference itself is known as a non-recursive CTE. A non-recursive CTE is simple and easier to understand because it does not use the concept of recursion. According to the CTE Syntax, each CTE query will begin with a &apos; <strong>With</strong> &apos; clause followed by the CTE name and column list, then AS with parenthesis.</p> <h2>Disadvantages of CTE</h2> <p>The following are the limitations of using CTE in SQL Server:</p> <ul> <li>CTE members are unable to use the keyword clauses like Distinct, Group By, Having, Top, Joins, etc.</li> <li>The CTE can only be referenced once by the Recursive member.</li> <li>We cannot use the table variables and CTEs as parameters in stored procedures.</li> <li>We already know that the CTE could be used in place of a view, but a CTE cannot be nested, while Views can.</li> <li>Since it&apos;s just a shortcut for a query or subquery, it can&apos;t be reused in another query.</li> <li>The number of columns in the CTE arguments and the number of columns in the query must be the same.</li> </ul> <hr></5>

Bu CTE, çalışan verilerinin hiyerarşisini görebileceğimiz aşağıdaki çıktıyı verecektir:

Yinelemeli Olmayan CTE

Kendisine referans vermeyen ortak bir tablo ifadesi, yinelemeli olmayan CTE olarak bilinir. Özyinelemeli olmayan bir CTE, özyineleme kavramını kullanmadığı için basit ve anlaşılması kolaydır. CTE Sözdizimine göre her CTE sorgusu ' ile başlayacaktır. İle ' cümleciği ve ardından CTE adı ve sütun listesi, ardından parantezli AS.

CTE'nin dezavantajları

SQL Server'da CTE kullanmanın sınırlamaları şunlardır:

• CTE üyeleri Farklı, Gruplandırmaya Göre, Sahip Olma, Üst, Birleştirmeler vb. gibi anahtar kelime tümcelerini kullanamaz.
• CTE'ye Recursive üye tarafından yalnızca bir kez başvurulabilir.
• Saklı prosedürlerde tablo değişkenlerini ve CTE'leri parametre olarak kullanamayız.
• CTE'nin bir görünüm yerine kullanılabileceğini zaten biliyoruz, ancak bir CTE iç içe yerleştirilemezken Görünümler bunu yapabilir.
• Bu yalnızca bir sorgu veya alt sorgunun kısayolu olduğundan başka bir sorguda yeniden kullanılamaz.
• CTE bağımsız değişkenlerindeki sütun sayısı ile sorgudaki sütun sayısı aynı olmalıdır.