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 EmpIDEmpCodeEmpName 4004dddddddddddddddddddd 6006ffffffffffffffffffff 1001aaaaaaaaaaaaaaaaaaaa 3003cccccccccccccccccccc 10010jjjjjjjjjjjjjjjjjjjj 2002bbbbbbbbbbbbbbbbbbbb 5005eeeeeeeeeeeeeeeeeeee 8008hhhhhhhhhhhhhhhhhhhh 9009iiiiiiiiiiiiiiiiiiii 7007gggggggggggggggggggg
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 EmpIDEmpCodeEmpName 1001aaaaaaaaaaaaaaaaaaaa 2002bbbbbbbbbbbbbbbbbbbb 3003cccccccccccccccccccc 4004dddddddddddddddddddd 5005eeeeeeeeeeeeeeeeeeee 6006ffffffffffffffffffff 7007gggggggggggggggggggg 8008hhhhhhhhhhhhhhhhhhhh 9009iiiiiiiiiiiiiiiiiiii 10010jjjjjjjjjjjjjjjjjjjj
A few questions have arisen in my mind:
- Why SQL server has not arranged the data in order?
- Why can't SQL Server decide itself EmpID = 1 has to come first and EmpID = 2 has to come second and so on.
- When will SQL Server order the data in a table?
- Is SQL Server expecting something from a user to arrange the data?
- If so, what is that?
For all these questions, MS-SQL server has arranges the data in two possible ways.
- Clustered index in a table
- 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 EmpCodeEmpName 004dddddddddddddddddddd 006Ffffffffffffffffffff 001aaaaaaaaaaaaaaaaaaaa 003cccccccccccccccccccc 010Jjjjjjjjjjjjjjjjjjjj 002bbbbbbbbbbbbbbbbbbbb 005eeeeeeeeeeeeeeeeeeee 008hhhhhhhhhhhhhhhhhhhh 009Iiiiiiiiiiiiiiiiiiii 007gggggggggggggggggggg
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 EmpCodeEmpName 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 EmpCodeEmpName 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 EmpIDEmpCodeEmpName 4004dddddddddddddddddddd 6006ffffffffffffffffffff 1001aaaaaaaaaaaaaaaaaaaa 3003cccccccccccccccccccc 10010jjjjjjjjjjjjjjjjjjjj 2002bbbbbbbbbbbbbbbbbbbb 5005eeeeeeeeeeeeeeeeeeee 8008hhhhhhhhhhhhhhhhhhhh 9009iiiiiiiiiiiiiiiiiiii 7007gggggggggggggggggggg
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 EmpIDEmpCodeEmpName 1001aaaaaaaaaaaaaaaaaaaa 2002bbbbbbbbbbbbbbbbbbbb 3003cccccccccccccccccccc 4004dddddddddddddddddddd 5005eeeeeeeeeeeeeeeeeeee 6006Ffffffffffffffffffff 7007gggggggggggggggggggg 8008hhhhhhhhhhhhhhhhhhhh 9009Iiiiiiiiiiiiiiiiiiii 10010Jjjjjjjjjjjjjjjjjjjj
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.
- We do not require an "order by" clause for only an index column in a select statement.
- 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 EmpIDEmpCodeEmpName 4 004dddddddddddddddddddd 6 006ffffffffffffffffffff 1 001aaaaaaaaaaaaaaaaaaaa 3 003cccccccccccccccccccc 10 010jjjjjjjjjjjjjjjjjjjj 2 002bbbbbbbbbbbbbbbbbbbb 5 005eeeeeeeeeeeeeeeeeeee 8 008hhhhhhhhhhhhhhhhhhhh 9 009iiiiiiiiiiiiiiiiiiii 7 007gggggggggggggggggggg
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 EmpCodeEmpName 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 EmpIDEmpCodeEmpName 1001aaaaaaaaaaaaaaaaaaaa 2002bbbbbbbbbbbbbbbbbbbb 3003cccccccccccccccccccc 4004dddddddddddddddddddd 5005eeeeeeeeeeeeeeeeeeee 6006ffffffffffffffffffff 7007gggggggggggggggggggg 8008hhhhhhhhhhhhhhhhhhhh 9009iiiiiiiiiiiiiiiiiiii 10010jjjjjjjjjjjjjjjjjjjj
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.
- We do not require an "order by" clause in a table, which has got a clustered index.
- 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.
