Artykuł
07/22/2011

Tworzenie, modyfikowanie i usuwanie indeksów

W SQL Server hierarchii obiektów zarządzania (SMO), indeksy są reprezentowane przez Index obiektu.Kolumny indeksowane są reprezentowane przez kolekcja IndexedColumn obiektów, reprezentowane przez IndexedColumns właściwość.

kolumna XML można utworzyć indeks, określając IsXmlIndex Właściwość Index obiektu.

Przykłady

Aby używać dostarczonych przykładów kodu źródłowego, należy wybrać środowisko, szablon oraz język programowania, które będą używane do tworzenia aplikacji.Aby uzyskać więcej informacji, zobacz Jak Tworzenie projektu SMO Visual Basic w programie Visual Studio.NET lub Jak Tworzenie projektu programu Visual C# SMO w programie Visual Studio.NET.

Tworzenie indeksu nieklastrowanym, złożony w języku Visual Basic

Ten przykładowy kod przedstawia sposób tworzenia złożony nie-indeks klastrowany.Indeks złożony należy dodać więcej niż jedną kolumna do indeksu.Ustaw IsClustered Właściwość False indeks nieklastrowany.

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

Tworzenie indeksu nieklastrowanym, złożony w języku Visual C#

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

Tworzenie indeksu nieklastrowanym, złożony w PowerShell

# Set the path context to the local, default instance of SQL Server and to the
#database tables in Adventureworks2008R2
CD \sql\localhost\default\databases\AdventureWorks2008R2\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();

Tworzenie indeksu XML w języku Visual Basic

Poniższy przykład kodu pokazuje jak utworzyć indeks XML na typ danych XML.Typ danych XML jest kolekcja schematu XML o nazwie MySampleCollection, który jest tworzony w Korzystanie ze schematów XML.Indeksy XML ma pewne ograniczenia, z których jedna jest, że należy utworzyć na tabela zawierającej klastrowany klucz podstawowy.

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

Tworzenie indeksu XML w języku Visual C#

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

Tworzenie indeksu XML w PowerShell

# Set the path context to the local, default instance of SQL Server and get a reference to adventureworks2008R2
CD \sql\localhost\default\databases
$db = get-item Adventureworks2008R2

#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()

Zobacz także

Odwołanie

Index

Udostępnij za pośrednictwem