Indexes are used by queries to find data from the table quickly. It can be created on table and views. An Index in SQL server is very similar to indexes in books.
Indexes are commonly used for retrieving the data in effective fast ways. An index is a database object which can be created on one or more than one columns.
If you don’t have an index, you need to find the specific chapter you will have to start from starting page, if have, then straight go to the index of the chapter. It reduces the time of searching data.
Example: Suppose we have a table name tblEmployee like as;
Let’s make a query to find less and greater than salary from the table.
Select * from tblEmployee where Salary > 5200 and Salary < 7200
This query will find all of the employees in the table tblEmployee, it will find out who has a salary less than 7200 and greater than 5200, first, the query engine will check each and every row in the table. As a resultant after scanning the table, which affects the performance, on a special note if the table is containing a large amount of data. To enhance the query, the query will perform a complete table scan.
To solve this problem, let’s create an index on column name salary in tblEmployee table.
In the output, Index works as storage of data, here, index store salary records of each and every employee in tblEmployee table in ascending order.
To execute the index type, this command will return all of the records of the table tblEmployee.
Execute sp_helptext tblEmployee
An Index can be created graphically by using SQL Server Management Studio
- In the Object Explorer, first, expand the Databases folder and then chose specific database you are working with
- Expand the Tables folder in the Object Explorer window.
- Expand the Table on where you want to create the index in the table
- Press Right-click on the Indexes folder and select New Index.
- In the New Index dialog box, type in a meaningful name of your choice.
- Then, select the Index Type and mention a Unique or Non-Unique Index
- Click the Add button in the window.
- Select the columns tag that you want to add as index key in the table
- Then, Press OK button.
- In the end, just save the table and view the index.
Clustered and Non-Clustered Indexes
Clustered Indexes, which define the physical order of the table. Usually set with a Primary key by default on a table. Leaf node of the clustered index consists data pages.
Create the script for clustered index as shown below;
Note: ‘Id’ column is marked as with primary key and primary key constraints generate clustered indexes automatically if there no other clustered index exists on the table.
To get the output, must execute sp_helpindex tblEmployee, you will get a unique clustered index created on the Id column on the table.
Now insert the records into tblEmployee table. Note that, the values for Id column are not in a sequential order.
Insert into tblEmployee Values(3,’John’,4500,’Male’,’New York’)
Insert into tblEmployee Values(1,’Sam’,2500,’Male’,’London’),
Insert into tblEmployee Values(4,’Sara’,5500,’Female’,’Tokyo’),
Insert into tblEmployee Values(5,’Todd’,3100,’Male’,’Toronto’),
Insert into tblEmployee Values(2,’Pam’,6500,’Female’,’Sydney’)
To execute the following SELECT query
Select * from tblEmployee
And the table output will look like as;
When we execute the select query, we can see that all of the rows on the table are specifically arranged in an ascending order based on their Id column. Just because a clustered index describes the physical order of data in a table, and we have got a clustered index on the Id column in the table.
SQL server does not allow creating clustered index not more than one. For this reason, we can make the clustered index by this,
Create Clustered Index IX_tblEmployee_Name
Non-clustered index, which defines the logical order of the table. The non-clustered index can be made on any key in the table. Usually, it does not consist data pages.
It seems data stored in different place and the index is placed in the different location. The index considers storage location of the stored data. Since, then, non-clustered index stores data separately from the real actual data in the table. Normally, a table can have one or more than one non-clustered index.
For this reason, we can make the non-clustered index by this,
Create NonClustered Index IX_tblEmployee_Name
Difference between Clustered Index and Non-Clustered Index
Unique and Non-Unique Indexes
The unique index is used to define the uniqueness of the values of a key in the index. Let’s understand with a practical example. Let’s create table tblEmployee as;
We have set the primary key on Id column, it means a Unique clustered index gets generated on the Id column. We can verify this by running command as
Execute sp_helpindex tblEmployee
Now, we have created a unique clustered index on the column ‘Id’ in the table, but at any attempt to see duplicate the key values, it will give an error statement like ‘Violation of PRIMARY KEY constraint ‘PK__tblEmplo__3214EC07236943A5′. Cannot insert duplicate key in object dbo.tblEmployee’
Example: The insert queries will fail
Insert into tblEmployee Values(1,’Mike’, ‘Sandoz’,4500,’Male’,’New York’)
Insert into tblEmployee Values(1,’John’, ‘Menco’,2500,’Male’,’London’)
Now let’s drop the Unique Clustered index on the Id column. This will throw an error statement like – ‘An explicit DROP INDEX is not allowed on index tblEmployee.PK__tblEmplo__3214EC07236943A5. It is being used for PRIMARY KEY constraint enforcement.’
Drop index tblEmployee.PK__tblEmplo__3214EC07236943A5
First, open the key folder in object explorer window and follow the given details
Unique Non-Clustered Index
By creating unique non-clustered index as;
Create Unique NonClustered Index UIX_tblEmployee_FirstName_LastName
On tblEmployee(FirstName, LastName)
This unique non clustered index as given above, make sure that no 2 entries in the index have the same first and last names. When you add a unique constraint to the index, let’s add a unique constraint on the city column of the tblEmployee table.
ALTER TABLE tblEmployee
ADD CONSTRAINT UQ_tblEmployee_City
UNIQUE NONCLUSTERED (City)
Now execute the query, EXECUTE SP_HELPCONSTRAINT tblEmployee, it will give you an output like this;
At the final words, it’s hardly proved that your running application does not need an existing index on the table. The only reason for creating rarely used indexes to get forgotten over the period of time. People will come together and no one will get to know if it’s never been used or getting critical for year-end reporting.
So, once you create an index key in production base, but dropping an index is much risky. This may lead to the worst case where a table has more indexes than columns.