SQL Server Performance

Is there any meaning that creating a cluster index on a datetime column?

Discussion in 'Performance Tuning for DBAs' started by zhenyuanDeng, Jul 4, 2008.

  1. zhenyuanDeng New Member

    logic sequence :
    row1:2008-01-01 12:03:24
    row2:2008-01-02 12:03:24
    row3:2008-01-01 12:03:24
    Will SQL SERVER save the data like that(physical):
    row1:2008-01-01 12:03:24
    row3:2008-01-01 12:03:24
    row2:2008-01-02 12:03:24
    zhenyuanDeng, Jul 4, 2008
    #2

  2. MohammedU New Member

    MohammedU, Jul 4, 2008
    #3

  3. FrankKalis Moderator

    How SQL Server stores the data physically is meaningless. It might be contigous or not. However in any case the page chain will be maintained and the page chain will look like your example. A smalldatetime attribute alone probably makes up only for a poor clustered index, imho
    FrankKalis, Jul 7, 2008
    #4

  4. satya Moderator

    SQL Server can use index on DateTimeField or on any other filed based on UniqueNumberWithIndex. If any of those is determined (based on the values and the statistics) very selective, then that will be used. If both are less selective, then SQL Server can join the indexesin run-time and used the combination of those two indexes (this is called index intersection).Checking the query plan will tell you which is always a best one to check, and also depending on the query you have to provide index hint . If you are adding a column to your select , may be better to add that column as second column in your datetimeindex
    satya, Jul 8, 2008
    #5

  5. zhenyuanDeng New Member

    My meaning is that: a table has many fields including one calling d_date (datetime) , and i want to select d_date for clusted index.
    Is that right? why?
    Thank
    zhenyuanDeng, Jul 9, 2008
    #9

  6. zhenyuanDeng New Member

    My meaning is that: a table has many fields including one calling d_date (datetime) , and i want to select d_date for clusted index.
    Is that right? why?
    Thanks everybody!
    zhenyuanDeng, Jul 9, 2008
    #10

  7. FrankKalis Moderator

    Sorry, but without indepth knowledge of your system, tables, queries, etc... it is impossible to judge to the clustered index. My guess would be that there are better candidates for it, but this is just my guts.
    FrankKalis, Jul 9, 2008
    #11

  8. satya Moderator

    satya, Jul 10, 2008
    #12

  9. zhenyuanDeng New Member

    Thanks satya again. I think i understand what is a "datetime column" cluster index now.[:D]
    zhenyuanDeng, Jul 27, 2008
    #13

  10. Mahmoud_H New Member

    you can , but why not to be non clustered index.
    you have only one cluster index for the table, and it is best to be used for PK

    Mahmoud_H, Jul 9, 2008
    #14

Share This Page