CREATE TABLE AS SELECT

Aplica-se a: Azure Synapse Analytics Analytics Platform System (PDW)

CTAS (CREATE TABLE AS SELECT) é um dos recursos mais importantes do T-SQL disponíveis. Essa é uma operação totalmente em paralelo que cria uma nova tabela com base na saída de uma instrução SELECT. CTAS é a maneira mais rápida e simples de criar uma cópia de uma tabela.

Por exemplo, use CTAS para:

• Recriar uma tabela com uma coluna de distribuição de hash diferente.
• Recriar uma tabela como replicada.
• Criar um índice columnstore em apenas algumas das colunas da tabela.
• Consultar ou importar dados externos.

Observação

Como CTAS complementa os recursos de criação de uma tabela, este tópico tenta não repetir o tópico CREATE TABLE. Ele descreve as diferenças entre as instruções CTAS e CREATE TABLE. Para obter detalhes sobre CREATE TABLE, confira a instrução CREATE TABLE (Azure Synapse Analytics).

• Não há suporte a essa sintaxe para o pool de SQL sem servidor no Azure Synapse Analytics.
• O CTAS tem suporte no Warehouse no Microsoft Fabric.

Convenções de sintaxe de Transact-SQL

Sintaxe

CREATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    [ ( column_name [ ,...n ] ) ]  
    WITH ( 
      <distribution_option> -- required
      [ , <table_option> [ ,...n ] ]    
    )  
    AS <select_statement>  
    OPTION <query_hint> 
[;]  

<distribution_option> ::=
    { 
        DISTRIBUTION = HASH ( distribution_column_name ) 
      | DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] )
      | DISTRIBUTION = ROUND_ROBIN 
      | DISTRIBUTION = REPLICATE
    }   

<table_option> ::= 
    {   
        CLUSTERED COLUMNSTORE INDEX --default for Synapse Analytics 
      | CLUSTERED COLUMNSTORE INDEX ORDER (column[,...n])
      | HEAP --default for Parallel Data Warehouse   
      | CLUSTERED INDEX ( { index_column_name [ ASC | DESC ] } [ ,...n ] ) --default is ASC 
    }  
      | PARTITION ( partition_column_name RANGE [ LEFT | RIGHT ] --default is LEFT  
        FOR VALUES ( [ boundary_value [,...n] ] ) ) 

<select_statement> ::=  
    [ WITH <common_table_expression> [ ,...n ] ]  
    SELECT select_criteria  

<query_hint> ::=
    {
        MAXDOP 
    }

Argumentos

Para obter detalhes, confira a seção Argumentos em CREATE TABLE.

Opções de Coluna

column_name [ ,...n ]
Os nomes de coluna não permitem as opções de coluna mencionadas em CREATE TABLE. Nesse caso, você pode fornecer uma lista opcional de um ou mais nomes de coluna para a nova tabela. As colunas na nova tabela usam os nomes que você especificar. Quando você especificar nomes de coluna, o número de colunas na lista de colunas deverá corresponder ao número de colunas nos resultados de select. Se você não especificar nenhum nome de coluna, a nova tabela de destino usará os nomes de coluna nos resultados da instrução select.

Não é possível especificar nenhuma outra opção de coluna, como tipos de dados, ordenação ou nulidade. Cada um desses atributos é derivado dos resultados da instrução SELECT. No entanto, você pode usar a instrução SELECT para alterar os atributos. Para obter um exemplo, confira Usar CTAS para alterar os atributos da coluna.

Opções de distribuição da tabela

Para obter detalhes e entender como escolher a melhor coluna de distribuição, veja a seção Opções de distribuição da tabela em CREATE TABLE. Para obter recomendações sobre qual distribuição escolher para uma tabela com base no uso real ou em consultas de exemplo, confira o Assistente de Distribuição no SQL do Azure Synapse.

DISTRIBUTION = HASH (distribution_column_name) | ROUND_ROBIN | REPLICATE A instrução CTAS requer uma opção de distribuição e não têm valores padrão. Isso é diferente de CREATE TABLE, que tem padrões.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] ) Distribui as linhas com base nos valores de hash de até oito colunas, o que permite uma distribuição mais uniforme dos dados da tabela base, reduz a distorção de dados ao longo do tempo e melhora o desempenho da consulta.

Observação

• Para habilitar o recurso, altere o nível de compatibilidade do banco de dados para 50 com esse comando. Para obter mais informações sobre como definir o nível de compatibilidade do banco de dados, confira ALTERAR A CONFIGURAÇÃO DO ESCOPO DO BANCO DE DADOS. Por exemplo: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
• Para desabilitar o recurso MCD (Distribuição de Várias Colunas), execute este comando para alterar o nível de compatibilidade do banco de dados para AUTO. Por exemplo: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; As tabelas de MCD existentes serão mantidas, mas ficarão ilegíveis. As consultas em tabelas de MCD retornarão este erro: Related table/view is not readable because it distributes data on multiple columns and multi-column distribution is not supported by this product version or this feature is disabled.
  • Para recuperar o acesso às tabelas MCD, habilite o recurso novamente.
  • Para carregar dados em uma tabela de MCD, deve-se usar a instrução CTAS e a fonte de dados precisa consistir em tabelas de SQL do Synapse.
  • Não há suporte para CTAS em tabelas de destino MCD HEAP. Nesse caso, use INSERT SELECT como solução alternativa para carregar dados em tabelas MCD HEAP.
• Atualmente, o uso do SSMS para gerar um script para criar tabelas MCD tem suporte além da versão 19 do SSMS.

Para obter detalhes e entender como escolher a melhor coluna de distribuição, veja a seção Opções de distribuição da tabela em CREATE TABLE.

Para obter recomendações sobre a melhor distribuição a ser usada com base nas suas cargas de trabalho, confira o Assistente de Distribuição do SQL do Synapse (Versão prévia).

Opções de partição da tabela

A instrução CTAS cria uma tabela não particionada por padrão, mesmo quando a tabela de origem está particionada. Para criar uma tabela particionada com a instrução CTAS, você precisa especificar a opção de partição.

Para obter detalhes, veja a seção Opções de partição da tabela em CREATE TABLE.

Instrução SELECT

A instrução SELECT é a diferença fundamental entre CTAS e CREATE TABLE.

WITH common_table_expression

Especifica um conjunto de resultados nomeado temporário, conhecido como uma CTE (expressão de tabela comum). Para obter mais informações, confira WITH common_table_expression (Transact-SQL).

SELECT select_criteria

Popula a nova tabela com os resultados de uma instrução SELECT. select_criteria é o corpo da instrução SELECT que determina quais dados serão copiados para a nova tabela. Para obter informações sobre as instruções SELECT, confira SELECT (Transact-SQL).

Dica de consulta

Os usuários podem definir MAXDOP como um valor inteiro para controlar o grau máximo de paralelismo. Quando MAXDOP é definido como 1, a consulta é executada por um único thread.

Permissões

CTAS requer a permissão SELECT em todos os objetos referenciados em select_criteria.

Para obter permissões para criar uma tabela, confira Permissões em CREATE TABLE.

Comentários

Para obter detalhes, confira Comentários gerais em CREATE TABLE.

Limitações e restrições

Para obter mais detalhes sobre limitações e restrições, consulte Limitações e restrições em CREATE TABLE.

• Um índice columnstore clusterizado e ordenado poderá ser criado em colunas de todos os tipos de dados compatíveis com o Azure Synapse Analytics, exceto em colunas de cadeia de caracteres.

• SET ROWCOUNT (Transact-SQL) não tem efeito em CTAS. Para obter um comportamento semelhante, use TOP (Transact-SQL).

• O CTAS não suporta a OPENJSON função como parte da SELECT instrução. Como alternativa, use INSERT INTO ... SELECT. Por exemplo:

  DECLARE @json NVARCHAR(MAX) = N'
  [
      {
          "id": 1,
          "name": "Alice",
          "age": 30,
          "address": {
              "street": "123 Main St",
              "city": "Wonderland"
          }
      },
      {
          "id": 2,
          "name": "Bob",
          "age": 25,
          "address": {
              "street": "456 Elm St",
              "city": "Gotham"
          }
      }
  ]';
  
  INSERT INTO Users (id, name, age, street, city)
  SELECT 
      id,
      name,
      age,
      JSON_VALUE(address, '$.street') AS street,
      JSON_VALUE(address, '$.city') AS city
  FROM OPENJSON(@json)
  WITH (
      id INT,
      name NVARCHAR(50),
      age INT,
      address NVARCHAR(MAX) AS JSON
  );
  

Comportamento de bloqueio

Para obter detalhes, confira Comportamento de bloqueio em CREATE TABLE.

Desempenho

Para uma tabela distribuída por hash, você pode usar CTAS para escolher uma coluna de distribuição diferente para melhorar o desempenho das junções e agregações. Se escolher uma coluna de distribuição diferente não for seu objetivo, você terá o melhor desempenho em CTAS se especificar a mesma coluna de distribuição, pois isso evitará a redistribuição das linhas.

Se você estiver usando CTAS para criar a tabela e o desempenho não for um fator, especifique ROUND_ROBIN para não precisar decidir por uma coluna de distribuição.

Para evitar a movimentação de dados nas próximas consultas, especifique REPLICATE, às custas do aumento do armazenamento, para carregar uma cópia completa da tabela em cada nó de computação.

Exemplos para copiar uma tabela

a. Usar CTAS para copiar uma tabela

Aplicável a: Azure Synapse Analytics e PDW (Analytics Platform System)

Talvez um dos tipos de uso mais comuns do CTAS é criar uma cópia de uma tabela para que você possa alterar a DDL. Por exemplo, se você criou a tabela originalmente como ROUND_ROBIN e agora deseja alterá-la para uma tabela distribuída em uma coluna, CTAS será a forma de alterar a coluna de distribuição. CTAS também pode ser usado para alterar os tipos de particionamento, de indexação ou de coluna.

Vamos supor que você criou esta tabela especificando HEAP e usando o tipo de distribuição padrão de ROUND_ROBIN.

CREATE TABLE FactInternetSales
(
    ProductKey INT NOT NULL,
    OrderDateKey INT NOT NULL,
    DueDateKey INT NOT NULL,
    ShipDateKey INT NOT NULL,
    CustomerKey INT NOT NULL,
    PromotionKey INT NOT NULL,
    CurrencyKey INT NOT NULL,
    SalesTerritoryKey INT NOT NULL,
    SalesOrderNumber NVARCHAR(20) NOT NULL,
    SalesOrderLineNumber TINYINT NOT NULL,
    RevisionNumber TINYINT NOT NULL,
    OrderQuantity SMALLINT NOT NULL,
    UnitPrice MONEY NOT NULL,
    ExtendedAmount MONEY NOT NULL,
    UnitPriceDiscountPct FLOAT NOT NULL,
    DiscountAmount FLOAT NOT NULL,
    ProductStandardCost MONEY NOT NULL,
    TotalProductCost MONEY NOT NULL,
    SalesAmount MONEY NOT NULL,
    TaxAmt MONEY NOT NULL,
    Freight MONEY NOT NULL,
    CarrierTrackingNumber NVARCHAR(25),
    CustomerPONumber NVARCHAR(25)
)
WITH( 
 HEAP, 
 DISTRIBUTION = ROUND_ROBIN 
);

Agora você deseja criar uma nova cópia dessa tabela com um índice columnstore clusterizado para poder usufruir do desempenho das tabelas columnstore clusterizadas. Convém também distribuir essa tabela em ProductKey, pois isso irá prever as junções nesta coluna, e evitar a movimentação de dados durante as junções no ProductKey. Por fim, também convém adicionar o particionamento em OrderDateKey para que seja possível excluir rapidamente os dados antigos descartando as partições antigas. Veja aqui a instrução CTAS que copiaria sua tabela antiga em uma nova tabela:

