Question

Nicole is working on migrating on-premises SQL Server databases to Azure SQL data warehouse  (Synapse dedicated SQL pools) tables. The tables of the dedicated SQL pools of Synapse Analytics require partition. She’s designing SQL table partitions for this data migration to the Azure Synapse. The partition of the tables already contains the data, and she’s looking for the most efficient method to split the partitions in the dedicated SQL pool tables.

What T-SQL statement can she use for splitting partitions that contain data?

A. CTAS

B. CETAS

C. OPENROWSET

D. Clustered Columnstore Indexes

Answer 1

Correct Answer: A

Reason:-

Option A is correct because the CTAS statement can be used as the most efficient method while splitting the partitions that contain data.

Option B is incorrect because CETAS is used in the dedicated SQL pool of Synapse for creating External table and data export operations in parallel for Hadoop, Azure Blob Storage, and ADLS Gen2.

Option C is incorrect because the OPENROWSET function in Synapse SQL reads the content of the file(s) from a data source and returns the content as a set of rows.

Option D is incorrect because Clustered column store tables offer both the highest level of data compression and the best overall query performance. It doesn’t help in partition splitting.

Reference: https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-tables-partition?context=/azure/synapse-analytics/context/context