Get different records or random order at each time when you fetch record by newId() TIP#33

Hello friends,

Sometimes your project require whenever a page load or user search then each time you need to show a random order or we can say different records or order.

To achieve this we can use following command

SELECT * FROM yourtableName order by NewID()

in below snap I am fetching person table of Adventureworks database

NewId_For_Order_By

 

So each time when I press F5 I get different records or order.

The only thing which need to remember here NEWID() make your fetch slow on large collection so the best idea is use it with selected record set.

I hope this will help you somewhere enjoy.

Thanks

Rajat Jaiswal

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