How to set index for these select queries

  • In my table (large one) there is mainly three select queries:

    1) select * from Product where productid=101

    Here productid is primary key so it is clustered index, so no problem here.

    2) select * from Product where IsDeleted=0 order by SubmitDate Desc

    3) select * from Product where SubmitDate>=' ' and SubmitDate<' ' and IsDeleted=0 order by SubmitDate Desc

    for 2) and 3) IsDeleted is a bit type contains duplicate values, and SubmitDate is datetime contains duplicate values.

    How to set index for 2) and 3)

  • Index on IsDeleted, SubmitDate DESC

    Probably won't be used unless there's a small number of rows that qualify or you can get rid of that SELECT * , retrieve only the columns required and make the index covering.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • prakash.kumar3669 (1/17/2013)


    In my table (large one) there is mainly three select queries:

    1) select * from Product where productid=101

    Here productid is primary key so it is clustered index, so no problem here.

    2) select * from Product where IsDeleted=0 order by SubmitDate Desc

    3) select * from Product where SubmitDate>=' ' and SubmitDate<' ' and IsDeleted=0 order by SubmitDate Desc

    for 2) and 3) IsDeleted is a bit type contains duplicate values, and SubmitDate is datetime contains duplicate values.

    How to set index for 2) and 3)

    Do you always require "SELECT *" in your every query ?

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • Do you always require "SELECT *" in your every query ?

    the number of columns i need to display is 6-7 so i have put on * for the reference, in other words covering queries in not an option i guess.

  • prakash.kumar3669 (1/17/2013)


    in other words covering queries in not an option i guess.

    Why not?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Why not?

    I don't know, i have no experience of implementing indexing till now. What i have read from online articles, its important to index the columns which comes in where condition.

    if i have 5 columns to retrieve in my queries then what's the best way of indexing ?

  • prakash.kumar3669 (1/17/2013)


    What i have read from online articles, its important to index the columns which comes in where condition.

    Not only Where BUT group by, order by , JOIN's ON clause , Partititon by , and also the SELECT's columns.

    See this http://www.sqlservercentral.com/articles/Indexing/68439/

    prakash.kumar3669 (1/17/2013)


    if i have 5 columns to retrieve in my queries then what's the best way of indexing ?

    then those columns should be part of Covering index INCLUDE's part.

    For better solution.post that Query

    -------Bhuvnesh----------
    I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

  • For better solution.post that Query

    select ProdName,Category,TotalStock,Price from Product where SubmitDate>=' ' and SubmitDate<' ' and IsDeleted=0 order by SubmitDate Desc

  • Index on (IsDeleted, SubmitDate DESC) include (ProdName,Category,TotalStock,Price)

    btw, that's a rather odd predicate on the date column...

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (1/17/2013)


    Index on (IsDeleted, SubmitDate DESC) include (ProdName,Category,TotalStock,Price)

    What would be the exact sql query to create the index.

    btw, that's a rather odd predicate on the date column...

    how can you ellaborate ?

  • prakash.kumar3669 (1/17/2013)


    GilaMonster (1/17/2013)


    Index on (IsDeleted, SubmitDate DESC) include (ProdName,Category,TotalStock,Price)

    What would be the exact sql query to create the index.

    Why don't you open up books online and read over Create Index.

    btw, that's a rather odd predicate on the date column...

    how can you ellaborate ?

    SubmitDate>=' ' and SubmitDate<' '

    There is no possible way that any date can be both greater than or equal to '' and less than ''. Let me rephrase that into numbers...

    WHERE SomeNumber >=0 AND SomeNumber < 0

    That's completely, logically impossible as it is only true for a number that's both positive and negative at the same time, which no number is.

    Either you mis-typed when you copied the query over, or there's some serious flawed logic at work.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • WHERE SomeNumber >=0 AND SomeNumber < 0

    this is the actual condition: SubmitDate>=DATEADD(day,DATEDIFF(day,0,GETDATE()),@duration) AND SubmitDate < DATEADD(day,DATEDIFF(day,0,GETDATE()),1)

  • @duration is negative number....like -1,-2,-3

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply