Multiple “clustered” indexes on a SQL Server table
You can only have one clustered index on each SQL Server table, however there are two ways to create pseudo clustered indexes on a table:
- Create clustered indexed on a view (indexed view) that covers the table
- Create covering index on the entire table
Let's look at the following table:
CREATE TABLE t1 ( c1 int, c2 varchar(5), c3 bigint, c4 datetime, CONSTRAINT PK_t1 PRIMARY KEY CLUSTERED (c1, c2, c3, c4) ) GO
For option 1 above, here's the indexed view and clustered index:
SET NUMERIC_ROUNDABORT OFF; SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON; GO CREATE VIEW v1 WITH SCHEMABINDING AS SELECT c1, c2, c3, c4 FROM dbo.t1 GO CREATE UNIQUE CLUSTERED INDEX cidx_v1 ON dbo.v1(c2, c3, c4, c1) GO
For option 2 above, here’s the covering index:
CREATE INDEX idx_PseudoClust ON t1(c3, c4) INCLUDE (c2, c1) GO
So why would you want to do this? I'm sure that there are some very good reasons, but I've never had the need to do it. I recently had the discussion with a Microsoft engineer and thought it was interesting enough to share.
Legacy Comments
Steve
2009-11-10 |
re: Multiple “clustered” indexes on a SQL Server table We use indexed views with clustered indexes on them all of the time. We typically don't cover the entire table though when doing it. Here is one reason that we use this technique: You have a table like this: MyTable Id uniqueidentifier PK (Clustered) ForeignId uniqueidentifier FK ForeignId2 uniqueidentifier FK OtherField nvarchar(100)..... MyTable_ByForeignId (this is an indexed view) ForeignId Id PK (Clustered) will be ForeignId, Id MyTable_ByForeignId2 (this is an indexed view) ForeignId2 Id PK (Clustered) will be ForeignId2, Id This table has the primary key on the Id, because you want to be able to read, write and delete very fast for records in this table. But you need to be able to lookup data in this table by either ForeignId or ForeignId2. What you would do is create an indexed view (schema bound) (or 2 one for each) and setup the cluster so that ForeignId is the first element and Id is the second element. Then you can write a query to join the data. SELECT MyTable.Id, MyTable.ForeignId, MyTable.ForeignId2, MyTable.OtherField FROM MyTable JOIN MyTable_ByForeignId WITH(NOEXPAND) ON MyTable_ByForeignId.Id = MyTable.Id AND MyTable_ByForeignId.ForeignId = @SomeValue JOIN MyTable_ByForeignId2 WITH(NOEXPAND) ON MyTable_ByForeignId2.Id = MyTable.Id AND MyTable_ByForeignId2.ForeignId2 = @SomeValue2 When you check the performance you'll note that the query will use Clustered Index Seeks instead of scans to find the records matching the query. This can yield extremely fast performance for reads. There isn't much of a performance hit for updates since the foreign keys rarely (if ever) change....but make sure you don't update them unless they are changed, meaning don't just update them to themselves if you can avoid it so that the indexed views don't need to be updated when you update the table. We've found this technique to by extremely helpful for our application. |
Michael Valentine Jones
2009-11-10 |
re: Multiple “clustered” indexes on a SQL Server table I used option 2 once in a data warehouse application for an inventory balance table. The composite clustered primary key was product number and location number. This worked fine for queries that were interested mainly in particular product numbers. Queries that were interested mainly in totals by location caused tables scans because products for a particular location were distributed fairly evenly throughout the table. I created a covering index that reversed the order of the first two columns to location number and product number. |