Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SQLStudies

My name is Kenneth Fisher and I am Senior DBA for a large (multi-national) insurance company. I have been working with databases for over 20 years starting with Clarion and Foxpro. I’ve been working with SQL Server for 12 years but have only really started “studying” the subject for the last 3. I don’t have any real "specialities" but I enjoy trouble shooting and teaching. Thus far I’ve earned by MCITP Database Administrator 2008, MCTS Database Administrator 2005, and MCTS Database Developer 2008. I’m currently studying for my MCITP Database Developer 2008 and should start in on the 2012 exams next year. My blog is at www.sqlstudies.com.

Scripting a partitioned table or index, a caution.

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.

ScriptPartitionedTable1

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.

ScriptPartitionedTable2

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

ScriptPartitionedTable3

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”.

ScriptPartitionedTable4


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

Comments

Leave a comment on the original post [sqlstudies.com, opens in a new window]

Loading comments...