What noncluster index would be better to create on SQL Server?
- by Junior Mayhé
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)?
