Use Of Indexes in SQL Server


Use of indexes in database SQL Server, real world example or live sql code for indexes sql server, how index can create, update and deleted with rename

Use of indexes in database SQL Server

One of the physical storage structures provided by most SQL-based DBMS is an index, which is a structure that provides rapid access to the rows of a table based on the values of one or more columns.
Indexes speed up the querying process by providing swift access to rows in the data tables, similarly to the way a book’s index helps you find information quickly within that book.
Indexes are created on columns in tables or views.

Data types which are not supported to indexes

We can create indexes on most columns in a table or a view. The exceptions are primarily those columns configured with large object (LOB) data types, such as image, text, and varchar (max).

Explained the Index Structures

Some DBMS products support two or more different types of indexes, which are optimized for different types of database access:

What is B-tree index – It uses a tree structure of index entries and index blocks (groups of index entries) to organize the data values that it contains into ascending or descending order. This structure is hierarchical in nature, with the root node at the top of the hierarchy and the leaf nodes at the bottom. This type of index, which is the default type in nearly all DBMS products, provides efficient searching for a single value or for a range of values, such as the search required for an inequality comparison operator or a range test (BETWEEN) operation.

Hash index explained – It uses a randomizing technique to place all of the possible data values into a moderate number of buckets within the index. Since a given data value is always placed into the same bucket, the DBMS can search for that value simply by locating the appropriate bucket and searching within it. But the assignment of values to buckets does not preserve the order of data values, so a hash index cannot be used for inequality or range searches.

Definition of T-tree index – It is a variation of the B-tree index that is optimized for in-memory databases.

Bitmap index Theory– It is useful when there are a relatively small number of possible data values.

How on can create Index-organized table – It is relatively a new option that stores the entire table in the index. This is useful for tables that have few columns other than the primary key, such as code lookup tables that typically have only a code (such as a department code) and a description (such as a department name).

Explained all types of Index

i. First one is Implicit Index: They are created when a column is explicitly defined with PRIMARY KEY, UNIQUE KEY constraint.

ii. Second one is Explicit Index: They are created using the "CREATE INDEX …" syntax.

iii. Third one is Clustered Index: A clustered index stores the actual data rows at the leaf level of the index. It is like a telephone directory, where you find the entry you are looking for.
An important characteristic of the clustered index is that the indexed values are sorted in either ascending or descending order. As a result, there can be only one clustered index on a table or view because the set of rows can be maintained in only one order at a time.
In addition, data in a table is sorted only if a clustered index has been defined on a table.
Clustered Index is also called Physical Index.

Keep in Mind: A table that has a clustered index is referred to as a clustered table. A table that has no clustered index is referred to as a heap.

Explained the Non-Clustered index: The leaf nodes of a non-clustered index contain only the values from the indexed columns and row locators that point to the actual data rows. This means that the query engine must take an additional step in order to locate the actual data.
A non-clustered index is like the index in the back of a book. You can quickly search the index for the topic you want, and then you get a reference to a page that you must look up to find the rest of the information.
A row locator’s structure depends on whether it points to a clustered table or to a heap. If referencing a clustered table, the row locator points to the clustered index, using the value from the clustered index to navigate to the correct data row. If referencing a heap, the row locator points to the actual data row.
Non-Clustered indexes cannot be sorted like clustered indexes; however, you can create more than one non-clustered index per table or view.
Non-Clustered Index is also called Logical Index.

Keep in Mind: SQL Server 2005 supports up to 249 non-clustered indexes, and SQL Server 2008 support up to 999.

In addition to an index being clustered or non-clustered, it can be configured in other ways:

  • What is Composite index: An index that contains more than one column. In both SQL Server 2005 and 2008, you can include up to 16 columns in an index, as long as the index doesn’t exceed the 900-byte limit. Both clustered and non-clustered indexes can be composite indexes.
  • Unique Index definitions: Indexes on primary keys are a special type called a unique index, in which each value can appear only once. This is how the database system ensures that primary key values are never duplicated in the tables. If the index is composite, the uniqueness is enforced across the columns as a whole, not on the individual columns. A unique index is automatically created when you define a primary key or unique constraint:
      What is Primary Key: When you define a primary key constraint on one or more columns, SQL Server automatically creates a unique, clustered index if a clustered index does not already exist on the table or view. However, you can override the default behavior and define a unique, non-clustered index on the primary key.

The index on the primary key can be clustered or non-clustered. In SQL Server, it defaults to being a clustered index. The SQL Server syntax for manually creating a clustered index on the primary key fields is shown below:

CREATE CLUSTERED INDEX index_f_name ON table_t_name (column_c_name1, column_c_name2, …);

        Explain the Unique Key: When you define a unique constraint, SQL Server automatically creates a unique, non-clustered index. You can specify that a unique clustered index be created if a clustered index does not already exist on the table. 
     CREATE UNIQUE CLUSTERED INDEX index_name
            ON table_name (column_name1, column_name2, …);
Whate is Covering index: This is a type of index that includes all the columns that are needed to process a particular query. For example, your query might retrieve the FirstName and LastName columns from a table, based on a value in the ContactID column. You can create a covering index that includes all three columns.

Simple Syntax for CREATE INDEX:

CREATE INDEX index_n_name
ON table_t_name;

Following is the example for Single-Column Indexes:

CREATE
INDEX index_n_name
ON table_t_name (column_c_name1);

Example on Composite Indexes:

CREATE INDEX index_i_name;
ON table_t_name (column12, column21…);

Nice Example on Unique Indexes:

CREATE
UNIQUE INDEX index_i_name 
 
ON table_t_name (column_c_name1, column_c_name2...);

What is DROP INDEX Command:

An index can be dropped using SQL DROP command. Care should be taken when dropping an index because performance may be slowed or improved. Some indexes are not so easy to drop, namely any index supporting a unique or primary key constraint.

DROP INDEX index_n_name;

Explain the View Existing Indexes:

EXEC sp_helpindex table_t_name;

How to Rename an Index:

EXEC sp_t_rename 'table_t_name.index_New_name', 'index_New_name';

How and when indexes can be used / avoided? 


Although indexes are intended to enhance a database's performance, there are times when they should be avoided. The following guidelines indicate when the use of an index should be reconsidered:
·                                 Indexes should not be used on small tables or tables that have frequent, large batch update or insert operations.
·                                 Indexes should not be used on columns that contain a high number of NULL values. 
·                                 Columns that are frequently manipulated should not be indexed.
·                                 If you have a lot of indexes, then every time you add, delete, or amend a row in a table (DML operations like INSERT, UPDATE, DELETE), all the indexes must be updated which takes time. So while indexes can speed up retrieval, they may slow some maintenance operations. Indexes also take up room on your storage device.
·                                 Indexes should be used only on columns which are used to search the table frequently.
·                                 Try to insert or modify as many rows as possible in a single statement, rather than using multiple queries.
·                                 Create non-clustered indexes on columns used frequently in your statement’s predicates and join conditions.
·                                 Consider indexing columns used in exact-match queries  .