创建、更改和删除索引
在 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();
}