THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

Technical content about Microsoft data technologies. All opinions expressed are purely my own and do not reflect positions of my employer or associates.

Updated Warehouse Re-Index Script

As I talked about in my last post, I just went through a re-indexing project that took the partitioned fact rows from our warehouse and relocated them into new files. There are a lot of tables and indexes involved, so I have a PowerShell “helper” script to generate the actual T-SQL that moves the data. The idea is to find all the indexes that use a particular partition scheme in the database, and make the CREATE INDEX statements that would recreate them on the new partition scheme. This script doesn’t do the re-indexing work, it just outputs the T-SQL that would do the work, so that the SQL can be verified and/or edited and run later, in the context of other modifications to the warehouse.

(This is a much improved version of the work described in this even older post. It frankly wasn’t all that great back then.)

The script was developed on PowerShell 2 and SQL Server 2012 client tools against SQL Server 2005, but I believe the work that the script does (the process of generating the T-SQL and the results) is compatible with any SQL Server EE 2005 or later. It assumes you’ve loaded the SQL provider or SQLPS module into PowerShell.

 PowerShell Script to generate CREATE INDEX statements from existing indexes
 mapped to a new partition scheme.

 14 Jan 2013 Merrill Aldrich
 Free for your use, but without warranty. Use at your sole risk.

 $ErrorActionPreference = 'Stop'

 # Create a connection to the SQL Server instance
 Set-Location SQLSERVER:\SQL\<yourserver>\DEFAULT\

 # Names of existing and new partition schemes. New will replace existing in script output:
 $oldPartitionScheme = 'nameOfOldScheme'
 $newPartitionScheme = 'nameOfNewScheme'

 # Compose a query that will list out the tables that use a specific partition scheme
 $partitionedTables = @( Get-ChildItem .\Databases\<yourdatabase>\tables |
     Where-Object -filter { $_.PartitionScheme -eq $oldPartitionScheme } )

 # For each partitioned table, script out create statements for partitioned indexes
 $scripter = New-Object ('Microsoft.SqlServer.Management.Smo.Scripter') ( get-item ( get-location ) )

 # Set scripting options here as needed
 # $scripter.Options.<someoption> = <somevalue>
 $scripter.Options.ScriptDrops = $False

 $partitionedTables | foreach {
     # Note current table in output:
     "/***  Table $($_.Schema).$($_.Name)   ***/"

     $partitionedIndexes = ( $_.indexes |
         Where-Object -Filter { $_.IsPartitioned -eq $True -and $_.PartitionScheme -eq $oldPartitionScheme }

     # Script create statements

     $partitionedIndexes | foreach {
         $indexCreateScr = $scripter.Script( $_ )

         # Change the partition scheme for every statement to the new partition scheme
         $indexCreateScr = $IndexCreateScr -replace $oldPartitionScheme, $newPartitionScheme

         # Change ALTER TABLE ADD CONSTRAINT to CREATE INDEX statements for PKs
         $indexCreateScr = $indexCreateScr -replace `
             'ALTER TABLE (\[[^\]]*\])\.(\[[^\]]*\]) ADD  CONSTRAINT (\[[^\]]*\]) PRIMARY KEY CLUSTERED', `
             ('/* $& */' + "`n" + 'CREATE UNIQUE CLUSTERED INDEX $3 ON $1.$2')

         # For ALTER TABLEs the DROP_EXISTING index option has to be added to the script
         # Find any "WITH (" clause not containing the DROP_EXISTING option, and add it
         $indexCreateScr = $IndexCreateScr -replace 'WITH \((?!.*DROP_EXISTING)', 'WITH ( DROP_EXISTING = ON, '

         # Change index create options, if necessary. Edit to suit:
         $indexCreateScr = $IndexCreateScr -replace 'PAD_INDEX = OFF, ', ''
         $indexCreateScr = $IndexCreateScr -replace 'STATISTICS_NORECOMPUTE = OFF, ', ''
         $indexCreateScr = $IndexCreateScr -replace 'IGNORE_DUP_KEY = OFF, ', ''
         $indexCreateScr = $IndexCreateScr -replace 'SORT_IN_TEMPDB = OFF, ', ''

         $indexCreateScr = $IndexCreateScr -replace 'ONLINE = OFF', 'ONLINE = ON'
         $indexCreateScr = $IndexCreateScr -replace 'DROP_EXISTING = OFF', 'DROP_EXISTING = ON'

         # Insert some line terminators for legibility
         $indexCreateScr = $IndexCreateScr -replace "ON \[$($newPartitionScheme)\]", "`nON [$($newPartitionScheme)]"
         $indexCreateScr = $IndexCreateScr -replace ', ', ", `n`t"

         # Output the revised script

Most of this is pretty basic – put the tables into a collection, loop over them, and for each table go through each partitioned index, scripting them out. The only parts that were tricky to develop involve the indexes that support primary keys.

The scripter from SMO wants to script out a PK as “ALTER TABLE … ADD CONSTRAINT,” and the problem with that is you can’t use it to recreate the existing index using the DROP_EXISTING option. But, in fact, in SQL Server it is perfectly valid to do a CREATE INDEX … WITH ( DROP_EXISTING = ON ) against the index that supports a primary key. It’s just that the scripter isn’t designed to deal with that idea (as far as I know).

I searched around for some solution to this issue to no avail, but instead fell back on good old hacky find and replace. There are a few semi-hairy regex expressions in the script that locate instances of ALTER TABLE … ADD CONSTRAINT and substitute the equivalent CREATE INDEX statement, and also locate the WITH clauses that don’t have the DROP_EXISTING option, and add that. The gibberish parts of those expressions are mostly looking for object names and escaping square brackets.

If it’s not clear what this sort of thing means (\[[^\]]*\])\.(\[[^\]]*\]) drop me a comment and I’ll try to clarify. Conversely, if you are better at regex than I am, which is likely, any advice you might have to simplify this would be welcome!

A side note about regex and PowerShell:

If you are used to using regex in the find and replace dialog in SSMS / Visual Studio, it’s not the same flavor of regex that PowerShell uses, and there are some important differences:

Tagging expressions/ Grouping is accomplished with parens (), not curlies {}

Substitution is done with dollar signs $1 and not backslashes \1

AND, epically confusing, the dollar signs in regex are not the dollar signs in PowerShell. So

"$1" and '$1'
in a –replace expression are different in important and confusing ways. One will look for a PowerShell variable $1 and try to expand it, while the other is the regex matching group #1 and does regex replacement.

What makes this sad is that the regex one might use in editing T-SQL in SSMS can’t be moved over to PowerShell without a lot of changes.

Published Monday, January 14, 2013 5:30 PM by merrillaldrich
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Gonçalo Ferreira said:

Something like the following would probably be more readable.

$indexCreateScr = $indexCreateScr -replace `


            ('/* $& */' + "`n" + 'CREATE UNIQUE CLUSTERED INDEX $2 ON $1')

January 15, 2013 4:01 AM

Leave a Comment


This Blog


Privacy Statement