I have following table with six rows
CREATE TABLE TimePeriod
(
TimePeriodId int not null identity(1,1) primary key,
TimePeriodName nvarchar(30) not null,
OrderId int not null,
sDelete bit not null default 'False'
)
When I run this query
SELECT * FROM TimePeriod where timeperiodname='quarter'
Then
Estimated subtree cost comes 0.0032886
But when defined non clustered index on column TimePeriodName
CREATE UNIQUE NONCLUSTERED INDEX IX_TimePeriod_TimePeriodName
ON TimePeriod
(
TimePeriodName 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)
Then run query
SELECT * FROM TimePeriod where timeperiodname='quarter'
I get
Estimated subtree cost is 0.0065704
So why is cost incresing when non clustered index is defined on the table?
Where I am doing mistake?