创建、更改和删除索引 - SQL Server

适用于：SQL ServerAzure SQL 数据库Azure SQL 托管实例Azure Synapse AnalyticsMicrosoft Fabric 中的 SQL 数据库

在 SQL Server 管理对象（SMO）层次结构中，索引由 Index 对象表示。 索引列由 IndexedColumn 对象的集合表示，而该对象由 IndexedColumns 属性表示。

可以通过指定 IsXmlIndex 对象的 Index 属性对 XML 列创建索引。

Examples

若要使用所提供的任何代码示例，您必须选择创建应用程序所需的编程环境、编程模板和编程语言。 有关详细信息，请参阅 在 Visual Studio .NET 中创建 Visual C# SMO 项目。

在 Visual Basic 中创建非聚集组合索引

此代码示例演示如何创建复合非聚集索引。 对于复合索引，请将超过多个列添加到索引中。 将 IsClustered 非聚集索引的属性设置为 False 。

' /r:Microsoft.SqlServer.Smo.dll

' /r:Microsoft.SqlServer.ConnectionInfo.dll

' /r:Microsoft.SqlServer.SqlEnum.dll

' /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll

Imports Microsoft.SqlServer.Management.Smo

Public Class A

Public Shared Sub Main()

' Connect to the local, default instance of SQL Server.

Dim srv As Server

srv = New Server()

' Reference the AdventureWorks2022 database.

Dim db As Database

db = srv.Databases("AdventureWorks2022")

' Declare a Table object and reference the HumanResources table.

Dim tb As Table

tb = db.Tables("Employee", "HumanResources")

' Define an Index object variable by providing the parent table and index name in the constructor.

Dim idx As Index

idx = New Index(tb, "TestIndex")

' Add indexed columns to the index.

Dim icol1 As IndexedColumn

icol1 = New IndexedColumn(idx, "BusinessEntityID", True)

idx.IndexedColumns.Add(icol1)

Dim icol2 As IndexedColumn

icol2 = New IndexedColumn(idx, "HireDate", True)

idx.IndexedColumns.Add(icol2)

' Set the index properties.

idx.IndexKeyType = IndexKeyType.DriUniqueKey

idx.IsClustered = False

idx.FillFactor = 50

' Create the index on the instance of SQL Server.

idx.Create()

' Modify the page locks property.

idx.DisallowPageLocks = True

' Run the Alter method to make the change on the instance of SQL Server.

idx.Alter()

' Remove the index from the table.

idx.Drop()

End Sub

End Class

在 Visual C# 中创建非聚集组合索引

此代码示例演示如何创建复合非聚集索引。 对于复合索引，请将超过多个列添加到索引中。 将 IsClustered 非聚集索引的属性设置为 False 。

// /r:Microsoft.SqlServer.Smo.dll

// /r:Microsoft.SqlServer.ConnectionInfo.dll

// /r:Microsoft.SqlServer.SqlEnum.dll

// /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll

using Microsoft.SqlServer.Management.Smo;

public class A {

public static void Main() {

// Connect to the local, default instance of SQL Server.

Server srv;

srv = new Server();

// Reference the AdventureWorks2022 database.

Database db;

db = srv.Databases["AdventureWorks2022"];

// Declare a Table object and reference the HumanResources table.

Table tb;

tb = db.Tables["Employee", "HumanResources"];

// Define an Index object variable by providing the parent table and index name in the constructor.

Index idx;

idx = new Index(tb, "TestIndex");

// Add indexed columns to the index.

IndexedColumn icol1;

icol1 = new IndexedColumn(idx, "BusinessEntityID", true);

idx.IndexedColumns.Add(icol1);

IndexedColumn icol2;

icol2 = new IndexedColumn(idx, "HireDate", true);

idx.IndexedColumns.Add(icol2);

// Set the index properties.

idx.IndexKeyType = IndexKeyType.DriUniqueKey;

idx.IsClustered = false;

idx.FillFactor = 50;

// Create the index on the instance of SQL Server.

idx.Create();

// Modify the page locks property.

idx.DisallowPageLocks = true;

// Run the Alter method to make the change on the instance of SQL Server.

idx.Alter();

// Remove the index from the table.

idx.Drop();

}

}

