CREATE TABLE AS SELECT builds and loads a table from a query in one statement, with the query deciding the schema. The source's table metadata is not carried over, which is where it differs from CLONE.
Also called CREATE TABLE AS SELECT.
Read more: Microsoft Learn
In the Ultra Transcenders books
Each book explains CTAS in context, with comparison tables and the common traps.
Terms in this definition
- Event
Table in Log Analytics where entries from Windows event logs are kept.
- Schema
The middle part of a Unity Catalog name (
catalog.schema.table), grouping tables, views, volumes, functions and models inside a catalog. A grant on it covers everything in it now and later, and nothing inside can be reached withoutUSE SCHEMA. - Metadata
Describes data rather than being the data itself, for instance how a file is laid out or what rows each chunk contains, so tools can read things efficiently.
- OVER
Gives a T-SQL window function its window: PARTITION BY, ORDER BY and, if wanted, a ROWS or RANGE frame. Rankings and running totals can then be worked out while every row is kept.
- WHERE
Limits a SELECT, UPDATE or DELETE to just the rows meeting a condition. Omit it, and the statement hits every row.
- CLONE
Copies a table as of a given version through
CREATE TABLE ... CLONE. Deep is the default and duplicates data plus metadata; shallow copies the metadata alone and keeps referencing the original files.
Related terms
- Data clustering
A Fabric Data Warehouse preview capability that, as data is loaded, keeps rows with similar values in up to four chosen columns physically together, letting filtered queries avoid reading irrelevant files. You set it once with
WITH (CLUSTER BY (...))inCREATE TABLEor CTAS and cannot alter it afterwards; lakehouse Delta liquid clustering is a different feature. - OPENROWSET
Lets Fabric's T-SQL engines treat data files in Azure storage or OneLake (Parquet, CSV or JSONL) as a table. That is handy for peeking at a file, or for loading it with INSERT ... SELECT or CTAS.