USE     master;
GO
IF      DB_ID('ScansAndSeeks')
        IS NOT NULL
        DROP DATABASE ScansAndSeeks;
GO
CREATE  DATABASE ScansAndSeeks;
GO
USE     ScansAndSeeks;
GO
ALTER   DATABASE ScansAndSeeks
SET     ALLOW_SNAPSHOT_ISOLATION OFF
;
ALTER   DATABASE ScansAndSeeks 
SET     AUTO_CLOSE OFF,
        AUTO_SHRINK OFF,
        AUTO_CREATE_STATISTICS OFF,
        AUTO_UPDATE_STATISTICS OFF,
        PARAMETERIZATION SIMPLE,
        READ_COMMITTED_SNAPSHOT OFF,
        RESTRICTED_USER;
GO
CREATE  PROCEDURE dbo.ResetTest
        @Partitioned    BIT = 'false'
AS
BEGIN
        SET     NOCOUNT ON
        ;
        IF      OBJECT_ID(N'dbo.Example', N'U')
                IS NOT NULL
        BEGIN
                DROP TABLE dbo.Example;
        END
        ;
        -- Test table is a heap
        -- Non-clustered primary key on 'key_col'
        CREATE  TABLE 
                dbo.Example
                (
                key_col     INTEGER     NOT NULL,
                data        INTEGER     NOT NULL,
                padding     CHAR(100)   NOT NULL DEFAULT SPACE(100),
                
                CONSTRAINT [PK dbo.Example key_col]
                    PRIMARY KEY NONCLUSTERED (key_col)
                )
        ;
        IF      @Partitioned = 'true'
        BEGIN
                -- Enterprise, Trial, or Developer 
                -- required for partitioning tests
                IF  SERVERPROPERTY('EngineEdition') = 3
                BEGIN
                    EXECUTE
                    ('
                    DROP    TABLE dbo.Example
                    ;
                    IF      EXISTS 
                            (
                            SELECT  1 
                            FROM    sys.partition_schemes 
                            WHERE   name = N''PS''
                            ) 
                            DROP PARTITION SCHEME PS
                    ;
                    IF      EXISTS 
                            (
                            SELECT  1 
                            FROM    sys.partition_functions 
                            WHERE   name = N''PF''
                            ) 
                            DROP PARTITION FUNCTION PF
                    ;
                    CREATE  PARTITION FUNCTION 
                            PF (INTEGER)
                    AS      RANGE RIGHT
                            FOR VALUES (20, 40, 60, 80, 100)
                    ;
                    CREATE  PARTITION SCHEME PS
                    AS      PARTITION PF
                            ALL TO ([PRIMARY])
                    ;
                    CREATE  TABLE 
                            dbo.Example
                            (
                            key_col     INTEGER     NOT NULL,
                            data        INTEGER     NOT NULL,
                            padding     CHAR(100)   NOT NULL 
                                            DEFAULT SPACE(100),
                            
                            CONSTRAINT [PK dbo.Example key_col]
                                PRIMARY KEY NONCLUSTERED (key_col)
                            )
                    ON      PS (key_col);
                    ');
            END
            ELSE
            BEGIN
                    RAISERROR('Invalid SKU for partition test', 16, 1);
                    RETURN;
            END;
        END
        ;
        -- Non-unique non-clustered index on the 'data' column
        CREATE  NONCLUSTERED INDEX 
                [IX dbo.Example data]
                ON dbo.Example (data)
        ;
        -- Add 100 rows
        INSERT  dbo.Example 
                WITH (TABLOCKX)
                (
                key_col, 
                data
                )
        SELECT  key_col = V.number, 
                data = V.number
        FROM    master.dbo.spt_values AS V
        WHERE   V.[type] = N'P'
        AND     V.number BETWEEN 1 AND 100
        ;
END;
GO
CREATE  PROCEDURE dbo.ShowStats
        @Partitioned    BIT = 'false'
AS
BEGIN
        -- Index Usage Stats DMV (QE)
        SELECT  index_name = ISNULL(I.name, I.type_desc),
                scans = IUS.user_scans, 
                seeks = IUS.user_seeks, 
                lookups = IUS.user_lookups
        FROM    sys.dm_db_index_usage_stats AS IUS
        JOIN    sys.indexes AS I
                ON  I.object_id = IUS.object_id
                AND I.index_id = IUS.index_id
        WHERE   IUS.database_id = DB_ID(N'ScansAndSeeks')
        AND     IUS.object_id = OBJECT_ID(N'dbo.Example', N'U')
        ORDER   BY
                I.index_id
        ;
        -- Index Operational Stats DMV (SE)
        IF      @Partitioned = 'true'
                SELECT  index_name = ISNULL(I.name, I.type_desc),
                        partitions = COUNT(IOS.partition_number),
                        range_scans = SUM(IOS.range_scan_count),
                        single_lookups = SUM(IOS.singleton_lookup_count)
                FROM    sys.dm_db_index_operational_stats
                            (
                            DB_ID(N'ScansAndSeeks'), 
                            OBJECT_ID(N'dbo.Example', N'U'), 
                            NULL, 
                            NULL
                            ) AS IOS
                JOIN    sys.indexes AS I
                        ON  I.object_id = IOS.object_id
                        AND I.index_id = IOS.index_id
                GROUP   BY
                        I.index_id, -- Key
                        I.name, 
                        I.type_desc
                ORDER   BY
                        I.index_id;
        ELSE
                SELECT  index_name = ISNULL(I.name, I.type_desc),
                        range_scans = SUM(IOS.range_scan_count),
                        single_lookups = SUM(IOS.singleton_lookup_count)
                FROM    sys.dm_db_index_operational_stats
                            (
                            DB_ID(N'ScansAndSeeks'), 
                            OBJECT_ID(N'dbo.Example', N'U'), 
                            NULL, 
                            NULL
                            ) AS IOS
                JOIN    sys.indexes AS I
                        ON  I.object_id = IOS.object_id
                        AND I.index_id = IOS.index_id
                GROUP   BY
                        I.index_id, -- Key
                        I.name, 
                        I.type_desc
                ORDER   BY
                        I.index_id;
END;
GO
CREATE  PROCEDURE dbo.RunTest
        @SQL            VARCHAR(8000),
        @Partitioned    BIT = 'false'
AS
BEGIN
        -- No execution plan yet
        SET     STATISTICS XML OFF
        ;
        -- Reset the test environment
        EXECUTE dbo.ResetTest @Partitioned
        ;
        -- Previous call will throw an error if a partitioned
        -- test was requested, but SKU does not support it
        IF      @@ERROR = 0
        BEGIN
                -- IO statistics and plan on
                SET     STATISTICS XML, IO ON
                ;
                -- Test statement
                EXECUTE (@SQL)
                ;
                -- Plan and IO statistics off
                SET     STATISTICS XML, IO OFF
                ;
                EXECUTE dbo.ShowStats @Partitioned;
        END;
END;
GO
-- Table Scan plan : 1 scan on heap, 1 range scan on heap
EXECUTE dbo.RunTest 
        @SQL = 'SELECT * FROM Example';

-- Index seek, single value in unique index : 1 seek on PK, 1 single lookup on PK
EXECUTE dbo.RunTest 
        @SQL = 'SELECT key_col 
                FROM Example 
                WHERE key_col = 32';

-- Index seek, single value in non-unique index : 1 seek on IX, 1 range scan on IX
EXECUTE dbo.RunTest 
        @SQL = 'SELECT data 
                FROM Example 
                WHERE data = 32';

-- Index seek in non-unique index : 1 seek on IX, 2 range scans on IX
EXECUTE dbo.RunTest 
        @SQL = 'SELECT data 
                FROM Example 
                WHERE data = 32 
                OR data = 33';

-- Index seek, range in non-unique index : 1 seek on IX, 1 range scan on IX
EXECUTE dbo.RunTest 
        @SQL = 'SELECT data 
                FROM Example 
                WHERE data BETWEEN 32 AND 33';

-- Index seek, four values in unique index : 1 seek on PK, 4 single lookups on PK
EXECUTE dbo.RunTest 
        @SQL = 'SELECT key_col 
                FROM Example 
                WHERE key_col IN (2,4,6,8)';

-- Index seek, (same four values) : 1 seek on PK, 1 range scan on PK (+ residual predicate)
EXECUTE dbo.RunTest 
        @SQL = 'SELECT key_col 
                FROM Example 
                WHERE key_col BETWEEN 2 AND 8 
                AND key_col % 2 = 0';

-- Index seek + RID lookup: 1 seek on PK + 1 lookup on heap; 1 range scan on PK + 8 single lookups on heap
EXECUTE dbo.RunTest 
        @SQL = 'SELECT * FROM Example 
                WITH (INDEX([PK dbo.Example key_col])) 
                WHERE key_col BETWEEN 1 AND 8';

-- Index seek + RID lookup: 1 seek on PK + 1 lookup on heap; 1 range scan on PK + 8 single lookups on heap (6 partitions)
EXECUTE dbo.RunTest 
        @SQL = 'SELECT * FROM Example 
                WITH (INDEX([PK dbo.Example key_col])) 
                WHERE key_col BETWEEN 1 AND 8', @Partitioned = 'true';

-- Index seek, startup filter, no executions : 1 seek on PK, no SE activity
EXECUTE dbo.RunTest 
        @SQL = 'SELECT key_col 
                FROM Example 
                WHERE key_col = 8 
                AND @@TRANCOUNT < 0';

-- Table scan : 1 scan on heap; 6 partitions, 6 range scans on IX
EXECUTE dbo.RunTest 
        @SQL = 'SELECT * FROM Example', 
        @Partitioned = 'true';

-- Ordered seek on a heap (2008+ only)
-- 1 seek on heap, 2 range scans (one per partition) on heap
EXECUTE dbo.RunTest 
        @SQL = 'SELECT * 
                FROM Example 
                WHERE key_col BETWEEN 1 AND 30', 
        @Partitioned = 'true';

-- Backward scan (2008+ only)
EXECUTE dbo.RunTest 
        @SQL = 'SELECT TOP (2) WITH TIES * 
                FROM Example 
                WHERE key_col BETWEEN 1 AND 50 
                ORDER BY $PARTITION.PF(key_col) DESC', 
        @Partitioned = 'true';