Scripting a partitioned table or index, a caution.

July 15, 2013 by Kenneth Fisher

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

Category: Microsoft SQL Server, Partitioning, SQLServerPedia Syndication, SSMS, T-SQL | Tags: code language, language sql, microsoft sql server, Partitioning, Scripting, sql statements, SSMS, T-SQL

14 thoughts on “Scripting a partitioned table or index, a caution.”

Something for the Weekend - SQL Server Links 19/07/13 • John Sansom says:

July 20, 2013 at 4:06 AM

[…] Scripting a partioned table or index, a caution – Kenneth Fisher (Blog) […]

greenantim says:

July 22, 2013 at 7:38 AM

Hi Ken,

Did you check the scripting options in SSMS? Tools > Options > SQL Server Object Explorer > Scripting. Under Table and view options verify that Script file groups is set to True. This should include the partition scheme is included when you use the Script Table as Create TO option.

Tim

Kenneth Fisher says:

July 22, 2013 at 8:57 AM

Thanks for catching this! And thanks in particular for mentioning it. I’d never have thought to look otherwise. “Script file groups” is for something else (I’m not sure what) and is set to true by default. The correct option appears to be “Script partition schemes”. I’ll add it to the post momentarily.

Thanks again!

Reply

Tom Hamilton says:

July 22, 2013 at 8:50 AM

Greenantim – On my 2008R2, even with Script file groups set to True, the result was as Ken noted. The only way I was able to get a partition-aware script was to use the ‘Task’ method. I don

Lee Everest says:

December 21, 2013 at 7:17 AM

Did not know. Thanks for posting, Kenneth!

Rosie Stringfellow says:

March 20, 2014 at 10:19 AM

I am having the same issue. I have “Script Parition Schemes” set to True but when I make a create table script, the indexes included in the script are not partitioned. The only way to get them with partitions is to use Generate Scripts under Tasks which is a much longer process. I am having a meeting with our company’s Microsoft representative tomorrow to discuss this issue. I will report the outcome of that discussion here.

Kenneth Fisher says:

March 20, 2014 at 10:36 AM

Please do! I would love to hear the outcome.

Reply
- Rosie Stringfellow says:
  
  March 21, 2014 at 10:55 AM
  
  I met with a Microsoft SQL Server Dedicated Support Engineer this morning. I demonstrated for him that while using my instance of SSMS 2012 installed in my laptop, the Create Table script with the option “Script partitions schemes = True” does not include the partition in the indexes. I also demonstrated that using >Tasks > Generate Scripts from the database does include the partitions on the indexes but it takes many more steps to do it that way, especially for a single table.
  
  The Support Engineer was unable to reproduce the same results I had, but he noted that he was generating scripts from his Local database, not from a remote database connection. I was connecting to our Development database server, not a Local database. Next he asked me to use Remote Desktop connection to log directly into our Development database server and try SSMS 2012 from there. Lo and behold the Create Table script did successfully include the partition information on the same table when I ran it directly from our Dev server, which in that case would have made it the Local instance. Next, while still remotely connected to Dev, I connected to the Database Engine for our Test environment which is still running SQL Server 2005. In this case, the Create Table script again successfully included the partitions on the indexes.
  
  The Support Engineer said he needed to make some fixes to one of his environments before he could properly test to see if he could replicate my results from a remote database server. If not, he plans to capture what SSMS is processing behind the scenes from my machine to see if he can debug it.
  
  Below is my version information from my laptop. I will report back when I have more news.
  
  Microsoft SQL Server Management Studio 11.0.3401.0
  Microsoft Analysis Services Client Tools 11.0.3401.0
  Microsoft Data Access Components (MDAC) 6.1.7601.17514
  Microsoft MSXML 3.0 4.0 5.0 6.0
  Microsoft Internet Explorer 9.0.8112.16421
  Microsoft .NET Framework 4.0.30319.18063
  Operating System 6.1.7601
  
  Reply
  - Kenneth Fisher says:
    
    March 21, 2014 at 11:00 AM
    
    Any chance you compared your local version of SSMS to the one on the instance? Frequently SSMS (if installed) on servers is patched while the local version on a workstation/laptop is not.
  - Rosie Stringfellow says:
    
    March 21, 2014 at 11:03 AM
    
    Yes Kenneth, we did. My last response has my version info from my laptop, and below is from our Development server:
    
    Microsoft SQL Server Management Studio 11.0.3373.0
    Microsoft Analysis Services Client Tools 11.0.3373.0
    Microsoft Data Access Components (MDAC) 6.1.7601.17514
    Microsoft MSXML 3.0 6.0
    Microsoft Internet Explorer 8.0.7601.17514
    Microsoft .NET Framework 4.0.30319.1022
    Operating System 6.1.7601
    
    The Management Studio, Client Tools, MDAC, and .NET Framework are all newer versions on my laptop than on our Development server. Both my laptop and our Development server had newer versions that the Support Engineer had installed too.
  - Rosie Stringfellow says:
    
    May 13, 2014 at 3:08 PM
    
    Hi Kenneth and everyone,
    
    I finally met with the Microsoft SQL Server Dedicated Support Engineer again today. As we were going through my SSMS settings, we found something that may be the culprit. If you go to >Tools >Options >SQL Server Object Explorer >Scripting and under the “General scripting options” section, look at “Script for database engine type” (fifth from the top). Mine was somehow blank, which is not an allowable value. The Engineer said it must have been some sort of failure during the installation process and he’s looking into it. When I selected “Standalone SQL Server” from the dropdown menu and closed the options window, I was finally able to create a table script that included partitioned indexes!!
    
    Please let me know if and of you have the blank setting like I did. I will pass that along to the MS Engineer.
    
    Thanks,
    Rosie

Rosie Stringfellow says:

May 13, 2014 at 3:09 PM

That was supposed to be “let me know if any of you” have the blank setting

Kenneth Fisher says:

May 13, 2014 at 3:16 PM

Can’t say I’ve ever seen that before. Glad to know it can happen though. Also glad you got it fixed!

Reply