Posted by Erland Sommarskog on 04/24/07 08:07

Lee (lee.jenkins.ca@gmail.com) writes:
> Should I remove the clusterd index on the record_id field and create
> nonclustered index on this field and create a clustered index on
> record_date field since in my query, I always select a range of data
> by date.

Yes, that was precisely my reaction when I saw the table. Make the primary
key on record_id non-clustered, and add a clustered index on the date
column. I would guess you should do this on the archive table as well.

Also, I don't see the point with having the IDENTITY property on the
archive table. Just make it a normal column, and you don't need that
SET IDENTITY_INSERT. Not that it affects performance, but it looks cleaner.
However to change this, you would need rename the existing table, create
it a new and copy over. There is no ALTER syntax for changing the
IDENTITY property.




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]