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

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]))


|--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]))

Question 2 — Indexed Top-N

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

SET STATISTICS PROFILE 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)

Question 3 — Column Order

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

SET STATISTICS PROFILE 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)


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

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

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)


|--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]))

About the Author

As an author, trainer, and coach Markus Winand specializes in helping developers cope with SQL performance issues. He also published the book SQL Performance Explained and tweets his best performance tips via @SQLPerfTips.http://winand.at/

Recent Questions at Ask.Use-The-Index-Luke.com

0
votes
1
answer
101
views
0
votes
0
answers
349
views

Fanout in R-Tree

Mar 27 at 08:07 jamie 1
tree indexing
0
votes
1
answer
132
views

Think About It

Mar 26 at 12:54 Markus Winand ♦♦ 511
reflection