Tag Archives: Microsoft

Clustered Index in SQL Server

As per the promise I have made in “Some basics about Index in SQL Server” article, I am coming up with each Indexes of SQL Server and first and most important is “Clustered Index”. 
Understanding of Clustered Index:
As I told in previous article too that Index is the key of performance, good and managed Index could boost up your speed of retrieving of data from table.
Clustered Index contains actual data of the table in leaf level pages in logically sorted order (to understand root and leaf level page, click here). Since it is logically sorted, it doesn’t need to go for all data present there in Index. 
For example if you are looking for the phone number of “Ritesh Shah” in telephone directory, you can move to a page which has phone number of the person whose first name starts with “RI” and once all instance of “RI” over in directory, you don’t need to move on as there is NO CHANCE, you will get “Ritesh” anywhere in directory except those particular pages.
BTW, if you don’t have clustered index on your table, than your table would be called “HEAP”, which wouldn’t not have logically sorted data so if you are searching for “Ritesh Shah”, you can to check complete table as you never know, where you will find “Ritesh Shah”, just one method, go each and every row of table and check for matching criteria.
Like any other columns, you can define clustered index in more than one field too and all the columns covered up under the index, called key column.
While choosing a prime candidate for Clustered Index column in your table, you have to select the columns which meet few of the general criteria defined below. (you can say following criteria as a best practice while choosing index candidate)
–> You key column or combination of key columns should be unique and not null. If your You key column or combination of key columns are not unique than SQL Server has to add one more hidden column of 4-byte INT to make it unique. However, you can’t see that hidden column neither can query it directly; it would be purely for SQL Server’s internal use.
–> It should be short as wide key value would increase the depth of Clustered Index and will reduce the performance a bit and also increase the size of non-clustered index as it is being there as a reference in all non-clustered index.
–> Select less changing or no changing fields for you clustered index as Key value indicates the location of page where actual data resides, if you change this key value, row has to be deleted from that page and has to move to another appropriate page which reduces the performance and increase unnecessary overhead to IO.
Generally whenever you make Primary Key in any of your table, SQL Server itself create clustered index on it but if you want to keep clustered index on any other column(s) due to high selectivity on those column(s), you can do it.
Have you got bored of so long theory? Let us do some practical and check it out?
–create one database which you can delete after running this example
create database SQLHub
GO
USE SQLHub
GO
–if orders table is already there. you can delete it than create new one with name “Orders”
IF OBJECT_ID(‘orders’, ‘U’) IS NOT NULL BEGIN
      DROP TABLEorders
END
GO
–creating table
CREATE TABLE orders (OrderID INT IDENTITY, OrderDate DATETIME, Amount MONEY, Refno INT)
GO
–inserting 100000 fack rows into table (BTW, thank to Jacob Sebastian, copy in INSERT script from one of his demo to generate big table)
INSERT INTO orders (OrderDate, Amount, Refno)
SELECT TOP 100000
      DATEADD(minute, ABS(a.object_id % 50000 ), CAST(‘2010-02-01’ AS DATETIME)),
      ABS(a.object_id % 10),
      CAST(ABS(a.object_id) AS VARCHAR)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
–creating clustered index, however it is not meeting the criteria I have
–given in article, but this is ok for example
CREATE CLUSTERED INDEXidx_refno ON orders(refno)
GO
–run both of the following query with execution plan and see the results in execution plan
–you can see execution plan with the following steps
–first select both of the below given query
–Press Ctrl+M
–press F5
SELECT TOP 10 * from orders whererefno=4
SELECT TOP 10 * from orders whereOrderDate = ‘2010-02-01 00:04:00.000’
GO
–if you wish, you can uncomment below code and delete SQLHub database
–use master
–go
–drop database sqlhub
If you execute both the query with “Actual Execution Plan”, you will see first query is having Index Seek and second query is having Index Scan. 
Seek and Scan is really interesting topic which I will cover later but just keep in mind that, Seek is good, Scan is bad as it will check all records of the index.
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

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

Tech-Ed 2011 on the Road in Ahmedabad

Ahmedabad (Amdavad) was fortunate to had “Tech-Ed 2011 on the Road” event on 11th Jun 2011. I have attended many Tech-Ed events in Ahmedabad but I am happy and surprised too with the overwhelming response this event gets this time. 
Event was held in AMA, Ahmedabad. Hall capacity was approx 300 person even organizers had to arrange chairs to accommodate more persons, so even Pinal & Jacob had to take initiative to find empty space in the room and arrange the chair there so more people can sit there. Hats off to their spirit and feelings towards community members. BTW, even arranging more chairs, many people were standing at the back of the hall without any complain and I am sure, they too were enjoying sessions.
We had back to back interesting technical sessions. 
1st session was taken by Mr. Mahesh Dhola regarding Light Switch which was quite interesting due to practical demos.
2nd Session was taken by Pinal and we all knew that his presentation and technical skills. It was not like just purely TECHNICAL SESSION but it was so interactive and been more interesting by his presentation skill. It was like our Gujarati idioms “Gyan sathe Gammat”. I really really really enjoyed it a lot. It was awesome, amazing. He used to give understanding of really really hard topic, like Wait Types, in very easy way which most of the people can’t do. Thanks to you Pinal.
After Pinal’s session, It was Ahmedabad’s true honor to have speaker like star Evangelist of Microsoft India, Mr. Harish Vaidyanathan. He took session for HTML5, CSS3, Javascript, though I have left web development for years but I thoroughly enjoyed his session, Especially Canvas, Video and Audio facility given in HTML5. It was really awesome, If I would not have attended this session, I would never know about these features this early as I have no touch with HTML now. Thanks Harish.
After Harish’s session, There was MOST AWAITED session there, which is LUNCH. J you know what??? “Gulab Jamun” and “Paneer Butter Masala Sabji”  are my favorite so enjoyed it a lot and wanted to had small nap but one more guest speaker “Dhananjay Kumar”  won’t let me do it by his good session over Microsoft’s “Mango Phone”.
He had small session but was interesting to see multitasking 3rd Party application functionality in Mango Phone. After this session there was no chance to sleep, no SQL guys could ever think to have nap in the session of legendary speaker Mr. Jacob Sebastian.
He is the proud of Ahmedabad and real SQL/XML guru. As Pinal has said in the starting of session, Jacob really felt us bad by his session, he show us what we were doing wrong so far everyday in our TSQL, he show us worst practices in TSQL code.
After finishing of this SQL session there was one more interesting session by one of my friend Tejas Shah on ASP.NET Tips & Tricks. He comes up with less slides and more practical example, people were happy to see the few of the very good tips from him.
After Tejas’s seesion, we had many gifts giveaway to the winners who won the quiz during the event and then we had tea after finishing the event and good chance for networking. 
Finally a BIG THANKS to Microsoft to give us this good opportunity.
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

Life savior Dedicated Administrator Connection (DAC) in SQL Server 2008

DAC could become a real life savior for SQL Server DBA in some extreme critical situation when you are not able to connect to your SQL Server instance by any other way or you found you instance totally unresponsive, you can use DAC connection.
You shouldn’t practice to login via DAC connection in normal situation as it is special connection used to use as a rescue commando only. It intends to diagnose the issue, troubleshoot it and hopefully resolve it when you are not able to make connection to instance vial any other way.
BTW, you couldn’t make more than one connection with DAC at the same time and doing so will be resulted in an error. 
Before we see how to login with DAC, I would like to tell you few points which you should keep in mind before you try out DAC.
–) you must have sysadmin server role with your login to make DAC connection
–) there are few restrictions while you make connection with DAC, you can’t Backup/Restore database. Anyhow it is advisable not to use any heavy task while you are connected via DAC as it is designed to diagnose the problem and fix it only so you might want to look around to some catalog view or DMVs.
–) Generally DAC is available locally only by default, if you try to login via DAC from remote computer, you might see following error (however, you can enable DAC from remote computer too).

