Horizontal partitioning, Enterprise style
CREATE PARTITION FUNCTION pfOrderDate
(
DATETIME
)
AS RANGE RIGHT FOR VALUES (
'20000101',
'20010101',
'20020101'
)
GO
CREATE PARTITION SCHEME psYak
AS PARTITION pfOrderDate
ALL TO ([PRIMARY])
GO
CREATE TABLE Orders
(
OrderID INT NOT NULL,
CustomerID VARCHAR(15) NOT NULL,
OrderDate DATETIME NOT NULL
)
ON psYak(OrderDate)
GO
CREATE CLUSTERED INDEX IX_OrderID
ON Orders
(
OrderID
)
CREATE NONCLUSTERED INDEX IX_OrderDate
ON Orders
(
OrderDate
)
INCLUDE (
OrderID,
CustomerID
)
GO
INSERT Orders
(
OrderID,
CustomerID,
OrderDate
)
SELECT 1, 'Peso', '20011225' UNION ALL
SELECT 2, 'Jennie', '20020314'
SELECT OrderID,
OrderDate
FROM Orders
WHERE OrderDate = '20011225'
SELECT OrderID,
OrderDate
FROM Orders
WHERE OrderID = 1
DROP TABLE Orders
DROP PARTITION SCHEME psYak
DROP PARTITION FUNCTION pfOrderDate