Brettb.Com
  HOME | ABOUT ME | BIOTECHNOLOGY | ARTICLES | GALLERY | CONTACT
Search: Go
TECHNICAL ARTICLES
 ASP
 ASP.NET
 JavaScript
 Transact SQL
 Other Articles
 Software Reviews

PHOTO GALLERIES
 Canon EOS 300D Samples
 Akihabara Maids!
 More Galleries...

TRAVEL LOG
 2009: China
 2008: Tokyo
 2007: Tokyo
 2006: Hong Kong
 2005: New York City

MORE STUFF
 Search Engine Optimisation
 Build an ASP Search Engine
 My Tropical Fishtank
 Autoglass
 SQL Month Name
 SQL Get Date Today
 SQL Year Month
 Other New Stuff...

POPULAR STUFF
 Regular Expressions
 Index Server & ASP
 JavaScript Ad Rotator

Home > Articles > Transact SQL Programming Articles

Generating Randomly Ordered SQL Server Result Sets

This article describes a straightforward method of generating randomly sorted result sets in SQL Server. This procedure has a number of potential uses, such as displaying a few randomly chosen news article headlines on a website, or it could be used to randomly select a few advertisements while ensuring the same advert isn't always displayed at the top of the advertising space.

Although it is possible to introduce randomness in SQL Server using time functions, in practice this does not work (especially in stored procedures) because of the speed of execution of the SQL statements [hence many or all of the rows could be returned in exactly the same instant of time]. A far better alternative is, therefore, to use the NewID function to create a unique identifier for each row returned. This returns GUID-like identifiers such as AF53DB47-766F-44B7-82EC-7459E353B3F3.The results set can then be ordered by this column.

The use of the NewID function is shown in this example stored procedure shown below:

CREATE PROCEDURE sp_GetAdverts
(
@MaxNumberOfAdverts int
)
AS

set rowcount @MaxNumberOfAdverts

select top 100 t_Adverts.AdvertID,
    t_Adverts.TargetURL,
    t_Adverts.AltTag,

    newID() as 'RandomColumn'

from t_Adverts
where getdate() between t_Adverts.AdvertStartDate and t_Adverts.AdvertEndDate

order by newID()
GO

Useful Links

  • Reverse the order of results with the SQL Order Desc Ordering Statement.

  Site Map | Privacy Policy

All content is 1995 - 2012