本节书摘来自异步社区出版社《PowerShell V3—SQL Server 2012数据库自动化运维权威指南》一书中的第2章,第2.16节,作者:【加拿大】Donabel Santos,更多章节内容可以访问云栖社区“异步社区”公众号查看。
本方案描述了如何用PowerShell和SMO创建一个包含列的非聚集索引。
我们将使用AdventureWorks2008R2数据库的Person.Person表。我们将在FirstName和LastName上创建一个非聚集索引,包含MiddleName列。这个任务的等价T-SQL语句为:
CREATE NONCLUSTERED INDEX [idxLastNameFirstName] ON [Person].[Person] ( [LastName] ASC, [FirstName] ASC ) INCLUDE ( [MiddleName]) GO1.通过“Start | Accessories | Windows PowerShell | Windows PowerShell ISE”打开PowerShell控制台。
2.导入SQLPS模块,创建一个新的SMO服务器对象。
#import SQL Server module Import-Module SQLPS –DisableNameChecking #replace this with your instance name $instanceName = "KERRIGAN" $server = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName3.添加如下脚本并运行。
$dbName = "AdventureWorks2008R2" $db = $server.Databases[$dbName] $tableName = "Person" $schemaName = "Person" $table = $db.Tables | Where Schema -Like "$schemaName" | Where Name -Like "$tableName" $indexName = "idxLastNameFirstName" $index = $table.Indexes[$indexName] #if stored procedure exists, drop it if ($index) { $index.Drop() } $index = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index -ArgumentList $table, $indexName #first index column, by default sorted ascending $idxCol1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -ArgumentList $index, "LastName", $false $index.IndexedColumns.Add($idxCol1) #second index column, by default sorted ascending $idxCol2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -ArgumentList $index, "FirstName", $false $index.IndexedColumns.Add($idxCol2) #included column $inclCol1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -ArgumentList $index, "MiddleName" $inclCol1.IsIncluded = $true $index.IndexedColumns.Add($inclCol1) #Set the index properties. <# None - no constraint DriPrimaryKey - primary key DriUniqueKey - unique constraint #> $index.IndexKeyType = [Microsoft.SqlServer.Management.SMO.IndexKeyType]::None $index.IsClustered = $false $index.FillFactor = 70 #Create the index on the instance of SQL Server. $index.Create()4.检查这个索引已经创建。打开SSMS。
创建索引的第一步是创建一个SMO index对象,它需要表/视图的句柄和索引名称。
$index = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index –ArgumentList $table, $indexName下一步是使用Microsoft.SqlServer.Management.SMO.Index类的IndexedColumn属性设置所有的索引列。
#first index column $idxCol1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -ArgumentList $index, "LastName", $false; #sort asc $index.IndexedColumns.Add($idxCol1) #second index column $idxCol2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -ArgumentList $index, "FirstName", $false; #sort asc $index.IndexedColumns.Add($idxCol2) 你也可以添加包含列,换句话说,包含列打上了索引的“标签”,但不是索引列的一部分: #i```javascript ncluded column $inclCol1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn -ArgumentList $index, "MiddleName" $inclCol1.IsIncluded = $true $index.IndexedColumns.Add($inclCol1)索引的类型可以指定为Microsoft.SqlServer.Management.SMO.IndexedColumn类的IndexKey Type属性,它接受3个可能的值:
None:不唯一DriPrimaryKey:主键DriUniqueKey:唯一您也可以设置额外的属性,包括FillFactor、是否为聚集索引。
$index.IndexKeyType = [Microsoft.SqlServer.Management.SMO.IndexKeyType]::None $index.IsClustered = $false $index.FillFactor = 70当设置好所有的属性后,调用SMO index对象的Create方法。
#Create the index on the instance of SQL Server. $index.Create()SMO索引对象也支持不同类型的索引。
关于索引选项的更多信息,查看MSDN文档关于SMO索引部分。
创建表方案