创建、更改和删除索引

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

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

示例

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

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

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

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'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, "EmployeeID", 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()

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

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

{ 
//Connect to the local, default instance of SQL Server. 
Server srv; 
srv = new Server(); 
//Reference the AdventureWorks database. 
Database db; 
db = srv.Databases("AdventureWorks"); 
//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, "EmployeeID", 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();
}

在 Visual Basic 中创建 XML 索引

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

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server()
'Reference the AdventureWorks database.
Dim db As Database
db = srv.Databases("AdventureWorks")
'Define a Table object variable and add an XML type column. 
Dim tb As Table
tb = New Table(db, "XmlTable")
Dim col1 As Column
'This sample requires that an XML schema type called MySampleCollection exists on the database.
col1 = New Column(tb, "XMLValue", DataType.Xml("MySampleCollection"))
'Add another integer column that can be made into a unique, primary key.
tb.Columns.Add(col1)
Dim col2 As Column
col2 = New Column(tb, "Number", DataType.Int)
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.
Dim cp As Index
cp = New Index(tb, "clusprimindex")
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()

在 Visual C# 中创建 XML 索引

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

{
//Connect to the local, default instance of SQL Server. 
Server srv; 
srv = new Server(); 
//Reference the AdventureWorks database. 
Database db; 
db = srv.Databases("AdventureWorks"); 
//Define a Table object variable and add an XML type column. 
Table tb; 
tb = new Table(db, "XmlTable"); 
Column col1; 
//This sample requires that an XML schema type called MySampleCollection exists on the database. 
col1 = new Column(tb, "XMLValue", DataType.Xml("MySampleCollection")); 
//Add another integer column that can be made into a unique, primary key. 
tb.Columns.Add(col1); 
Column col2; 
col2 = new Column(tb, "Number", DataType.Int); 
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. 
Index cp; 
cp = new Index(tb, "clusprimindex"); 
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(); 
}

请参阅

参考