by Markus Winand.

SQL Server Scripts for “3-Minute 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.

The create and insert statements are available in the example schema archive.

Question 1 — DATE Anit-Pattern

CREATE INDEX tbl_idx ON tbl (date_column)
SELECT COUNT(*)
  FROM tbl
 WHERE DATEPART(YEAR, date_column) = 2017
SELECT COUNT(*)
  FROM tbl
 WHERE date_column >= {d'2017-01-01'}
   AND date_column <  {d'2018-01-01'}
|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1005],0)))
   |--Stream Aggregate(DEFINE:([Expr1005]=Count(*)))
      |--Index Scan(OBJECT:(tbl.tbl_idx),
             WHERE:(datepart(year,tbl.date_column)=(2017)))
|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1013],0)))
   |--Stream Aggregate(DEFINE:([Expr1013]=Count(*)))
      |--Nested Loops(Inner Join)
         |--Merge Interval
         |  |--Concatenation
         |     |--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=...))
         |     |  |--Constant Scan
         |     |--Compute Scalar(DEFINE:(([Expr1008],[Expr1009],[Expr1007])=...))
         |        |--Constant Scan
         |--Index Seek(OBJECT:(tbl.tbl_idx),
                 SEEK:(tbl.date_column > [Expr1010]
                   AND tbl.date_column < [Expr1011])
               ORDERED FORWARD)

Question 2 — Indexed Top-N

CREATE INDEX tbl_idx ON tbl (a, date_column)
SELECT TOP 1 *
  FROM tbl
 WHERE a = 12
 ORDER BY date_column DESC
 

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

|--Top(TOP EXPRESSION:(1))
   |--Nested Loops(Inner Join)
      |--Index Seek(OBJECT:(tbl.tbl_idx),
      |       SEEK:(a=12.)
      |      ORDERED BACKWARD)
      |--Clustered Index Seek(OBJECT:(tbl.PK__tbl__3213E83F20C1E124),
                        SEEK:(tbl.id=tbl.id)
                       LOOKUP ORDERED FORWARD)

Question 3 — Column Order

CREATE INDEX tbl_idx ON tbl (a, b)
SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1
SELECT *
  FROM tbl
 WHERE b = 1
DROP INDEX tbl_idx ON tbl
CREATE INDEX tbl_idx ON tbl (b, a)
SELECT *
  FROM tbl
 WHERE a = 38
   AND b = 1
SELECT *
  FROM tbl
 WHERE b = 1

The first query can use both index very efficiently (Index Seek with only SEEK predicates):

|--Nested Loops(Inner Join)
   |--Index Seek(OBJECT:(tbl.tbl_idx), 
   |       SEEK:(tbl.a=38. AND tbl.b=1.)
   |     ORDERED FORWARD)
   |--Clustered Index Seek(OBJECT:(tbl.PK__tbl__3213E83F20C1E124),
                     SEEK:(tbl.id=tbl.id)
                    LOOKUP ORDERED FORWARD)
|--Nested Loops(Inner Join)
   |--Index Seek(OBJECT:(tbl.tbl_idx), 
   |       SEEK:(tbl.b=1. AND tbl.a=38.)
   |     ORDERED FORWARD)
   |--Clustered Index Seek(OBJECT:(tbl.PK__tbl__3213E83F20C1E124),
                     SEEK:(tbl.id=tbl.id)
                    LOOKUP ORDERED FORWARD)

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

|--Nested Loops(Inner Join)
   |--Index Scan(OBJECT:(tbl.tbl_idx),
   |      WHERE:(tbl.b=1.))
   |--Clustered Index Seek(OBJECT:(tbl.PK__tbl__3213E83F20C1E124),
                     SEEK:(tbl.id=tbl.id)
                     LOOKUP ORDERED FORWARD)

With the reversed index column order, the second query can use the index efficiently:

|--Nested Loops(Inner Join)
   |--Index Seek(OBJECT:(tbl.tbl_idx), 
   |       SEEK:(tbl.b=1.)
   |      ORDERED FORWARD)
   |--Clustered Index Seek(OBJECT:(tbl.PK__tbl__3213E83F20C1E124],
                     SEEK:(tbl.id=tbl.id)
                    LOOKUP ORDERED FORWARD)

Question 4 — LIKE

CREATE INDEX tbl_idx ON tbl (text)
SELECT *
  FROM tbl
 WHERE text LIKE 'TJ%'

The execution plan states that it is doing an Index Seek. Since there is only wild card character at the very end, the full search text 'TERM' can be used as index access predicate.

|--Nested Loops(Inner Join, OUTER REFERENCES:(tbl.id))
   |--Index Seek(OBJECT:(tbl.tbl_idx)
   |             , SEEK:(tbl.text >= 'TÏþ'
   |                 AND tbl.text <  'TK')
   |             ,  WHERE:(tbl.text like 'TJ%')
   |             ORDERED FORWARD
   |            )
   |--Clustered Index Seek(OBJECT:(tbl.tbl_pk)
                 , SEEK:(tbl.id=tbl.id)
                 LOOKUP ORDERED FORWARD
                )

Question 5 — Index Only Scan

CREATE INDEX tbl_idx ON tbl (a, date_column)
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
 GROUP BY date_column
SELECT date_column, count(*)
  FROM tbl
 WHERE a = 38
   AND b = 1
 GROUP BY date_column

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:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1006],0)))
   |--Stream Aggregate(GROUP BY:(tbl.date_column)
   |  DEFINE:([Expr1006]=Count(*)))
   |--Index Seek(OBJECT:(tbl.tbl_idx),
           SEEK:(tbl.a=38.)
          ORDERED FORWARD)
|--Compute Scalar(DEFINE:([Expr1003]=CONVERT_IMPLICIT(int,[Expr1006],0)))
   |--Stream Aggregate(GROUP BY:(tbl.date_column)
      |  DEFINE:([Expr1006]=Count(*)))
      |--Nested Loops(Inner Join)
         |--Index Seek(OBJECT:(tbl.tbl_idx),
         |       SEEK:(tbl.a=38.)
         |     ORDERED FORWARD)
         |--Clustered Index Seek(OBJECT:(tbl.PK__tbl__3213E83F20C1E124),
                           SEEK:(tbl.id=tbl.id),
                          WHERE:(tbl.b=1.)
                          LOOKUP ORDERED FORWARD)

About the Author

Photo of Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing. His book entitled SQL Performance Explained has become standard reading.

Buy his Book on Amazon

Cover of “SQL Performance Explained”: Squirrel running on grass

The essence of SQL tuning in 200 pages

Buy on Amazon
(paperback only)

Paperback and PDF also available at Markus’ store.

Hire Markus

The quick and easy way to benefit from his extensive knowledge and experience.
Learn more »

“Use The Index, Luke!” by Markus Winand is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Legal | Contact | NO WARRANTY | Trademarks | Privacy | CC-BY-NC-ND 3.0 license