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

Select top N using aligned non-clustered index on partitioned table
2011-12-18
*****************************************************/
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
	@MaxDate datetime
	
select @MaxDate = '2011-12-01'

;with CTE(ID)
as
(
	select 1 
	union all
	select ID + 1
	from CTE
	where ID < 1000000
)
insert into dbo.Data(DateCreated, DateModified)
select 
	DATEADD(day,-(ID % 365),@MaxDate),
	DATEADD(day,-(ID % 365) + (ID % 365) % 5,@MaxDate)
from CTE
option (MAXRECURSION 0)
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 > '2011-06-30'
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 right for values 
('2011-01-01', '2011-04-01', '2011-07-01',
'2011-10-01','2012-01-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 > '2011-06-30'
order by DateModified, ID
go

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



-- "Kind of" solution
;with PData1(ID, DateModified)
as
(	-- DateCreated < 2011-01-01
	select top 100 ID, DateModified
	from dbo.Data
	where DateModified > '2011-06-30' and $Partition.pfData(DateCreated)  = 1
	order by DateModified, ID
)
,PData2(ID, DateModified)
as
(	-- DateCreated >= 2011-01-01 and < 2011-04-01
	select top 100 ID, DateModified
	from dbo.Data
	where DateModified > '2011-06-30' and $Partition.pfData(DateCreated)  = 2
	order by DateModified, ID
)
,PData3(ID, DateModified)
as
(	-- DateCreated >= 2011-04-01 and < 2011-07-01
	select top 100 ID, DateModified
	from dbo.Data
	where DateModified > '2011-06-30' and $Partition.pfData(DateCreated)  = 3
	order by DateModified, ID
)
,PData4(ID, DateModified)
as
(	-- DateCreated >= 2011-07-01 and < 2011-10-01
	select top 100 ID, DateModified
	from dbo.Data
	where DateModified > '2011-06-30' and $Partition.pfData(DateCreated)  = 4
	order by DateModified, ID
)
,PData5(ID, DateModified)
as
(	-- DateCreated >= 2011-10-01 and < 2012-01-01
	select top 100 ID, DateModified
	from dbo.Data
	where DateModified > '2011-06-30' and $Partition.pfData(DateCreated)  = 5
	order by DateModified, ID
)
,PData6(ID, DateModified)
as
(	-- DateCreated >= 2012-01-01
	select top 100 ID, DateModified
	from dbo.Data
	where DateModified > '2011-06-30' and $Partition.pfData(DateCreated)  = 6
	order by DateModified, ID
)
,AllData(ID)
as
(
	select top 100 ID
	from
		(
			select ID, DateModified from PData1 union all
			select ID, DateModified from PData2 union all
			select ID, DateModified from PData3 union all
			select ID, DateModified from PData4 union all
			select ID, DateModified from PData5 union all
			select ID, DateModified from PData6 
		) t
	order by DateModified, ID
)
select d.Id, d.DateCreated, d.DateModified, d.Placeholder 
	,$Partition.pfData(DateCreated) as [Partition #]
from Data d join AllData a on d.Id = a.Id
order by d.DateModified, d.ID
go


