INDEX in Sql Server 2005

Indexes may be either Clustered or Non-Clustered

Clustered Index 

Every table can have one and only Clustered Index because index is built on unique key columns and the key values in data rows is unique. It stores the data rows in table based on its key values. Table having clustered index also called as clustered table.

Non-Clustered Index

It has structure different from the data rows. Key value of non clustered index is used for pointing data rows containing key values. This value is known as row locator. Type of storage of data pages determines the structure of this row Locator. Row locator becomes pointer if these data pages stored as a heap. As well as row locator becomes a clustered index key if data page is stored in clustered table.

Both of these may be unique. Wherever we make changes to the data table, managing of indexes is done automatically.

SQL Server allows us to add non-key column at the leaf node of the non clustered index by passing current index key limit and to execute fully covered index query.

Automatic index is created wherever we create primary key, unique key constraints to table.

The Query Optimizer

Query Optimizer indexes to reduce operations of disk input-output and using of system resources when we fire query on data. Data manipulation Query statements (like SELECT, DELETE OR UPDATE) need indexes for maximization of the performance. When Query fires the most efficient method for retrieval of the data is evaluated among available methods. It uses table scans or index scans.

Table scans uses many Input-output operations, it also uses large number of resources as all rows from the table are scanned.

Index scan used for searching index key columns to find storage location.
The index containing fewer columns results in to faster query execution and vice-versa.

Creating an Index

1. Connect to Northwind database from Object Explorer, right click on the Customers table to create an index and click on modify.
 2. Click on Index/Keys from Table Desinger Menu on top or right click on any column and click on Index/Keys.
3. Click on Add from Indexes/Keys dialog box.
4. From Selected Primary/Unique Key or Index list, select the new index and set properties for the index in the grid on right hand side.

• Now just specify other settings if any for the index and click Close.
• When we save the table, the index is created in the database.

Creating Unique Index

SQL Server permits us to create Unique Indexes on columns which are unique to identify. (like employee’s Reference ID, Email-id etc.) We use set of columns to create unique index.

• Right-click on the Customers and click Modify in Object Explorer.
• Now, click on Indexes/Keys from Table Designer menu and click on Click Add.
• The Selected Primary/Unique Key or Index list displays the automatically generated name of the new index.  
• In the grid, click on Type, from the drop-down list, Choose Index.
• Under Column name,we can choose columns we want to index and click on OK. Maximum we can setup 16 columns. For optimum performance, it is recommended that we use one or two columns per index. For every column we values of these columns are arranged in ascending or descending order.  
• In the grid, click Is Unique and select select Yes.

• Null is treated as duplicate values. So, it is not possible to create unique index on one column if it contains null in more than one row. Likewise index cannot be created on multiple columns if those columns contains null in same row.
• Now select Ignore duplicate keys option. If it is required to ignore new or updated data that will lead to creation of duplicate key in the index (with the INSERT or UPDATE statement).
• When we save the table, the index is created in the database.

Creating Clustered Index

table can have only one clustered index. In Clustered index logical order and physical order of the index key values is identical of rows in the table.

• In the Object Explorer click on the Northwind database, right click on the Customres to create an index and click on modify.
• Now we have Table Designer for the table.
• From the Table Designer menu, click Indexes/Keys and from Indexes/Keys dialog box, click Add. 
• Now from Selected Primary/Unique Key or Index list, Select the new index
• In the grid, select Create as Clustered, and choose Yes from the drop-down list to the right of the property. 
• When we save the table, the index is created in the database.