SQL Server Scripts for “3-Minuten Test”


This section contains the create, insert and select statements for the “Test your SQL Know-How in 3 Minutes” test. You may want to test yourself before reading this page.

Table Setup

The table TBL is created as clustered index on the primary key. Change to PRIMARY KEY NONCLUSTERED if you like to test without an clustered index.

CREATE TABLE tbl (
  id          NUMERIC NOT NULL,
  date_column DATE,
  a           NUMERIC,
  b           NUMERIC,
  text        VARCHAR(255),
  state       CHAR(1),
  CONSTRAINT tbl_pk PRIMARY KEY (id)
);
GO
IF OBJECT_ID('rand_helper') IS NOT NULL
   DROP VIEW rand_helper;
GO

CREATE VIEW rand_helper AS SELECT RND=RAND();
GO
IF OBJECT_ID('random_string') IS NOT NULL
   DROP FUNCTION random_string;
GO

CREATE FUNCTION random_string (@maxlen int)
   RETURNS VARCHAR(255)
AS BEGIN
   DECLARE @rv VARCHAR(255)
   DECLARE @loop int
   DECLARE @len int

   SET @len = (SELECT CAST(rnd * (@maxlen-3) AS INT) + 3
                 FROM rand_helper)
   SET @rv = ''
   SET @loop = 0

   WHILE @loop < @len BEGIN
      SET @rv = @rv 
              + CHAR(CAST((SELECT rnd * 26
                             FROM rand_helper) AS INT )+97)
      IF @loop = 0 BEGIN
          SET @rv = UPPER(@rv)
      END
      SET @loop = @loop +1;
   END

   RETURN @rv
END
GO
IF OBJECT_ID('random_int') IS NOT NULL
   DROP FUNCTION random_int;
GO

CREATE FUNCTION random_int (@min int, @max int)
   RETURNS INT
AS BEGIN
   DECLARE @rv INT
   SET @rv = (SELECT rnd * (@max) + @min
                FROM rand_helper)
   RETURN @rv
END
GO
WITH generator_first (n) AS
( SELECT 1
   UNION ALL
  SELECT n + 1
    FROM generator_first
   WHERE N < 300
),
 generator (n) AS
( SELECT ROW_NUMBER() OVER (ORDER BY f1.n, f2.n)
    FROM generator_first f1
   CROSS JOIN generator_first f2
)
INSERT INTO tbl
SELECT n
     , DATEADD(day, -n, GetDate())
     , n % 1234
     , [dbo].random_int(1, 10)
     , [dbo].random_string(20)
     , CASE WHEN (n % 5) = 0 THEN 'X' ELSE 'A' END
  FROM generator
OPTION (MAXRECURSION 30000);
GO
exec sp_updatestats;
GO

Question 1 — DATE Anit-Pattern

CREATE INDEX tbl_idx ON tbl (date_column);
GO

SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON

SELECT text, date_column
  FROM tbl
 WHERE DATEPART(yyyy, date_column) = 2011;
GO

SELECT text, date_column
  FROM tbl
 WHERE date_column >= CAST('2011-01-01' AS DATE)
   AND date_column <  CAST('2012-01-01' AS DATE)
GO

SET STATISTICS PROFILE OFF

DROP INDEX tbl_idx ON tbl;
GO

The Index Scan in the first plan reads the entire index. The second plan uses the index to locate the required data (Index Seek).

|--Nested Loops(Inner Join)
     |--Index Scan(OBJECT:([tbl].[tbl_idx]),
             WHERE:(datepart(year,[tbl].[date_column])=2011))
     |--Clustered Index Seek(OBJECT:([tbl].[tbl_pk]),
              SEEK:([tbl].[id]=[tbl].[id]))

Table 'tbl'. Scan count 1, logical reads 806,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.



|--Nested Loops(Inner Join)
     |--Index Seek(OBJECT:([tbl].[tbl_idx]),
              SEEK:([tbl].[date_column] >= '2011-01-01'
                AND [tbl].[date_column] < ' 2012-01-01'))
     |--Clustered Index Seek(OBJECT:([tbl].[tbl_pk]),
              SEEK:([tbl].[id]=[tbl].[id]))

Table 'tbl'. Scan count 1, logical reads 628,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.

Starting with SQL Server 2014, the DATEPART(YYYY, ...) expression is automatically rewritten so that the index could be used. However, with my test-data set, it opts for a Clustered Index Seek anyways:

|--Clustered Index Scan(OBJECT:([test].[dbo].[tbl].[tbl_pk]),
      WHERE:([test].[dbo].[tbl].[date_column]>='2011-01-01'
         AND [test].[dbo].[tbl].[date_column]<'2012-01-01'))

Table 'tbl'. Scan count 1,
             logical reads 618, physical reads 0, read-ahead reads 0,
             lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Question 2 — Indexed Top-N

CREATE INDEX tbl_idx ON tbl (a, date_column);
GO

SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON

DECLARE @a numeric
SET @a = 123;
SELECT TOP 1 id, date_column
  FROM tbl
 WHERE a = @a
 ORDER BY date_column DESC
GO

SET STATISTICS PROFILE OFF

DROP INDEX tbl_idx ON tbl;
GO

