I was researching a problem with a partitioned table that had somehow become unpartitioned and discovered something rather interesting.

First here is a script to create a simple partitioned table so you can follow along if you like.

CREATE PARTITION FUNCTION fn_PartTestFunction (int)
AS RANGE LEFT FOR VALUES  (1,2,3,4)
GO
CREATE PARTITION SCHEME sch_PartTestScheme
AS PARTITION [fn_PartTestFunction]
ALL TO ( [PRIMARY] )
GO
CREATE TABLE PartTable (
PartColumn Int,
Column1 Int,
Column2 Int,
CONSTRAINT pk_PartTable PRIMARY KEY (PartColumn, Column1) ON sch_PartTestScheme (PartColumn)
) ON sch_PartTestScheme (PartColumn)
GO

Once the PartTable is created right click on it in the object explorer and script the table out. You could also script out the primary key or clustered index with a similar result.

Here is the script you get.

CREATE TABLE [dbo].[PartTable](
[PartColumn] [int] NOT NULL,
[Column1] [int] NOT NULL,
[Column2] [int] NULL,
 CONSTRAINT [pk_PartTable] PRIMARY KEY CLUSTERED 
(
[PartColumn] ASC,
[Column1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON)
)

You will notice that the partition scheme is not mentioned at all. This could be a problem if I am scripting this table out to create it in another location, or to save it in a version store somewhere.

As a side note, during my testing I scripted out the clustered index (or primary key) and noticed the partition scheme was also not mentioned. Interestingly (at least to me) when I dropped the clustered index (or primary key) and then recreated it without mentioning the partition scheme, the table and index both remained partitioned. I guess because I didn’t explicitly mentioned the filegroup location/partitioning option. Now if I ran a script like this:

ALTER TABLE [dbo].[PartTable] ADD  CONSTRAINT [pk_PartTable] PRIMARY KEY CLUSTERED 
(
[PartColumn] ASC,
[Column1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, 
ALLOW_PAGE_LOCKS  = ON)
ON [PRIMARY]

The partitioning is now removed because I have explicitly mentioned the filegroup location/partitioning option for the table.

So how do I script my partitioned table out and include the partition scheme associated with it?

I use the “Generate Scripts” option. Right click on the database name, go to Tasks, then Generate Scripts.

Then pick “Select specific database objects” and select the table from the tables list.

Proceed through the wizard and you get the following script.

CREATE TABLE [dbo].[PartTable](
[PartColumn] [int] NOT NULL,
[Column1] [int] NOT NULL,
[Column2] [int] NULL,
 CONSTRAINT [pk_PartTable] PRIMARY KEY CLUSTERED 
(
[PartColumn] ASC,
[Column1] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [sch_PartTestScheme]([PartColumn])
) ON [sch_PartTestScheme]([PartColumn])
GO

Note the partition scheme is now mentioned.

I’m using the 2008 R2 tools, so this may have been fixed (assuming it’s considered a bug) in the 2012 tools. So if anyone is using them and feels like testing it let me know.

Update

greenantim pointed out that I should take a look in the SSMS options. Once there I found “Script partition schemes”. Set this to TRUE and the partition schemes will start showing up when you do a “Script as”.

Filed under: Microsoft SQL Server, Partitioning, SQLServerPedia Syndication, SSMS, T-SQL Tagged: code language, language sql, microsoft sql server, Partitioning, Scripting, sql statements, SSMS, T-SQL