Home Forums SQL Server 2012 SQL Server 2012 - T-SQL finding the right index RE: finding the right index

  • February 19, 2018 at 5:36 pm

    #1980367

    Jackie Lowery - Monday, February 19, 2018 4:14 PM

    ScottPletcher - Monday, February 19, 2018 2:45 PM

    Wow, yeah, for sure cluster by DateSent if that's the case.  The other conditions won't matter.

    Btw, the other tables I was worried about being scanned, I had more time to look, and they are small anyway, so a scan on those doesn't matter (at least for now).

    I'm not clear on the indexes i should end up with.  No primary key, just a clustered index on DateSent?

    You should have a primary key.  It just doesn't need to be a Clustered primary key.  If possible, the Clustered Index should be unique.  You can do that by making the clustered index on DateSent and whatever column (hopefully, it's just one column) makes up the PK.

    Also, from your original post, the following will NOT return a range of dates.  It will only return data that has an exact match with the variable.

     WHERE DateSent >= @dateVariable AND DateSent <= @dateVariable

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)