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.