Heaps and indexes

Question

Post reply

Heaps and indexes

Steve Jones - SSC Editor

SSC Guru

Points: 736212
More actions
October 24, 2016 at 8:16 pm

#72656

Comments posted to this topic are about the item Heaps and indexes

Viewing 14 posts - 1 through 13 (of 13 total)

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

Carlo Romagnano SSC-Insane Points: 22669 More actions · Answer 1

Carlo Romagnano

SSC-Insane

Points: 22669

October 25, 2016 at 2:07 am

#1908518

Very interesting!

Thanks!

🙂

edwardwill SSCertifiable Points: 5851 More actions · Answer 2

Weird. I'm not a DBA so I likely didn't really understand, but I executed this:

select * from sys.indexes -- returned 159 rows

I then executed the first statement (CREATE TABLE) and again executed:

select * from sys.indexes -- returned 159 rows

Finally, I executed the second statement (CREATE NONCLUSTERED INDEX ) and again executed:

select * from sys.indexes -- returned 159 rows

The count of rows in sys.indexes didn't change from the first execution.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 3

Good question, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

edwardwill (10/25/2016)
Weird. I'm not a DBA so I likely didn't really understand, but I executed this:
select * from sys.indexes -- returned 159 rows
I then executed the first statement (CREATE TABLE) and again executed:
select * from sys.indexes -- returned 159 rows
Finally, I executed the second statement (CREATE NONCLUSTERED INDEX ) and again executed:
select * from sys.indexes -- returned 159 rows
The count of rows in sys.indexes didn't change from the first execution.

Did you execute the statements in the correct database context?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

edwardwill SSCertifiable Points: 5851 More actions · Answer 5

Koen Verbeeck (10/25/2016)
edwardwill (10/25/2016)
Weird. I'm not a DBA so I likely didn't really understand, but I executed this:
select * from sys.indexes -- returned 159 rows
I then executed the first statement (CREATE TABLE) and again executed:
select * from sys.indexes -- returned 159 rows
Finally, I executed the second statement (CREATE NONCLUSTERED INDEX ) and again executed:
select * from sys.indexes -- returned 159 rows
The count of rows in sys.indexes didn't change from the first execution.
Did you execute the statements in the correct database context?

I executed the DDL statements against my test database, and the DML statements against master.

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 6

edwardwill (10/25/2016)
I executed the DDL statements against my test database, and the DML statements against master.

Then you get the index information of the master database.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

edwardwill SSCertifiable Points: 5851 More actions · Answer 7

Koen Verbeeck (10/25/2016)
edwardwill (10/25/2016)
I executed the DDL statements against my test database, and the DML statements against master.
Then you get the index information of the master database.

Cool. I learnt something!