SQL Server Partition Management

Created Issue: compression on partitions seems to be ignored [5338]

binarymechanic — Mon, 26 Oct 2009 19:11:28 GMT

Scripting a partitioned temp/staging table does not include reference to source table's level of page level compression.

Created Issue: Partition functions with a '.' in its name are not handled properly (and maybe so do the partition schemes). [5337]

MiSchroe — Mon, 26 Oct 2009 17:00:49 GMT

You can create a partition function with CREATE PARTITION FUNCTION [Person.PF_Address](int) RANGE LEFT FOR VALUES (10000, 20000, 30000);

This function will not be handled properly due to the dot ('.') in the name. It should work if you quote the name of the partition function with '[' and ']'. Maybe the same error will rise with a partition scheme with a dot inside the name.

Workaround: Changing the name of the partition function (e.g. replace all '.' with '_').

Reviewed: Partition Management Release 2.0 (Oct 09, 2009)

mufford — Fri, 09 Oct 2009 15:33:30 GMT

Rated 5 Stars (out of 5) - This application does exactly what it says. Once you learn how to use it, it makes building staging tables easy for switching partitions.

Closed Issue: Release 1.0 known issues: index creation order; 10-min. timeout

Stuarto — Mon, 23 Feb 2009 20:25:48 GMT

The following are known issues for Release 1.0. I've been informed by the developer(s) that these issues will be remedied with the next release, slated for 2008 Q4. The next release will also provide support for SQL Server 2008.

* There is no order enforced when building indexes, whereas it would be advantageous to create clustered indexes before creating nonclustered indexes on a non-empty table.

* Issued statements are given 10 minutes to run before failing with a time-out error. For example, if an individual index requires more than 10 minutes to create, an error will occur at the 10-minute mark. The remedy is to explicitly set the ServerConnection.StatementTimeout property to an appropriate value higher than the default value; the default value for this property is 600 (seconds, or 10 minutes).

Thanks to the developer(s) for such a valuable utility.
Comments: Fixed with Release 2.0

Updated Release: Partition Management Release 2.0 (Feb 23, 2009)

stuarto — Mon, 23 Feb 2009 20:24:26 GMT

Release 2.0 adds support for SQL Server 2008, but is also fully compatible with SQL Server 2005.
The release consists of:
• A Readme file
• The Executable
• The source code (Visual Studio project)

This release is designed and tested to work with SQL Server 2005, Service Pack 1 or later.

Enhancements from V1 include:

Support for automatically handling partition-aligned indexed views in SQL Server 2008
Handles sparse columns and filtered indexes in SQL Server 2008
Accommodates the new date and time data types in SQL Server 2008
Clustered index is always created first
New command “CreateStagingClusteredIndex” supports creating only the Clustered Index to optimize a load of presorted data; additional indexes can be automatically built later using “IndexStaging”
Support for Nullable partitioning keys
Support for compressed tables and indexes in SQL Server 2008
Default constraints are inherited from the partitioned table to assist with data loading into staging tables
Connection Timeout eliminated

Test scripts for both SQL Server 2005 and SQL Server 2008 are included in the Visual Studio project. To modify or compile the source code, please use Visual Studio 2008 SP1 or later.

Released: Partition Management Release 2.0 (Feb 23, 2009)

Mon, 23 Feb 2009 20:24:26 GMT

Support for automatically handling partition-aligned indexed views in SQL Server 2008
Handles sparse columns and filtered indexes in SQL Server 2008
Accommodates the new date and time data types in SQL Server 2008
Clustered index is always created first
New command “CreateStagingClusteredIndex” supports creating only the Clustered Index to optimize a load of presorted data; additional indexes can be automatically built later using “IndexStaging”
Support for Nullable partitioning keys
Support for compressed tables and indexes in SQL Server 2008
Default constraints are inherited from the partitioned table to assist with data loading into staging tables
Connection Timeout eliminated

Test scripts for both SQL Server 2005 and SQL Server 2008 are included in the Visual Studio project. To modify or compile the source code, please use Visual Studio 2008 SP1 or later.

Updated Wiki: Home

Stuarto — Mon, 23 Feb 2009 20:22:01 GMT

Project Description
Sliding Window technique is commonly used with partitioned tables to manage large data volumes. It allows efficient loading of new data and archival or removal of the old data. The challenge in a sliding window scenario is to create the staging table(s) correctly for efficient partition SWITCH operations -- matching all columns, indexes, constraints, storage locations, and potentially indexed views with those of the partitioned table's partition of interest. This utility provides a command line interface to:

1. Remove all the data from one partition by switching it out to a staging table. It creates the required staging table.
2. Create a staging table for loading data into a partition. The staging table can be created with or without indexes -- if created without indexes this utility provides a separate command to create appropriate indexes on the staging table, before SWITCHing it into the partitioned table.

The commands can be invoked from other scripts for end-to-end sliding window scenarios. Using the utility allows you to avoid maintaining partition maintenance scripts that must remain synchronized with index or column changes in the permanent table, since necessary staging objects can be created on-demand.

Refer to the attached readme.htm file for more details and command syntax.

Created Issue: Release 1.0 known issues: index creation order; 10-min. timeout

