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: 300
        
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