SQL Saturday 53, takes place in 41 days. I will presenting 45 new TSQL features in 45 minutes which will be a purely demo driven presentation designed to give the audience a taste of what's out there and serve a springboard for them to explore on their own. Sign up now for this great opportunity for free SQL Server training in Kansas City, MO.
TableSample
Tablesample is a hint to SQL Server to just grab some rows. Which rows? If you're asking "which rows", tablesample isn't for you. No, tablesample is a hint to SQL Server that you're in a lascivious mood and you just want some data now. It's clean data, no dirty reads but you might get some really odd, inconsistent results if you try to do things like apply filtering. Best use I've found so far is quasi-psuedo-random sampling of data. It's not random enough for true random so don't treat it as such but if you just want a drink of the data in the table, it's probably "good enough," assuming you remember the syntax.The TABLESAMPLE clause is a different sort of animal. Conceptually, one may think it's nothing more than an ugly synonym for TOP or LIMIT, if you're using a different RDBMS vendor. However, from a query processing perspective, TOP can't run until after the query has been run through the wringer which for large tables might negate any performance savings. At least based on my chapter 1 understanding of logical query processing. It's an odd duck because it either includes a page of information or it doesn't. The query plan will always indicate it's a table scan but it will only read from disk if the selected pages, something something. At this point, read the BOL article above because we are unfortunately way beyond my SQL Server knowledge zone.
Calls to tablesample can either be deterministic or non-deterministic based on whether the REPEATABLE option is used. Activity in the database that affects data pages may also affect the deterministic-ness of your REPEATABLE option. These activities include "inserting, updating, deleting, index rebuilding, index defragmenting, restoring a database, and attaching a database" Consult your DBA if you experience eye watering, mouth watering, flower watering or if your queries last for more than 4 hours.Syntax
TABLESAMPLE is a clause in your FROM statement. From BOL, the syntax is TABLESAMPLE [SYSTEM] (sample_number [ PERCENT | ROWS ])[REPEATABLE (repeat_seed)]. As tablesample requires real tables, none of the jiggered up examples I have used thus far will work. Rather than forcing you to have adventureworks installed, I figured querying against master.dbo.spt_values seemed a safe table to assume will exist on your machine. What I wasn't ready for, was seeing the caveat about pages of data and maybe you won't get anything back. The other surprise was just how variable "10 rows" can be in result sets. In the handful of times I reran, I saw sets ranging from empty, to 47 to 149. Changing it from rows to percent didn't really dial back the variability like I'd have thought based on this statement in BOL "When a number of rows is specified, instead of a percentage based on the total number of rows in the table, that number is converted into a percentage of the rows and, therefore, pages that should be returned. The TABLESAMPLE operation is then performed with that computed percentage." I still saw some pretty big swings from 0 to 61 to 358 rows.SELECT COUNT(1) rc FROM master.dbo.spt_values T
| rc |
|---|
| 2508 |
SELECT * FROM master.dbo.spt_values T tablesample (10 rows)
Invocation 1
| name | number | type | low | high | status |
|---|
Invocation 2
| name | number | type | low | high | status |
|---|---|---|---|---|---|
| ROUTE | 21586 | EOB | NULL | NULL | 0 |
| STATISTICS | 21587 | EOB | NULL | NULL | 0 |
| ASYMMETRIC KEY USER | 21825 | EOB | NULL | NULL | 0 |
| CERTIFICATE USER | 21827 | EOB | NULL | NULL | 0 |
| GROUP USER | 21831 | EOB | NULL | NULL | 0 |
| SQL USER | 21843 | EOB | NULL | NULL | 0 |
| WINDOWS USER | 21847 | EOB | NULL | NULL | 0 |
| SERVICE | 22099 | EOB | NULL | NULL | 0 |
| INDEX | 22601 | EOB | NULL | NULL | 0 |
| LOGIN | 22604 | EOB | NULL | NULL | 0 |
| XML SCHEMA COLLECTION | 22611 | EOB | NULL | NULL | 0 |
| TYPE | 22868 | EOB | NULL | NULL | 0 |
| SERVER AUDIT | 8257 | EOD | NULL | NULL | 0 |
| CHECK CONSTRAINT | 8259 | EOD | NULL | NULL | 0 |
| DEFAULT | 8260 | EOD | NULL | NULL | 0 |
| FOREIGN KEY CONSTRAINT | 8262 | EOD | NULL | NULL | 0 |
| STORED PROCEDURE | 8272 | EOD | NULL | NULL | 0 |
| RULE | 8274 | EOD | NULL | NULL | 0 |
| TABLE SYSTEM | 8275 | EOD | NULL | NULL | 0 |
| TRIGGER SERVER | 8276 | EOD | NULL | NULL | 0 |
| TABLE | 8277 | EOD | NULL | NULL | 0 |
| VIEW | 8278 | EOD | NULL | NULL | 0 |
| STORED PROCEDURE EXTENDED | 8280 | EOD | NULL | NULL | 0 |
| DATABASE AUDIT SPECIFICATION | 16708 | EOD | NULL | NULL | 0 |
| SERVER AUDIT SPECIFICATION | 16723 | EOD | NULL | NULL | 0 |
| TRIGGER ASSEMBLY | 16724 | EOD | NULL | NULL | 0 |
| DATABASE | 16964 | EOD | NULL | NULL | 0 |
| OBJECT | 16975 | EOD | NULL | NULL | 0 |
| FULLTEXT CATALOG | 17222 | EOD | NULL | NULL | 0 |
| STORED PROCEDURE ASSEMBLY | 17232 | EOD | NULL | NULL | 0 |
| SCHEMA | 17235 | EOD | NULL | NULL | 0 |
| CREDENTIAL | 17475 | EOD | NULL | NULL | 0 |
| EVENT NOTIFICATION SERVER | 17491 | EOD | NULL | NULL | 0 |
| EVENT SESSION | 17747 | EOD | NULL | NULL | 0 |
| AGGREGATE | 17985 | EOD | NULL | NULL | 0 |
| FUNCTION TABLE-VALUED INLINE SQL | 17993 | EOD | NULL | NULL | 0 |
| PARTITION FUNCTION | 18000 | EOD | NULL | NULL | 0 |
| STORED PROCEDURE REPLICATION FILTER | 18002 | EOD | NULL | NULL | 0 |
| FUNCTION TABLE-VALUED SQL | 18004 | EOD | NULL | NULL | 0 |
| RESOURCE GOVERNOR | 18258 | EOD | NULL | NULL | 0 |
| SERVER ROLE | 18259 | EOD | NULL | NULL | 0 |
| WINDOWS GROUP | 18263 | EOD | NULL | NULL | 0 |
| ASYMMETRIC KEY | 19265 | EOD | NULL | NULL | 0 |
| DATABASE ENCRYPTION KEY | 19268 | EOD | NULL | NULL | 0 |
| MASTER KEY | 19277 | EOD | NULL | NULL | 0 |
| PRIMARY KEY | 19280 | EOD | NULL | NULL | 0 |
| SYMMETRIC KEY | 19283 | EOD | NULL | NULL | 0 |
No comments:
Post a Comment