CODEWITHSUNDEEP
azure-synapse
LRonHoover
LRonHoover

Reputation: 23

Azure SQL Data Warehouse CTAS statistics

Does the "Create table as" function in SQL Data Warehouse create statistics in the background, or do they have to manually be created (as I would when I do a normal "Create table" statement?)

Upvotes: 2

Views: 278

Answers (1)

wBob
wBob

Reputation: 14379

As of the current version, you always have to create column-level statistics on tables, irrespective of whether it was created with a normal CREATE TABLE or the CTAS CREATE TABLE AS... command. It's also good practice to create stats for columns used in JOINs, WHERE clauses, GROUP BY, ORDER BY and DISTINCT clauses.

Regarding tables created with CTAS, the database engine has a correct idea of how many rows are in the table as listed in sys.partitions, but not at the column-level statistics level. For tables created by CREATE TABLE this defaults to 1,000 rows. For the example below, the first table was created with a CTAS and has 208 rows, the second table with an ordinary CREATE TABLE and INSERT from the first table and also has 208 rows, but sys.partitions believes it to have 1,000 eg

sys.partitions

Creating any column-level statistics manually will correct this number.

In summary, always manually create statistics against important columns irrespective of how the table was created.

Upvotes: 4

Related Questions