Indexes - a useful database tool
Indexes constitute an essential element of the SQL Server armory. They increase the performance of queries and reduce query execution time. They allow data to be accessed easily and effectively. They can be created and dropped like other database objects. An Index Tuning wizard exists in sql server too.
Have you wondered what role an index plays?
Consider the common scenario of a book that you read. Suppose you want to search for a particular chapter.
Where do you go?
The answer, obviously, is the index. It provides faster look-up for pages and organises the data.
In SQL Server indexes play a similar role.
Indexes are created on a column or multiple columns at a time, depending on the need.
Indexes can be created on columns of all datatypes, except those configured as LOB data types, namely, image, text and varchar(max).
Indexes are organized as a B-tree.
How do B-tree's work? They locate a particular data item by traversing the nodes of the tree till they arrive at the required value. Suppose your data is for id's from 1 to 500 and you are searching for Id 256. First the search filters values 250-500, then 250-375 and so on. It traverses the nodes at the intermediate level till it arrives at either -
a) data itself - this is a clustered index where the leaf nodes contain the data itself.
b) pointer to the data - here the address of the data is stored and knowing this address we can locate the particular data.
This is a non-clustered index.
You can have only one clustered index on a table.
When you create a primary key on a table, a clustered index is created by default.
You can have upto 999 non-clustered indexes on a table. (SQL Server 2008).
Index design considerations
----------------------------------------
- For tables that are heavily updated, it is prudent not to over index them.
This is because every time an update happens the index has to be re-built and this constitutes a overhead.
-For small tables don't use indexes.
-Try to create indexes on columns that are more unique.
eg)Given a choice of userId, age and sex create index on userId as it has the greatest selectivity.
Monotonically increasing values constitute a good choice of index columns.
-For indexes on multiple columns the order of columns in the index matters
eg)Given a choice of userId, age and sex create index on userId, age, sex in that order.
-Before creating an index remember that indexes are stored on disk.
Where disk real estate is in doubt, it is unwise to create indexes which will take up a lot of space on disk.
Syntax
-------
-For non-clustered indexes :
Here you do not have to explicitly specify non-clustered as an option. It is the default.
CREATE INDEX index_name
ON table_name (column_name)
-For clustered indexes
CREATE CLUSTERED INDEX index_name
ON table_name (column_name)
-For unique indexes
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
Example
-------
CREATE INDEX YIndex
ON Persons (LName, FName)
Dropping indexes
-----------------
Indexes can be dropped like other objects in the database.
USE DatabaseName;
DROP INDEX IX_Index_1
ON dbo.Table;
View existing indexes
-----------------------
There is also a stored procedure named sp_helpindex.
This stored procedure gives all of the indexes for a table, along with all of the relevant attributes.
Example
---------
EXEC sp_helpindex Company
Impact on execution plan of a query
------------------------------------
If you create an appropriate index, say clustered, on a table your execution plan will change from a clustered index scan to a clustered index seek.
The first query will use 99% of the cost of the batch whereas the latter will use only 1%.
This constitutes a dramatic improvement in performance.
To sum up, Indexes constitute a tool in the hands of the programmer or dba which is extremely useful and productive.