Let’s say we have a SQL table and want to retrieve 10 rows randomly - how would you do that? Although I have been working with SQL for x years, I have never encountered that problem. The solution however is quite “simple” (at least if you don’t be picky how we define “randomness” and if you try this on millions of rows):
ORDER BY NEWID()
The most boring way is to use the ORDER BY NEWID() clause:
SELECT TOP 10 FROM Products ORDER BY NEWID()
This works, but if you do that on “large” datasets you might hit performance problems (e.g. more on that here)
TABLESAMPE
The SQL Server implements the Tablesample clause which was new to me. It seems to perform much bettern then the ORDER BY NEWID() clause, but behaves a bit weird. With this clause you can specify the “sample” from a table. The size of the sample can be specified as PERCENT or ROWS (which are then converted to percent internally).
Syntax:
SELECT TOP 10 FROM Products TABLESAMPLE (25 Percent)
SELECT TOP 10 FROM Products TABLESAMPLE (100 ROWS)
The weird part is that the given number might not match the number of rows of your result. You might got more or less results and if our tablesample is too small you might even got nothing in return. There are some clever ways to work around this (e.g. using the TOP 100 statement with a much larger tablesample clause to get a guaranteed result set), but it feels “strange”.
If you hit limitations with the first solution you might want to read more on this blog or in the Microsoft Docs.
HOW TO RECOVER STOLEN BITCOIN ? follow this whatsapp link: creditosantajusta@gmail.com OR Whatsapp +1(659) 210-0433 I lost my bitcoin to fake blockchain impostors on Facebook, they contacted me as blockchain official support and I fell stupidly for their mischievous act, this made them gain access into my blockchain wallet account, whereby 7.0938 btc was stolen from my wallet in total .I was almost in a comma and dumbfounded because this was all my savings I banked up on , waiting for bitcoin rate to improve . Then my niece recommended me to an expert, I researched online and found the recovery expert , with the contact address- creditosantajusta@gmail.com OR Whatsapp +1(659) 210-0433 .I wrote directly to the specialist explaining my loss. Hence, he helped me recover my bitcoin just after 2 days. He helped me launch the recovery program , and the culprits were identified as well , all thanks to his expertise . I hope I have been able to help someone as well . Reach out to the recovery specialist to recover your lost funds from any form of online scam, he is dependable and trustworthy
Robert Mühsig ist Webentwickler bei der T-Systems Multimedia Solutions GmbH in Dresden und wurde von Microsoft mit dem ASP.NET MVP Award ausgezeichnet.