I wrote an article about index “Be
selective while creating Index” on my blog few days back based on the question
I have received in my “ASK Profile” in BeyondRelational.Com.
After writing above article I felt to write few more stuff
regarding Index as I still see so many queries, concerns regarding index in
developer’s mind, especially who are .NET developer and have very less touch
with SQL Server. I know this is not the task of .NET developer but there are so
many companies in which .NET developer used to undergo the task of creating
database table, SPs etc.
Since most of the people knew that Index is the key of good
performance over SELECT statements, very few of them really know how to utilize
this sword which has blade on both the side, if you don’t use it wisely, it
will harm your performance too.
BTW, all discussion about index in this article is generic
for SQL Server; it is not related to specific SQL Server version.
Before jumping more into the Index concept, let me tell you
that all data in SQL Server being stored in page, one page consume 8KB. So whenever
you create any table and insert data, it goes to one page, suppose you have 4KB
of data in one row than two row will comes in one SQL Server page.
Understanding of
Index:
Index is nothing but just a kind of data structure which
helps 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 for
it 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 you
have created an Index on are called Key
Columns.
Have you ever studied “B-Tree” in any of the OOPs languages
like 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 have
Intermediate page (based on the size of data) and one or more than one leaf
pages. In sort, Root page would be the top of index structure and leaf page
would be lower part of the index structure.
You will have entry of each row of your data table in leaf
level 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 of
SQL Server, I may cover those up in future article).
1.) Clustered
Index
2.) Non-Clustered
Index
You can have only one clustered Index on each table as
Clustered Index stores data in logical order leaf pages of your clustered index
has actual sorted data within and this is the reason why you can’t have more
than one clustered index on your table.
As long as non clustered index concern, you can have up to
249 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 physically
in its leaf level pages but it stores the pointer of the each row of the
included column. Pointer may points to clustered index key and if you don’t
have clustered index in your table than pointer points to “Row Identifier”.
“Row-Identifier” is nothing but the unique combination of File ID
and Page Number and Slot Index crated by SQL Server to identify each row
uniquely in absence of Clustered Index.
So this is it, for the basics of Index, I will come up with
some more detailed article regarding each type of index very soon.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile”
0 comments:
Post a Comment