| Page Item | Value |
| Title | SQL Help: Generating Randomly Ordered SQL Server Result Sets |
| Description | This SQL Help article describes how to create randomly ordered SQL Server result sets by using the NewID() Transact SQL (TSQL) function |
| Keywords | tutorial, example, sql server, tsql, transact sql, sql, random, randomly, newid |
| Robots Meta Tag | |
| Page Content | HOME | ABOUT ME | BIOTECHNOLOGY | ARTICLES | TOOLS | GALLERY | CONTACT Search: Go DEVELOPER TOOLS ASP Doc Tool ASP.NET Doc Tool SQL Doc Tool Index Server Companion The Website Utility TECHNICAL ARTICLES ASP ASP.NET JavaScript Transact SQL PHOTO GALLERIES Canon EOS 300D Samples Red Arrows 2004 Living Coasts Web Page Backgrounds More Galleries... NEW STUFF SQL Color Coder Canon EOS 300D Samples The Website Utility Search Engine Optimisation Build an ASP Search Engine My Tropical Fishtank Savings Other New Stuff... POPULAR STUFF Regular Expressions ASP Documentation Tool Index Server ASP JavaScript Ad Rotator LINKS Business Website ASPAlliance Articles
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 The SQL Documentation Tool builds technical documentation for Microsoft SQL Server databases. A free trial version is available for download. The ASP Documentation Tool can automatically document your ASP (VBScript and JScript) websites and SQL Server/Microsoft Access databases. An ASP.NET Documentation Tool is also available! Site Map All content is © 1995 - 2006 Brett Burridge |
| Image Alt Tags | Brettb.Com Microsoft Certified Professional |
| Internal Links | http://www.brettb.com/Default.asp (3 links in this page) [ Robot View of this URL ] http://www.brettb.com/ASPDocumentationTool.asp (2 links in this page) [ Robot View of this URL ] http://www.brettb.com/technicalwriting.asp (2 links in this page) [ Robot View of this URL ] http://www.brettb.com/DeveloperTools.asp (2 links in this page) [ Robot View of this URL ] http://www.brettb.com/SQL_Help.asp (2 links in this page) [ Robot View of this URL ] http://www.brettb.com/TheWebsiteUtility.asp (2 links in this page) [ Robot View of this URL ] http://www.brettb.com/JavaScriptArticles.asp [ Robot View of this URL ] http://www.brettb.com/Website_Search_Engine_Optimisation.asp [ Robot View of this URL ] http://www.brettb.com/SearchResults.asp [ Robot View of this URL ] http://www.brettb.com/js_banner_ad_rotator.asp [ Robot View of this URL ] http://www.brettb.com/web.asp [ Robot View of this URL ] http://www.brettb.com/CanonEOS300D_Gallery1.asp [ Robot View of this URL ] http://www.brettb.com/BuildingAnASPSearchEngine.asp [ Robot View of this URL ] http://www.brettb.com/backgrounds.asp [ Robot View of this URL ] http://www.brettb.com/VBScriptRegularExpressions.asp [ Robot View of this URL ] http://www.brettb.com/toc.asp [ Robot View of this URL ] http://www.brettb.com/ASP.NETArticles.asp [ Robot View of this URL ] http://www.brettb.com/Investments_ISAs.asp [ Robot View of this URL ] http://www.brettb.com/TransactSQLColorCoder.asp [ Robot View of this URL ] http://www.brettb.com/MyTropicalFishtank.asp [ Robot View of this URL ] http://www.brettb.com/CanonEOS300D_Gallery3.asp [ Robot View of this URL ] http://www.brettb.com/ASPWatchArticles.asp [ Robot View of this URL ] http://www.brettb.com/Red_Arrows_2004.asp [ Robot View of this URL ] http://www.brettb.com/Living_Coasts_Photos.asp [ Robot View of this URL ] http://www.brettb.com/Gallery.asp [ Robot View of this URL ] http://www.brettb.com/contact.asp [ Robot View of this URL ] http://www.brettb.com/Biotechnology.asp [ Robot View of this URL ] http://www.brettb.com/SearchingIndexServerWithASP.asp [ Robot View of this URL ] http://www.brettb.com/ASPNetDocumentationTool.asp [ Robot View of this URL ] http://www.brettb.com/gallery.asp [ Robot View of this URL ] http://www.brettb.com/what's_new.asp [ Robot View of this URL ] http://www.brettb.com/IndexServerCompanion.asp [ Robot View of this URL ] |
Report generated by The Website Utility 2.8