tizu32Ur

1------------------------------- setup
2-- Create functions PartitionDate and PartitionQueryDate
3create function PartitionDate() returns date as
4begin
5  return GETDATE() - 91 -- returns 1/4/2019 today
6end
7go
8create function PartitionQueryDate() returns date as
9begin
10  return GETDATE() - 90 -- returns 1/5/2019
11end
12go
13
14-- Create partition func and scheme using above functions
15CREATE PARTITION FUNCTION order_pf (smalldatetime) AS RANGE RIGHT FOR VALUES (dbo.PartitionDate())
16CREATE PARTITION SCHEME order_ps AS PARTITION order_pf ALL TO ([PRIMARY])
17
18-- Create Order (pk, OrderDate, Fk), Customer (pk) tables.  Order is partitioned
19create table Customer
20(
21    id int primary key identity(1,1), 
22    FirstName varchar(255) not null
23)
24create table [Order]
25(
26    id int identity(1,1), OrderDate smalldatetime not null, 
27    CustomerId int not null,
28    CONSTRAINT [FK_Orders_Customer] FOREIGN KEY ([CustomerId]) REFERENCES Customer([id])
29) on order_ps(OrderDate);
30
31-- Add in indexes to Order: only OrderDate on the partition func
32CREATE CLUSTERED INDEX [Order_OrderDate] ON [Order]([OrderDate] ASC) ON [order_ps] ([OrderDate]);
33CREATE NONCLUSTERED INDEX [FK_Order_Customer] ON [Order](CustomerId) ON [order_ps] ([OrderDate]) -- seems to work the same with or without the partition reference.
34go
35
36-- Add some data before and after the partition break
37insert Customer values ('bob')
38insert [Order] values('12-31-2018', SCOPE_IDENTITY())
39insert Customer values ('hank')
40insert [Order] values('1-6-2019', SCOPE_IDENTITY())
41
42---------------------------- test
43-- verify a row per partition:
44SELECT $PARTITION.order_pf(OrderDate) as Partition_Number, COUNT(*) as Row_Count 
45FROM [Order]
46GROUP BY $PARTITION.order_pf(OrderDate)
47
48-- Simple queries with actual execution plan turned on.  The queries are logically equivalent.
49select COUNT(1) from [Order] where OrderDate > '1-5-2019'   -- Index seek Order_OrderDate; actual partition count 1
50select COUNT(1) from [Order] where OrderDate > dbo.PartitionQueryDate() -- Index seek Order_OrderDate; actual partition count 2
51
52-- Cleanup
53drop table if exists [Order]
54drop table if exists Customer
55drop partition scheme order_ps
56drop partition function order_pf
57drop function if exists PartitionDate
58drop function if exists PartitionQueryDate