Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ravilobo
Master Smack Fu Yak Hacker
1184 Posts |
 Posted - 2007-05-09 : 16:13:38
|
| Following query some times doesn't give any output. The table has 20K records. Any idea why SQL2K5 behaves like that?select id from SOURCE TABLESAMPLE (300 rows);------------------------I think, therefore I am - Rene Descartes |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2007-05-10 : 02:39:27
|
| That is normal behavior. TABLESAMPLE fetches random number of rows each time you query. The rows returned may be more or less than what you specified.Also, in your case, percentage of rows to be returned is very less (mere 1.5%). So SQL server may or may not choose to return data. Put higher value in TABLESAMPLE clause to increase the probability of getting records.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
chiragkhabaria
Master Smack Fu Yak Hacker
1907 Posts |
Posted - 2007-05-10 : 04:06:00
|
BOL quote:
TABLESAMPLE SYSTEM returns an approximate percentage of rows. It generates a random value for each physical 8 KB page in the table. Based on the random value for a page and the percentage specified in the query, a page is included in the sample or excluded. Each page that is included returns all rows in the sample result set. For example, when specifying TABLESAMPLE SYSTEM 10 PERCENT, SQL Server returns all the rows on approximately 10 percent of the specified table's data pages. If the rows are evenly distributed on the pages of the table, and if there is a sufficient number of pages in the table, the number of rows returned should approximate the sample size requested. However, as the random value generated for each page is independent of the values generated for any other page, it is possible that a larger, or smaller, percentage of pages than requested are returned. The TOP(n) operator can be used to limit the number of rows to a given maximum.
Chiraghttp://chirikworld.blogspot.com/ |
|
|
|
|
|
|
|
|
TABLESAMPLE