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

Table Partitioning, aligned indexes and SORT TOP N
2010-12-22
*****************************************************/
set nocount on
go

create table dbo.Data
(
	Id int not null identity(1,1),
	DateCreated datetime not null,
	DateModified datetime not null,
	Placeholder char(100) not null
		constraint Def_Data_Placeholder
		default 'a',
			
	constraint PK_Data
	primary key clustered(ID)
)
go

declare
	@I int
	,@MaxDate datetime
	
select @I = 0, @MaxDate = '2010-12-01'

begin tran
	while @I < 100000
	begin
		insert into dbo.Data(DateCreated, DateModified)
		values(
			DATEADD(day,-(@I % 365),@MaxDate),
			DATEADD(day,-(@I % 365) + 100,@MaxDate)
		)
		select @I += 1
	end
commit
go

create unique index IDX_Data_ModTime
on dbo.Data(DateModified, ID)
go

set statistics io on
go

-- table is not partitioned. Enable display execution plan
select top 100 * 
from dbo.Data
where DateModified > '2010-06-01'
order by DateModified, ID
go

set statistics io off
go

drop index IDX_Data_ModTime
on dbo.Data
go

alter table dbo.Data
drop constraint PK_Data
go

create partition function pfData(datetime)
as range left for values 
('2010-01-01', '2010-03-01', '2010-05-01',
'2010-07-01','2010-09-01','2010-11-01')
go

create partition scheme psData 
as partition pfData
all to ([primary])
go

alter table dbo.Data
add constraint PK_Data
primary key clustered(ID,DateCreated)
on psData(DateCreated)
go
	
create unique index IDX_Data_ModTime
on dbo.Data(DateModified, ID, DateCreated)
on psData(DateCreated)
go

set statistics io on
go

-- table is partitioned. Enable display execution plan
select top 100 * 
from dbo.Data
where DateModified > '2010-06-01'
order by DateModified, ID
go

select top 100 * 
from dbo.Data with (index = IDX_Data_ModTime)
where DateModified > '2010-06-01'
order by DateModified, ID
go