Sandeep Singh Shekhawat

Sandeep Singh Shekhawat

  • NA
  • 19.9k
  • 30.4m

Why did non clustered index increase cost?

May 11 2013 2:33 AM

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 am I doing mistake?

public static MvcHtmlString RadioButtonForEnum<TModel, TProperty>(this HtmlHelper<TModel> htmlHelper,

                                                                         Expression<Func<TModel, TProperty>> expression

                                                                        )

       {

           var metaData = ModelMetadata.FromLambdaExpression(expression, htmlHelper.ViewData);

           var names = Enum.GetNames(metaData.ModelType);

           var sb = new StringBuilder();

           foreach (var name in names)

           {

               var id = string.Format(

                   "{0}_{1}_{2}",

                   htmlHelper.ViewData.TemplateInfo.HtmlFieldPrefix,

                   metaData.PropertyName,

                   name

               );

 

               var radio = htmlHelper.RadioButtonFor(expression, name, new { id = id }).ToHtmlString();

               sb.AppendFormat("<label for=\"{0}\">{1}</label> {2}", id, radio, HttpUtility.HtmlEncode(name));

           }

           return MvcHtmlString.Create(sb.ToString());

       }