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