五餅二魚工作室

在 ANSI 的 SQL 語法中，當我們有需要做到 Hierarchical and recursive queries 的時候，有標準的  Common table expression ( 簡稱 CTE ) 可以來作使用。最近剛好在做一些處理的時候，就遇到一個一個好玩的狀況，因此把問題整理一下。

狀況類似以下的處理，我們原本有一個簡單的 SQL

with A as
(
	select 1 col0, cast('test1' as varchar(5)) col1
	union all
	select col0+1, cast('test'+cast((col0)%5 +1 as varchar(1)) as varchar(5) )
	from A where col0 < 10
)
SELECT * FROM A

因為這樣的指令會很常用，因此會想要把這樣的指令封裝成為 View 來做使用，所以就想說把指令改成下面這樣

CREATE VIEW v_Sample1 AS
with A as
(
	select 1 col0, cast('test1' as varchar(5)) col1
	union all
	select col0+1, cast('test'+cast((col0)%5 +1 as varchar(1)) as varchar(5) )
	from A where col0 < 1000
)
SELECT * FROM A
GO

雖然這樣可以建立，但在使用的時候，因為遞迴的階層超過 100，因此使用上的時候就要注意一下，需要特別加上 OPTION (MAXRECURSION 0)，而且我們沒有辦法將那個參數放到 View 裡面在建立的時候宣告，只能使用的時候才加入。

雖然這樣的限制對一般使用上來說沒有甚麼影響，但剛好遇到我需要在 View 裡面再去包一層 View 的時候，那麼在建立的時候還是不能建立，也是要等到要使用的時候才能加上

CREATE OR ALTER VIEW v_Sample2 AS
	SELECT col1,count(*) rows FROM v_Sample1 where col0 < 101
	GROUP BY col1
GO

SELECT * FROM v_Sample2 OPTION (MAXRECURSION 0)
GO

這樣看起來似乎就沒有那麼方便了，因為只要有直接或間接用到的都要特別給加上這樣一個 OPTION，於是就有同事問那是否有方法避開呢 ? 原本我們是想透過 Function 來做，

CREATE OR ALTER Function udf_Sample1() 
RETURNS TABLE
AS
RETURN
(
	with A as
	(
		select 1 col0, cast('test1' as varchar(5)) col1
		union all
		select col0+1, cast('test'+cast((col0)%5 +1 as varchar(1)) as varchar(5) )
		from A where col0 < 1000
	)
	SELECT * FROM A 
)
GO

SELECT * FROM udf_Sample1() OPTION (MAXRECURSION 0)
GO

但看起來似乎跟 View 沒有兩樣，也不能寫在定義裡面，只能在最外層給加上。但如果單純使用 inline table valued function 沒有辦法，那是否我可以用 multi-statement table-valued function 來避開呢 ? 從下面的測試過程中，看起來結果如同我們所想要的

CREATE OR ALTER Function udf_Sample2() 
RETURNS @OUTPUT TABLE ( col0 int, col1 varchar(10) )
AS
BEGIN
	with A as
	(
		select 1 col0, cast('test1' as varchar(5)) col1
		union all
		select col0+1, cast('test'+cast((col0)%5 +1 as varchar(1)) as varchar(5) )
		from A where col0 < 1000
	)
	INSERT INTO @OUTPUT
		SELECT * FROM A OPTION (MAXRECURSION 0);
	RETURN
END
GO

SELECT col1,count(*) rows 
FROM udf_Sample2()
GROUP BY col1
ORDER BY col1
GO