There are two ways to get connected with DAC. 1.) SQLCMD 2.) SSMS. All you need to do is, specify ADMIN: as a prefix of your SQL Server instance. Suppose my instance name is “SQLHub”, I should use “Admin:SQLHub”. Suppose my instance name is “SQLHub\SQL2K8R2”, I should use “Admin:SQLHub\SQL2K8R2”.
From SQLCMD, you can connect to DAC by specifying -A flag. Now, let us see, how we can do it via SSMS.
While opening SSMS, you will see login prompt “Connect to Server”, if you will give you credential of sysadmin server role along with admin:servername(instance), you will be greeted with an error given below.

TITLE: Connect to Server
——————————

Cannot connect to admin:WIN-7XRT6YL02S0.

——————————
ADDITIONAL INFORMATION:
Dedicated administrator connections are not supported. (ObjectExplorer)

——————————
BUTTONS:

OK
——————————

 

The reason is, only one DAC connection is possible as I have already written above, if you want to access DAC connection via SSMS, don’t try to login via object explorer, rather cancel that window and follow the given steps below.
File Menu->New->Database Engine Query
Give you credential of sysadmin account, keep admin: before your server name and you will be able to connect to DAC.

WIN-7XRT6YL02S0 is my server name of SQL Server 2008, you can see “Admin:” before my server name. I am going to use my windows administrator account as it has sysadmin server role, however you can use any of your login which has sysadmin server role permission.
Keep this information ready as you never know when you need it?
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

Understanding basic tools of SQL Server 2008/2005

One of my friends has just updated his live database from SQL Server 2000 to SQL Server 2008. You may know that we had direct access of system table in SQL Server 2000 which is not directly possible in SQL Server 2008/2005. He has developed many scripts in last 8 years which was directly accessing many of the system tables to generate certain kind of report, out of such scripts, few was not working. 
In recent SQL Server (2005/2008) version, we do have those “System Base tables” but it is not directly visible as well as accessible unless you are making dedicated administrator connection (DAC).  You have to access those information stored in system base table via “Compatibility View” or “Catalog View”.
“Compatibility View” is there to support your old scripts (though few very specific won’t work) which was using direct system tables in SQL Server 2000. For example we had sysobjects, sysindexes, sysusers etc. tables in older version but now we have views with the same name so your scripts of older version would works fine. Though it is “Compatibility View” intend to provide backward compatibility only, it doesn’t consist many columns which provides information about specific features of newer SQL Server version so it is recommended to use “Catalog View”.
Right from SQL Server 2005+ we are having “Catalog View” which was reading may useful information from system base tables and provide us. “Catalog View” along with “Dynamic Management View (DMV)” is under sys schema. I would prefer to go with “Catalog View” rather than “Compatibility View”. Because “Catalog View” is much richer than “Compatibility View”, you can run following query and see result by your own.
–Compatability View
select * from sysdatabases
–Catalog View
select * from sys.databases
Apart from these Views, there is one more powerful tool to read metadata of SQL Server 2008/2005 and internal information about SQL Server is Dynamic Management View (DMV) and Dynamic Management Functions (DMF). DMV and DMF generally starts with “dm_” and it resides in “sys” database so it would always starts with prefixes “sys.dm_” for example:
–return information about authenticated session in SQL Server
select * from sys.dm_exec_sessions
–returns info about currently locked object.
select * from sys.dm_tran_locks
–this is DMF and retun I/O stat for MDF and LDF file.
select * from sys.dm_io_virtual_file_stats(DB_ID(N’master’), 2);
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

Sharpen your .NET Skills with .NET Quiz 2011 at BeyondRelational.com

