ORDER BY Issue WITH Temporary table in SQL SERVER ?

Dear Friends,

Yesterday was a very good learning day for me. One of my colleagues stuck in a very interesting issue.
He was fetching records from database with an ORDER BY clause on a VARCHAR field.
Couple of records of his table containing “-“hyphen symbol in the field on which he applied Sorting.

So after facing that problem I am sharing how to use collation in ORDER BY CLAUSE with your select statement.

Let’s consider below example

DECLARE @tblSubjects TABLE(
Subjects VARCHAR(100)
)

INSERT INTO @tblSubjects VALUES (‘HINDIBALBHARTI’)
INSERT INTO @tblSubjects VALUES (‘HINDI-BALBHARTI’)

SELECT Subjects AS Default_Collation_Sort_Order FROM @tblSubjects ORDER BY Subjects ;
SELECT Subjects AS Explicit_Changed_Collation_Sort_Order FROM @tblSubjects ORDER BY Subjects COLLATE Latin1_General_CI_AI ;

When you run above statement you will get different order in result set.

ORDER BY ISSUE in temp Table
Generally this problem occurs when you use temp tables and your sort order and it is not as per your expectation.

So if you found this type of ORDER BY issue than you can explicitly use COLLATION

Enjoy learning.

Your host
Rajat Jaiswal

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s