Thought of Genius

SQL 2005中有個CTE (Common Table Expression)功能十分強大，可以用來做遞迴式的參考，藉以達成複雜的查詢邏輯並簡化查詢指令，是 SQL2005的一個新的重要功能。在使用 MySQL 時，由於沒有查詢端的存取模型好用，所以分頁通常都由程式自己來，在查詢指令下 limit m，n後，即可將要指定的分頁資料傳回，不會浪費 Network IO 在傳遞資料上。在 SQL Server 2000之前要達成這樣的功能，通常是配合temp table並配合 identity 欄位達成，但是仍需要將所有資料從第一筆到第 n 筆寫入 temp table 後，再取 top 的方式來操作，效能仍舊不理想。

所以我們可以使用SQL Server 2005中的CTE (Common Table Expression) 搭配 行號函數 ROW_NUMBER()來達到目的。與 ADO.NET 的分頁做比較，不僅可以降低 Network IO 外，更能在複雜查詢時提升效能，減少不必要的資源浪費，對於網頁分頁使用時有很大的幫助。

例如：查詢訂單資料表(orders)，依訂單金額由大而小排序，取出第301到第400筆資料:

<br />-- MySQL:<br />select * from orders order by orderamount desc limit 301,400<br /><br />-- SQL Server 2005:<br />with cteorder as (<br />select row_number() over (order by orderamount desc) as sn, * from orders )<br />select * from cteorder where sn between 301 and 400<br />

範例程式碼如下：
<br />-- Create Test Table<br />Create Table Demo(<br /> [DemoNo] [int] IDENTITY(1,1) NOT NULL,<br /> [RegDate] [datetime] NOT NULL CONSTRAINT [DF_Demo_RegDate] DEFAULT (getdate()),<br /> [Name] [nvarchar](50) NOT NULL,<br /> [Description] [nvarchar](50) NOT NULL,<br /> [OrderColum] [float] NOT NULL,<br /> CONSTRAINT [PK_Demo] PRIMARY KEY CLUSTERED ([DemoNo])<br />);<br /><br />-- Insert Into Data<br />Declare @i int<br />set @i = 1<br />While @i <= 30000<br />Begin<br /> insert into Demo([Name],[Description],[OrderColum]) values ('names', 'descriiption', @i * rand())<br /> set @i = @i +1<br />End<br /><br />-- Create Procedure Separate Result Set<br />Create Procedure usp_SeparateSet<br /> @sqlcmd nvarchar(max),<br /> @ordercmd nvarchar(max),<br /> @startnum int, @endnum int<br />AS<br /> Declare @ctecmd nvarchar(max)<br /> Select @ctecmd = '<br /> with ctequery as (<br /> select row_number() over ( ' + @ordercmd + ' ) as num, '+replace(@sqlcmd,'select ',' ')+' )<br /> select * from ctequery where num between ' + convert(nvarchar,@startnum) + ' and ' + convert(nvarchar,@endnum)<br /> Exec sp_executesql @ctecmd<br />GO<br /><br />-- Execute Procedure<br />Exec usp_SeparateSet 'select * from Demo', 'order by DemoNo', 1201,2000<br />-- Clean up test environment<br />Drop Table Demo<br />Drop Procedure<br /><br />-- Clean up test environment<br />Drop Table Demo<br />Drop Procedure usp_SeparateSet<br />