Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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
 

Print | posted on Thursday, June 12, 2008 4:18 PM | Filed Under [ Optimization SQL Server 2008 Administration SQL Server 2005 ]

Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET