In the following code snippet we show you how to randomize the result set on the SQL Server. In many cases you may wish to randomly return a set records. For example, in one application we wanted to return five records, but have them returned in a random pattern for a banner ad application on a Website.
Since the database could potentially contain many records than just five, we needed a way to even out the impressions that would be displayed on the site.
CREATE PROCEDURE dbo.GetRandomAds
-- outputs random top ads
(
)
AS
SELECT Top 5 Ads.* FROM Ads ORDER By NewId()
RETURN
GO
The random sort is being provided by adding the NewId() directive to the end of the TSQL statement. This enables us to pull five records out a time from the Ads table in a random order.
For small applications like this banner ad manager where the amount of records are not extremely high this solution would be viable.
