Tag Archives: microsoft sql server

Some basics about Index in SQL Server

I wrote an article about index “Be selective 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 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.
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 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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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 in one table and there is PK on fields so it suppose to have clustered index by its own on that field so it is working fast when we keep that PK field in WHERE clause but working very slow when they keep other than that PK field in WHERE clause. He wants to make as many as possible index on his table so it works fast with any of the fields in WHERE clause.
This seems interesting case to me so I am writing this blog post, actually I have seen this kind of confusion and concerns in many other developers too. Let us make it clear.
Till SQL Server 2005, there was a limit of 249 nonclustered index on one table and 1 clustered index on the same table but nonclustered index limit was increased from 249 to 999 in SQL Server 2008 version so people might think that Microsoft has given us facility to create so many indexes on one table, why shouldn’t we use it?
Keep one thing in mind that, Index may boost up performance of you SELECT statement but it puts overhead in Insert/Delete DML commands as while manipulating records in the table, it has to go to each nonclustered index and add/remove records in each index which simply decrease the performance of your Insert/Delete statement if you have unnecessary indexes.
Apart from the reason given above, each index needs disk space to store its data and cache memory to load all indexes which simply affect the performance again due to high IO hits.
There is one more reason, if you have so many indexes; your SQL Server optimizer might get confused regarding what index to go for while executing the query so it evaluates all indexes to find the best suited index to run for, which is simply waste of time and resources especially if you are having less cache.
So keeping long story short, you should have to be selective while creating index. You shouldn’t have to create nonclustered index on every fields but find out the best selectivity in your table and create index on that only.
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
Ask me any SQL Server related question at my “ASK Profile”.
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah