/****************************************************
AboutSQLServer.com blog
Written by Dmitri Korotkevitch

A few more words about Uniquifier and Uniqueness of the Clustered Index
2011-11-24
*****************************************************/

set nocount on
set xact_abort on
go

create table dbo.UniqueCI
(
	KeyValue int not null,
	ID int not null,
	Data char(992) not null,
)
go

create unique clustered index IDX_UniqueCI_KeyValue
on dbo.UniqueCI(KeyValue)
go

create table dbo.NonUniqueCINoDups
(
	KeyValue int not null,
	ID int not null,
	Data char(992) not null,
)
go

create /*unique*/ clustered index IDX_NonUniqueCINoDups_KeyValue
on dbo.NonUniqueCINoDups(KeyValue)
go

create table dbo.NonUniqueCIDups
(
	KeyValue int not null,
	ID int not null,
	Data char(992) not null,
)
go

create /*unique*/ clustered index IDX_NonUniqueCIDups_KeyValue
on dbo.NonUniqueCIDups(KeyValue)
go
-- Populating data
begin tran
	;with CTE(ID)
	as
	(
		select 1
		union all 
		select ID + 1 from CTE where ID < 100000
	)
	insert into dbo.UniqueCI(KeyValue, ID, Data)
		select ID, ID, REPLICATE('a',992)
		from CTE
	option (maxrecursion 0)
	
	;with CTE(ID)
	as
	(
		select 1
		union all 
		select ID + 1 from CTE where ID < 100000
	)
	insert into dbo.NonUniqueCINoDups(KeyValue, ID, Data)
		select ID, ID, REPLICATE('b',992)
		from CTE
	option (maxrecursion 0)
	

	;with CTE(ID)
	as
	(
		select 1
		union all 
		select ID + 1 from CTE where ID < 100000
	)
	insert into dbo.NonUniqueCIDups(KeyValue, ID, Data)
		select convert(int,ID / 1000) + 1, ID, REPLICATE('c',992)
		from CTE
	option (maxrecursion 0)	
commit
go

-- Let's check index statistics
SELECT index_level, page_count, min_record_size_in_bytes
	, max_record_size_in_bytes, avg_record_size_in_bytes, *
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(N'dbo.UniqueCI'), 1, NULL , 'DETAILED')

SELECT index_level, page_count, min_record_size_in_bytes
	, max_record_size_in_bytes, avg_record_size_in_bytes, *
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(N'dbo.NonUniqueCINoDups'), 1, NULL , 'DETAILED')

SELECT index_level, page_count, min_record_size_in_bytes
	, max_record_size_in_bytes, avg_record_size_in_bytes, *
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(N'dbo.NonUniqueCIDups'), 1, NULL , 'DETAILED')
go

-- Let's create non-clustered indexes on ID
create nonclustered index IDX_UniqueCI_ID
on dbo.UniqueCI(ID)

create nonclustered index IDX_NonUniqueCINoDups_ID
on dbo.NonUniqueCINoDups(ID)

create nonclustered index IDX_NonUniqueCIDups_ID
on dbo.NonUniqueCIDups(ID)
go

-- Let's check index statistics on NCI
SELECT index_level, page_count, min_record_size_in_bytes
	, max_record_size_in_bytes, avg_record_size_in_bytes, *
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(N'dbo.UniqueCI'), 2, NULL , 'DETAILED')

SELECT index_level, page_count, min_record_size_in_bytes
	, max_record_size_in_bytes, avg_record_size_in_bytes, *
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(N'dbo.NonUniqueCINoDups'), 2, NULL , 'DETAILED')

SELECT index_level, page_count, min_record_size_in_bytes
	, max_record_size_in_bytes, avg_record_size_in_bytes, *
FROM sys.dm_db_index_physical_stats
(DB_ID(), OBJECT_ID(N'dbo.NonUniqueCIDups'), 2, NULL , 'DETAILED')
go


-- Let's look at the actual pages. Change values below
-- First, let's find the page #
DBCC IND
(
	'AboutSqlServerDb' /* DB Name */, 
	'dbo.UniqueCI' /*Table Name */, 
	1 /* Index ID */
) 
go

-- Choose the page with IndexLevel = 0 and PrevPageFID = 0
DBCC TRACEON(3604) -- Enable output to console
DBCC PAGE
(
	'AboutSqlServerDb' /* DB Name */, 
	1, /* File Id   */
	3134, /* Page ID - Use PagePID from DBCC IND*/
	3 /* Output Stype */
)
go

-- NonUniqueCINoDups
DBCC IND
(
	'AboutSqlServerDb' /* DB Name */, 
	'dbo.NonUniqueCINoDups' /*Table Name */, 
	1 /* Index ID */
) 
go

-- Choose the page with IndexLevel = 0 and PrevPageFID = 0
DBCC PAGE
(
	'AboutSqlServerDb' /* DB Name */, 
	1, /* File Id   */
	3143, /* Page ID - Use PagePID from DBCC IND*/
	3 /* Output Stype */
)
go

-- NonUniqueCIDups
DBCC IND
(
	'AboutSqlServerDb' /* DB Name */, 
	'dbo.NonUniqueCIDups' /*Table Name */, 
	1 /* Index ID */
) 
go

-- Choose the page with IndexLevel = 0 and PrevPageFID = 0
DBCC PAGE
(
	'AboutSqlServerDb' /* DB Name */, 
	1, /* File Id   */
	28288, /* Page ID - Use PagePID from DBCC IND*/
	3 /* Output Stype */
)
go

