Implementing partition table to improve
performance, a feature in 2008/2005 is very easy to do.
Steps:
1) create filegroups and database files for partitioning.
This can be seen in the first portion of the above script.
2) Create a partition scheme and partition function. This
can be seen in the second portion of the above script. Remember to use generic
name if it is to be used by multiple tables.
3) create a table to use the partition scheme. This can
be seen in the third portion of the above script.
4) once inserted all records, you can use the last query
to see the partion being used by records.
For we have table used to be small but grown over a few
million records now, I have to create a new partition table, move over all
records. Remember the key column should be on the cluster index (the
fastest).
Violla, we are ready. You can use the same partition
scheme on another table.
-- 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
|