Enhance Your Web Apps with T-SQL Tricks
Your first step is to create your SELECT statement with a column for the Random ID. The Random ID column will be used to randomize the order that the rows will be retrieved in your next step.
SELECT CAST(CAST(NEWID() AS BINARY(4)) AS INT) AS Random, Id FROM Customer WHERE Active = 1 ORDER BY DateCreated DESC
The query above returns a set of results with a column that contains a random integer value for each row. To make this result set get returned in a random order, you need to wrap this query with an outer query that will order the results by the Random column. The inner query can be used just like a table, so you can join it to other tables in your query to return additional information.
SELECT TOP 8 Subquery.Id, Subquery.FirstName, Subquery.LastName, Address.City FROM ( SELECT CAST(CAST(NEWID() AS BINARY (4)) AS INT) AS Random, Id, FirstName, LastName FROM Customer WHERE Active = 1 ORDER BY DateCreated DESC ) AS SubQuery LEFT JOIN Address.Id ORDER BY Random
That's all it takes to make your T-SQL query randomized. Every time you call this query, it will return a new set of results.
This article demonstrates ways of using some of the new T-SQL features to increase performance and enhance your .NET web applications. There are still many other uses for these features that allow .NET and T-SQL to work harmoniously. I recommend that you take full advantage of the new T-SQL functionality to simplify and enhance your project.
About the Author
|Andrew Bonslater (MCAD, MCSD) is a solutions developer for mid to large-sized organizations. He is a thought leader with Crowe Chizek in Indianapolis, Indiana. Andrew specializes in Web-Based solutions, Reach him at firstname.lastname@example.org.|
Article edited by Chad Campbell
Page 2 of 2