WITH common_table_expression (Transact-SQL)

Poniższe wskazówki dotyczą nonrecursive typowych wyrażeń tabela.Wskazówki dotyczące typowych wyrażeń tabela cyklicznych Zobacz „ wskazówki dotyczące dla definiowanie i za pomocą cyklicznych wspólne tabela wyrażenia"znajdujący się.

• CTE musi następować jedną AKTUALIZACJĘ SELECT, INSERT, korespondencji SERYJNEJ, lub DELETE instrukcja, która odwołuje się do niektórych lub wszystkich CTE kolumny.CTE może być również określone w instrukcja CREATE VIEW jako część definiowanie instrukcja SELECT w widoku.

• Wiele definicji kwerendy CTE mogą być definiowane w nonrecursive CTE.Definicje muszą być połączone za pomocą jednej z tych zestaw operatorów: UNION ALL, UNION, PRZETNIJ lub EXCEPT.

• CTE może odwoływać się i uprzednio zdefiniowane CTEs w tym samym z klauzula.Odwoływanie się do przodu jest niedozwolone.

• Określanie więcej niż jeden z klauzula w CTE jest niedozwolone.Na przykład jeśli CTE_query_definition zawiera podkwerenda, tym podkwerendzie nie może zawierać zagnieżdżonych z klauzula, która definiuje CTE innego.

• Następujące klauzule nie mogą być używane w CTE_query_definition:

  • COMPUTE lub COMPUTE BY

  • ORDER BY (z wyjątkiem przypadków, gdy określono klauzulę TOP)

  • DO

  • Opcja klauzula z podpowiedzi kwerendy

  • DLA DANYCH XML

  • DO PRZEGLĄDANIA

• Użyto CTE w instrukcja, która jest częścią serii instrukcja przed nim musi następować średnikiem.

• Kwerendy, odwoływanie się do CTE może służyć do definiowania kursor.

• W CTE można odwoływać się do tabel, na serwerach zdalnych.

• Podczas wykonywania CTE, wszystkie wskazówki, odwołujące się do CTE może powodować konflikt z innymi wskazówki, które są wykrywane w momencie CTE uzyskuje dostęp do jego tabel podstawowych w taki sam sposób jak wskazówki, które odwołują się do widoku w kwerendach.W takim wypadku kwerenda zwraca błąd.Aby uzyskać więcej informacji zobaczWyświetl rozwiązanie.

• Gdy jest CTE miejsce docelowe instrukcja UPDATE muszą być zgodne wszystkie odwołania do CTE w instrukcja.Na przykład przypisane alias w klauzula FROM the CTE aliasu należy używać dla wszystkich innych odwołań do CTE.Niejednoznaczne odwołania CTE mogą dawać łączyć nieoczekiwane zachowanie i wyniki kwerendy niezamierzone.Aby uzyskać więcej informacji zobaczUPDATE (Transact-SQL).

Poniższe wskazówki dotyczą Definiowanie cyklicznej typowe wyrażenie tabela:

• Definicja cykliczna CTE musi zawierać co najmniej dwie definicje kwerendy CTE, zakotwiczony element członkowski i członek cyklicznych.Można zdefiniować wiele składników zakotwiczenie i składników cyklicznych, jednak wszystkie definicje kwerendy zakotwiczony element członkowski musi znajdować się przed pierwszym definicji elementów członkowskich cyklicznych.Wszystkie definicje kwerendy CTEsą członkami zakotwiczenia, chyba że odwołują CTE, sam.

• Członkowie zakotwiczenia należy łączyć za pomocą jednej z tych operatorów ustawić: UNION ALL, UNION, PRZETNIJ lub EXCEPT. UNION ALL jest wyłącznie zestaw między ostatnim dozwolone operator zakotwiczony element członkowski i pierwszy członek cykliczną i podczas łączenia wielu członków cyklicznych.

• Liczba kolumn w członków zakotwiczenie i cyklicznej muszą być takie same.

• Typ danych kolumna w cyklicznej element członkowski musi być taka sama, jak typ danych kolumna odpowiadające im w kotwicy element członkowski.

• Klauzula FROM element członkowski członkowski cyklicznej muszą odwoływać się tylko jedna czas do CTE expression_name.

• Następujące elementy nie są dozwolone w CTE_query_definition z cyklicznej element członkowski:

  • ZAZNACZ RÓŻNE

  • GRUPOWANIE WEDŁUG

  • O

  • wartość skalarna agregacja

  • U GÓRY

  • LEFT RIGHT, łączyć zewnętrzne (łączyć wewnętrzne są dozwolone)

  • Podkwerendy

  • Wskazówki dotyczą odwołanie cykliczne do CTE wewnątrz CTE_query_definition.

Poniższe wskazówki dotyczą za pomocą cyklicznych typowe wyrażenie tabela:

• Wszystkie kolumny zwrócony przez cykliczne, CTE są nullable niezależnie od opcje dopuszczania wartości null kolumn zwracanych przez uczestniczących instrukcji SELECT.

• Niepoprawnie wykonane cyklicznej CTE może spowodować, że nieskończoną pętlę.Na przykład jeśli definicja kwerendy rekurencyjne element członkowski członkowski zwraca te same wartości dla kolumn nadrzędnych i podrzędność, jest tworzony nieskończoną pętlę.Aby zapobiec nieskończoną pętlę, można ograniczyć liczbę poziomów rekursji dozwolone dla danego sprawozdania przy użyciu wskazówka MAXRECURSION i wartość z przedziału od 0 do 32 767 w klauzula INSERT, UPDATE, korespondencji SERYJNEJ, DELETE lub instrukcja SELECT OPTION.W ten sposób można kontrolować wykonywanie instrukcja do czasu rozwiązania problemu kod, który jest tworzenie pętli.Wartość domyślna dla całego serwera jest 100.Jeżeli określono wartość 0, brak limitu jest stosowana.Tylko jedną wartość MAXRECURSION może być określona dla każdej instrukcja.Aby uzyskać więcej informacji zobaczQuery Hints (Transact-SQL).

