Which non-clustered index should I use?

Posted by Junior Mayhé on Stack Overflow See other posts from Stack Overflow or by Junior Mayhé
Published on 2010-05-23T00:56:44Z Indexed on 2010/05/23 1:00 UTC
Read the original article Hit count: 359

Here I am studying nonclustered indexes on SQL Server Management Studio.

I've created a table with more than 1 million records. This table has a primary key.

CREATE TABLE [dbo].[Customers](
    [CustomerId] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [varchar](100) NOT NULL,
    [Deleted] [bit] NOT NULL,
    [Active] [bit] NOT NULL,
 CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
(
    [CustomerId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

This is the query I'll be using to see what execution plan is showing:

SELECT CustomerName FROM Customers

Well, executing this command with no additional non-clustered index, it leads the execution plan to show me:

I/O cost = 3.45646
Operator cost = 4.57715

Now I'm trying to see if it's possible to improve performance, so I've created a non-clustered index for this table:

1) First non-clustered index

CREATE NONCLUSTERED INDEX [IX_CustomerID_CustomerName] ON [dbo].[Customers] 
(
    [CustomerId] ASC,
    [CustomerName] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

Executing again the select against Customers table, the execution plan shows me:

I/O cost = 2.79942
Operator cost = 3.92001

It seems better. Now I've deleted this just created non-clustered index, in order to create a new one:

2) First non-clustered index

CREATE NONCLUSTERED INDEX [IX_CustomerIDIncludeCustomerName] ON [dbo].[Customers] 
(
    [CustomerId] ASC
)
INCLUDE ( [CustomerName]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,  
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF,   
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

With this new non-clustered index, I've executed the select statement again and the execution plan shows me the same result:

I/O cost = 2.79942
Operator cost = 3.92001

So, which non-clustered index should I use? Why the costs are the same on execution plan for I/O and Operator? Am I doing something wrong or this is expected?

thank you

© Stack Overflow or respective owner

Related posts about sql-server-2008

Related posts about index