Index:
Index is most important concept of SQL
Server database to increase performance of retrieving data from database.
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. When there are thousands of records in a table,
retrieving information will take a long time. Therefore indexes are created on
columns which are accessed frequently, so that the information can be retrieved
quickly. Indexes can be created on a single column or a group of columns.
Syntax: Creating Index on Table
-----SYNTAX
DEMONSTRATION OF CREATING INDEX
CREATE
INDEX <INDEX_NAME>
ON
<TABLE_NAME> <[COLUMN_NAME1],[COLUMN_NAME2],.....[COLUMN_NAME'N']>
Example: Creating Index on Table
----
DEMONSTRATION OF CREATING INDEX ON TABLE-----
CREATE
INDEX TEST_INDEX
ON
USERLOGIN(ID)
Types of Index use in SQL Server:
There are many types of Indexes in SQL Server in which some important indexes
are given as follows:
·
Unique Index
·
Clustered Index
·
Non-Clustered Index
Unique Index:
A unique index guarantees that the index key contains no duplicate values and
therefore every row in the table is in some way unique. Specifying a unique
index makes sense only when uniqueness is a characteristic of the data itself.
UNIQUE constraints allow SQL Server administrators to specify that a column may
not contain duplicate values. When you create a new UNIQUE constraint, SQL
Server checks the column in question to determine whether it contains any
duplicate values. If the table contains preexisting duplicates, the constraint
creation command fails.
Syntax: Creating Unique Index on Table
----SYNTAX
DEMONSTRATION OF CREATING UNIQUE INDEX ON TABLE
CREATE
UNIQUE INDEX
<INDEX_NAME>
ON
<TABLE_NAME> <[COLUMN_NAME1],[COLUMN_NAME2],.....[COLUMN_NAME'N']>
Example: Creating Unique Index on Table
----
DEMONSTRATION OF CREATING UNIQUE INDEX ON TABLE-----
CREATE
UNIQUE INDEX
TEST_UNIQUE
ON USERLOGIN(ID,EmailId)
Syntax: Drop
Index from table
----SYNTAX
DEMONSTRATION OF DROP INDEX FROM TABLE
DROP
INDEX <INDEX_NAME> ON
<TABLE_NAME>
Example:
Drop Index from table
----
DEMONSTRATION OF DROP INDEX FROM TABLE
DROP
INDEX TEST_UNIQUE
ON USERLOGIN
The benefits
of unique indexes include the following:
Data integrity of the defined
columns is ensured.
Additional information helpful to the query optimizer is provided.
Clustered Index:
Clustered indexes sort and store the data rows in the table based on their key
values. There can only be one clustered index per table, because the data rows
themselves can only be sorted in one order. If there is no primary key in a
table, you can add one clustered index to that table with CREATE CLUSTERED INDEX
statement.
A clustered index determines the physical order of data in a table. A clustered
index is analogous to a telephone directory, which arranges data by last name.
Because the clustered index dictates the physical storage order of the data in
the table, a table can contain only one clustered index.
With few exceptions, every table
should have a clustered index defined on the column, or columns, that offer the
following:
Can be used it for frequently used
queries.
Provide a high degree of
uniqueness.
Syntax:
Creating Cluster index on Table
--- SYNTAX
DEMONSTRATION OF CREATING CLUSTERED INDEX-------------
CREATE
CLUSTERED INDEX
<CLUSTERED_INDEX_NAME>
ON
<TABLE_NAME> <[COLUMN_NAME1],[COLUMN_NAME2],.....[COLUMN_NAME'N']>
Example:
Creating Cluster index on Table
---
DEMONSTRATION OF CREATING CLUSTERED INDEX------
CREATE
CLUSTERED INDEX
TEST_CLUSTERED
ON
USERLOGIN(ID,EMAILID)
Syntax: Drop
Clustered Index from table
----SYNTAX
DEMONSTRATION OF DROP INDEX FROM TABLE
DROP
INDEX <INDEX_NAME> ON
<TABLE_NAME>
Example:
Drop Clustered Index from table
----
DEMONSTRATION OF DROP INDEX FROM TABLE
DROP
INDEX TEST_UNIQUE
ON USERLOGIN
Non-Clustered Index:
A Non-Clustered index contains the index key values and row locators that point
to the storage location of the table data. You can create multiple Non-Clustered
indexes on a table or indexed view. Generally, Non-Clustered indexes should be
designed to improve the performance of frequently used queries that are not
covered by the clustered index. Similar to the way you use an index in a book,
the query optimizer searches for a data value by searching the Non-Clustered
index to find the location of the data value in the table and then retrieves the
data directly from that location.
This makes Non-Clustered indexes the optimal choice for exact match queries
because the index contains entries describing the exact location in the table of
the data values being searched for in the queries.
Non-Clustered Index implemented in following ways:
PRIMARY KEY and UNIQUE constraints.
Index independent of a constraint.
Non-Clustered index on an indexed view.
Syntax: Creating Non-Clustered Index on Table
---SYNTAX
DEMONSTRATION OF CREATING NON-CLUSTERED INDEX--
CREATE
NONCLUSTERED INDEX
<NONCLUSTERED_INDEX_NAME>
ON
<TABLE_NAME> <[COLUMN_NAME1],[COLUMN_NAME2],.....[COLUMN_NAME'N']>
Example: Creating Non-Clustered Index on Table
---DEMONSTRATION OF CREATING NON-CLUSTERED INDEX--
CREATE
NONCLUSTERED INDEX
TEST_NONCLUSTERED
ON
USERLOGIN
(ID,NAME,EMAILID)
Syntax: Drop
Non-Clustered Index from table
----SYNTAX
DEMONSTRATION OF DROP INDEX FROM TABLE
DROP
INDEX <INDEX_NAME> ON
<TABLE_NAME>
Example:
Drop Non-Clustered Index from table
----
DEMONSTRATION OF DROP INDEX FROM TABLE
DROP
INDEX TEST_UNIQUE
ON USERLOGIN
Limit of indexes on a table:
For SQL
Server 2005:
1 Clustered Index + 249 Non-Clustered Index = 250 Index on a table
For SQL Server 2008:
1 Clustered Index + 999 Non-Clustered Index = 1000 Index on a table
|