Watch Our YouTube Channel
It is currently Tue Jan 23, 2018 1:56 am

All times are UTC

Post new topic Reply to topic  [ 2 posts ] 
Author Message
 Post subject: What is Index in SQL?
PostPosted: Thu Oct 13, 2016 8:04 am 

Joined: Fri Nov 13, 2015 3:56 am
Posts: 460
SQL Indexes are special lookup tables that the database search engine can use to speed up data retrieval.
An index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.
An index helps speed up SELECT queries and WHERE clauses, but it slows down data input, with UPDATE and INSERT statements. Indexes can be created or dropped with no effect on the data.
Indexes can also be unique, similar to the UNIQUE constraint, in that the index prevents duplicate entries in the column or combination of columns on which there's an index.

The basic syntax of CREATE INDEX is as follows:
CREATE INDEX index_name ON table_name(column_name);

Types of indexes:
Unique Index is Guarantees unique values for the column(or set of columns) included in the index.A unique index ensures that the index key contains no duplicate values and therefore every row in the table or view is in some way unique.Uniqueness can be a property of both clustered and nonclustered indexes.

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A non clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Covering indexes are non-clustered indexes that include nonkey columns in the leaf level. These types of indexes improve query performance, cover more queries, and reduce IO operations as the columns necessary to satisfy a query are included in the index itself either as key or nonkey columns. Covering indexes can greatly reduce bookmark lookups.

Filtered indexes can take a WHERE clause to indicate which rows are to be indexed. Since you index only a portion of rows in a table, you can create only a non-clustered filtered index. If you try to create a filtered clustered index, SQL Server returns a syntax error.

Hash index, data is accessed through an in-memory hash table. Hash indexes consume a fixed amount of memory, which is a function of the bucket count.

Memory-optimized nonclustered indexes,memory consumption is a function of the row count and the size of the index key columns

A spatial index provides the ability to perform certain operations more efficiently on spatial objects (spatial data) in a column of the geometry data type. The spatial index reduces the number of objects on which relatively costly spatial operations need to be applied.

Index configurations:
A table can have one of the following index configurations:
No indexes
A clustered index
A clustered index and many nonclustered indexes
A nonclustered index
Many nonclustered indexes

Index Limitations:
Indexes should not be used on small tables.
Indexes should not be used on columns that contain a high number of NULL values.
Columns that are frequently manipulated should not be indexed.
Tables that have frequent, large batch update or insert operations.

Learn Web Development | Latest Technology News |Motivational Quotes

Reply with quote  
PostPosted: Tue Oct 25, 2016 9:14 am 

Joined: Wed Jul 01, 2015 11:28 am
Posts: 172
Location: Bangalore
Very informative. It is a detailed post on index used in SQL.

Digital Marketing Company in India| Web Design Company in India | SEO Services in India

Reply with quote  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

All times are UTC

Who is online

Users browsing this forum: No registered users and 1 guest

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to: