|
|
Posted by Erland Sommarskog on 08/17/07 22:04
WhytheQ (WhytheQ@gmail.com) writes:
> I have been using NewID but was advised to use TABLESAMPLE as it would
> be more efficient - unfortunately I can get it to work properly.
>
> This comes up with nothing:
> '=====================
> USE myDatabase
> SELECT *
> FROM myTable
> TABLESAMPLE (1 ROWS)
> '====================
>
> ...whereas this works:
> '=====================
> USE myDatabase
> SELECT *
> FROM myTable
> TABLESAMPLE (1 PERCENT)
> '====================
I saw the suggestion to use TABLESAMPLE, and I was not very happy with
it, but I did not reply to at the time. TABLESAMPLE is fairly
approxamite in its nature. I ran
SELECT * FROM Orders TABLESAMPLE (1 ROWS)
SELECT * FROM Orders TABLESAMPLE (10 ROWS)
SELECT * FROM Orders TABLESAMPLE (100 ROWS)
in an inflated version of Northwind with 344000 orders. The first two
selects returned no rows at all, the last returned 86 rows. The first
time. The second time it returned no rows, and the last time it
return 132 rows. Furthermore, the sample was not entirely random,
but I got a couple of sequences of order ids. Presumably because
TABLESAMPLE works on page level.
Possibly you could combine newid() and TABLESAMPLE:
SELECT TOP 1 * FROM (
select * from Orders TABLESAMPLE (100 ROWS)) AS d
ORDER BY newid()
You would need to make your sample size big enough so that you are
sure that it retrieves a row each time, but the bigger you make it,
the bigger the cost for the sorting.
If your table is only some few thousand of rows, it's not likely to
be worth the pain.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|