The Multifunctioning DBA

Jun 8 2012   10:00AM GMT

Table Partitioning in MS SQL Server

Colin Smith Colin Smith Profile: Colin Smith

How to create a partitioned table in SQL Server. This example is based on weekly partitions by date but it could be anything you need.

1.    Create the filegroup(s) that the partition table will reside on.
a.    ALTER DATABASE databasename ADD filegroup [filegroupname]

2.    Create the file(s) that the partitioned table will reside on on the filegroup(s)
a.    ALTER DATABASE databasename ADD FILE (name = N’filename’, filename = N’Path to file.ndf’, SIZE = initiailsize, maxsize = maxsize, FILEGROWTH = autogrow size) TO filegroup filegroupname.

3.    Create the Partition Function.
a.    CREATE PARTITION FUNCTION
weekly52(DATETIME)
AS
RANGE RIGHT FOR VALUES
(‘20120521′, –week 1
‘20120528’, –week 2
‘20120604’, –week 3
‘20120611’, –week 4
‘20120618’, –week 5 etc…
‘20130610’ –week 52
)

4.    Create the Partition Scheme
a.    CREATE PARTITION SCHEME [weekly52_scheme]
AS PARTITION weekly52 TO
( [PRIMARY],
[parttable1],
[parttable1], –etc
[parttable1])

5.    Create a heap table on the Primary Filegroup to load the initial data set into.
a.    CREATE TABLE [dbo].[tablename](
[DATE] [smalldatetime] NOT NULL,
[SomeValue] [varchar](16) NOT NULL,
[SomeValue1] [varchar](16) NOT NULL,
[SomeValue2] [decimal](8, 2) NOT NULL,
[SomeValue3] [decimal](8, 2) NOT NULL,
[SomeValue4] [int] NOT NULL,
[SomeValue5] [decimal](8, 2) NOT NULL,
[SomeValue6] [int] NOT NULL)

6.    Load Data (This can be by any process. Verify that the date ranges are correct for the partition function. If not – then drop and recreate the partition function with the correct dates.)
7.    Create the clustered index on the heap table and put the clustered index on the new partition scheme that you created for the partitioned table.
a.    ALTER TABLE TableName
ADD CONSTRAINT Index_Constraint_Name
PRIMARY KEY CLUSTERED(DATE] ASC)
On filegroupname

8.    Verify that the data is in the correct partitions.
a.    SELECT $partition.PartitionFunctionName(o.date) AS [PARTITION#],
MIN(o.date) AS [MIN DATE],
MAX(o.date) AS [MAX DATE],
COUNT(*) AS [ROWS IN Part]
FROM parttest AS o
GROUP BY $partition.PartitionFunctionName(o.date)
ORDER BY [PARTITION#]

9.    If the data looks correct in the partitions then the table is set up and the data has been distributed to the correct partitions. You can double check in SSMS by right clicking on the TableName and going to the storage tab. You should see that table partitioned = true and you should see the number of partitions that the table spans.

 Comment on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: