USE     tempdb;
GO
DROP    TABLE dbo.Test;
GO
CREATE  TABLE 
        dbo.Test 
        (
        data            INTEGER NOT NULL,
        );
GO
CREATE  CLUSTERED INDEX c ON dbo.Test (data);
GO
-- Lots of duplicated values
INSERT  dbo.Test WITH (TABLOCK)
        (data)
SELECT  TOP (5000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT 0)) / 117329
FROM    master.sys.columns C1,
        master.sys.columns C2,
        master.sys.columns C3;
GO

SET     STATISTICS TIME ON;

-- 1591ms CPU
SELECT  DISTINCT 
        data
FROM    dbo.Test;

SQL Server 分析和编译时间: 
   CPU 时间 = 859 毫秒，占用时间 = 2702 毫秒。

SQL Server 执行时间:
   CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。

(43 行受影响)

SQL Server 执行时间:
   CPU 时间 = 967 毫秒，占用时间 = 5881 毫秒。
   
-- 15ms CPU
WITH    RecursiveCTE
AS      (
        SELECT  data = MIN(T.data)
        FROM    dbo.Test T
        UNION   ALL
        SELECT  R.data
        FROM    (
                -- A cunning way to use TOP in the recursive part of a CTE :)
                SELECT  T.data,
                        rn = ROW_NUMBER() OVER (ORDER BY T.data)
                FROM    dbo.Test T
                JOIN    RecursiveCTE R
                        ON  R.data < T.data
                ) R
        WHERE   R.rn = 1
        )
SELECT  *
FROM    RecursiveCTE

--OPTION  (MAXRECURSION 0);

在有索引且排好序的情况下，后者速度更快！

SET     STATISTICS TIME OFF;
GO
DROP    TABLE dbo.Test;