Crear, modificar y eliminar índices

Artículo
03/20/2013

En la jerarquía de objetos de administración de SQL Server (SMO), el objeto Index representa los índices. Una colección de objetos IndexedColumn representada por la propiedad IndexedColumns representa las columnas indizadas.

Puede crear un índice en una columna XML especificando la propiedad IsXmlIndex del objeto Index.

Ejemplos

Para utilizar cualquier ejemplo de código que se proporcione, deberá elegir el entorno de programación, la plantilla de programación y el lenguaje de programación en los que crear su aplicación. Para obtener más información, vea Crear un proyecto de Visual Basic SMO en Visual Studio .NET o Crear un proyecto de Visual C# SMO en Visual Studio .NET.

Crear un índice compuesto no clúster en Visual Basic

En este ejemplo de código se muestra cómo crear un índice compuesto no clúster. Para un índice compuesto, agregue más de una columna al índice. Establezca la propiedad IsClustered en False para un índice no clúster.

' /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

Crear un índice compuesto no clúster 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();
   }
}

Crear un índice compuesto no clúster en 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();

Crear un índice XML en Visual Basic

En este ejemplo de código se muestra cómo crear un índice XML en un tipo de datos XML. El tipo de datos XML es una colección de esquemas XML denominada MySampleCollection, que se crea en Utilizar esquemas XML. Los índices XML presentan algunas restricciones, una de las cuales es que deben crearse en una tabla que ya tenga una clave principal en clúster.

' /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

Crear un índice 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();
   }
}

Crear un índice XML en 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()

Vea también

Referencia

Index

Compartir a través de