TJY — Thu, 09 Oct 2008 03:03:46 GMT

UPDATED WIKI: Home

sanjaymi — Sat, 05 Apr 2008 15:38:12 GMT

Project Description
Sliding Window technique is commonly used with partitioned tables to manage large data volumes. It allows efficient loading of new data and archival or removal of the old data. The challenges in a sliding window scenario is to create the staging table(s) correctly for efficient partition SWITCH operations. This utility provides a command line interface to:

1. Remove all the data from one partition by switching it out to a staging table. It creates the required staging table.
2. Create a staging table for loading data into a partition. The staging table can be created with or without indexes -- if created without indexes this utility provides a separate command to create appropriate indexes on the staging table, before SWITCHing it into the partitioned table.

The commands can be invoked from other scripts for end-to-end sliding window scenarios.

Refer to the attached readme.htm file for more details and command syntax.

RELEASED: Partition Management Release 1.0 (Jan 03, 2008)

Fri, 04 Jan 2008 17:56:43 GMT

Release 1.0 consists of:
* A Readme file
* The Executable
* The source code (Visual Studio project)

This release is designed and tested to work with SQL Server 2005, Service Pack 1 or later.

*+Note+*: After you download the .zip file, do the following before unzipping it:
Right-click the Zip file, choose Properties, click UnBlock, click Apply.
Otherwise, you may get an error message "The project location is not trusted"

UPDATED RELEASE: Partition Management Release 1.0 (Jan 03, 2008)

Fri, 04 Jan 2008 17:56:43 GMT

UPDATED WIKI: Home

sanjaymi — Thu, 03 Jan 2008 16:53:04 GMT

UPDATED RELEASE: Partition Management Release 1.0

Thu, 03 Jan 2008 16:51:37 GMT

Release 1.0 consists of:
* A Readme file
* The Executable
* The source code (Visual Studio project)

*+Note+*: After you download the .zip file, do the following before unzipping it:
Right-click the Zip file, choose Properties, click UnBlock, click Apply.
Otherwise, you may get an error message "The project location is not trusted"

UPDATED RELEASE: Partition Management Release 1.0

Fri, 28 Dec 2007 18:12:56 GMT

Release 1.0 consists of:
* A Readme file
* The Executable
* The source code (Visual Studio project)

UPDATED RELEASE: Partition Management Release 1.0

Fri, 28 Dec 2007 18:12:13 GMT

Release 1.0 consists of:
* A Readme file
* The Executable
* The source code (Visual Studio project)

UPDATED RELEASE: Partition Management Release 1.0

Fri, 28 Dec 2007 18:11:49 GMT

Release 1.0 consists of:
* A Readme file
* The Executable
* The source code (Visual Studio project)

UPDATED RELEASE: Partition Management Release 1.0

Wed, 26 Dec 2007 20:18:38 GMT

Release 1.0 consists of:
* The source code (Visual Studio project)
* A Readme file

UPDATED WIKI: Home

sanjaymi — Wed, 26 Dec 2007 20:09:12 GMT

Project Description
Sliding Widnow technique is commonly used with partitioned tables to manage large data volumes. It allows efficient loading of new data and archival or removal of the old data. The challenges in a sliding window scenario is to create the staging table(s) correctly for efficient partition SWITCH operations. This utility provides a command line interface to:

1. Remove all the data from one partition by switching it out to a staging table. It creates the required staging table.
2. Create a staging table for loading data into a partition. The staging table can be created with or without indexes -- if created without indexes this utility provides a separate command to create appropriate indexes on the staging table, before SWITCHing it into the partitioned table.

The commands can be invoked from other scripts for end-to-end sliding window scenarios.

Refer to the attached readme.htm file for more details and command syntax.

**Delete the following note before publishing **

This project is currently in setup mode and only available to project coordinators and developers. Once you have finished setting up your project you can publish it to make it available to all CodePlex visitors.

There are three requirements before you publish:

- Edit this page to provide information about your project
- Upload the initial source code for your project
- Add your project license

Additional information on starting a new project is available here: Project Startup Guide.

UPDATED WIKI: Home

sanjaymi — Wed, 26 Dec 2007 20:07:55 GMT

Project Description
Sliding Widnow technique is commonly used with partitioned tables to manage large data volumes. It allows efficient loading of new data and archival or removal of the old data. The challenges in a sliding window scenario is to create the staging table(s) correctly for efficient partition SWITCH operations. This utility provides a command line interface to:

1. Remove all the data from one partition by switching it out to a staging table. It creates the required staging table.
2. Create a staging table for loading data into a partition. The staging table can be created with or without indexes -- if created without indexes this utility provides a separate command to create appropriate indexes on the staging table, before SWITCHing it into the partitioned table.

The commands can be invoked from other scripts for end-to-end sliding window scenarios.

**Delete the following note before publishing **

This project is currently in setup mode and only available to project coordinators and developers. Once you have finished setting up your project you can publish it to make it available to all CodePlex visitors.

There are three requirements before you publish:

- Edit this page to provide information about your project
- Upload the initial source code for your project
- Add your project license

Additional information on starting a new project is available here: Project Startup Guide.