The Index Seek returns the result BACKWARD to reflect the ORDER BY DESC. Note that there is no SORT operation.

|--Top(TOP EXPRESSION:((1)))
     |--Index Seek(OBJECT:([tbl].[tbl_idx]),
             SEEK:([tbl].[a]=[@a]) ORDERED BACKWARD)

Table 'tbl'. Scan count 1, logical reads 3,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.

Question 3 — Column Order

CREATE INDEX tbl_idx ON tbl (a, b);
GO

SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON

DECLARE @a numeric
      , @b numeric;
SET @a = 123;
SET @b = 1;

SELECT id, a, b
  FROM tbl
 WHERE a = @a
   AND b = @b;
GO

DECLARE @b numeric;
SET @b = 1;
SELECT id, a, b
  FROM tbl
 WHERE b = @b;
GO

SET STATISTICS PROFILE OFF

DROP INDEX tbl_idx ON tbl;
GO

The second query cannot perform an Index Seek, so it makes an Index Scan—reading the entire index.

|--Index Seek(OBJECT:([tbl].[tbl_idx]),
         SEEK:([tbl].[a]=[@a]
          AND  [tbl].[b]=[@b]) ORDERED FORWARD)


Table 'tbl'. Scan count 1, logical reads 3,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.

|--Index Scan(OBJECT:([tbl].[tbl_idx]),
        WHERE:([tbl].[b]=[@b])


Table 'tbl'. Scan count 1, logical reads 372,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.
|--Index Seek(OBJECT:([tbl].[tbl_idx]),
         SEEK:([tbl].[b]=[@b]
           AND [tbl].[a]=[@a]) ORDERED FORWARD)

Table 'tbl'. Scan count 1, logical reads 3,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.



|--Index Seek(OBJECT:([tbl].[tbl_idx]),
         SEEK:([tbl].[b]=[@b]) ORDERED FORWARD)

Table 'tbl'. Scan count 1, logical reads 40,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.

Question 4 — LIKE

CREATE INDEX tbl_idx ON tbl (text);
GO

SET STATISTICS PROFILE ON

SELECT id, text
  FROM tbl
 WHERE text LIKE '%TERM%';

SET STATISTICS PROFILE OFF

DROP INDEX tbl_idx ON tbl;
GO

The leading asterisk prevents an Index Seek so that the entire index is scanned.

|--Index Scan(OBJECT:([tbl].[tbl_idx]), 
       WHERE:([tbl].[text] like '%TERM%'))

Question 5 — Index Only Scan

CREATE INDEX tab_idx ON tbl (a, date_column);
GO

SET STATISTICS PROFILE ON
SET STATISTICS TIME ON
SET STATISTICS IO ON

DECLARE @a numeric;
SET @a = 123;

SELECT date_column, count(*)
  FROM tbl
 WHERE a = @a
 GROUP BY date_column;
GO


DECLARE @a numeric
      , @b numeric;
SET @a = 123;
SET @b = 1;

SELECT date_column, count(*)
  FROM tbl
 WHERE a = @a
   AND b = @b
 GROUP BY date_column;
GO

DROP INDEX tbl_idx ON tbl;
GO

The first query uses one index to search based on column A but can also retrieve the DATE_COLUMN from the very same index. The second query must run a key lookup on the clustered index (or RID Lookup (HEAP)) to apply the filter on the B column.

|--Compute Scalar(DEFINE:([...])
     |--Stream Aggregate(GROUP BY:([tbl].[date_column]))
          |--Index Seek(OBJECT:([tbl].[tab_idx]),
                  SEEK:([tbl].[a]=[@a]) ORDERED FORWARD)

Table 'tbl'. Scan count 1, logical reads 3,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.



|--Compute Scalar(DEFINE:[...])
     |--Stream Aggregate(GROUP BY:([tbl].[date_column]))
          |--Nested Loops(Inner Join)
               |--Index Seek(
                      OBJECT:([tbl].[tab_idx]),
                        SEEK:([tbl].[a]=[@a]))
               |--Clustered Index Seek(
                      OBJECT:([tbl].[tbl_pk]),
                        SEEK:([tbl].[id]=[tbl].[id]),
                       WHERE:([tbl].[b]=[@b]))

Table 'tbl'. Scan count 1, logical reads 235,
             physical reads 0, read-ahead reads 0, lob logical reads 0,
             lob physical reads 0, lob read-ahead reads 0.

About the Author

Photo of Markus Winand
Markus Winand tunes developers for high SQL performance. He also published the book SQL Performance Explained and offers in-house training as well as remote coaching at http://winand.at/

?Recent questions at
Ask.Use-The-Index-Luke.com

0
votes
1
answer
164
views

PostgreSQL Scripts: Performance Testing and Scalability problem and question

Nov 12 at 14:53 Markus Winand ♦♦ 936
testing postgresql scalability
0
votes
1
answer
562
views

PostgreSQL Bitmap Heap Scan on index is very slow but Index Only Scan is fast

Oct 31 at 11:31 Markus Winand ♦♦ 936
index postgresql postgres sql
3
votes
2
answers
588
views

pagination with nulls

Oct 29 at 22:39 Rocky 46
pagination