Click here to Skip to main content
15,887,596 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
I have following table with six rows

SQL
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
SQL
SELECT * FROM TimePeriod where timeperiodname='quarter'


Then Estimated subtree cost comes 0.0032886

But when defined non clustered index on column TimePeriodName

SQL
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
SQL
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?
Posted

1 solution

 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900