All Questions
Tagged with database-internals clustered-index
6 questions
0
votes
2
answers
771
views
Understanding of the %%lockres%% and %%physloc%% pseudo columns with index hint
I am experimenting with clustered and non-clustered indexes in SQL Server and I have noticed something interesting.
Here is a simplified version of my T-SQL code:
IF OBJECT_ID('dbo.dept') IS NOT NULL ...
- 1,239
8
votes
3
answers
4k
views
Postgres heap vs SQL Server clustered index
I am transitioning from SQL Server to Postgres, and one of the biggest things for me to digest is the non-existence of the "clustered key" that sorts the data in Postgres.
Can someone share their ...
- 93
2
votes
0
answers
73
views
How is page data rearranged after clustered index is created?
Can anyone please explain what happens at the page level when clustered indexes are created? How do pages rearrange data once clustered indexes are created?
- 141
11
votes
2
answers
5k
views
SUM of DATALENGTHs not matching table size from sys.allocation_units
I was under the impression that if I were to sum the DATALENGTH() of all fields for all records in a table that I would get the total size of the table. Am I mistaken?
SELECT
SUM(DATALENGTH(Field1)) ...
- 1,801
11
votes
2
answers
15k
views
Why disabling a clustered index makes the table inaccessible?
When an index is disabled, the definition remains in the system catalog but is no longer used. SQL Server does not maintain the index (as data in the table changes), and the index cannot be used to ...
- 255
2
votes
1
answer
1k
views
Index with multiple leaf levels
I have a three-column (int, smallint, smallint) composite clustered index with three leaf levels. My question is how and when does SQL Server create multiple leaf levels (index_level 0) for the same ...
- 23