Index in SQL Server 2008

In this article I will describe how to make a Cluster Index, Unique Index and Normal Index in SQL Server 2008 Express editions.

First see why index is useful. See the following query:

SELECT CustomerID,ContactName,CompanyName,City
FROM Customers
WHERE Country ='India'

The above query retrieves Customer information where country is India from the Customers table of the Master database.
Now there is no Index on this table, so the database engine performs a table scan and reads every row to check if Country is "USA". Suppose there are 100 rows of records. The database engine scans 13 rows and returns the matches.

So the performance will degrade.

Indexing means to fetch the data of a row using a proper pointer.

Now to create the "Cluster Index" in a particular table in SQL Server 2008:

Step 1:

Each table can have one and only one Clustered Index because the index is built on unique key columns. It stores the data rows in the table based on it's key values. The table having a clustered index is also called a clustered table.

Right-click your desired table and after that click design.



Step 2:

Now right-click on the "CustId" of the Customers Table then click "Indexes/Click".



Now a window will open.


See the properties here. It's unique, correct? The index name is "pk_Customer". Created as cluster is also "Yes"
After that click Add.

You can write the query also. The query will be:

CREATE CLUSTERED INDEX PK_Customers on Customer(CustId)

Step 4:

Now it is time for creating the Unique Index.

Now right-click on the "Amount" column (for my case, I want to make this column a Unique Index) of the Customers Table and click "Indexes/Click".


Step 5:

Again the same window will open.



See the drop down list. There you can choose "Unique key" or "Index" type. And give the index name. After that click the "Add" button.

You can write the query also

CREATE UNIQUE INDEX UNQ_Amount ON Customer(Amount)

Now Suppose I want to Change the Index Name.So the query will be

EXEC sp_rename 'Customer. UNQ_Amount ', 'Amont'

So here the previous index name "UNQ_Amount" will change to "Amount".