In one of the website I have developed, we feature random products displayed on a webpart. This can be done several ways either of the code behind or via t-sql but I think the simplest way to do it is via t-sql with the help of Guid columns.
Given GUID is random, adding a GUID column to the original resultset and use it as the sort column surely yields a random result. Sample script below:
1 -->
2 IF EXISTS(SELECT 1 FROM sys.tables WHERE Name = 'Product')
3 DROP TABLE Product;
4 GO
5
6 --create the test product table
7 CREATE TABLE Product (
8 IdProduct int PRIMARY KEY IDENTITY(1001,1),
9 ProductName nvarchar(255),
10 Price decimal(18,2),
11 AverageRating int,
12 ItemViewed int,
13 ItemSold int,
14 SortOrder int,
15 DateCreated smalldatetime,
16 LastUpdated smalldatetime)
17 GO
18
19 --initialize table with test data
20 INSERT INTO Product VALUES('Item 1', 10.00, 1, 99, 23, 1000, GETDATE(), GETDATE());
21 INSERT INTO Product VALUES('Item 2', 21.00, 2, 97, 56, 1002, GETDATE(), GETDATE());
22 INSERT INTO Product VALUES('Item 3', 11.00, 3, 95, 23, 1001, GETDATE(), GETDATE());
23 INSERT INTO Product VALUES('Item 4', 22.00, 4, 93, 65, 1000, GETDATE(), GETDATE());
24 INSERT INTO Product VALUES('Item 5', 12.00, 5, 91, 54, 1000, GETDATE(), GETDATE());
25 INSERT INTO Product VALUES('Item 6', 23.00, 6, 89, 12, 1006, GETDATE(), GETDATE());
26 INSERT INTO Product VALUES('Item 7', 13.00, 8, 87, 89, 1050, GETDATE(), GETDATE());
27 INSERT INTO Product VALUES('Item 8', 24.00, 9, 85, 21, 1021, GETDATE(), GETDATE());
28 INSERT INTO Product VALUES('Item 9', 14.00, 1, 98, 67, 1020, GETDATE(), GETDATE());
29 INSERT INTO Product VALUES('Item 10', 25.00, 3, 99, 78, 1001, GETDATE(), GETDATE());
30
31
32 --create the stored procedures
33 IF EXISTS ( SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_query_all_featured_product]') and type IN (N'P', N'PC'))
34 DROP PROCEDURE [dbo].[usp_query_all_featured_product];
35 GO
36
37 /*------------------------------------------------------------------------------
38 // <procedureInformation>
39 // procedureName : [dbo].[usp_query_all_featured_product]
40 // description : get random products from a table
41 // createdBy : rdagumampan
42 // createdWhen : 06-20-2007 02:08 AM
43 //
44 // modification history
45 // date Modified remarks
46 // <procedureInformation>
47 */------------------------------------------------------------------------------
48
49 CREATE PROC [dbo].[usp_query_all_featured_product]
50 @pageIndex [int] = 1, -- current page
51 @pageSize [int] = 999, -- number of rows per page
52 @totalRows [int] OUTPUT -- total number of rows
53 AS
54 BEGIN
55
56 ----xx begin unit testing paramters
57 -- DECLARE @returnTop int;
58 -- SELECT @returnTop = 5;
59 ----xx end unit testing parameters
60 --perform attempt to retrieve all records
61 WITH result AS (
62 SELECT
63 ROW_NUMBER() OVER (ORDER BY NewId() ASC) AS RowNumber,
64 prod.IdProduct,
65 prod.ProductName,
66 prod.Price,
67 prod.AverageRating,
68 prod.ItemViewed,
69 prod.ItemSold,
70 prod.SortOrder,
71 prod.DateCreated,
72 prod.LastUpdated
73 FROM Product prod )
74 SELECT
75 prod.IdProduct,
76 prod.ProductName,
77 prod.Price,
78 prod.AverageRating,
79 prod.ItemViewed,
80 prod.ItemSold,
81 prod.SortOrder,
82 prod.DateCreated,
83 prod.LastUpdated
84 FROM result prod
85 WHERE prod.RowNumber BETWEEN
86 (@pageIndex - 1) * @pageSize + 1 AND @pageIndex * @pageSize
87
88 SELECT @totalRows = COUNT(IdProduct) FROM Product;
89 END
90
91 GO
92
93 --execute stored product for testing
94 DECLARE @totalRows INT;
95 EXEC [usp_query_all_featured_product] @pageIndex = 1, @pageSize = 5, @totalRows = @totalRows ;
DISCLAIMER: Use this script at your own risk. Never execute this script on a production database.