Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

When To Use the Order By Clause

By Gulappa Vijaya Kumar,

Editor's Note: The advice given in this article is not necessarily endorsed by the editor. The only way to guarantee the order of rows in a result set is with the ORDER BY clause.

However, in the interest of allowing disparate opinions, we have published this article. Please be sure you read the discussion for this article to see dissenting opinions.

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.

Create Table

To explain the SQL Server "Order by" clause; let's commence to create a new table and add some sample data using the following script. I presume you have got a database to use the following script otherwise create a one to use these examples.

Create Table DummyTable1
(
EmpId Int,
EmpCode  Varchar(10),
EmpName Varchar(8000) 
)

The table has been created successful. This table has contained no data with a table structure. We should need data to explore the use of "order by" clause in SQL Server. Now let's add a few records in this table using this script:

Insert Into DummyTable1 Values (4, `004', Replicate ('d',20))
GO
Insert Into DummyTable1 Values (6, `006', Replicate ('f',20))
GO
Insert Into DummyTable1 Values (1, `001', Replicate ('a',20))
GO
Insert Into DummyTable1 Values (3, `003', Replicate ('c',20))
GO
Insert Into DummyTable1 Values (10, `010', Replicate ('j',20))
GO
Insert Into DummyTable1 Values (2, `002', Replicate ('b',20))
GO
Insert Into DummyTable1 Values (5, `005', Replicate ('e',20))
GO
Insert Into DummyTable1 Values (8, `008', Replicate ('h',20))
GO
Insert Into DummyTable1 Values (9, `009', Replicate ('i',20))
GO
Insert Into DummyTable1 Values (7, `007', Replicate ('g',20))
GO

Now, let's view the contests of the table by executing the following command in Query Analyzer for our new table.

Select EmpID, EmpCode, EmpName From DummyTable1
GO
 
EmpID	EmpCode	EmpName	   
4	004	dddddddddddddddddddd	   
6	006	ffffffffffffffffffff	   
1	001	aaaaaaaaaaaaaaaaaaaa	   
3	003	cccccccccccccccccccc	   
10	010	jjjjjjjjjjjjjjjjjjjj	   
2	002	bbbbbbbbbbbbbbbbbbbb	   
5	005	eeeeeeeeeeeeeeeeeeee	   
8	008	hhhhhhhhhhhhhhhhhhhh	   
9	009	iiiiiiiiiiiiiiiiiiii	   
7	007	gggggggggggggggggggg	 

The data is not arranged orderly. As you would expect, the data we inserted earlier has been displayed. Note that the order of the results is in the same order that I inserted them in, which is in no order at all.

MS-SQL Server retrieves the data as it is unless we specify an "order by" clause. We will explicit use an "order by" clause in the select statement to view the content in proper order.

Now, let's view the contests of the table using "order by" clause by executing the following command in Query Analyzer for our new table.

Select EmpID, EmpCode, EmpName From DummyTable1 Order By EmpID
GO
 
EmpID	EmpCode	EmpName	   
1	001	aaaaaaaaaaaaaaaaaaaa	   
2	002	bbbbbbbbbbbbbbbbbbbb	   
3	003	cccccccccccccccccccc	   
4	004	dddddddddddddddddddd	   
5	005	eeeeeeeeeeeeeeeeeeee	   
6	006	ffffffffffffffffffff	   
7	007	gggggggggggggggggggg	   
8	008	hhhhhhhhhhhhhhhhhhhh	   
9	009	iiiiiiiiiiiiiiiiiiii	   
10	010	jjjjjjjjjjjjjjjjjjjj	 

A few questions have arisen in my mind:

  1. Why SQL server has not arranged the data in order?
  2. Why can't SQL Server decide itself EmpID = 1 has to come first and EmpID = 2 has to come second and so on.
  3. When will SQL Server order the data in a table?
  4. Is SQL Server expecting something from a user to arrange the data?
  5. If so, what is that?
For all these questions, MS-SQL server has arranges the data in two possible ways.
  1. Clustered index in a table
  2. Non-clustered index in a table.

I will not explain how MS-SQL server stores data for clustered and non-clustered indexes. Please click here http://www.sql-server-performance.com/gv_index_data_structures.asp for more information on data storage for clustered and non-clustered indexes.

Let us explore when to we use the "order by" clause in the select statement using non-clustered index on empid column.

Non-Clustered index

CREATE UNIQUE NONCLUSTERED INDEX DummyTable1_EmpID
ON DummyTable1 (EmpID)
GO

Non-clustered index has been created successful. Now, let's view the contests of the table by executing the following command in Query Analyzer.

Select EmpID From DummyTable1
GO
 
EmpID	   
1	   
2	   
3	   
4	   
5	   
6	   
7	   
8	   
9	   
10	 

Data has been displayed in a proper order and there is no "order by" clause in the select statement. There is no need for "order by" clause in the select statement. Why? Only an Indexed column has been specified in the select statement and need not require an "order by" clause command. We have asked only an EmpID data in the select statement. SQL Server has already arranged the Empid data in a proper order, which has got a non-clustered index. We need not explicit specify an "order by" clause in the select statement.

So, "order by" clause might be required for Non-index column in the select statement and Non-index column along with index-column in the select statement. Let us get in to some practical knowledge.

Now, let's view the non-index column contests of the table without an "order by" clause by executing the following command in Query Analyzer.

Select EmpCode, EmpName From DummyTable1
GO
 
EmpCode	EmpName	   
004	dddddddddddddddddddd	   
006	Ffffffffffffffffffff	   
001	aaaaaaaaaaaaaaaaaaaa	   
003	cccccccccccccccccccc	   
010	Jjjjjjjjjjjjjjjjjjjj	   
002	bbbbbbbbbbbbbbbbbbbb	   
005	eeeeeeeeeeeeeeeeeeee	   
008	hhhhhhhhhhhhhhhhhhhh	   
009	Iiiiiiiiiiiiiiiiiiii	   
007	gggggggggggggggggggg	 

The data is not arranged orderly. As you would expect, the data we inserted earlier has been displayed. Note that the order of the results is in the same order that I inserted them in, which is in no order at all.

Let's view the non-index column contests of the table with an "order by" clause by executing the following command in Query Analyzer.

Select EmpCode, EmpName From DummyTable1 Order by EmpCode
GO
 
EmpCode	EmpName	   
001 	aaaaaaaaaaaaaaaaaaaa	   
002 	bbbbbbbbbbbbbbbbbbbb	   
003 	cccccccccccccccccccc	   
004 	dddddddddddddddddddd	   
005 	eeeeeeeeeeeeeeeeeeee	   
006 	Ffffffffffffffffffff	   
007 	gggggggggggggggggggg	   
008 	hhhhhhhhhhhhhhhhhhhh	   
009 	Iiiiiiiiiiiiiiiiiiii	   
010 	Jjjjjjjjjjjjjjjjjjjj	 
Or
Select EmpCode, EmpName From DummyTable1 Order by EmpName
GO
 
EmpCode	EmpName	   
001 	aaaaaaaaaaaaaaaaaaaa	   
002 	bbbbbbbbbbbbbbbbbbbb	   
003 	cccccccccccccccccccc	   
004 	dddddddddddddddddddd	   
005 	eeeeeeeeeeeeeeeeeeee	   
006 	Ffffffffffffffffffff	   
007 	gggggggggggggggggggg	   
008 	hhhhhhhhhhhhhhhhhhhh	   
009 	Iiiiiiiiiiiiiiiiiiii	   
010 	Jjjjjjjjjjjjjjjjjjjj	 

MS-SQL server has retrieved the data in an order for a non-index column by using "order by" clause in a select statement. "Order by" clause is required in the select statement if we need the data in order for a non-index column.

Let us see what will happen if we club both index column and non-index column in a select statement without an "order by" and with an "order by" clause. Let's view the index column and non-index column contests of the table without an "order by" by executing the following command in Query Analyzer.

Select EmpID, EmpCode, EmpName From DummyTable1
GO
 
EmpID	EmpCode	EmpName	   
4	004	dddddddddddddddddddd	   
6	006	ffffffffffffffffffff	   
1	001	aaaaaaaaaaaaaaaaaaaa	   
3	003	cccccccccccccccccccc	   
10	010	jjjjjjjjjjjjjjjjjjjj	   
2	002	bbbbbbbbbbbbbbbbbbbb	   
5	005	eeeeeeeeeeeeeeeeeeee	   
8	008	hhhhhhhhhhhhhhhhhhhh	   
9	009	iiiiiiiiiiiiiiiiiiii	   
7	007	gggggggggggggggggggg	 

MS-SQL Server has retrieves the data in disorder. Non-clustered index only on Empid column and EmpCode, EmpName column does not have an index. We specify only an index column [EmpID] in a select statement. We do not want an "order by" clause in a select statement and data will display in order.

Let's view the index column and non-index column contests of the table with an "order by" by executing the following command in Query Analyzer.

Select EmpID, EmpCode, EmpName From DummyTable1 Order By EmpID
GO
 
EmpID	EmpCode	EmpName	   
1	001	aaaaaaaaaaaaaaaaaaaa	   
2	002	bbbbbbbbbbbbbbbbbbbb	   
3	003	cccccccccccccccccccc	   
4	004	dddddddddddddddddddd	   
5	005	eeeeeeeeeeeeeeeeeeee	   
6	006	Ffffffffffffffffffff	   
7	007	gggggggggggggggggggg	   
8	008	hhhhhhhhhhhhhhhhhhhh	   
9	009	Iiiiiiiiiiiiiiiiiiii	   
10	010	Jjjjjjjjjjjjjjjjjjjj	 

Data has been retrieved in correct order. All the above examples, I have disclosed "when to use an order by clause" and "when not to use an order by clause" in a select statement in two possible ways.

  1. We do not require an "order by" clause for only an index column in a select statement.
  2. We should have an "order by" clause for a non-index column in a select statement and both index and non-index column in a select statement.

Clustered index

Let us start with creating a clustered index on empid column. Before create an index on empid column. We will drop an existing index [DummyTable1_EmpID] in DummyTable1 on empid column.

DROP INDEX DummyTable1.DummyTable1_EmpID
GO

Index has been dropped. Now, let's view the contests of the table by executing the following command in Query Analyzer.

Select EmpID, EmpCode, EmpName From DummyTable1
GO
 
EmpID	EmpCode	EmpName	   
4   	004	dddddddddddddddddddd	   
6   	006	ffffffffffffffffffff	   
1   	001	aaaaaaaaaaaaaaaaaaaa	   
3   	003	cccccccccccccccccccc	   
10   	010	jjjjjjjjjjjjjjjjjjjj	   
2   	002	bbbbbbbbbbbbbbbbbbbb	   
5   	005	eeeeeeeeeeeeeeeeeeee	   
8   	008	hhhhhhhhhhhhhhhhhhhh	   
9   	009	iiiiiiiiiiiiiiiiiiii	   
7   	007	gggggggggggggggggggg	 

The data is not in correct order. The data we inserted earlier has been displayed. Note that the order of the results is in the same order that I inserted them in, which is in no order at all.

Let us explore when to we use the "order by" clause in the select statement using a clustered index on empid column.

CREATE UNIQUE CLUSTERED INDEX DummyTable1_EmpID
ON DummyTable1 (EmpID)
GO

Clustered index has been created successful. Now, let's view the contests of the table by executing the following command in Query Analyzer.

Select EmpID From DummyTable1
GO
 
EmpID	   
1	   
2	   
3	   
4	   
5	   
6	   
7	   
8	   
9	   
10	 

Data has been displayed in a proper order and there is no "order by" clause in the select statement. There is no need for "order by" clause in the select statement. Why? SQL Server will store the data in order for a Clustered Index table. We have asked an EmpID data in the select statement. SQL Server has already arranged the Empid data in a proper order, which has got a clustered index. We need not explicit specify an "order by" clause in the select statement.

So, "order by" clause is needed for Non-index column in the select statement and Non-index column along with index-column in the select statement. Let us get in to some practical knowledge.

Now, let's view the non-index column contests of the table by executing the following command in Query Analyzer.

Select EmpCode, EmpName From DummyTable1
GO
 
EmpCode	EmpName	   
001 	aaaaaaaaaaaaaaaaaaaa	   
002 	bbbbbbbbbbbbbbbbbbbb	   
003 	cccccccccccccccccccc	   
004 	dddddddddddddddddddd	   
005 	eeeeeeeeeeeeeeeeeeee	   
006 	Ffffffffffffffffffff	   
007 	gggggggggggggggggggg	   
008 	hhhhhhhhhhhhhhhhhhhh	   
009 	Iiiiiiiiiiiiiiiiiiii	   
010 	Jjjjjjjjjjjjjjjjjjjj	 

The data is arranged in order. Clustered index in a table will arrange the whole data in order. "Order by" clause is not required in a select statement or in a table has got a clustered index.

Let's view the non-index column contests of the table by executing the following command in Query Analyzer. Though, we club both index column and non-index column in a select statement.

Select EmpID, EmpCode, EmpName From DummyTable1
GO
 
EmpID	EmpCode	EmpName	   
1	001	aaaaaaaaaaaaaaaaaaaa	   
2	002	bbbbbbbbbbbbbbbbbbbb	   
3	003	cccccccccccccccccccc	   
4	004	dddddddddddddddddddd	   
5	005	eeeeeeeeeeeeeeeeeeee	   
6	006	ffffffffffffffffffff	   
7	007	gggggggggggggggggggg	   
8	008	hhhhhhhhhhhhhhhhhhhh	   
9	009	iiiiiiiiiiiiiiiiiiii	   
10	010	jjjjjjjjjjjjjjjjjjjj	 

MS-SQL Server displays the data in order.

All the above examples, we will disclosed "when to use an order by clause" and "when not to use an order by clause" in a select statement for a clustered index in a table in two possible ways.

  1. We do not require an "order by" clause in a table, which has got a clustered index.
  2. We do not require an "order by" clause for a non-index column in a select statement and both index and non-index column in a select statement.

Editor's Note: The advice given in this article is not necessarily endorsed by the editor. The only way to guarantee the order of rows in a result set is with the ORDER BY clause.

However, in the interest of allowing disparate opinions, we have published this article. Please be sure you read the discussion for this article to see dissenting opinions.

Total article views: 11291 | Views in the last 30 days: 3
 
Related Articles
FORUM

Columns order in the index

Columns order in the index

ARTICLE

Column Order in an Index

This short article shows the importance of order of columns in an index.

ARTICLE

The Cost of Function Use In A Where Clause

Discusses Index Selection impact when functions are wrapped around WHERE clause filtering columns

FORUM

Order by clause in openrowset

Order by clause in openrowset

BLOG

SQL Server – Custom sorting in ORDER BY clause

ORDER BY clause can be used to sort the results returned by SELECT statement in SQL Server. It order...

Tags
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones