SQLServer Execution Plan elements. Part 2

SQLServer Execution Plan elements. Part 2

MSDN info about Execution Plan elements for SQLServer 2005:
Graphical Execution Plan Icons (SQL Server Management Studio)

Lets drop all indexes on Employees table (if exists) and look at the execution plan produced by server engine for the query that retrives three columns: EmpNo, FirstName, LastName. Filter criteria is defined by FirstName equal to 'anna':

SELECT EmpNo, FirstName, LastName
FROM dbo.Employees
WHERE FirstName = 'anna';

Select query text and chose "Include Actual Execution Plan" option.
The results will look like this:

EmpNo	FirstName	LastName
7	anna	poperplatz

The Execution Plan will look like this:

As in the Part 1 example optimizer used "Table Scan" operator to process the query. Let's see how index added on employee name columns can improve the situation:

CREATE NONCLUSTERED INDEX [idx2_empname]
ON [dbo].[Employees]
(
[FirstName] ASC,
[LastName] ASC
)
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];
GO

EXEC sp_updatestats;

It remaines to be "Table Scan". And the reason is that EmpNo info is beyond the data covered by the index we created.
Adding all three columns to the index does not seem a good idea. Instead we can use a INCLUDE column feature (available in SQLServer 2005, 2008).

DROP INDEX [idx2_empname] ON [dbo].[Employees] ;
GO

CREATE NONCLUSTERED INDEX [idx3_empname_includ]
ON [dbo].[Employees]
(
[FirstName] ASC,
[LastName] ASC
)
INCLUDE ([EmpNo])
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];
GO

EXEC sp_updatestats;

The Execution Plan now will look like this:

As a result we have got "Index Seek" operator, that promisses fast performance, cause now all data that we need is covered by the index.

Pay attention that changing search condition in the WHERE clause from
WHERE FirstName = 'anna'
to
WHERE LastName = 'poperplatz'
will downgrade our execution schema to "Index Scan". So we will have to create additonal index in which LastName column will be listed first:

CREATE NONCLUSTERED INDEX [idx4_empname_includ]
ON [dbo].[Employees]
(
[LastName] ASC,
[FirstName] ASC
)
INCLUDE ([EmpNo])
WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY];
GO

EXEC sp_updatestats;

sqlexamples.info