Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

TABLESAMPLE Expand / Collapse
Author
Message
Posted Tuesday, July 1, 2008 3:50 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 2011 7:20 AM
Points: 977, Visits: 1,499
SanjayAttray (7/1/2008)

...does any body uses this in day to day programming? If no, what's the actual use of this function -- "tablesample system".


I would like to know that too.

One thing I like about forums, and especially this one is the occasional HOLY S**T moment when you realize "Man... I can get some use out of this."

It's eluding me on this one.


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #526940
Posted Wednesday, July 2, 2008 12:53 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, May 10, 2009 10:10 AM
Points: 33, Visits: 30
I think the ratio of number of rows in the table and the percent specified is also a deciding factor for the number of rows to be returned.

This is actually used in statistical calculations where we use to collect data randomly to find the growth rate and stuff like that.
Post #527052
Posted Wednesday, July 2, 2008 6:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, April 22, 2010 3:00 AM
Points: 31, Visits: 21
Please look at the documantation that provided by Micorosoft SQL 2005

TABLESAMPLE (10 PERCENT) /*Return a sample 10 percent of the rows of the result set. */
TABLESAMPLE (15 ROWS) /* Return a sample of 15 rows from the result set. */.


SYSTEM specifies an ANSI SQL implementation-dependent sampling method. Specifying SYSTEM is optional, but it is the only sampling method available in Microsoft SQL Server 2005 and is applied by default.

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
Post #527207
Posted Thursday, July 3, 2008 3:10 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, July 17, 2014 10:36 PM
Points: 5,303, Visits: 1,378
I think very few programmers ever used this one. :)


Post #527860
Posted Thursday, July 10, 2008 11:48 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 1:33 AM
Points: 527, Visits: 1,246
i don't know where i could use it but above all good to learn something new. :)
Post #532138
Posted Tuesday, June 15, 2010 2:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 15, 2010 2:18 AM
Points: 15, Visits: 21
Sometimes our table contains large number of records where we need to retrieve some of them (randomly). The TABLESAMPLE clause in SQL Server allows to extract a sampling of rows from a table in the FROM clause. It limits the number of rows returned from a table in the FORM clause to a sample number or PERCENT of rows. The TABLESAMPLE clause takes a parameter that can be a percent or a number representing how many rows to retrieve. The retrieved result of rows are random and they are not in any order. Each time you get a different result set when you run query.


Eliza


Cheers,
Bijayani
Proud to be a part of Team Mindfire.

Mindfire: India's Only Company to be both Apple Premier & Microsoft Gold certified.
Post #937307
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse