Pros and Cons of Composite Clustered Indexes
Posted on 2011-04-27
We have a Datawarehouse table with the following Primary Key and associated Clustered Index:
ALTER TABLE [dbo].[PosSaleTransactionDerivedCategory] ADD CONSTRAINT [PK_PosSaleTransactionDerivedCategory] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
1) Given that PK_PosSaleTransactionDerivedCategory is and IDENTITY field that auto-increments by 1, is there any point having all of these fields as part of the primary key?
2) Given that PK_PosSaleTransactionDerivedCategory is and IDENTITY field that auto-increments by 1, is there any point having all of these fields as part of the clustered index?
3) Does the sequence of the fields in the clustered index matter from a performance point of view? (Note that there is a significant difference in the cardinality of each of the fields above, with DateID having a very high cardinality and TypeID having fewer than 10 distinct values.)