在 PowerShell 中创建非聚集组合索引

此代码示例演示如何创建复合非聚集索引。 对于复合索引，请将超过多个列添加到索引中。 将 IsClustered 非聚集索引的属性设置为 False 。

# Set the path context to the local, default instance of SQL Server and to the

#database tables in AdventureWorks2022

CD \sql\localhost\default\databases\AdventureWorks2022\Tables\

#Get a reference to the table

$tb = get-item HumanResources.Employee

#Define an Index object variable by providing the parent table and index name in the constructor.

$idx = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index -argumentlist $tb, "TestIndex"

#Add indexed columns to the index.

$icol1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn `

-argumentlist $idx, "BusinessEntityId", $true

$idx.IndexedColumns.Add($icol1)

$icol2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn `

-argumentlist $idx, "HireDate", $true

$idx.IndexedColumns.Add($icol2)

#Set the index properties.

$idx.IndexKeyType = [Microsoft.SqlServer.Management.SMO.IndexKeyType]::DriUniqueKey

$idx.IsClustered = $false

$idx.FillFactor = 50

#Create the index on the instance of SQL Server.

$idx.Create()

#Modify the page locks property.

$idx.DisallowPageLocks = $true

#Run the Alter method to make the change on the instance of SQL Server.

$idx.Alter()

#Remove the index from the table.

$idx.Drop();

在 Visual Basic 中创建 XML 索引

此代码示例演示如何对 XML 数据类型创建 XML 索引。 XML 数据类型是一个名为 MySampleCollection 的 XML 架构集合，该集合是在 使用 XML 架构中创建的。 XML 索引具有一些限制，其中一个限制是 XML 索引必须是对已具有聚集主键的表创建的。

' /r:Microsoft.SqlServer.Smo.dll

' /r:Microsoft.SqlServer.ConnectionInfo.dll

' /r:Microsoft.SqlServer.SqlEnum.dll

' /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll

Imports Microsoft.SqlServer.Management.Smo

Public Class A

Public Shared Sub Main()

' Connect to the local, default instance of SQL Server.

Dim srv As Server

srv = New Server()

Dim db1 As Database = srv.Databases("TESTDB")

' Define a Table object variable and add an XML type column.

Dim tb As New Table(db1, "XmlTable3")

Dim mySample As New XmlSchemaCollection(db1, "Sample4", "dbo")

mySample.Text = " "

mySample.Create()

Dim col11 As Column

' This sample requires that an XML schema type called MySampleCollection exists on the database.

col11 = New Column(tb, "XMLValue", DataType.Xml("Sample4"))

' Add another integer column that can be made into a unique, primary key.

tb.Columns.Add(col11)

Dim col21 As Column

col21 = New Column(tb, "Number", DataType.Int)

col21.Nullable = False

tb.Columns.Add(col21)

' Create the table of the instance of SQL Server.

tb.Create()

' Create a unique, clustered, primary key index on the integer column. This is required for an XML index.

Dim cp As Index

cp = New Index(tb, "clusprimindex3")

cp.IsClustered = True

cp.IndexKeyType = IndexKeyType.DriPrimaryKey

Dim cpcol As IndexedColumn

cpcol = New IndexedColumn(cp, "Number", True)

cp.IndexedColumns.Add(cpcol)

cp.Create()

' Define and XML Index object variable by supplying the parent table and the XML index name arguments in the constructor.

Dim i As Index

i = New Index(tb, "xmlindex")

Dim ic As IndexedColumn

ic = New IndexedColumn(i, "XMLValue", True)

i.IndexedColumns.Add(ic)

' Create the XML index on the instance of SQL Server.

i.Create()

End Sub

End Class

在 Visual C# 中创建 XML 索引

此代码示例演示如何对 XML 数据类型创建 XML 索引。 XML 数据类型是一个名为 MySampleCollection 的 XML 架构集合，该集合是在 使用 XML 架构中创建的。 XML 索引具有一些限制，其中一个限制是 XML 索引必须是对已具有聚集主键的表创建的。

// /r:Microsoft.SqlServer.Smo.dll

// /r:Microsoft.SqlServer.ConnectionInfo.dll

