Some basics about Index in SQL Server

I wrote an article about index “Beselective while creating Index” on my blog few days back based on the questionI have received in my “ASK Profile” in BeyondRelational.Com.
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.
Understanding ofIndex:
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).
1.)    ClusteredIndex
2.)    Non-ClusteredIndex
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
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a defaultreference of all articles but examples and explanations prepared by RiteshShah, founder of http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile
Advertisements

Be selective while creating an Index in SQL Server

I have just received a question in my “ASK Profile” in BeyondRelational.com.Poster asking me that he has big table with hundreds of thousands of rows inone table and there is PK on fields so it suppose to have clustered index byits own on that field so it is working fast when we keep that PK field in WHEREclause but working very slow when they keep other than that PK field in WHEREclause. He wants to make as many as possible index on his table so it worksfast with any of the fields in WHERE clause.
This seems interesting case to me so I am writing this blogpost, actually I have seen this kind of confusion and concerns in many otherdevelopers too. Let us make it clear.
Till SQL Server 2005, there was a limit of 249 nonclusteredindex on one table and 1 clustered index on the same table but nonclusteredindex limit was increased from 249 to 999 in SQL Server 2008 version so peoplemight think that Microsoft has given us facility to create so many indexes onone table, why shouldn’t we use it?
Keep one thing in mind that, Index may boost up performanceof you SELECT statement but it puts overhead in Insert/Delete DML commands aswhile manipulating records in the table, it has to go to each nonclusteredindex and add/remove records in each index which simply decrease theperformance of your Insert/Delete statement if you have unnecessary indexes.
Apart from the reason given above, each index needs diskspace to store its data and cache memory to load all indexes which simplyaffect the performance again due to high IO hits.
There is one more reason, if you have so many indexes; yourSQL Server optimizer might get confused regarding what index to go for whileexecuting the query so it evaluates all indexes to find the best suited indexto run for, which is simply waste of time and resources especially if you arehaving less cache.
So keeping long story short, you should have to be selectivewhile creating index. You shouldn’t have to create nonclustered index on everyfields but find out the best selectivity in your table and create index on thatonly.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile”.

Be selective while creating an Index in SQL Server

I have just received a question in my “ASK Profile” in BeyondRelational.com.Poster asking me that he has big table with hundreds of thousands of rows inone table and there is PK on fields so it suppose to have clustered index byits own on that field so it is working fast when we keep that PK field in WHEREclause but working very slow when they keep other than that PK field in WHEREclause. He wants to make as many as possible index on his table so it worksfast with any of the fields in WHERE clause.
This seems interesting case to me so I am writing this blogpost, actually I have seen this kind of confusion and concerns in many otherdevelopers too. Let us make it clear.
Till SQL Server 2005, there was a limit of 249 nonclusteredindex on one table and 1 clustered index on the same table but nonclusteredindex limit was increased from 249 to 999 in SQL Server 2008 version so peoplemight think that Microsoft has given us facility to create so many indexes onone table, why shouldn’t we use it?
Keep one thing in mind that, Index may boost up performanceof you SELECT statement but it puts overhead in Insert/Delete DML commands aswhile manipulating records in the table, it has to go to each nonclusteredindex and add/remove records in each index which simply decrease theperformance of your Insert/Delete statement if you have unnecessary indexes.
Apart from the reason given above, each index needs diskspace to store its data and cache memory to load all indexes which simplyaffect the performance again due to high IO hits.
There is one more reason, if you have so many indexes; yourSQL Server optimizer might get confused regarding what index to go for whileexecuting the query so it evaluates all indexes to find the best suited indexto run for, which is simply waste of time and resources especially if you arehaving less cache.
So keeping long story short, you should have to be selectivewhile creating index. You shouldn’t have to create nonclustered index on everyfields but find out the best selectivity in your table and create index on thatonly.
Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles butexamples and explanations prepared by Ritesh Shah, founder of
http://www.SQLHub.com
Ask me any SQL Server related question at my “ASK Profile”.

ASK Expert in BeyondRelational.com

SQL & XML GURU Mr. Jacob Sebastian has real passion to reach more and more IT person to help them in Microsoft technology. I always appreciate him for his endeavor efforts to help community.  Jacob has started new forum “ASK” under BeyondRelational.com  where you can ask question directly to the industry experts.
There are many experts available there for various subjects like TSQL, XML, ETL, SSIS, SQL Server, .NET, DBA, performance tuning and many more.
If you want to ask me any SQL Server related question, feel free to ask at following link.
You may find many forums regarding all topics I have mentioned above, the main advantage here is, you can directly ask to experts rather than raising question in other forums and wait for somebody to look at your question and answer it.
So, what are you waiting for? Register in beyondrelational.com and ask question which is giving you sleepless night.
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