Let's talk about indexing

Without an index, the RDBMS would serve no purpose because they could not afford any quick search in the data.Understand and use indexes to their full potential is what will give you the most important lever to increase the performance of your databases

You are the administrator of databases, your professional life is busy. But this does not relieve the great metaphysical questions: what is the meaning of life? To answer this, you decide to meet a philosopher. What are you going to do to find one in your town? Of course, you'll consult the phone book, and more specifically the yellow pages. If you do not have the book, you have no alternative but to roam the streets in search randomly, on a plate or a mailbox, the reference of business philosopher. SQL Server is no different. When you filter the contents of a table in a query, using usually a predicate in a WHERE clause, SQL Server must scan the table to evaluate this predicate on each line. This operation is obviously very expensive in proportion to the size of the table.

The entire table must be stored and searched, that is to say that all data pages that contain the table are loaded, which may mean gigabytes for a simple SELECT ... This path is called a scan. The only way to avoid this is to create an index. An index is a proper kind of table of contents, in a tree structure containing all values in one or more columns, which allows an optimized search on the values it contains. This tree is called balanced tree or B-tree).

Unlike other DBMS that implement bitmap index, or by chopping, there is yet a single physical structure index in SQL Server: B-Tree (except the full-text index, a little apart). Even XML index and spatial indexes on spatial data are internally index of B-Tree structure on an internal table for XML, and a hierarchical decomposition of space for spatial indexes. The B-tree index can be of two types: nonclustered and clustered (sometimes called unordered and ordered).

Choice of index

when must you select a clustered index, and when must you select a nonclustered index? Like the last level of the index is the line itself, any search through the clustered index will be very fast, since the lookup stage will be useless. The clustered index is to choose carefully, because, of course, you can create only one clustered index per table (as there is only one alphabetically order in a dictionary ... it is only in the world quantum physics that an object can duplicate itself and take two different natures. A table cannot be physically sorted by two different keys at once).


No responses found. Be the first to comment...

  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: