After writing above article I felt to write few more stuffregarding Index as I still see so many queries, concerns regarding index indeveloper’s mind, especially who are .NET developer and have very less touchwith SQL Server. I know this is not the task of .NET developer but there are somany companies in which .NET developer used to undergo the task of creatingdatabase table, SPs etc.
Since most of the people knew that Index is the key of goodperformance over SELECT statements, very few of them really know how to utilizethis sword which has blade on both the side, if you don’t use it wisely, itwill harm your performance too.
BTW, all discussion about index in this article is genericfor SQL Server; it is not related to specific SQL Server version.
Before jumping more into the Index concept, let me tell youthat all data in SQL Server being stored in page, one page consume 8KB. So wheneveryou create any table and insert data, it goes to one page, suppose you have 4KBof data in one row than two row will comes in one SQL Server page.
Index is nothing but just a kind of data structure whichhelps optimizer to find data row easily and fast. You can compare it with the “Index”you get in almost all books. If you know the topic you wanted to find, look forit in Book’s Index and you will get page number where that topic is explained.
Generally you can define index on one or more than one column,if you define your index on more than one column, it is called “Composite Index”. All the columns youhave created an Index on are called KeyColumns.
Have you ever studied “B-Tree” in any of the OOPs languageslike C++, C# etc.? Index is kind of “B-Tree” and store data in “B-Tree”structure. It suppose to have one Root Page, it may or may not haveIntermediate page (based on the size of data) and one or more than one leafpages. In sort, Root page would be the top of index structure and leaf pagewould be lower part of the index structure.
You will have entry of each row of your data table in leaflevel pages sorted in logical order. Mainly there are two type of indexes you can create on SQL Server table(I am not considering all new type of index comes up with each new version ofSQL Server, I may cover those up in future article).
You can have only one clustered Index on each table asClustered Index stores data in logical order leaf pages of your clustered indexhas actual sorted data within and this is the reason why you can’t have morethan one clustered index on your table.
As long as non clustered index concern, you can have up to249 Non-Clustered Index per table up to SQL Server 2005 and in later version,you can have limit of 999. Non-Clustered Index don’t store data sorted physicallyin its leaf level pages but it stores the pointer of the each row of theincluded column. Pointer may points to clustered index key and if you don’thave clustered index in your table than pointer points to “Row Identifier”.
“Row-Identifier” is nothing but the unique combination of File IDand Page Number and Slot Index crated by SQL Server to identify each rowuniquely in absence of Clustered Index.
So this is it, for the basics of Index, I will come up withsome more detailed article regarding each type of index very soon.
if you want to refer all other articles related to index, click here
Ask me any SQL Server related question at my “ASK Profile”