can Columnstore Indexe co- exists cluster index

Question

can Columnstore Indexe co- exists cluster index

nightowl23

Mr or Mrs. 500

Points: 522
More actions
August 18, 2015 at 3:26 pm

#319966

hi,
i have created a fact table which has unique cluster index as below,
CREATE UNIQUE CLUSTERED INDEX [FactSales_SalesID] ON [dbo].[FactSales] (salesid ASC)
WITH (DATA_COMPRESSION = PAGE)
GO
however later when i add CLUSTERED COLUMNSTORE INDEXES :
CREATE CLUSTERED COLUMNSTORE INDEX CSI_FactSales
ON dbo.FactSales WITH (DATA_COMPRESSION = COLUMNSTORE)
GO
it prompts error.
Msg 35372, Level 16, State 3, Line 167
You cannot create more than one clustered index on table 'dbo.FactSales'. Consider creating a new clustered index using 'with (drop_existing = on)' option.
can anyone help? many thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Error message is very clear, you cannot create more than one clustered index on the same table.

CREATE UNIQUE CLUSTERED INDEX [FactSales_SalesID] ON [dbo].[FactSales] (salesid ASC)
WITH (DATA_COMPRESSION = PAGE)
GO
CREATE CLUSTERED COLUMNSTORE INDEX CSI_FactSales
ON dbo.FactSales WITH (DATA_COMPRESSION = COLUMNSTORE)

You can either have a (normal) clustered index, or a clustered columnstore.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

nightowl23 Mr or Mrs. 500 Points: 522 More actions · Answer 2

nightowl23

Mr or Mrs. 500

Points: 522

August 18, 2015 at 3:33 pm

#1820905

what is the reason, why they can't co-exists?

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Same reason you can't put two normal clustered indexes on a single table. A clustered index *is* the table. It's either in a row-based format (CREATE [UNIQUE] CLUSTERED INDEX) or it's in a columnar format (CREATE CLUSTERED COLUMNSTORE INDEX), a single table can't be both at once.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

But you can create a nonclustered columnstore with a row-based clustered index.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning