Yes Yes Yes in Visual Basic .NET

Create Quick Response Code in Visual Basic .NET Yes Yes Yes

2

You can also rebuild an index by first dropping it with the DROP INDEX statement and re-creating it with a separate CREATE INDEX statement. However, Microsoft does not recommend this procedure.

You can create an index by using the CREATE INDEX Transact-SQL statement. This creates a relational index on a specified table or view, or an XML index on a specified table. You can create an index before there is data in the table. You can create indexes on tables or views in another database by specifying a qualified database name. You can create unique, clustered, and nonclustered indexes and specify whether the index uses ascending or descending sort direction for the specified index column.

For more information, search for CREATE INDEX in Books Online or access msdn.microsoft.com/library/default.asp url=/library/en-us/tsqlref/ts_create_64l4.asp.

Creating or modifying a table and specifying the PRIMARY KEY or UNIQUE constraint enforces the uniqueness of rows and implicitly creates an index to do so. This index is clustered by default. A table typically has a column or combination of columns that contains values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. You can create a PK by defining a PRIMARY KEY constraint when you create or modify a table. A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints guarantee unique data, they are frequently defined on an identity column. When you specify a PRIMARY KEY constraint for a table, the SQL Server 2005 database engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the PK is used in queries. Therefore, the PKs that are chosen must follow the rules for creating unique indexes. If you define a PRIMARY KEY constraint on more than one column, values can be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.

When you specify a UNIQUE constraint, a unique nonclustered index is created to enforce the constraint. You can specify a unique clustered index if a clustered index on the table does not already exist. You can also create a unique index independent of a constraint and define multiple unique nonclustered indexes on a table. To create an indexed view, you define a unique clustered index on one or more view columns. The view is executed (materialized), and the result set is stored in the leaf level of the index in the same way table data is stored in a clustered index.

When you create or rebuild an index, the fill factor value determines the percentage of space on each leaf-level page that SQL Server fills with data, and it reserves a percentage of free space for future growth. You specify the fill factor value as a percentage from 1 to 100. In most situations, you would not change the default of 0 (which actually means 100 percent). With this fill factor, SQL Server fills the leaf-level pages almost to capacity, but some space remains for at least one additional index row on each page. You can use the CREATE INDEX or ALTER INDEX statements to set the fill factor value for individual indexes, and the sys.indexes catalog view to view the fill factor value of one or more indexes. The sp_configure system stored procedure can modify the server-wide default fill factor value. Fill factor is an advanced option. If you use this stored procedure, you can change the fill factor setting only when you set Show Advanced Options to 1. The setting takes effect after you restart the server.