ORDER BY | SQL with Manoj

Clustered Index will not always guarantee Sorted Rows

June 10, 2013 3 comments

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

–> Output:

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.

Categories: Indexes, Misconception Tags: Clustered Index, Clustered Index sorting, Non Clustered Index, ORDER BY

T-SQL Query for SORTing Numbers stored as String (VARCHAR)

December 6, 2011 2 comments

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

Categories: SQL Tips Tags: ORDER BY, REPLICATE

more on – ORDER BY with CASE

April 9, 2011 1 comment

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

Categories: SQL Tips Tags: ORDER BY, ORDER BY CASE

Using ORDER BY with CASE in SQL Queries

February 22, 2011 2 comments

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.