CREATE TABLE FactInternetSales_new
WITH
(
    CLUSTERED COLUMNSTORE INDEX,
    DISTRIBUTION = HASH(ProductKey),
    PARTITION
    (
        OrderDateKey RANGE RIGHT FOR VALUES
        (
        20000101,20010101,20020101,20030101,20040101,20050101,20060101,20070101,20080101,20090101,
        20100101,20110101,20120101,20130101,20140101,20150101,20160101,20170101,20180101,20190101,
        20200101,20210101,20220101,20230101,20240101,20250101,20260101,20270101,20280101,20290101
        )
    )
)
AS SELECT * FROM FactInternetSales;

Por fim, você pode renomear as tabelas para fazer a troca pela nova tabela e, em seguida, remover a tabela antiga.

RENAME OBJECT FactInternetSales TO FactInternetSales_old;
RENAME OBJECT FactInternetSales_new TO FactInternetSales;

DROP TABLE FactInternetSales_old;

Exemplos de opções de coluna

B. Usar CTAS para alterar atributos de coluna

Aplicável a: Azure Synapse Analytics e PDW (Analytics Platform System)

Este exemplo usa CTAS para alterar os tipos de dados, a nulidade e a ordenação de várias colunas da tabela DimCustomer2.

-- Original table 
CREATE TABLE [dbo].[DimCustomer2] (  
    [CustomerKey] INT NOT NULL,  
    [GeographyKey] INT NULL,  
    [CustomerAlternateKey] nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  
)  
WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH([CustomerKey]));  

-- CTAS example to change data types, nullability, and column collations  
CREATE TABLE test  
WITH (HEAP, DISTRIBUTION = ROUND_ROBIN)  
AS  
SELECT  
    CustomerKey AS CustomerKeyNoChange,  
    CustomerKey*1 AS CustomerKeyChangeNullable,  
    CAST(CustomerKey AS DECIMAL(10,2)) AS CustomerKeyChangeDataTypeNullable,  
    ISNULL(CAST(CustomerKey AS DECIMAL(10,2)),0) AS CustomerKeyChangeDataTypeNotNullable,  
    GeographyKey AS GeographyKeyNoChange,  
    ISNULL(GeographyKey,0) AS GeographyKeyChangeNotNullable,  
    CustomerAlternateKey AS CustomerAlternateKeyNoChange,  
    CASE WHEN CustomerAlternateKey = CustomerAlternateKey 
        THEN CustomerAlternateKey END AS CustomerAlternateKeyNullable,  
    CustomerAlternateKey COLLATE Latin1_General_CS_AS_KS_WS AS CustomerAlternateKeyChangeCollation  
FROM [dbo].[DimCustomer2]  

-- Resulting table 
CREATE TABLE [dbo].[test] (
    [CustomerKeyNoChange] INT NOT NULL, 
    [CustomerKeyChangeNullable] INT NULL, 
    [CustomerKeyChangeDataTypeNullable] DECIMAL(10, 2) NULL, 
    [CustomerKeyChangeDataTypeNotNullable] DECIMAL(10, 2) NOT NULL, 
    [GeographyKeyNoChange] INT NULL, 
    [GeographyKeyChangeNotNullable] INT NOT NULL, 
    [CustomerAlternateKeyNoChange] NVARCHAR(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
    [CustomerAlternateKeyNullable] NVARCHAR(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
    [CustomerAlternateKeyChangeCollation] NVARCHAR(15) COLLATE Latin1_General_CS_AS_KS_WS NOT NULL
)
WITH (DISTRIBUTION = ROUND_ROBIN);

Como etapa final, você pode usar RENAME (Transact-SQL) para mudar os nomes das tabelas. Assim, DimCustomer2 torna-se a nova tabela.

RENAME OBJECT DimCustomer2 TO DimCustomer2_old;
RENAME OBJECT test TO DimCustomer2;

DROP TABLE DimCustomer2_old;

Exemplos de distribuição da tabela

C. Usar CTAS para alterar o método de distribuição de uma tabela

Aplicável a: Azure Synapse Analytics e PDW (Analytics Platform System)

Esse exemplo simples mostra como alterar o método de distribuição de uma tabela. Para mostrar o mecanismo de como fazer isso, ele altera uma tabela distribuída por hash para round robin e, em seguida, altera a tabela de round robin para distribuída por hash. A tabela final corresponde à tabela original.

Na maioria dos casos, não será necessário converter uma tabela distribuída por hash em uma tabela de round robin. Com um frequência maior, pode ser necessário converter uma tabela de round robin em uma tabela distribuída por hash. Por exemplo, você pode carregar inicialmente uma nova tabela como round robin e, mais tarde, convertê-la em uma tabela distribuída por hash para melhorar o desempenho da junção.

Este exemplo usa o banco de dados de exemplo AdventureWorksDW. Para carregar a versão do Azure Synapse Analytics, confira Guia de início rápido: Criar e consultar um pool de SQL dedicado (antigo SQL DW) no Azure Synapse Analytics usando o portal do Azure.

-- DimSalesTerritory is hash-distributed.
-- Copy it to a round-robin table.
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN  
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

Em seguida, converta-a novamente em uma tabela distribuída por hash.

-- You just made DimSalesTerritory a round-robin table.
-- Change it back to the original hash-distributed table. 
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH(SalesTerritoryKey) 
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

D. Usar CTAS para converter uma tabela em uma tabela replicada

Aplicável a: Azure Synapse Analytics e PDW (Analytics Platform System)

Este exemplo aplica-se para converter tabelas round robin ou distribuídas por hash em uma tabela replicada. Esse exemplo específico avança mais uma etapa do método anterior de alterar o tipo de distribuição. Como DimSalesTerritory é uma dimensão e provavelmente uma tabela menor, você pode escolher recriar a tabela como replicada para evitar a movimentação de dados ao unir a outras tabelas.

-- DimSalesTerritory is hash-distributed.
-- Copy it to a replicated table.
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = REPLICATE 
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

E. Usar CTAS para criar uma tabela com menos colunas

Aplicável a: Azure Synapse Analytics e PDW (Analytics Platform System)

O exemplo a seguir cria uma tabela distribuída de round robin denominada myTable (c, ln). A nova tabela tem apenas duas colunas. Ela usa os aliases de coluna na instrução SELECT para os nomes das colunas.

CREATE TABLE myTable  
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN  
  )  
AS SELECT CustomerKey AS c, LastName AS ln  
    FROM dimCustomer; 

Exemplos de dicas de consulta

F. Usar uma dica de consulta com CTAS (CREATE TABLE AS SELECT)

Aplicável a: Azure Synapse Analytics e PDW (Analytics Platform System)

Esta consulta mostra a sintaxe básica para usar uma dica de junção de consulta com a instrução CTAS. Depois que a consulta é enviada, o Azure Synapse Analytics aplica a estratégia de junção de hash ao gerar o plano de consulta para cada distribuição individual. Para obter mais informações sobre a dica de consulta de junção hash, confira Cláusula OPTION (Transact-SQL).

CREATE TABLE dbo.FactInternetSalesNew  
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN   
  )  
AS SELECT T1.* FROM dbo.FactInternetSales T1 JOIN dbo.DimCustomer T2  
ON ( T1.CustomerKey = T2.CustomerKey )  
OPTION ( HASH JOIN );  

Exemplos de tabelas externas

G. Usar CTAS para importar dados do Armazenamento de Blobs do Azure

Aplicável a: Azure Synapse Analytics e PDW (Analytics Platform System)

Para importar dados de uma tabela externa, use CREATE TABLE AS SELECT para selecionar a tabela externa. A sintaxe para selecionar dados de uma tabela externa no Azure Synapse Analytics é a mesma que a sintaxe para selecionar dados de uma tabela regular.

O exemplo a seguir define uma tabela externa usando dados em uma conta de Armazenamento de Blobs do Azure. Em seguida, ele usa CREATE TABLE AS SELECT para selecionar na tabela externa. Essa ação importa os dados dos arquivos de texto delimitados do Armazenamento de Blobs do Azure e armazena os dados em uma nova tabela do Azure Synapse Analytics.

--Use your own processes to create the text-delimited files on Azure Blob Storage.  
--Create the external table called ClickStream.  
CREATE EXTERNAL TABLE ClickStreamExt (   
    url VARCHAR(50),  
    event_date DATE,  
    user_IP VARCHAR(50)  
)  
WITH (  
    LOCATION='/logs/clickstream/2015/',  
    DATA_SOURCE = MyAzureStorage,  
    FILE_FORMAT = TextFileFormat)  
;  

--Use CREATE TABLE AS SELECT to import the Azure Blob Storage data into a new   
--Synapse Analytics table called ClickStreamData  
CREATE TABLE ClickStreamData   
WITH  
  (  
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH (user_IP)  
  )  
AS SELECT * FROM ClickStreamExt  
;  

H. Usar CTAS para importar dados do Hadoop de uma tabela externa

Aplica-se a: PDW (Analytics Platform System)

Para importar dados de uma tabela externa, simplesmente use CREATE TABLE AS SELECT para selecionar a tabela externa. A sintaxe para selecionar dados de uma tabela externa no PDW (Analytics Platform System) é a mesma que a sintaxe para selecionar dados de uma tabela regular.

O exemplo a seguir define uma tabela externa em um cluster do Hadoop. Em seguida, ele usa CREATE TABLE AS SELECT para selecionar na tabela externa. Essa ação importa os dados dos arquivos de texto delimitados do Hadoop e armazena os dados em uma nova tabela do PDW (Analytics Platform System).

-- Create the external table called ClickStream.  
CREATE EXTERNAL TABLE ClickStreamExt (   
    url VARCHAR(50),  
    event_date DATE,  
    user_IP VARCHAR(50)  
)  
WITH (  
    LOCATION = 'hdfs://MyHadoop:5000/tpch1GB/employee.tbl',  
    FORMAT_OPTIONS ( FIELD_TERMINATOR = '|')  
)  
;  

-- Use your own processes to create the Hadoop text-delimited files 
-- on the Hadoop Cluster.  

-- Use CREATE TABLE AS SELECT to import the Hadoop data into a new 
-- table called ClickStreamPDW  
CREATE TABLE ClickStreamPDW   
WITH  
  (  
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH (user_IP)  
  )  
AS SELECT * FROM ClickStreamExt  
;   

Exemplos que usam CTAS para substituir o código do SQL Server

Use CTAS para solucionar a não compatibilidade com alguns recursos. Além de permitir a execução do código no data warehouse, reescrever o código existente para usar CTAS normalmente melhora o desempenho. Este é um resultado de seu design totalmente em paralelo.

Observação

Tente imaginar "CTAS primeiro". Se você achar que é possível resolver um problema usando CTAS, geralmente essa será a melhor maneira de abordá-lo, mesmo que mais dados sejam escritos como resultado.

I. Usar CTAS em vez de SELECT..INTO

Aplicável a: Azure Synapse Analytics e PDW (Analytics Platform System)

O código do SQL Server normalmente usa SELECT..INTO para popular uma tabela com os resultados de uma instrução SELECT. Este é um exemplo de uma instrução SELECT..INTO do SQL Server.

SELECT *
INTO    #tmp_fct
FROM    [dbo].[FactInternetSales]

Não há suporte para essa sintaxe no Azure Synapse Analytics e no Parallel Data Warehouse. Este exemplo mostra como reescrever a instrução SELECT..INTO anterior como uma instrução CTAS. Você pode escolher uma das opções de DISTRIBUTION descritas na sintaxe de CTAS. Este exemplo usa o método de distribuição de ROUND_ROBIN.

CREATE TABLE #tmp_fct
WITH
(
    DISTRIBUTION = ROUND_ROBIN
)
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
;

J. Usar CTAS para simplificar instruções de mesclagem

Aplicável a: Azure Synapse Analytics e PDW (Analytics Platform System)

As instruções de mesclagem podem ser substituídas, pelo menos em parte, usando CTAS. Você pode consolidar INSERT e UPDATE em uma única instrução. Os registros excluídos precisariam ser separados em uma segunda instrução.

Segue um exemplo de UPSERT:

CREATE TABLE dbo.[DimProduct_upsert]
WITH
(   DISTRIBUTION = HASH([ProductKey])
,   CLUSTERED INDEX ([ProductKey])
)
AS
-- New rows and new versions of rows
SELECT      s.[ProductKey]
,           s.[EnglishProductName]
,           s.[Color]
FROM      dbo.[stg_DimProduct] AS s
UNION ALL  
-- Keep rows that are not being touched
SELECT      p.[ProductKey]
,           p.[EnglishProductName]
,           p.[Color]
FROM      dbo.[DimProduct] AS p
WHERE NOT EXISTS
(   SELECT  *
    FROM    [dbo].[stg_DimProduct] s
    WHERE   s.[ProductKey] = p.[ProductKey]
)
;

RENAME OBJECT dbo.[DimProduct]          TO [DimProduct_old];
RENAME OBJECT dbo.[DimProduct_upsert]  TO [DimProduct];

K. Declarar explicitamente o tipo de dados e a nulidade da saída

Aplicável a: Azure Synapse Analytics e PDW (Analytics Platform System)

Ao migrar o código do SQL Server para o Azure Synapse Analytics, será possível encontrar este tipo de padrão de codificação:

DECLARE @d DECIMAL(7,2) = 85.455
,       @f FLOAT(24)    = 85.455

CREATE TABLE result
(result DECIMAL(7,2) NOT NULL
)
WITH (DISTRIBUTION = ROUND_ROBIN)

INSERT INTO result
SELECT @d*@f
;

Instintivamente você poderá pensar que deve migrar este código para um CTAS, e isso estará correto. No entanto, há um problema oculto aqui.

O código a seguir NÃO produz o mesmo resultado:

DECLARE @d DECIMAL(7,2) = 85.455
,       @f FLOAT(24)    = 85.455
;

CREATE TABLE ctas_r
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT @d*@f as result
;

Observe que a coluna "resultado" transfere os valores de tipo de dados e de nulidade da expressão. Se você não tiver cuidado, isso poderá levar a variações sutis nos valores.

Tente o seguinte como exemplo:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

O valor armazenado para o resultado é diferente. Como o valor persistente na coluna de resultado é usado em outras expressões, o erro torna-se ainda mais significativo.

Isso é importante principalmente para migrações de dados. Embora a segunda consulta seja indiscutivelmente mais precisa, há um problema. Os dados seriam diferentes em comparação com o sistema de origem, o que levantaria dúvidas sobre a integridade da migração. Este é um dos raros casos em que a resposta "errada" é, na verdade, a melhor!

O motivo pelo qual existe essa discrepância entre os dois resultados, ocorre devido à conversão de tipo implícito. No primeiro exemplo, a tabela define a definição de coluna. Quando a linha é inserida, ocorre uma conversão implícita de tipo. No segundo exemplo não há nenhuma conversão implícita de tipo, pois a expressão define o tipo de dados da coluna. Observe também que a coluna no segundo exemplo foi definida como uma coluna anulável, mas no primeiro exemplo ela não foi. Quando a tabela foi criada no primeiro exemplo, a nulidade da coluna foi definida explicitamente. No segundo exemplo, isso ficou a cargo da expressão, o que, por padrão, resultaria em uma definição NULL.

Para resolver esses problemas, você precisa definir explicitamente a conversão de tipo e a nulidade na parte SELECT da instrução CTAS. Não é possível definir essas propriedades na parte create table.

