What noncluster index would be better to create on SQL Server?

Posted by Junior Mayhé on Stack Overflow See other posts from Stack Overflow or by Junior Mayhé
Published on 2010-05-22T23:58:55Z Indexed on 2010/05/23 0:00 UTC
Read the original article Hit count: 228

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.

SELECT CustomerName FROM Customers

Which leads the execution plan to show me:

I/O cost = 3.45646
Operator cost = 4.57715

For the first attempt to improve performance, I've created a nonclustered index for this table:

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

With this first try, I've executed the select statement and the execution plan shows me:

I/O cost = 2.79942
Operator cost = 3.92001

Now the second try, I've deleted this nonclustered index in order to create a new one.

CREATE NONCLUSTERED INDEX [IX_CategoryName] ON [dbo].[Categories] 
(
    [CategoryId] ASC
)
INCLUDE ( [CategoryName]) 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 second try, I've executed the select statement and the execution plan shows me the same result:

I/O cost = 2.79942
Operator cost = 3.92001

Am I doing something wrong or this is expected? Shall I use the first nonclustered index with two fields, or the second nonclustered with one field (CategoryID) including the second field (CategoryName)?

© Stack Overflow or respective owner

Related posts about sql-server-2008

Related posts about index