Getting Started with Adobe After Effects - Part 6: Motion Blur
Ask
Ask questions, discuss or help others by answering
Related Posts · View All
SQL Server 141
TSQL 75
SSRS 70
SSIS 66
XML 54

Top Categories · View All
SQL Server 141
TSQL 75
SSRS 70
SSIS 66
XML 54

Clustered Index

Mar 31 2012 12:00AM by Manas Ranjan Dash   

Is it a good idea to have a clustered index on a column whose datatype is datetime ?

Submitted under: Microsoft SQL Server · TSQL ·  ·  · 


Manas Ranjan Dash
39 · 5% · 1482

2 Replies

  • It depends.

    Before creating clustered indexes, understand how your data will be accessed. Consider using a clustered index for:

         •Columns that contain a large number of distinct values.
         •Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
         •Columns that are accessed sequentially.
         •Queries that return large result sets.
         •Columns that are frequently accessed by queries involving join or GROUP BY clauses; typically these are foreign key columns. An index on the column(s) specified in the ORDER BY or GROUP BY clause eliminates the need for SQL Server to sort the data because the rows are already sorted. This improves query performance.
         •OLTP-type applications where very fast single row lookup is required, typically by means of the primary key. Create a clustered index on the primary key.
    

    Clustered indexes are not a good choice for:

        •Columns that undergo frequent changes 
        This results in the entire row moving (because SQL Server must keep the data values of a row in physical order). This is an important consideration in high-volume transaction processing systems where data tends to be volatile.
        •Wide keys 
        The key values from the clustered index are used by all nonclustered indexes as lookup keys and therefore are stored in each nonclustered index leaf entry.
    
    commented on Apr 1 2012 2:53AM
    Robert Dennyson
    11 · 14% · 4419
  • I will need to do some further testing (to study the impact of using only the datetime column on the clustered index), but from practical experience I can say that I do not see major issues. We have a couple of tables in our database that have a datetime column as part of the clustered index (alongwith a few INT columns). The smallest of these tables have about 500,000 records and we do not see any performance issues as long as the queries filter on the date columns.

    @Robert: Small correction. Data on the clustered index itself is stored in physical order, but not on the table. The table is still logically sorted via the information stored in the slot array. This was in fact, one of the myths busted via a demo in TechEd India 2012.

    commented on Apr 1 2012 3:00AM
    Nakul Vachhrajani
    4 · 33% · 10564

Your Reply


Sign Up or Login to post a comment.

    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]