Which non-clustered index should I use?
- 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.
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
