In my previous post I discussed about the how Clustered Index’s Data-Pages and Rows are allocated in memory (Disk). I tried to prove that that Clustered Indexes do not guarantee Physical Ordering of Rows. But instead they are Logically Ordered and Sorted.
As they are Logically Sorted and when you query a table without an “ORDER BY” clause you get Sorted Rows, but this is not what will happen always. You can also get rows in Unsorted Order, so to get Sorted rows always apply an “ORDER BY” clause. Here in this post we will see under what circumstances a table will not return Sorted rows:
–> Let’s create a simple table with a Clustered Index on it and add some records:
-- Create table with 2 columns, first beign a PK and an IDENTITY column: CREATE TABLE test2 ( i INT IDENTITY(1,1) PRIMARY KEY NOT NULL, j INT ) -- Let's insert some records in random order on second column: INSERT INTO test2 (j) SELECT 500 UNION ALL SELECT 300 UNION ALL SELECT 900 UNION ALL SELECT 100 UNION ALL SELECT 600 UNION ALL SELECT 200 -- Now we will query the table without using ORDER BY clause: SELECT * FROM test2
–> Following is the Output of the first SELECT query above:
You get sorted rows, as the Execution plan shows that Clustered Index was used to fetch the records.
–> Now, what if this table also has a Non Clustered Index, let’s see:
-- We will create a Non Clustered Index on the same table on second column: CREATE INDEX NCI_test ON test2 (j) -- Again query the table without using ORDER BY clause: SELECT * FROM test2
–> Following is the Output of the second SELECT query above:
This time the query returned rows in un-ordered fashion. As you can see in the Execution plan Query optimizer preferred to do a Non Clustered Scan. Thus the records were returned in un-ordered fashion.
–> Now, If you add an ORDER BY Clause to your SELECT Query how does it return rows:
-- Added an ORDER BY Clause: SELECT * FROM test2 ORDER BY i
–> Following is the Output of the third SELECT query above:
After adding an “ORDER BY” clause the query optimizer preferd to use the Clustered Index and returns rows in Ordered fashion again.
-- Final Cleanup DROP TABLE test2
So, it is necessary to provide an “ORDER BY” clause to your Queries when you expect to get sorted results, even if the table has Clustered Index on it.
Many people come to me as a point of SQL reference and ask, “How can we sort the numbers if they are stored in a VARCHAR column in a table?”.
This morning one guy approached me and asked the same question again.
So this as a generic question I thought to post it in my blog.
Let’s check this by a simple example:
-- Create table and insert some test data: DECLARE @StringNbrs TABLE (nbr VARCHAR(10)) INSERT INTO @StringNbrs SELECT C FROM (VALUES ('681'), ('21'), ('9'), ('1'), ('401'), ('158'), ('1000')) AS T(C) -- Using simple ORDER BY clause: SELECT nbr FROM @StringNbrs ORDER BY nbr
Output:- nbr 1 1000 158 21 401 681 9
The above query with ORDER BY clause won’t sort the nbr column as required.
-- ===================================================================== -- Let's see how can we sort the nbr column with 2 methods given below:- -- ===================================================================== -- Method #1: Using CAST/CONVERT with ORDER BY clause: SELECT nbr FROM @StringNbrs ORDER BY CAST(nbr as INT) -- Method #2: Another approach by using REPLICATE function in ORDER BY clause: SELECT nbr FROM @StringNbrs ORDER BY REPLICATE('0',4-LEN(nbr)) + nbr
Sorted Output:- nbr 1 9 21 158 401 681 1000
One of my previous post mentions about using CASE construct with ORDER BY clause to tweak the ordering of the result set. That example was a simple one, this post provides more insight on this tip.
Most of us must have experienced this problem at least once when you want to order some of your records in a specific way. Or your boss or client asks this weird request to see some specific records on top. And you are puzzelled, how to do this.
The following example shows how we can get this:
USE [AdventureWorks] GO --Simple sort by ORDER BY SELECT FirstName, LastName FROM Person.Contact ORDER BY FirstName -- ORDER BY with CASE SELECT FirstName, LastName FROM Person.Contact ORDER BY CASE LastName WHEN 'Wright' THEN '0' WHEN 'Jenkins' THEN '1' WHEN 'Torres' THEN '2' WHEN 'Sanchez' THEN '3' ELSE LastName END -- Multiple column sort with ORDER BY with CASE SELECT FirstName, LastName FROM Person.Contact ORDER BY CASE LastName WHEN 'Wright' THEN '0' WHEN 'Jenkins' THEN '1' WHEN 'Torres' THEN '2' WHEN 'Sanchez' THEN '3' ELSE LastName END, FirstName
ORDER BY clause orders the result set of a SQL query for a particular or specific set of columns provided in ORDER BY clause.
Ever wonder to tweak that order. Lets take a scenario when you fill out an online form. When you reach at Country dropdown you get lots of County names to select. But if your Country is not listed there, there an “Other” option and that too at the bottom. Why it is not ordered with other Country names alphabetically? How come it reached that bottom? Lets see how can we get that resultset by using ORDER BY CASE.
-- Create a Country table CREATE TABLE Country (ID INT IDENTITY(1,1), cname VARCHAR(50)) -- Insert dummy data INSERT INTO Country SELECT 'Afghanistan' UNION SELECT 'Australia' UNION SELECT 'France' UNION SELECT 'Oman' UNION SELECT 'United States' UNION SELECT 'Singapore' UNION SELECT 'Other' -- SELECT with plain ORDER BY SELECT * FROM Country ORDER BY cname
ID cname 1 Afghanistan 2 Australia 3 France 4 Oman 5 Other 6 Singapore 7 United States Here you will see the "Other" option just below "Oman". It should not be here, because it is not a Country name and the user should should see this option at the bottom.
-- SELECT with ORDER BY CASE SELECT * FROM Country ORDER BY CASE WHEN cname='other' THEN 1 ELSE 0 END
ID cname 1 Afghanistan 2 Australia 3 France 4 Oman 6 Singapore 7 United States 5 Other Applying ORDER BY clause with CASE tweaks the "Other" option and places it at the bottom. Other than Country this logic can be applied for other things like, University Name, City, State, etc.
-- Final Cleanup DROP TABLE Country