Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
USE tempdb
GO
CREATE PARTITION FUNCTION PF_Month (DATE) AS RANGE RIGHT FOR VALUES (
'2017-01-01',
'2017-02-01',
'2017-03-01',
'2017-04-01',
'2017-05-01',
'2017-06-01'
);
GO
CREATE PARTITION SCHEME PS_Month AS PARTITION PF_Month ALL TO ([Primary]);
GO
CREATE TABLE Logs
(
Id INT NOT NULL,
DateRecorded DATE NOT NULL,
FixStatus INT NOT NULL
);
GO
-- The previous primary key was invalid; the primary key
-- must include the partitioning column.
ALTER TABLE Logs
ADD CONSTRAINT PK_Logs PRIMARY KEY (Id, DateRecorded)
ON PS_Month(DateRecorded);
GO
-- Create a partition-aligned index that includes all
-- columns in your SELECT statements
CREATE NONCLUSTERED INDEX [IX_DateRecorded] ON Logs(DateRecorded)
INCLUDE (FixStatus) ON PS_Month(DateRecorded)
GO
-- Now that your index is properly defined, this query produces
-- an ordered index scan without a sort
SELECT * FROM Logs ORDER BY DateRecorded
GO
-- Cleanup
DROP TABLE Logs
GO
DROP PARTITION SCHEME PS_Month
GO
DROP PARTITION FUNCTION PF_Month
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment