[SQL]Index Usage Practice

Today’s practice is “Index usage”.  
Database: Adventurework2012.   Table: Sales.Customer.     T-SQL Scripts: 8 segments
Why i chose the table? Because the table is simple and has 4 Indexes. No including filter, XML index. So it’s simple to test.

The schema chart is let you can review clearly and go back to compare the columns.
1) Script-1

SELECT [CustomerID],[PersonID]
      ,[StoreID],[TerritoryID]
      ,[AccountNumber],[rowguid]
      ,[ModifiedDate]

   FROM [AdventureWorks2012].[Sales].[Customer]

We can find the SQL Server Engine used "Clustered Index Scan" if you think it used “SEEK”. For this situation, SCAN is faster than the “SEEK”


2) Script 2

SELECT [CustomerID]
      ,[PersonID]
      ,[StoreID]
      ,[TerritoryID]
      ,[AccountNumber]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2012].[Sales].[Customer]
  WHERE [CustomerID] = 100

Definitely, it will use "Clustered Index Seek". Because you indicated "WHERE" clause to specify the Key range. and the key is Index Clustered

Let’s see Script 3, if i give a key range?

3) Script 3

SELECT [CustomerID]
      ,[PersonID]
      ,[StoreID]
      ,[TerritoryID]
      ,[AccountNumber]
      ,[rowguid]
      ,[ModifiedDate]
  FROM [AdventureWorks2012].[Sales].[Customer]
  WHERE [CustomerID] < 100

It still use “SEEK”. Because you still use "WHERE" clause to specify the key range

But if we do a ">100" as well?

4) Script 4






It did “SEEK” as well. But! But it showed “Missing Index”. So it’s because of the Key range? so i adjusted the range from 100 to 15539 until 15540 and the Missing Index was gone. WHY? Does the SQL engine assumed the performance issue for this query?

So let’s run some tests:

4-1) SQL Server 2012 Performance Dashboard Reports. Instruction HERE.

4-2) Find out Missing Index with DMVs. The T-SQL scripts Here.

The results that I didn’t find anything about the “Missing Index”. So i decided to skip it but keep in mind.

5) Script 5, take off [CustomerID]

SELECT [PersonID] ,[StoreID]  ,[TerritoryID]
      ,[AccountNumber]  ,[rowguid] ,[ModifiedDate]
  FROM [AdventureWorks2012].[Sales].[Customer]
  WHERE [CustomerID] = 100

From the experience above, we know it’s because the “WHERE” and "Clustered index" for the Key range.

How about if we join non-Clustered Index for [TerritoryID] and change the sequences for “WHERE” clause into the query?

6) Script 6

SELECT [CustomerID],[PersonID],[StoreID]
      ,[TerritoryID],[AccountNumber],[rowguid],[ModifiedDate]
FROM [AdventureWorks2012].[Sales].[Customer]
WHERE TerritoryID = 6 and [CustomerID] = 100

So, we can assume SQL will look at “WHERE” clause before “SELECT” and if you indicate the “Primary” key that indexed by Clustered Index. SQL Engine will look for it primarily.

So, I take of [CustomerID] and try Script 7. If you assume SQL will use “Non-Clustered Index Seek” on [TerritoryID], you will be wrong! But if it’s wrong, what will be? and WHY?

7) Script 7

SELECT [CustomerID] --PK, Clu
      ,[PersonID],[StoreID]
      ,[TerritoryID] --FK, non-clu
      ,[AccountNumber],[rowguid],[ModifiedDate]
  FROM [AdventureWorks2012].[Sales].[Customer]
  WHERE TerritoryID = 6 --FK, non-clu

It did “Index Scan” rather than “Index Seek” and the last 2 node was actually [TerritoryID = 6]. WHY?

Remember, [AccountNumber] is a function field and also a Indexed column. So for SQL Engine, it used the best execution plan to run the query. The requests are more important for [AccountNumber] to SQL Engine but the “WHERE” clause didn’t indicate it. So SQL prefer to use "Clustered Index Scan" to fit your need.

So from the conclusion of above, if i add [AccountNumber] into “WHERE” clause, it will use “Seek” instead of [Scan]

8) Script 8

SELECT [CustomerID] --PK, Clu
      ,[PersonID],[StoreID]
      ,[TerritoryID] --FK, non-clu
      ,[AccountNumber],[rowguid],[ModifiedDate]
  FROM [AdventureWorks2012].[Sales].[Customer]
  WHERE TerritoryID = 6 --FK, non-clu
  and [AccountNumber] = 'AW00000100'

Interesting, isn’t it?

There is only one “Compute Scalar” at the execution plan, it’s just a “PlaceHolder”.