[SQL]為什麼在Function中的 Order By 沒有效?

2015-01-13

3194
0
SQL

為什麼在Function中的 Order By 沒有效?
只在客戶端發生，公司測試環境卻是好好的~~~
是SQL 2012的問題嗎?

環境:SQL 2012

今天同事問說在一個 Table-valued Function 之中有下 Order By ，

確沒有效果。而且在客戶端才會，公司的測試環境卻是OK的!

使用以下的範例來測試，

CREATE TABLE MYTABLE_1 
(
	CODE char(10)
	,CNAME nvarchar(80)
	,IDENTITY_FIELD INT IDENTITY
)
GO
INSERT INTO MYTABLE_1(CODE, CNAME) VALUES('0029', N'陪產假');
INSERT INTO MYTABLE_1(CODE, CNAME) VALUES('0001', N'特休假');
INSERT INTO MYTABLE_1(CODE, CNAME) VALUES('0002', N'生日假');
go

CREATE    FUNCTION [dbo].[fnMYTABLE]()
RETURNS @tbl_MYTABLE TABLE 
(
	CODE char(10)
	,CNAME nvarchar(80)
	,IDENTITY_FIELD INT  
)
AS

BEGIN 
		insert into @tbl_MYTABLE
		SELECT CODE, CNAME, IDENTITY_FIELD FROM MYTABLE_1 ORDER BY CODE;
RETURN

END
go

SELECT * FROM MYTABLE_1 ORDER BY CODE;
SELECT * FROM [dbo].[fnMYTABLE]();

在SQL 2008的結果如下，

在SQL 2012的結果如下，

後來查到「Table-Valued function - Order by is ignored in output」這篇。

在SQL 2012如果是 Insert into ... Select 的話，它會「忽略」Select的Order By哦!

所以我們將在Function做的 Insert into ... Select 拿出來執行測試，如下，

DECLARE @tbl_MYTABLE TABLE 
(
	CODE char(10)
	,CNAME nvarchar(80)
	,IDENTITY_FIELD INT  
);

INSERT INTO @tbl_MYTABLE
SELECT CODE, CNAME, IDENTITY_FIELD FROM MYTABLE_1 ORDER BY CODE;

SELECT * FROM @tbl_MYTABLE;

在SQL 2008的結果及執行計畫如下，

在SQL 2012的結果及執行計畫如下，

從上面可以明顯的看到在SQL 2012中是不會使用SORT的哦!

So...那要如何解這個問題呢?

1.在 Select Function 明確定義 Order By，如下，

SELECT * FROM [dbo].[fnMYTABLE]() Order By CODE;

2.在Function裡 Insert Into ... Select 裡多加入 ROW_NUMBER()，來強迫它先進行 Sort，如下，

DECLARE @tbl_MYTABLE TABLE 
(
	CODE char(10)
	,CNAME nvarchar(80)
	,IDENTITY_FIELD INT 
	,RM INT 
);

INSERT INTO @tbl_MYTABLE
SELECT CODE, CNAME, IDENTITY_FIELD 
	, ROW_NUMBER()over(order by CODE) AS RM
FROM MYTABLE_1 ORDER BY CODE;
SELECT * FROM  @tbl_MYTABLE;