his is a multi-part message in MIME format.
If it is used in joins, then I would put the clustered index on the IDENTITY column. This can speed up inserts into this table, inserts into related tables, and joins between this table and related tables. If the order of the IDENTITY increment matches the order of the clustered index on the IDENTITY column, then all inserts will occur at the end of the table, which minimizes the required index maintenance operations.
If a table has a clustered index, then all nonclustered indexes use the clustered index key to locate rows in the table. If you put a nonclustered index on the primary key, then every join will result in an additional step in the execution plan--a bookmark lookup. This extra level of indirection can significantly reduce the performance of every join. In addition, if you use a clustered index on a datetime column, and the datetime column is not a candidate key, then SQL Server will add a 4-byte uniqifier to every index row so that the index key can be used in nonclustered indexes to locate rows. This increases the size of each nonclustered index, and can further reduce query performance, especially with respect to joins.
To boost performance for reporting, you have other options aside from simply adding an index. Here are a couple: (1) use a covering index so that the bookmark lookup will not be necessary, or (2) create an indexed view, and use both the datetime and the identity column (in that order) as the clustered index key for the view. If all of the columns necessary for the query exist in the index key, then there is no need for SQL Server to access the actual data row, so the performance degradation resulting from the use a nonclustered index will be minimized. If that doesn't provide adequate reporting performance, the indexed view option will at least meet the select performance of accessing a table with a clustered index directly, without degrading the performance of the joins. It should be noted, however, that insert performance will be degraded by the addition of any index or indexed view.
"Pradeep Kutty" < XXXX@XXXXX.COM > wrote in message news:# XXXX@XXXXX.COM ...
I have a table (detail table) with fields ID (Identity) primary Key and a DTTM datetime field which is a heap.
Right now there is a non clustered index on ID which is used to join with its master table.
I have many reports which uses this table and for all the reports the basic criteria is between DTTM.
say I run the report for say for a date range of 1 month, 1 week or so.
Im planning to add a clustered index on DTTM field so that the reports would become faster compared to a table scan what its doing now.
My question is, is it a good idea to create a clustered index on a Datetime field?
or is it a better way to make ID the clustered index and then create a non clustered index on DTTM?
But I always had the doubt that, what is the purpose of creating a clustered index on an identity field that too which is already a primary key,
since an identity field is already ordered. Does it make sense to create a clustered in index on Identity field.
Add to this most of my Stored procedures which are used to retrieve uses ID to join with its master table.
DTTM would be used only in reports...
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">