TABLESAMPLE question

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)
'====================


Any help appreciated.
J

Re: TABLESAMPLE question

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/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: TABLESAMPLE question

Thanks for the help Erland - even though I'm using tables that are
several million rows long I think I'll just stick with the newID()
function, as it is atleast random and works well, even if a little
slow.

Regards
J



On 17 Aug, 23:04, Erland Sommarskog wrote:
> WhytheQ(Whyt...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx- Hide quoted text -
>
> - Show quoted text -