Este exemplo demonstra como corrigir o código:

DECLARE @d DECIMAL(7,2) = 85.455
,       @f FLOAT(24)    = 85.455

CREATE TABLE ctas_r
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT ISNULL(CAST(@d*@f AS DECIMAL(7,2)),0) as result

Observe o seguinte no exemplo:

• CAST ou CONVERT poderia ter sido usado.
• ISNULL é usado para forçar a nulidade, não COALESCE.
• ISNULL é a função mais distante.
• A segunda parte de ISNULL é uma constante, ou seja, 0.

Observação

Para que a nulidade seja definida corretamente, é essencial usar ISNULL e não COALESCE. COALESCE não é uma função determinística, portanto, o resultado da expressão sempre permitirá valor nulo. ISNULL é diferente. Ele é determinístico. Portanto, quando a segunda parte da função ISNULL for uma constante ou um literal, o valor resultante será NOT NULL.

Além dessa dica ser útil para garantir a integridade dos cálculos, ela também é importante para a alternância de partição de tabela. Imagine que você tenha esta tabela definida como o fato:

CREATE TABLE [dbo].[Sales]
(
    [date]      INT     NOT NULL
,   [product]   INT     NOT NULL
,   [store]     INT     NOT NULL
,   [quantity]  INT     NOT NULL
,   [price]     MONEY   NOT NULL
,   [amount]    MONEY   NOT NULL
)
WITH
(   DISTRIBUTION = HASH([product])
,   PARTITION   (   [date] RANGE RIGHT FOR VALUES
                    (20000101,20010101,20020101
                    ,20030101,20040101,20050101
                    )
                )
)
;

No entanto, o campo de valor é uma expressão calculada, ele não faz parte dos dados de origem.

Para criar seu conjunto de dados particionado, considere o seguinte exemplo:

CREATE TABLE [dbo].[Sales_in]
WITH    
(   DISTRIBUTION = HASH([product])
,   PARTITION   (   [date] RANGE RIGHT FOR VALUES
                    (20000101,20010101
                    )
                )
)
AS
SELECT
    [date]    
,   [product]
,   [store]
,   [quantity]
,   [price]   
,   [quantity]*[price]  AS [amount]
FROM [stg].[source]
OPTION (LABEL = 'CTAS : Partition IN table : Create')
;

A consulta seria executada de forma perfeitamente normal. O problema aparece quando você tenta executar a alternância de partição. As definições de tabela não correspondem. Para fazer com que as definições da tabela correspondam, o CTAS precisa ser modificado.

CREATE TABLE [dbo].[Sales_in]
WITH    
(   DISTRIBUTION = HASH([product])
,   PARTITION   (   [date] RANGE RIGHT FOR VALUES
                    (20000101,20010101
                    )
                )
)
AS
SELECT
    [date]    
,   [product]
,   [store]
,   [quantity]
,   [price]   
,   ISNULL(CAST([quantity]*[price] AS MONEY),0) AS [amount]
FROM [stg].[source]
OPTION (LABEL = 'CTAS : Partition IN table : Create');

Portanto, veja que manter a consistência de tipo e manter as propriedades de nulidade em um CTAS são uma prática recomendada de engenharia. Isso ajuda a manter a integridade em seus cálculos e também garante que a alternância de partição seja possível.

L. Criar um índice columnstore clusterizado ordenado com MAXDOP 1

CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);

Próximas etapas

• CREATE EXTERNAL DATA SOURCE (Transact-SQL)
• CREATE EXTERNAL FILE FORMAT (Transact-SQL)
• CREATE EXTERNAL TABLE (Transact-SQL)
• CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)
• CREATE TABLE (Azure Synapse Analytics)
• DROP TABLE (Transact-SQL)
• DROP EXTERNAL TABLE (Transact-SQL)
• ALTER TABLE (Transact-SQL)
• ALTER EXTERNAL TABLE (Transact-SQL)