Reply to Re: TABLESAMPLE question

Your name:

Reply:


Posted by WhytheQ on 08/20/07 14:48

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 <esq...@sommarskog.se> 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/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -
>
> - Show quoted text -

[Back to original message]


Удаленная работа для программистов  •  Как заработать на Google AdSense  •  England, UK  •  статьи на английском  •  PHP MySQL CMS Apache Oscommerce  •  Online Business Knowledge Base  •  DVD MP3 AVI MP4 players codecs conversion help
Home  •  Search  •  Site Map  •  Set as Homepage  •  Add to Favourites

Copyright © 2005-2006 Powered by Custom PHP Programming

Сайт изготовлен в Студии Валентина Петручека
изготовление и поддержка веб-сайтов, разработка программного обеспечения, поисковая оптимизация