Export (0) Print
Expand All

How to: Create a Non-Clustered, Composite Index in Visual Basic .NET

SQL Server 2005

This section describes how to build a non-clustered, composite index by using Visual Basic .NET.

The code example demonstrates how to create a composite, non-clustered index. For a composite index, add more than one column to the index. Set the IsClustered property to False for a non-clustered index.

  1. Start Visual Studio 2005.

  2. From the File menu, select New Project. The New Project dialog box appears.

  3. In the Project Types pane, select Visual Basic. In the Templates pane, select Console Application.

  4. (Optional) In the Name box, type the name of the new application.

  5. Click OK to load the Visual Basic console application template.

  6. On the Project menu, select Add Reference item. The Add Reference dialog box appears. Select Browse and locate the SMO assemblies in the C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies folder. Select the following files:

    Microsoft.SqlServer.ConnectionInfo.dll

    Microsoft.SqlServer.Smo.dll

    Microsoft.SqlServer.SqlEnum.dll

    Microsoft.SqlServer.SmoEnum.dll

  7. On the View menu, click Code.-Or-Select the Module1.vb window to display the code window.

  8. In the code, before any declarations, type the following Imports statements to qualify the types in the SMO namespace:

    Imports Microsoft.SqlServer.Management.Smo
    Imports Microsoft.SqlServer.Management.Common
    
  9. Insert the code that follows this procedure into the main program.

  10. Run and build the application.

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

Community Additions

ADD
Show:
© 2015 Microsoft