SQL Server & XML Guru Mr. Jacob Sebastian (SQL Server MVP) has recently completed one grand online event for SQL Server along with other 30 SQL Server MVP/Blogger, winners are already declared for that, find more details here.
After completing this grand international online event, he has now started .NET Quiz 2011 on BeyondRelational.Com with help of  Hima Bindu Vejella who is Quiz Manager for “.NET Quiz 2011”.
This event has already started from 1st January 2011 for 31 days. Each day one .NET quiz master will ask one question and s/he will moderate the discussion and answer of that question. Finally Quiz Master will rate your answer between rank 1 and 10. Score of all 31 questions will be summed up to identify the winner of the competition after 31st January 2011.
You don’t have only chance to share/enhance your knowledge but have chance to win some of exciting prices like Apple iPad and free license of some cool .NET tools.
So what are you waiting for??? 
Just grab the opportunity. If you are still not registered member of http://beyondrelational.com . Register now!!!. 
Here is the official home page for .NET Quiz 2011.
BTW, I am too one of the quiz master and my question is going to be enabled by today 2nd January 2011. Here is the link for the same.
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

Experiencing installation of SQL Server Denali in Windows Server 2008

Before we move further for technical discussion, want to say:
Happy New Year to all reader!!!
May GOD bless you with full of joy, health, wealth and prosperity.
BTW, I have been inactive in blogging since last few months so I am making New Year resolution that I keep writing more and more article and try to help community as much as possible.
Let us now begin journey of new powerful “Denali” in the starting of New Year. Microsoft has released SQL Server Denali CTP1 sometime back only. 
I am very much excited to make grip over it so I have prepared one Virtual PC with Windows Server 2008 SP1 as I had paper license of the same. As soon as I have installed Windows Server 2008 with SP1, I have downloaded Denali and started installing it and greeted with one message which was bit annoying to me. 
Error was “The operating system on this computer doesn’t meet the minimum requirement for SQL Server “Denali” CTP1”
Actually this is CTP1 only and doesn’t install required software by its own so we have to meet basic hardware and software requirement prior to install “Denali”.
I have installed following SP and software to make my system ready for “Denali”.
After installing all above stuff, I was able to successfully install SQL Server “Denali”.
Here is the splash screen of the same:
After looking at the new SSMS of SQL Server “Denali”, I got the feeling of Visual Studio 2010 as I have installed Visual Studio 2010 few weeks back only and getting same feeling due to changed UI of SSMS and blue back color.
 
Happy Coding!!!
At the end of this article, Wish you a great year ahead. Happy New Year 2011 Once again.
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

For XML PATH ROOT in SQL Server 2008

Now and Then I used to observe that when it comes to XML, .NET and SQL Developer start fearing. This is not because of It is VERY DIFFICULT but because of unawareness. Today I am going to show you the use of  “FOR XML PATH”  clause in SQL Server.
Suppose we have ID, FirstName and LastName columns in one of our SQL Server table and we want output something like this:
<Employees>
  <Employee>
    <ID value=1 />
    <FirstName value=Ritesh />
    <LastName value=Shah />
  Employee>
  <Employee>
    <ID value=2 />
    <FirstName value=Rajan />
    <LastName value=Jain />
  Employee>
Employees>
Let me give you TSQL to generate table in SQL Server and insert records in that.
Create Table EmployeeData
(
      ID INT Identity(1,1),
      FirstName varchar(10),
      LastName varchar(10)
)
insert into EmployeeData
select ‘Ritesh’,‘Shah’ UNION ALL
select ‘Rajan’,‘Jain’
GO
So now, here is the use of “FOR XML PATH ROOT” clause which will make our life easier in this kind of situation.
select ID as “ID/@value”
     , FirstName as “FirstName/@value”
     , LastName as “LastName/@value”
from (
   Select * from EmployeeData
) as t
for xml path(‘Employee’), root(‘Employees’);
Isn’t it easy to use?
BTW, below given are some links which will redirect you to my some of the past articles on XML subject.
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