• Widok, który zawiera cykliczne typowe wyrażenie tabela nie można zaktualizować danych.

• Kursory może być zdefiniowana na kwerendy przy użyciu CTEs.To CTE select_statement argument, który definiuje zestaw wyników kursor. Tylko szybko tylko przesyłanie dalej i statyczne kursory (Migawka) są dozwolone dla cyklicznych CTEs.Jeśli w cyklicznej CTE określono inny typ kursor, typ kursor zostanie przekonwertowana na statyczne.

• Tabele na serwerach zdalnych mogą się odwoływać w CTE.Jeśli w cyklicznej członkiem CTE odwołuje się do serwera zdalnego, buforu jest tworzone dla każdej tabela zdalnej w tabelach są ciągle dostępne lokalnie.Jeśli jest to kwerenda CTE, buforuje buforu/lazy indeksu jest wyświetlany w planu kwerend i będzie miała dodatkową predykat ze STOSU.Jest jednym ze sposobów na potwierdzenie prawidłowego rekursji.

• SQL Server 2008 nie zezwala na funkcje analityczne i agregacja w części CTE cyklicznych.

W poniższym przykładzie pokazuje liczbę pracowników zgłoszenie bezpośrednio do każdego menedżera na Adventure Works Cycles.

Średnia liczba pracowników zgłoszenie menedżerów, w poniższym przykładzie.

W poniższym przykładzie pokazano całkowitą liczbę zamówień sprzedaży i Data ostatniego zamówienia sprzedaży w SalesOrderHeader Tabela dla każdego sprzedawcy. W bieżących zasadach CTE odwołuje się do dwóch godzin: jeden raz, aby przywrócić wybrane kolumny dla sprzedawcy i ponownie mają być pobrane informacje podobne do menedżera sprzedawcy. Dane dla sprzedawcy i Menedżera są zwracane w jednym wierszu.

Poniższy przykład pokazuje hierarchiczną listę menedżerów i pracowników, którzy podlegają je.

W poniższym przykładzie pokazano, menedżerów i pracowników, raportowanie do nich.Liczba poziomów, zwracana jest ograniczone do dwóch.

W poniższym przykładzie opiera się na przykład C przez dodanie nazwy menedżera, pracowników i ich odpowiednich tytułów.Hierarchia menedżerów i pracowników jest ponadto wyróżniono przez wcięć poszczególnych poziomów.

MAXRECURSION można uniemożliwić źle sformułowany cyklicznej CTE wejść w nieskończoną pętlę.W poniższym przykładzie rozmyślnie tworzy nieskończonej pętli i używa MAXRECURSION Wskazówka do ograniczania liczby poziomów rekursji do dwóch.

USE AdventureWorks;
GO
--Creates an infinite loop
WITH cte (EmployeeID, ManagerID, Title) as
(
    SELECT EmployeeID, ManagerID, Title
    FROM HumanResources.Employee
    WHERE ManagerID IS NOT NULL
  UNION ALL
    SELECT cte.EmployeeID, cte.ManagerID, cte.Title
    FROM cte 
    JOIN  HumanResources.Employee AS e 
        ON cte.ManagerID = e.EmployeeID
)
--Uses MAXRECURSION to limit the recursive levels to 2
SELECT EmployeeID, ManagerID, Title
FROM cte
OPTION (MAXRECURSION 2);
GO

Po poprawieniu błędów kodowania MAXRECURSION nie jest już wymagany.W poniższym przykładzie pokazano kod poprawiony.

W poniższym przykładzie pokazano hierarchii zestawów produktów i składników, które są wymagane do budowania roweru dla ProductAssemblyID = 800.

Następujące aktualizacje przykład VacationHours wartość 25 procent dla wszystkich pracowników, którzy pośrednio lub bezpośrednio do raportu ManagerID 12. Typowe wyrażenie tabela zwraca hierarchiczną listę pracowników, którzy bezpośrednio do raportu ManagerID 12 i pracowników, którzy podlegają pracowników i tak dalej. Tylko te wiersze, które są zwracane przez typowe wyrażenie tabela zostaną zmienione.

USE AdventureWorks;
GO
WITH DirectReports(EmployeeID, NewVacationHours, EmployeeLevel)
AS
(SELECT e.EmployeeID, e.VacationHours, 1
  FROM HumanResources.Employee AS e
  WHERE e.ManagerID = 12
  UNION ALL
  SELECT e.EmployeeID, e.VacationHours, EmployeeLevel + 1
  FROM HumanResources.Employee as e
  JOIN DirectReports AS d ON e.ManagerID = d.EmployeeID
)
UPDATE HumanResources.Employee
SET VacationHours = VacationHours * 1.25
FROM HumanResources.Employee AS e
JOIN DirectReports AS d ON e.EmployeeID = d.EmployeeID;
GO

W poniższym przykładzie użyto wielu członków zakotwiczenie i cyklicznych do zwrócenia wszystkich obiektów nadrzędnych określonej osoby.Spis jest tworzony i wartości są wstawiane do ustanowienia genealogy rodziny, zwracane przez cykliczne CTE.