Order By for random rows? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Order By for random rows?

Author

Topic

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-11 : 21:27:37

I've read that ORDER BY should only be for presentation purposes, so isn't the following error-prone? How would I test it? (This is part of a procedure).


  EXEC
  ('if exists (select * from dbo.sysobjects where id = object_id(N''random_'+@temptablename+''') and OBJECTPROPERTY(id, N''IsUserTable'') = 1)
  drop table random_'+@temptablename+'')

  set @sql ='select top '+cast(@recordcount as varchar)+' * into random_'+@temptablename+' from '+@temptablename+
  ' order by rand(cast(idn as integer)*(datepart(ss,getdate()) + 1))'

  EXEC (@sql)

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2010-08-12 : 00:37:26

It looks like it is meant to randomly select a certain quantity of rows, but this is a better way to do it:

order by newid()

CODO ERGO SUM

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-08-12 : 03:29:18

You should be careful with order by newid() though...if you have a large recordset it will have a severe performance impact. Try to do as much filtering as possible before doing the order by and make use of derived tables to limit the impact:

-- This is far better:
SELECT TOP 10 * FROM (SELECT TOP 100 * FROM table WHERE DateColumn > GETDATE()) AS a ORDER BY NewID()

-- than this:
SELECT TOP 10 * FROM table WHERE DateColumn > GETDATE() ORDER BY NewID()

I realize that these two queries don't do the same thing (the second is "more random" than the first) but the first one is far better performing if your table/recordset is large.

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

dmilam
Posting Yak Master

185 Posts

Posted - 2010-08-12 : 18:31:11

Thanks; NewID() should be fine; the dataset is less than 10,000 rows

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-08-12 : 23:24:47

Be advised that ORDER BY NEWID() (or any non-columnar expression) will be deprecated in a future version of SQL Server.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources