Deep Drive into Indexes

Indexes In depth Knowledge

--CREATE DATABASE HeapsDB

use HeapsDB
go


CREATE TABLE NumbersTable(NumberValue bigint not null, BiggerNumber bigint not null, CharacterColumn char(50))

DBCC IND('HeapsDB',NumbersTable ,-1)

We have around 14 types of pages
Page type 1 is a datpage
and Page type 10 is a IAM page

The Page is of 3 Parts.
1. Header
2. Data
3. Row Offset

Header for 96 bytes
Data depends on DataType + 7 Bytes(Identify the Nulls)
Row Offset is for Address of Every Record

One page = 8192 Bytes

DataPages --> Actual data Get Stored.
IAM page --> Locates the DataPages.

DBCC TRACEON(3604)
DBCC PAGE('DATABASENAME',1,PAGEID,1)
GO

insert into NumbersTable
select
NumberValue,
NumberValue + 5000000,
LEFT(replicate((cast(NumberValue as varchar(50))),50),50)
from
(
select NumberValue = ROW_NUMBER() over( order by newid() asc) from
master..spt_values a
cross apply master..spt_values b where a.type = 'P' and a.number <= 200 and a.number>0 and b.type = 'P' and b.number <= 200 and b.number>0
) a;

select page_count, index_depth,page_Level = index_level,page_count,record_count,
* from sys.dm_db_index_physical_stats (db_id(),OBJECT_ID('NumbersTable'),NULL, NULL, 'DETAILED')

SELECT DB_NAME([ddips].[database_id]) AS [DatabaseName]
, OBJECT_NAME([ddips].[object_id]) AS [TableName]
, [i].[name] AS [IndexName]  FROM [sys].[dm_db_index_physical_stats](DB_ID(), NULL, NULL, NULL, NULL) AS ddips
INNER JOIN [sys].[indexes] AS i
ON [ddips].[index_id] = [i].[index_id]
AND [ddips].[object_id] = [i].[object_id]

--Page Allocation
SELECT allocated_page_iam_page_id ,allocated_page_page_id, next_page_page_id,previous_page_page_id
FROM sys.dm_db_database_page_allocations  (db_id(),OBJECT_ID('NumbersTable'),NULL, NULL, 'DETAILED')
WHERE page_type_desc is not null and page_type_desc = 'data_page'

--Create a Clustered Index
create clustered index CIX_NumbersTable on NumbersTable(NumberValue) with (maxdop=1)

-- Look into the data and Index pages

SELECT allocated_page_iam_page_id ,allocated_page_page_id, next_page_page_id,previous_page_page_id , page_level,page_type_desc

FROM sys.dm_db_database_page_allocations  (db_id(),OBJECT_ID('NumbersTable'),NULL, NULL, 'DETAILED')

WHERE page_type_desc is not null order by page_level desc

--and page_type_desc = 'index_page'