Sunday, July 20, 2014

Database Tuning – Indexes

Hi Guys, after a quite long time I am posting this article today, this post is more of into database tuning and in this I have elaborated following things.

1. What are indexes in databases?

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, 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.

Click here to know more

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.

Creating an index involves the CREATE INDEX statement, which allows you to name the index, to specify the table and which column or columns to index, and to indicate whether the index is in ascending or descending order.

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.

2. What is Index Fragmentation?

When data is inserted into, deleted from, or updated in a SQL Server table, the indexes defined on that table are automatically updated to reflect those changes. As the indexes are modified, the information stored in them becomes fragmented, resulting in the information being scattered across the data files. When this occurs, the logical ordering of the data no longer matches the physical ordering, which can lead to a deterioration of query performance.

There are two types of fragmentation:

a) Internal Fragmentation indicates that there is too much free space on the index page.

b) External Fragmentation indicates that the logical ordering and physical ordering do not match

I will explain more on this later, in my next post and off course very soon so stay tuned :) . Keep reading.