" Indexes are extremely useful in improving performance while accessing records." Most of the people know this fact. One of my friend asked a question when Index should not be used ? . Here is small introduction about indexes and when they should be used and when not.
Indexes are nothing but pointers to data in table.If you are searching a particular topic in a book, you go to index page in the end of the book and search that word there and go to pages mentioned in that. Similarly if you are searching a book in a library you search by name of book or author,then you will get rack number and you will search book there. Similar concept is used here in indexing.
For types of indexes in SQLServer 2012 you can refer this link of MSDN.
http://msdn.microsoft.com/en-IN/library/ms175049.aspx
CREATE INDEX index_name
ON table_name (column_name);
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.While creating an Index we should consider following things.
- How frequently the records be inserted or deleted ?
- How frequently will the key columns be updated ?
- How often be the index be used ?
- What processes does the index support ?
- How many other indexes are on the table ?
When not to use indexes in Database . Here is few instances, you should not use indexes.
- A table with a small number of fields may not benefit from an index if a large percentage of its records are always retrieved from it. Creating an index will not prevent full table scans. Note that removing primary key or foreign keys is not a good idea.
- Small static data tables are often small enough to be read as a table scan rather than an index scan, plus a point into a table. Let's explain by example. Underlying I/O activity in the operating system (UNIX, Windows, Linux, and many others) is read in what are sometimes called blocks or pages. These pages can be many sizes, but usually at the database level the pages become 2 KB, 4 KB, 8 KB, 16 KB, and sometimes even 32 KB pages. The point to understand is that if a table has a small enough number of records, to occupy a single physical page, why read an index and point to a table? Reading the index and the table is reading two pages, when reading the table only constitutes an I/O on a single page only.
- Often, tables created for reporting or during data warehouse periodical appending (batch updates) may already be in the required physical order.
- Indexes should usually be created on a small percentage of the fields in a table. Large composite indexes may be relatively large compared with the table. The relative size between index and table is important. The larger the ratio of index to table physical size, the less helpful the index will be in terms of decreasing physical space to be read. Fields containing NULL values may exacerbate this effect. It may be faster to read the entire table, rather than a large composite field index, containing a lot of NULL values. Why create indexes with every field for a composite? It is, of course, acceptable to create a duplicate valued index on less than all the fields desired by the composite field structure. Sometimes a partial index is more efficient than no index, and also more efficient than the complete field set for a composite field index.
2. "Beginning of Database design" by Gavin Powell
No comments:
Post a Comment