– Like an index in a book, an index in a database lets you quickly find specific information in a table or indexed view.
– An index contains keys built from one or more columns in the table, or view, and pointers that map to the storage location of the specified data.
– These keys are stored in a structure (B-tree) that enables SQL Server to find the row or rows associated with the key values quickly and efficiently.
– We can significantly improve the performance of database queries and applications by creating well-designed indexes to support your queries.
– Indexes can reduce the amount of data that must be read to return the query result set.
– Indexes can also enforce uniqueness on the rows in a table, ensuring the data integrity of the table data.
Types of indexes:
– Clustered indexes sort and store the data rows in the table or view based on their key values.
– These are the columns included in the index definition.
– There can be only one clustered index per table, because the data rows themselves can be sorted in only one order.
– The only time the data rows in a table are stored in sorted order is when the table contains a clustered index.
– When a table has a clustered index, the table is called a clustered table.
– A table can have only 1 Clustered index on it, which will be created when a primary key constraint is used in a table.
– Nonclustered indexes have a structure separate from the data rows.
– A nonclustered index contains the nonclustered index key values and each key value entry has a pointer to the data row that contains the key value.
– The pointer from an index row in a nonclustered index to a data row is called a row locator.
– If a table has no clustered index, its data rows are stored in an unordered structure called a heap.
– The structure of the row locator depends on whether the data pages are stored in a heap or a clustered table.
– For a heap, a row locator is a pointer to the row.
– For a clustered table, the row locator is the clustered index key.
– A table can have 249 Nonclustered indexes on it, which will be created whenever a unique constraint is used in the table.
How Indexes are used:
Well-designed indexes can reduce disk I/O operations and consume fewer system resources therefore improving query performance. Indexes can be helpful for a variety of queries that contain SELECT, UPDATE, or DELETE statements. When this query is executed, the query optimizer evaluates each available method for retrieving the data and selects the most efficient method. The method may be a table scan, or may be scanning one or more indexes if they exist.
When performing a table scan, the query optimizer reads all the rows in the table, and extracts the rows that meet the criteria of the query. A table scan generates many disk I/O operations and can be resource intensive. However, a table scan could be the most efficient method if, for example, the result set of the query is a high percentage of rows from the table.
When the query optimizer uses an index, it searches the index key columns, finds the storage location of the rows needed by the query and extracts the matching rows from that location. Generally, searching the index is much faster than searching the table because unlike a table, an index frequently contains very few columns per row and the rows are in sorted order.
The query optimizer typically selects the most efficient method when executing queries. However, if no indexes are available, the query optimizer must use a table scan. Your task is to design and create indexes that are best suited to your environment so that the query optimizer has a selection of efficient indexes from which to select.
The following tasks make up SQL Server recommended strategy for creating indexes:
- Design the index.
Index design is a critical task. Index design includes determining which columns to use, selecting the index type (for example, clustered or nonclustered), selecting appropriate index options, and determining filegroup or partition scheme placement. For more information, see Designing Indexes.
- Determine the best creation method. Indexes are created in the following ways:
– The SQL Server 2005 Database Engine automatically creates a unique index to enforce the uniqueness requirements of a PRIMARY KEY or UNIQUE constraint. By default, a unique clustered index is created to enforce a PRIMARY KEY constraint, unless a clustered index already exists on the table, or you specify a unique nonclustered index. By default, a unique nonclustered index is created to enforce a UNIQUE constraint unless a unique clustered index is explicitly specified and a clustered index on the table does not exist.
– An index created as part of a PRIMARY KEY or UNIQUE constraint is automatically given the same name as the constraint name.
– By creating an index independent of a constraint by using the CREATE INDEX statement, you must specify the name of the index, table, and columns to which the index applies. Index options and index location, filegroup or partition scheme, can also be specified. By default, a nonclustered, nonunique index is created if the clustered or unique options are not specified
3. Create the index:
– Whether the index will be created on an empty table or one that contains data is an important factor to consider. Creating an index on an empty table has no performance implications at the time the index is created; however, performance will be affected when data is added to the table.
– Creating indexes on large tables should be planned carefully so database performance is not hindered. The preferred way to create indexes on large tables is to start with the clustered index and then build any nonclustered indexes.
Syntax for creating a Index:
CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name ON <table_name | view_name> (column [ASC | DESC] [,…n ])
CREATE UNIQUE CLUSTERED INDEX ENO_IND ON EMP(EMPNO)
-In this case it creates a unique clustered index on the empno column.
CREATE INDEX ENAME_IND ON EMP(ENAME)
-In this case it creates a non-unique non-clustered index on the ename column.
- An indexed view is a view that has been materialized, this means it has been computed and stored.
- You index a view by creating a unique clustered index on it.
- Indexed views dramatically improve the performance of some types of queries.
- Indexed views work best for queries that aggregate many rows.
- They are not well-suited for underlying data sets that are frequently updated
Views are also known as virtual tables. The result set of a standard view is not stored permanently in the database. For a standard view, the overhead of dynamically building the result set for each query that references a view can be significant for views that involve complex processing of large numbers of rows, such as aggregating lots of data, or joining many rows. If such views are frequently referenced in queries, you can improve performance by creating a unique clustered index on the view, which is know as Indexed View. When a unique clustered index is created on a view, the result set is stored in the database just like a table with a clustered index is stored.
Another benefit of creating an index on a view is existing queries can benefit from the improved efficiency of retrieving data from the indexed view without having to be recoded.
As modifications are made to the data in the base tables, the data modifications are reflected in the data stored in the indexed view. The requirement that the clustered index of the view be unique improves the efficiency with which SQL Server can find the rows in the index that are affected by any data modification.
If we want to create an Indexed View we need to do the following:
- Create a View by using the with SchemaBinding Option.
- Create a Unique Clustered Index on the View
CREATE VIEW IND_VIEW
SELECT DEPTNO, SUM(ISNULL(SAL, 0)) AS [TOTAL SAL], COUNT_BIG(*) AS [TOTAL RECORDS] FROM DBO.EMP GROUP BY DEPTNO
CREATE UNIQUE CLUSTERED INDEX DEPTNO_IND ON IND_VIEW(DEPTNO)
-Once the index is created on the view it will internally the store the information of the View physicially in a location, any manupulation performed on the base table reflects to the the View also.