// /r:Microsoft.SqlServer.SqlEnum.dll

// /r:Microsoft.SqlServer.Management.Sdk.Sfc.dll

using Microsoft.SqlServer.Management.Smo;

public class A {

public static void Main() {

// Connect to the local, default instance of SQL Server.

Server srv;

srv = new Server();

Database db1 = srv.Databases["TESTDB"];

// Define a Table object variable and add an XML type column.

Table tb = new Table(db1, "XmlTable3");

XmlSchemaCollection mySample = new XmlSchemaCollection(db1, "Sample4", "dbo");

mySample.Text = " ";

mySample.Create();

Column col11;

// This sample requires that an XML schema type called MySampleCollection exists on the database.

col11 = new Column(tb, "XMLValue", DataType.Xml("Sample4"));

// Add another integer column that can be made into a unique, primary key.

tb.Columns.Add(col11);

Column col21;

col21 = new Column(tb, "Number", DataType.Int);

col21.Nullable = false;

tb.Columns.Add(col21);

// Create the table of the instance of SQL Server.

tb.Create();

// Create a unique, clustered, primary key index on the integer column. This is required for an XML index.

Index cp;

cp = new Index(tb, "clusprimindex3");

cp.IsClustered = true;

cp.IndexKeyType = IndexKeyType.DriPrimaryKey;

IndexedColumn cpcol;

cpcol = new IndexedColumn(cp, "Number", true);

cp.IndexedColumns.Add(cpcol);

cp.Create();

// Define and XML Index object variable by supplying the parent table and the XML index name arguments in the constructor.

Index i;

i = new Index(tb, "xmlindex");

IndexedColumn ic;

ic = new IndexedColumn(i, "XMLValue", true);

i.IndexedColumns.Add(ic);

// Create the XML index on the instance of SQL Server.

i.Create();

}

}

在 PowerShell 中创建 XML 索引

此代码示例演示如何对 XML 数据类型创建 XML 索引。 XML 数据类型是一个名为 MySampleCollection 的 XML 架构集合，该集合是在 使用 XML 架构中创建的。 XML 索引具有一些限制，其中一个限制是 XML 索引必须是对已具有聚集主键的表创建的。

# Set the path context to the local, default instance of SQL Server and get a reference to AdventureWorks2022

CD \sql\localhost\default\databases

$db = get-item AdventureWorks2022

#Define a Table object variable and add an XML type column.

#This sample requires that an XML schema type called MySampleCollection exists on the database.

#See sample on Creating an XML schema to do this

$tb = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Table -argumentlist $db, "XmlTable"

$Type = [Microsoft.SqlServer.Management.SMO.DataType]::Xml("MySampleCollection")

$col1 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -argumentlist $tb,"XMLValue", $Type

$tb.Columns.Add($col1)

#Add another integer column that can be made into a unique, primary key.

$Type = [Microsoft.SqlServer.Management.SMO.DataType]::Int

$col2 = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Column -argumentlist $tb,"Number", $Type

$col2.Nullable = $false

$tb.Columns.Add($col2)

#Create the table of the instance of SQL Server.

$tb.Create()

#Create a unique, clustered, primary key index on the integer column. This is required for an XML index.

#Define an Index object variable by providing the parent table and index name in the constructor.

$cp = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index -argumentlist $tb, "clusprimindex"

$cp.IsClustered = $true;

$cp.IndexKeyType = [Microsoft.SqlServer.Management.SMO.IndexKeyType]::DriPrimaryKey;

#Create and add an indexed column to the index.

$cpcol = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn `

-argumentlist $cp, "Number", $true

$cp.IndexedColumns.Add($cpcol)

$cp.Create()

#Define and XML Index object variable by supplying the parent table and

# the XML index name arguments in the constructor.

$i = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Index -argumentlist $tb, "xmlindex"

#Create and add an indexed column to the index.

$ic = New-Object -TypeName Microsoft.SqlServer.Management.SMO.IndexedColumn `

-argumentlist $i, "XMLValue", $true

$i.IndexedColumns.Add($ic)

#Create the XML index on the instance of SQL Server

$i.Create()

另请参阅

Index