0

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?
trincot
  • 317,000
  • 35
  • 244
  • 286
ali tekrar
  • 71
  • 8
  • Your question is unclear as to what "goes up to tree structure" means. The clustered index key is stored in the leaf nodes of non-clustered indexes as the row-locator, not the non-leaf nodes. In the case of a heap, the row-locator is the RID (file/page/slot). I don't believe the architecture has changed in SQL 2019. – Dan Guzman Jun 16 '20 at 16:06
  • thanks but when we use not unique non-clustered index , the clustered index key goes up to the root and you can see it in your database , just use `EXEC('DBCC IND([OutputTableName], "[yourtable]" , index you interested in) Select [IndexLevel],[PagePID] From [IndexPagesOutput] Order BY [IndexLevel] DESC ` to find your root page then `DBCC TRACEON (3604); DBCC PAGE(OutputTableName , 1 , root page number , index you interested in)` this is your root page and in this page you can see your not unique non-clustered index and the clustered index key that you use – ali tekrar Jun 16 '20 at 16:53

1 Answers1

1

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.

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
  • That is an almost-always-unnecessary micro-optimization, why? for example when we use multiple guid fk for pk , these multiple guid goes up to tree with every not unique non-clustered index and if we have several of them the size of tree became bigger than original table – ali tekrar Jun 16 '20 at 17:07
  • Yes but how much does that matter? Can you measure it in a realistic way? How many _additional_ indexes are created? – David Browne - Microsoft Jun 16 '20 at 17:10
  • when we have over million records ,its became larger and larger , its need more and more storage size and memory size then performance get so bad that no one wants to use it anymore , and for what? just not separate the PK from clustered key – ali tekrar Jun 16 '20 at 17:19
  • But you _still have_ the index with the wide keys. You've just moved the data rows to the leaf level of a different index. – David Browne - Microsoft Jun 16 '20 at 17:24
  • that's the point and you say it like it's good , it's an unnecessary and Annoying – ali tekrar Jun 16 '20 at 17:32
  • You're proposing to introduce an _additional_ index to optimize other non-clustered indexes. It's a tradeoff. Sometimes it's a good idea. More often not. Often just using a Heap or a Clustered Columnstore for the main row store is better. – David Browne - Microsoft Jun 16 '20 at 17:35
  • I dont use Clustered Columnstore yet , ef core support it? if yes , please send a link to how to use it – ali tekrar Jun 16 '20 at 17:42
  • I updated my answer with an example of moving from a clustered PK, to a non-clustered PK + clustered Columnstore index. – David Browne - Microsoft Jun 16 '20 at 17:53
  • 1
    thanks for your time, its not possible with ef core ? – ali tekrar Jun 16 '20 at 18:05