CODEWITHSUNDEEP
sql-serverazuresql-server-2019non-clustered-indexheap-table
ali tekrar
ali tekrar

Reputation: 71

Clustering key goes up to tree with non-clustered index in SQL Server

It seems in SQL Server before version 2019, the clustering key/keys goes up to tree structure with not unique non-clustered index. With bigger and multiple clustering key/keys, you gain much more wider and taller tree that costs you more storage size and memory size.

Because of that we used to separate PK from clustered key my questions are

  1. Have SQL Server 2019 and Azure changed in non-clustered indexing or not?
  2. Heaps do not have clustering key/keys at all, what's the way of indexing in heaps?

Upvotes: 0

Views: 62

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88971

Have SQL Server 2019 and Azure changed in non-clustered indexing or not

This behavior is older than many people on this site.

Because of that we used to separate PK from clustered

That is an almost-always-unnecessary micro-optimization.

Heaps do not have clustering key/keys at all, what's the way of indexing in heaps

Non-clustered non-unique indexes always have the row locator as index keys. For heaps the row locator is the ROWID (FileNo,PageNo,SlotNo).

If you want move the rows out from the leaf level of your wide PK, it's typically for a very large table. And so moving the rows to a clustered columstore index can be a good option. To do that just drop the clustered PK (this will leave the leaf level as a heap), create the CCI, and then recreate the PK as a nonclustered PK. eg

drop table if exists t
go
create table t(id int not null, a int, b int)

alter table t 
  add constraint pk_t 
  primary key clustered(id)

go

alter table t drop constraint pk_t

create clustered columnstore index cci_t on t

alter table t 
  add constraint pk_t 
  primary key nonclustered (id)

And if you have other non-clustered indexes drop them first, and only recreate them afterwords if you really need to. IE unique indexes, indexes supporting a foreign key, or indexes need to support specific queries. A CCI typically doesn't need lots of indexes since it's so efficient to scan.

Upvotes: 1

Related Questions