Table Partitioning

-- Implementing Table Partitioning

ALTER DATABASE QA_DB
ADD FILEGROUP DBhitfg1

ALTER DATABASE QA_DB
ADD FILEGROUP DBhitfg2

ALTER DATABASE QA_DB
ADD FILEGROUP DBhitfg3

ALTER DATABASE QA_DB
ADD FILEGROUP DBhitfg4

ALTER DATABASE QA_DB
ADD FILEGROUP DBhitfg5

ALTER DATABASE QA_DB
ADD FILEGROUP DBhitfg6

ALTER DATABASE QA_DB
ADD FILE
( NAME = DBDBhitfg1,
FILENAME = 'D:\SQLData\DB_DBhitfg1.ndf',
SIZE = 1MB
)
TO FILEGROUP DBhitfg1
GO

ALTER DATABASE QA_DB
ADD FILE
( NAME = DBDBhitfg2,
FILENAME = 'D:\SQLData\DB_DBhitfg2.ndf',
SIZE = 1MB
)
TO FILEGROUP DBhitfg2
GO

ALTER DATABASE QA_DB
ADD FILE
( NAME = DBDBhitfg3,
FILENAME = 'D:\SQLData\DB_DBhitfg3.ndf',
SIZE = 1MB
)
TO FILEGROUP DBhitfg3
GO

ALTER DATABASE QA_DB
ADD FILE
( NAME = DBDBhitfg4,
FILENAME = 'D:\SQLData\DB_DBhitfg4.ndf',
SIZE = 1MB
)
TO FILEGROUP DBhitfg4
GO

ALTER DATABASE QA_DB
ADD FILE
( NAME = DBDBhitfg5,
FILENAME = 'D:\SQLData\DB_DBhitfg5.ndf',
SIZE = 1MB
)
TO FILEGROUP DBhitfg5
GO

ALTER DATABASE QA_DB
ADD FILE
( NAME = DBDBhitfg6,
FILENAME = 'D:\SQLData\DB_DBhitfg6.ndf',
SIZE = 1MB
)
TO FILEGROUP DBhitfg6
GO

USE QA_DB
CREATE PARTITION FUNCTION PartitionDateRange (datetime)
AS RANGE LEFT FOR VALUES ('1/1/2007', '1/1/2008', '1/1/2009', '1/1/2010', '1/1/2011')
GO


CREATE PARTITION SCHEME PartitionDateRangeScheme
AS PARTITION PartitionDateRange
TO (DBhitfg1, DBhitfg2, DBhitfg3, DBhitfg4, DBhitfg5, DBhitfg6)

-----------------------------------------
CREATE TABLE dbo.OrdersHitServiceDate
(HitID bigint NOT NULL IDENTITY(1,1),
ServiceDate datetime NOT NULL,
CONSTRAINT PK_Hits
PRIMARY KEY (HitID, ServiceDate))
ON [PartitionDateRangeScheme] (ServiceDate)
-----------------------------------------

insert into dbo.OrdersHitServiceDate(ServiceDate) select servicedate from dbo.orders


SELECT ServiceDate,
$PARTITION.ServiceDateRange (ServiceDate) Partition
FROM dbo.OrdersHitServiceDate