An Introduction to Clustered and Non-Clustered Index Data Structures

When I first started using SQL Server as a novice, I was initially confused as to the differences between clustered and non-clustered indexes. As a developer, and new DBA, I took it upon myself to learn everything I could about these index types, and when they should be used. This article is a result of my learning and experience, and explains the differences between clustered and non-clustered index data structures for the DBA or developer new to SQL Server. If you are new to SQL Server, I hope you find this article useful.

As you read this article, if you choose, you can cut and paste the code I have provided in order to more fully understand and appreciate the differences between clustered and non-clustered indexes.

When To Use the Order By Clause

When I was using SQL Server, I was not using the "order by" clause properly. I always say myself that when I want the data in a particular order, I should use order by clause in select statement otherwise leave to the SQL Server. I presumed MS-SQL Server will return the data in its own order. I was not even thinking that am I using and doing correctly. In my initially days in my career, I was happy to retrieve the data and not knowing how it comes. It is the nature for the software engineers at the premature stage or using new command or tool or technology. They will not knowing properly or doing enough research of the each command before commence using the command. I would say it could be because of competition in the IT field and immature in IT field.

Where do we get the word "Order?" I have heard and seen in so many places the use of "Order" word. I have come across the listed places the "Order" word used regularly.


When an applicant applies for a student visa, visa officer will politely inform to the application order the applications and come back.
When a procurer gives a draft to the registrar officer, officer will politely return back the draft and request to order the draft.
Firm will display the designation in a proper order.
Construct a house; we will clean and even the land before establishing the Pillar.



The ultimate aim is "First IN has to first come OUT"

I have decided and kept in my mind that somewhere down the line in my IT career, I should know when I should use "order by" clause in the select statement. I know it will be on one FINE DAY". I will spend some time on "order by" command. That one fine day is TODAY to explore myself and find the ways to use the "order by" clause. This could be useful for a new developer and dba to SQL Server to avoid confusion on using "order by clause" and knowing when to use "order by" clause command.

To come along with me in this article, I have paste the script and result of the output. Example of the script can be used in a sql server to understand completely after reading the article.

Pros & Cons of Using SELECT, Views, and Stored Procedures in SQL Server

When I first started using SQL Server as a novice, I was initially confused as to the differences between the SELECT statement, views, and stored procedures. They all seemed to perform more or less the same task (retrieve data), and I wanted to know the pros and cons of using each.

Why would SQL Server offer three different options to retrieve data from database? As a developer and new DBA, I took it upon myself to learn everything I could about these options, why they may be required, and when they should be used. This article is a result of my learning and experience, and explains the differences between SELECT statements, views, and stored procedures for the DBA or developer new to SQL Server. I hope you find this article useful.

As you read this article, if you choose, you can cut and paste the code into Query Analyzer I have provided in order to more fully understand and appreciate the differences between the SELECT statement, views, and stored procedures. I have divided this article into three parts to better explain this information.

Stored Procedure vs Triggers

Last time, I wrote an article and focussed on Using SELECT, Views and Stored Procedures in SQL Server. I was quite glad to differentiate the three and its Pros & Cons. I was not even given much importance of Trigger not because of NO TIME. To be honest, I was just a beginner in use of Triggers. In this article I decided to write about Triggers and its difference with Stored Procedure. We had a team meeting to incorporate the audit information in our organization.

After a few months, we had a debate to incorporate the audit information either in Stored Procedure or Trigger. In the meeting, the set of team member has suggested having audit statements in Triggers and another set of team member has recommended having audit statements in Stored Procedures. I was totally confused to take a decision. Then, I told my team members to stop the debate. I requested my team members to involve in R&D for few days before come to the conclusion and judge the Pros & Cons of the two. This is how the article has been born to have audit information in our project. I devoted my influence to write an article on Stored ProcedureVsTrigger and to differentiate the two and its Pros & Cons.

So, how do I start! I decided to use some information from my previous article http://www.sql-server-performance.com/gv_sp_views_selects.asp. I have introduced the characters that would be involving in the article before the initiative. These characters would play important role in the article.

When To Use the Order By Clause

When I was using SQL Server, I was not using the "order by" clause properly. I always say myself that when I want the data in a particular order, I should use order by clause in select statement otherwise leave to the SQL Server. I presumed MS-SQL Server will return the data in its own order. I was not even thinking that am I using and doing correctly. In my initially days in my career, I was happy to retrieve the data and not knowing how it comes. It is the nature for the software engineers at the premature stage or using new command or tool or technology. They will not knowing properly or doing enough research of the each command before commence using the command. I would say it could be because of competition in the IT field and immature in IT field.

Where do we get the word "Order?" I have heard and seen in so many places the use of "Order" word. I have come across the listed places the "Order" word used regularly.


When an applicant applies for a student visa, visa officer will politely inform to the application order the applications and come back.
When a procurer gives a draft to the registrar officer, officer will politely return back the draft and request to order the draft.
Firm will display the designation in a proper order.
Construct a house; we will clean and even the land before establishing the Pillar.



The ultimate aim is "First IN has to first come OUT"

I have decided and kept in my mind that somewhere down the line in my IT career, I should know when I should use "order by" clause in the select statement. I know it will be on one FINE DAY". I will spend some time on "order by" command. That one fine day is TODAY to explore myself and find the ways to use the "order by" clause. This could be useful for a new developer and dba to SQL Server to avoid confusion on using "order by clause" and knowing when to use "order by" clause command.

To come along with me in this article, I have paste the script and result of the output. Example of the script can be used in a sql server to understand completely after reading the article.