共用方式為


建立、改變和移除索引

在 SQL Server 管理物件 (SMO) 階層中,索引是由 Index 物件表示。索引資料行是由 IndexedColumns 屬性表示的 IndexedColumn 物件集合來表示。

您可以指定 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 資料類型是稱為 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 資料類型是稱為 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(); 
}

請參閱

參考