Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc

[PL] CTE – fajne to jest, ale…

Common Table Expression (CTE) to bardzo przydatna składnia w SQL Server 2005/2008. Podzapytanie na sterydach... Pewnie i prawda. A jak ładnie działa rekursywnie! Hmmm, działa, a czasem i nie działa :-)

Czy próbowaliście kiedyś opakować rekurencyjne CTE w widok albo funkcję tabelaryczną? Udać się pewnie i uda, ale tylko pod warunkiem, że rekurencja nie wyjdzie ponad 100 iteracji. A wszystko dlatego, że nie można użyć w widokach i funkcjach klauzuli OPTION. Czyli takie coś nie pójdzie:

CREATE VIEW dbo.VV
AS
WITH CTE AS
(
SELECT 1 AS a
UNION ALL
SELECT (a + 1)
FROM CTE
WHERE a <= 180
)
SELECT * FROM CTE
OPTION (MAXRECURSION 180)
GO

Msg 156, Level 15, State 1, Procedure VV, Line 12
Incorrect syntax near the keyword 'OPTION'.

Dlaczego? BOL mówi, że:

The SELECT clauses in a view definition cannot include the following:
...
- The OPTION clause
...

I wszystko w zasadzie jasne :-) Czyli że się nie da i tyle. Fajnie. To co nam z takiego CTE rekurencyjnego, co nie może ponad 100 iteracji wykonać? W zasadzie niewiele nam...

Jeżeli komuś oprócz mnie jeszcze to przeszkadza, to spieszę powiadomić, że Steve Kass (SQL Server MVP) zgłosił tę niedoróbkę w 2005 roku (sic!): https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=124653.

A przy okazji, po porannej konwersacji i wymianie inwektyw na temat powyższego buga z kolegą z firmy (credits to Michał Lasota) znaleźliśmy pseudo-workaround dla problemu. Ale uwaga – działa tylko wtedy, gdy znamy maksymalną liczbę iteracji / maksymalną wartość wyjściową dla iteracji. Wystarczy wówczas podzielić iteracje na porcje po 100 (maksymalna liczba iteracji dla CTE nie używającego MAXRECURSION):

CREATE VIEW dbo.VV
AS
WITH CTE1 AS
(
SELECT 1 AS a
UNION ALL
SELECT (a + 1)
FROM CTE1
WHERE a <= 100
), CTE2 AS
(
SELECT MAX(a) AS a
FROM CTE1
UNION ALL
SELECT (a + 1)
FROM CTE2
WHERE a <= 200
), CTE3 AS
(
SELECT MAX(a) AS a
FROM CTE2
UNION ALL
SELECT (a + 1)
FROM CTE3
WHERE a < 260 -- maks. wartość to 260
)
SELECT * FROM CTE1
UNION
SELECT * FROM CTE2
UNION
SELECT * FROM CTE3
GO

Brzydkie i do zastosowania dla ograniczonej klasy przypadków.

Panowie i Panie z MS, czekamy na cud (bo jak inaczej nazwać reakcję na buga zgłoszonego 4 lata temu?).

Opublikowane 17 sierpnia 2009 08:10 przez brejk
Filed under: , ,

Komentarze:

# re: [PL] CTE – fajne to jest, ale…

17 sierpnia 2009 09:32 by Tomek

(...) (bo jak inaczej nazwać reakcję na buga zgłoszonego 4 lata temu?) (...)

Cieprliwość :)?

# re: [PL] CTE – fajne to jest, ale…

17 sierpnia 2009 09:44 by brejk

@Tomek: Hihi, też mi przyszło do głowy to słowo ;-) Choć z drugiej strony dziwię się MS, bo CTE jest często wymieniane jako jedna z najważniejszych nowinek dla programistów T-SQL w SQL Server 2005.

# re: [PL] CTE – fajne to jest, ale…

3 grudnia 2009 12:40 by tcichon

a ja po konsultacji z kolegami (grozycki i twrona) ma takie rozwiązanie;

CREATE VIEW dbo.VV

AS

WITH CTE AS

(

 SELECT 1 AS a

 UNION ALL

 SELECT (a + 1)

 FROM CTE

 WHERE a <= 180

)

SELECT * FROM CTE

GO

SELECT * FROM dbo.VV OPTION (MAXRECURSION 180)

i to działa

# re: [PL] CTE – fajne to jest, ale…

8 grudnia 2009 14:38 by brejk

@tcichon: Tak, ale teraz musisz zmusić kogoś, kto użyje Twojego widoku do zaaplikowania MAXRECURSION w zapytaniu :-)

# re: [PL] CTE – fajne to jest, ale…

27 stycznia 2011 14:59 by pjarosz

--MAXRECURSION 0 (zero) też działa ;)

;WITH CTE AS(  

SELECT 1 AS a  

UNION ALL  

SELECT (a + 1)  

FROM CTE  WHERE a <= 180)

SELECT * FROM CTE

OPTION (MAXRECURSION 0)

GO

Komentarze anonimowe wyłączone

About brejk

MVP, MCT, SQL Server geek