InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 11:01:38 GMT20http://www.sqlservercentral.com/Forums/Topic1035673-392-1.aspx[quote][b]The Dixie Flatline (12/17/2010)[/b][hr]Note to Jeff Moden: It may not be "Nasty Fast" but it's "Wicked Quick" [/quote]Heh... too funny, Bob. :-DFri, 17 Dec 2010 18:57:23 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1035673-392-1.aspxHere is an example of pulling 20 random rows from a million row table.Note to Jeff Moden: It may not be "Nasty Fast" but it's "Wicked Quick" :-DHad to make this an attachment. For some reason, SSC is blocking my posts.Fri, 17 Dec 2010 11:53:16 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic1035673-392-1.aspxUsing ORDER BY NEWID() with huge files is slow, because of the sort time.Generating TOP (X) WHERE (random number test) is fast, but tends to skew towards the front of the table, whatever that may be.If you have an indexed IDENTITY (ID) column in your primary table, I would generate a temp table of 10-20 distinct random numbers between min(ID) and max(ID) and join it to the primary.Thu, 16 Dec 2010 17:10:44 GMTThe Dixie Flatlinehttp://www.sqlservercentral.com/Forums/Topic1035673-392-1.aspxHere is another method I've seen/used that is pretty similar to the ORDER BY NEWID(), but much faster:[code="sql"]SELECT TOP 10 * FROM MyTableWHERE 0.01 >= CAST(CHECKSUM(NEWID(), SomeColumnName) & 0x7fffffff AS float) / CAST (0x7fffffff AS int) [/code]NOTE: Repalce "SomeColumnName" with some columns or set of columns from your table, PKs work well.Thu, 16 Dec 2010 16:17:04 GMTLamprey13http://www.sqlservercentral.com/Forums/Topic1035673-392-1.aspxWhat about the TABLESAMPLE clause?EG.[code="sql"] SELECT * FROM tableX TABLESAMPLE (10 percent)[/code]More info [url=http://www.mssqltips.com/tip.asp?tip=1308]here[/url]Thu, 16 Dec 2010 03:42:58 GMTgrahamchttp://www.sqlservercentral.com/Forums/Topic1035673-392-1.aspx[quote][b]Sachin Nandanwar (12/16/2010)[/b][hr]Use Top clause without order by clause.Select top 10 columnname from yourtable.[/quote]This would mostly give the same result again and again. Use NEWID() in the ORDER BY Clause[code="sql"]SELECT TOP 10 * FROM YourTable ORDER BY NEWID()[/code]Thu, 16 Dec 2010 02:05:10 GMTKingston Dhasianhttp://www.sqlservercentral.com/Forums/Topic1035673-392-1.aspxUse Top clause without order by clause.Select top 10 columnname from yourtable.Thu, 16 Dec 2010 01:38:29 GMTSachin Nandanwarhttp://www.sqlservercentral.com/Forums/Topic1035673-392-1.aspxHow to select random (10 or 20) records from table?Thu, 16 Dec 2010 00:42:47 GMTranadip.dey