dcsimg
December 5, 2016
Hot Topics:

Enhance Your Web Apps with T-SQL Tricks

  • September 26, 2007
  • By Andrew Bonslater
  • Send Email »
  • More Articles »

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.

Summary

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 abonslater@crowechizek.com.

Article edited by Chad Campbell





Page 2 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Enterprise Development Update

Don't miss an article. Subscribe to our newsletter below.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date
Rocket Fuel