Création, modification et suppression d'index

Article
03/19/2013

Dans la hiérarchie SMO (SQL Server Management Objects), les index sont représentés par l'objet Index. Les colonnes indexées sont représentées par une collection d'objets IndexedColumn représentés par la propriété IndexedColumns.

Vous pouvez créer un index sur une colonne XML en spécifiant la propriété IsXmlIndex de l'objet Index.

Exemples

Pour utiliser un exemple de code fourni, vous devrez sélectionner l'environnement, le modèle et le langage de programmation dans lequel créer votre application. Pour plus d'informations, consultez Créer un projet SMO Visual Basic dans Visual Studio .NET ou Créer un projet SMO Visual C# dans Visual Studio .NET.

Création d'un index composite non cluster en Visual Basic

Cet exemple de code montre comment créer un index composite, non cluster. Pour un index composite, ajoutez plusieurs colonnes à l'index. Affectez à la propriété IsClustered la valeur False pour un index non cluster.

' /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 AdventureWorks2012 database. 
        Dim db As Database
        db = srv.Databases("AdventureWorks2012")

        ' 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

Création d'un index composite non cluster en Visual C#

// /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 AdventureWorks2012 database. 
      Database db;
      db = srv.Databases["AdventureWorks2012"];

      // 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();
   }
}

Création d'un index composite non cluster dans PowerShell

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

Création d'un index XML en Visual Basic

Cet exemple de code montre comment créer un index XML sur un type de données XML. Le type de données XML est une collection de schémas XML appelée MySampleCollection, créée dans Utilisation de schémas XML. Les index XML présentent quelques restrictions, notamment ils doivent être créés sur une table possédant déjà une clé primaire en cluster.

' /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 = "<xsd:schema xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" targetNamespace=""NS2""> <xsd:element name=""elem1"" type=""xsd:integer""/></xsd:schema>"
        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

Création d'un index XML en Visual C#

// /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 = "<xsd:schema xmlns:xsd=\"http://www.w3.org/2001/XMLSchema\" targetNamespace=\"NS2\"> <xsd:element name=\"elem1\" type=\"xsd:integer\"/></xsd:schema>";
      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();
   }
}

Création d'un index XML dans PowerShell

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

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

Voir aussi

Référence

Index

Partager via