Indexes in SQL Server

What is Index:

Index is a collection of Pages associated with a table or view used to speed retrieval of rows from the table., Without an Index you would have to go through entire book  one  page at a time to find information about topic.

MS SQL Server uses indexes to point to the location of row on a data page instead of having to look through all data pages of a table.

SQL Server accesses data in one of two ways

 Table Scan :

•     By scanning all the data pages in a table , which is called a table scan 
•     When SQL Server performs Table Scan, it Starts the scan from the beginning of the         Table
•      Scan from one page to page through all the rows in the table
•      Extract the rows that meet the criteria of the Query.

 Index Scan :

•       By using the Indexes.

•       When SQL Server performs Index scan, it Extract only the needed rows that meet the criteria of the Query.

Type of Indexes:

•     Clustered Index
•     Non Clustered Index

Clustered Index:

• We can have only one clustered index per table. The data should be physically stored according to the key column specified for the clustered Index. We can create a Clustered index in a table based on primary key.
• Clustered Index is implemented as a B-Tree Structure. Each page in a B-Tree structure is called as Index Node.
• The top node of B-Tree Structure is called the root node.
• The bottom level of nodes in the index is called the leaf level.
• Any index level between root node and the leaf nodes are collectively known as Intermediate nodes.

Advantages:

•   Performance will be very fast when we are retrieving data based on Primary key.

 Non Clustered Index

•     We can have 249 non clustered indexes per table.
•     It have same B-Tree Structure as clustered index except that the data is not in a sorted   order and stored in a table based on their non-clustered index keys.
•     In Non Clustered Index, the data and the index are stored separately, and the leaf level of index consists of index pages instead of Data pages.

Advantages:

• If you are using Join or Group by Clause in a query,  we can create multiple non Clustered indexes. Due to that performance will improve.

Disadvantages:

•  Index needs more disk space. More indexes need more space.
•  It takes longer to update records if only when the fields in the clustered index are changed.

Limitations of Indexes:

• Index will degrade the performance while performing the Insert, Update and Delete operations.
• Indexes can be recommended only on large tables with 1000’s of rows.

Creating a Index:

 To create a clustered index by using Object Explorer

1.      In Object Explorer, expand the table on which you want to create a clustered index.

2.      Right-click the Indexes folder, point to New Index, and select Clustered Index….

3.      In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.

4.      Under Index key columns, click Add….

5.      In the Select Columns from table_name dialog box, select the check box of the table column to be added to the clustered index.

6.      Click OK.

7.      In the New Index dialog box, click OK.


To create a clustered index using TSQL

 USE  TestDB

GO

CREATE TABLE dbo.TestTable

    (

    TestCol1 int NOT NULL,

     TestCol2 nchar(10) NULL,

     TestCol3 nvarchar(50) NULL);

GO



CREATE CLUSTERED INDEX IX_TestTable_TestCol1

    ON dbo.TestTable (TestCol1);

GO


To create a nonclustered index by using Object Explorer

1. In Object Explorer, expand the database that contains the table on which you want to create a nonclustered index.

2. Expand the Tables folder.

3. Expand the table on which you want to create a nonclustered index.

4. Right-click the Indexes folder, point to New Index, and select Non-Clustered Index….

5. In the New Index dialog box, on the General page, enter the name of the new index in the Index name box.

6. Under Index key columns, click Add….

7. In the Select Columns from table_name dialog box, select the check box or check boxes of the table column or columns to be added to the nonclustered index.

8. Click OK.

9. In the New Index dialog box, click OK.


To create a nonclustered index using TSQL

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.



USE TestDB;

GO

 IF EXISTS (SELECT name FROM sys.indexes     WHERE name = N'IX_ProductVendor_VendorID')

    DROP INDEX IX_ProductVendor_VendorID ON Purchasing.ProductVendor;

GO


CREATE NONCLUSTERED INDEX IX_ProductVendor_VendorID

    ON Purchasing.ProductVendor (BusinessEntityID);

GO


Modify an index
1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.

2. Expand Databases, expand the database in which the table belongs, and then expand Tables.

3. Expand the table in which the index belongs and then expand Indexes.

4. Right-click the index that you want to modify and then click Properties.

5. In the Index Properties dialog box, make the desired changes. For example, you can add or remove a column from the index key, or change the setting of an index option.


To modify an index using TSQL

1. Connect to the Database Engine.

2. From the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute. This example drops and re-creates an existing index on the ProductID column of the Production.WorkOrder table by using the DROP_EXISTING option. The options FILLFACTOR andPAD_INDEX are also set.



USE AdventureWorks2012;

GO

CREATE NONCLUSTERED INDEX IX_WorkOrder_ProductID

    ON Production.WorkOrder(ProductID)

    WITH (FILLFACTOR = 80,

        PAD_INDEX = ON,

        DROP_EXISTING = ON);

GO



USE AdventureWorks2012;

GO

ALTER INDEX AK_SalesOrderHeader_SalesOrderNumber ON

    Sales.SalesOrderHeader

SET (

    STATISTICS_NORECOMPUTE = ON,

    IGNORE_DUP_KEY = ON,

    ALLOW_PAGE_LOCKS = ON

    ) ;

GO


To delete an index by using Object Explorer
1. In Object Explorer, expand the database that contains the table on which you want to delete an index.

2. Expand the Tables folder.

3. Expand the table that contains the index you want to delete.

4. Expand the Indexes folder.

5. Right-click the index you want to delete and select Delete.

6. In the Delete Object dialog box, verify that the correct index is in the Object to be deleted grid and click OK.


To delete an index using TSQL

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.



USE AdventureWorks2012;

GO

-- delete the IX_ProductVendor_BusinessEntityID index

-- from the Purchasing.ProductVendor table

DROP INDEX IX_ProductVendor_BusinessEntityID

    ON Purchasing.ProductVendor;

GO


To rename an index by using Object Explorer

1. In Object Explorer, click the plus sign to expand the database that contains the table on which you want to rename an index.

2. Click the plus sign to expand the Tables folder.

3. Click the plus sign to expand the table on which you want to rename an index.

4. Click the plus sign to expand the Indexes folder.

5. Right-click the index you want to rename and select Rename.

6. Type the index’s new name and press Enter.


To rename an index

1. In Object Explorer, connect to an instance of Database Engine.

2. On the Standard bar, click New Query.

3. Copy and paste the following example into the query window and click Execute.



USE AdventureWorks2012;

GO

--Renames the IX_ProductVendor_VendorID index on the Purchasing.ProductVendor table to IX_VendorID.



EXEC sp_rename N'Purchasing.ProductVendor.IX_ProductVendor_VendorID', N'IX_VendorID', N'INDEX';

GO