While a number of people have suggested the ORDER BY NEWID ( ), both
Umachandar Jayachandran and myself have argued against it. You are depending
upon undocumented behavior of the NEWID ( ) function. Depending upon
undocumented behavior is never a good thing.
A few comments ...
1. Since a table is by definition an unordered set of rows, adding the ORDER
BY NEWID ( ) to the INSERT statement is just a waste of CPU cycles.
2. If you want to return information in a random sequence, then try
order by rand(1000.0 * AdID * datepart(ms, current_timestamp))
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.
> This SP works on one server but not another SQL Server. It is used to pull
> random row based on a UniqueIdentifier Column called AdID
> Both are SQL Server 7.0, both base tables are exactly the same. I have
> tried dropping and recreating the table on the new server.
> Here is the code.
> Create proc GetAds
> set nocount on
> create table #ads (AdID uniqueidentifier)
> insert #ads select AdID from internalads order by newid()
> set rowcount 0
> update InternalAds SET Shows = Shows + 1 Where AdID in (select AdID from
> select FilePath, AdLink, AltText,AdID, adWidth, AdHeight from InternalAds
> where AdID in (select AdID from #ads) order by newid()
> drop table #ads
> set nocount off
> Your help is greatly appreciated and most urgent