Article on Sqlserver Indexes
One of the most powerful way to get high performance in a SQL Server database is the index.
Indexes speed up the your querying process by providing swift access to rows in the data tables, like the way a book’s index helps you find information quickly within that book.
Indexes operate 'behind the scenes' in support of the database engine. However, creating the proper index can drastically increase the performance of an application
we create Indexes on columns of tables or views. These provide a fast way to look up data based on the values within those columns. For example, if you create an index on the primary key of a Table and then search for a row of data based on one of the primary key values, SQL Server first finds that value in the index, and then uses the index to quickly locate the entire row of data. Without the index, a table scan would have to be performed in order to locate the row, which can have a significant effect on performance.
we can create indexes on most columns in a table or a view. Except the columns which have Data Type as large object (LOB), such as image, text, and varchar(max).
Microsoft SQL Server supports two types of indexes: i)Clustered indexes ii) Non-clustered indexes
Clustered indexes :
Clustered indexes define the physical sorting of a table’s rows in the storage media. For this reason, each database table may have only one clustered index. If a PRIMARY KEY constraint is created for a database table and no clustered index currently exists for that table, SQL Server automatically creates a clustered index on the primary key.
Non-clustered index :
Unlike Clustered Index, Non-clustered indexes are created outside of the database table and contain a sorted li of references to the table itself. SQL Server 2005 supports up to 249 nonclustered indexes, and SQL Server 2008 support up to 999.. However, it’s important to keep in mind that non-clustered indexes slow down the data modification and insertion process, so indexes should be kept to a minimum
One of the hardest tasks facing database administrators is the selection of appropriate columns for non-clustered indexes. You should consider creating non-clustered indexes on any columns that are frequently referenced in the WHERE clauses of SQL statements. Other good candidates are columns referenced by JOIN and GROUP BY operations.
You may wish to also consider creating non-clustered indexes that cover all of the columns used by certain frequently issued queries. These queries are referred to as “covered queries” and experience excellent performance gains.
Script to Create a Non Clustered Index
/****** Object: Index [NC_Index1] Script Date: 07/27/2009 02:35:28 ******/
CREATE NONCLUSTERED INDEX [NC_Index1] ON [dbo].[Operator_Master]
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
To see the Graphical Estimated Execution Plan, click the button shown below Figure 1 instead of running the query.
Additional Index Guidelines
Choosing the correct columns and types for an index is another important step in creation of index. In this section, we will talk about two main points,
i) short index keys
Keep Index Keys Short
The larger an index key is, the harder a database has to work to use the index. For instance, an integer key is smaller in size then a character field for holding 100 characters. In particular, keep clustered indexes as short as possible.
There are several approaches to keeping an index key short. First, try to limit the index to as few columns as possible. While composite indexes are useful and can sometimes optimize a query, they are also larger and cause more disk reads for the database. Secondly, try to choose a compact data type for an index column, based on the number of bytes required for each data type. Integer keys are small and easy for the database to compare. In contrast, strings require a character-by-character comparison.
As a rule of thumb, try to avoid using character columns in an index, particularly primary key indexes. Integer columns will always have an advantage over character fields in ability to boost the performance of a query.
Distinct Index Keys
The most effective indexes are the indexes with a small percentage of duplicated values. Think of having a phone book for a city where 75% of the population has the last name of Smith. A phone book in this area might be easier to use if the entries were sorted by the resident's first names instead. A good index will allow the database to disregard as many records as possible during a search.
An index with a high percentage of unique values is a selective index. Obviously, a unique index is the most selective index of all, because there are no duplicate values. SQL Server will track statistics for indexes and will know how selective each index is. The query optimizer utilizes these statistics when selecting the best index to use for a query.
In addition to creating an index, we'll need to view existing indexes, and sometimes delete or rename them. This is part of the ongoing maintenance cycle of a database as the schema changes, or even naming conventions change.
View Existing Indexes
A list of all indexes on a table is available in the dialog box we used to create an index. Click on the Selected index drop down control and scroll through the available 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. The only input parameter to the procedure is the name of the table, as shown below.
EXEC sp_helpindex Customers
Rename an Index
We can also rename any user created object with the sp_rename stored procedure, including indexes. The sp_rename procedure takes, at a minimum, the current name of the object and the new name for the object. For indexes, the current name must include the name of the table, a dot separator, and the name of the index, as shown below:
EXEC sp_rename 'Products.IX_UnitPrice', 'IX_Price'
This will change the name of the IX_UnitPrice index to IX_Price.
Delete an Index
It is a good idea to remove an index from the database if the index is not providing any benefit. For instance, if we know the queries in an application are no longer searching for records on a particular column, we can remove the index. Unneeded indexes only take up storage space and diminish the performance of modifications. You can remove most indexes with the Delete button on the index dialog box, which we saw earlier. The equivalent SQL command is shown below.
DROP Index Products.IX_Price
Again, we need to use the name of the table and the name of the index, with a dot separator. Some indexes are not so easy to drop, namely any index supporting a unique or primary key constraint. For example, the following command tries to drop the PK_Products index of the Products table.
DROP INDEX Products.PK_Products
Since the database uses PK_Products to enforce a primary key constraint on the Products table, the above command should produce the following error.
An explicit DROP INDEX is not allowed on index 'Products.PK_Products'.
It is being used for PRIMARY KEY constraint enforcement.
Removing a primary key constraint from a table is a redesign of the table, and requires careful thought. It makes sense to know the only way to achieve this task is to either drop the table and use a CREATE TABLE command to recreate the table without the index, or to use the ALTER TABLE command.