CRIAR TABELA COMO SELECIONAR

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

CREATE TABLE AS SELECT (CTAS) é uma das funcionalidades T-SQL mais importantes disponíveis. É uma operação totalmente paralelizada que cria uma nova tabela baseada na saída de uma instrução SELECT. O CTAS é a forma mais simples e rápida de criar uma cópia de uma tabela.

Por exemplo, use o CTAS para:

• Recrie uma tabela com uma coluna de distribuição de hash diferente.
• Recriar uma tabela tal como replicada.
• Crie um índice de loja de colunas apenas em algumas das colunas da tabela.
• Consulta ou importação de dados externos.

Observação

Como CREATE TABLE AS SELECT (CTAS) acrescenta capacidades para criar uma tabela, este tópico tenta não repetir o CREATE TABLE tema. Em vez disso, descreve as diferenças entre o CTAS e a CREATE TABLE.

• O CTAS é suportado no Warehouse do Microsoft Fabric. Veja a versão Fabric do artigo CRIAR TABELA COMO SELEÇÃO.
• Essa sintaxe não é suportada pelo pool SQL sem servidor no Azure Synapse Analytics.
• CREATE TABLE AS SELECT (CTAS) é suportado no Warehouse do Microsoft Fabric. Para mais informações, consulte a versão do Fabric Data Warehouse deste artigo.

Transact-SQL convenções de sintaxe

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 
    }

Arguments

Para mais informações, consulte a secção Argumentos em CREATE TABLE.

Opções da coluna

column_name [ ,...n ]
Os nomes das colunas não permitem as opções de coluna CRIAR TABELA mencionadas em CREATE TABLE. Em vez disso, 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 especificados. Quando você especifica nomes de colunas, o número de colunas na lista de colunas deve corresponder ao número de colunas nos resultados selecionados. 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 outras opções de coluna, como tipos de dados, agrupamento ou anulabilidade. Cada um destes atributos é derivado dos resultados da SELECT afirmação. No entanto, você pode usar a instrução SELECT para alterar os atributos. Para um exemplo, veja Usar CTAS para alterar atributos de coluna.

Opções de distribuição de tabelas

Para detalhes e para entender como escolher a melhor coluna de distribuição, consulte a secção de opções de distribuição da tabela em CREATE TABLE. Para recomendações sobre que distribuição escolher para uma tabela com base no uso real ou consultas de exemplo, consulte Distribution Advisor no Azure Synapse SQL.

DISTRIBUTION = HASH (distribution_column_name) | ROUND_ROBIN | REPLICAR

A instrução CTAS requer uma opção de distribuição e não tem valores predefinidos. Isto é diferente do CREATE TABLE, que tem os predefinidos.

DISTRIBUTION = HASH ( [distribution_column_name [, ...n]] )

Distribui as linhas com base nos valores de hash de até oito colunas, permitindo uma distribuição mais uniforme dos dados da tabela base, reduzindo o desvio dos dados ao longo do tempo e melhorando o desempenho das consultas.

Observação

• Para ativar a funcionalidade, altere o nível de compatibilidade da base de dados para 50 com este comando. Para mais informações sobre a definição do nível de compatibilidade da base de dados, consulte ALTERAR CONFIGURAÇÃO COM ÂMBITO DE BASE DE DADOS. Por exemplo: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
• Para desativar a funcionalidade de distribuição multi-colunas (MCD), execute este comando para alterar o nível de compatibilidade da base de dados para AUTO. Por exemplo: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = AUTO; As tabelas MCD existentes mantêm-se, mas tornam-se ilegíveis. Consultas sobre tabelas MCD devolverã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, ative novamente a funcionalidade.
  • Para carregar dados numa tabela MCD, use a instrução CTAS e a fonte de dados deve ser tabelas SQL Synapse.
  • O CTAS nas tabelas alvo do MCD HEAP não é suportado. Em vez disso, use o INSERT SELECT como solução alternativa para carregar dados nas tabelas do MCD HEAP.
• O uso do SSMS para gerar um script para criar tabelas MCD é atualmente suportado para além da versão 19 do SSMS.

Para detalhes e para entender como escolher a melhor coluna de distribuição, consulte a secção de opções de distribuição da tabela em CREATE TABLE.

Para recomendações sobre a melhor distribuição a usar com base nas suas cargas de trabalho, consulte o Synapse SQL Distribution Advisor (Preview).

Opções de partição da tabela

A instrução CTAS cria uma tabela não particionada por defeito, mesmo que a tabela de origem esteja particionada. Para criar uma tabela particionada com a instrução CTAS, deve especificar a opção de partição.

Para detalhes, consulte a secção de opções de partição da tabela em CREATE TABLE.

Declaraçã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 expressão de tabela comum (CTE). Para obter mais informações, consulte WITH common_table_expression (Transact-SQL).

SELECT select_criteria

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

Dica de consulta

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

Permissions

O CTAS requer SELECT permissão para quaisquer objetos referenciados na select_criteria.

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

Observações

Para detalhes, veja Observações Gerais em CREATE TABLE.

Limitações e restrições

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

• Um índice ordenado clustered columnstore pode ser criado em colunas de quaisquer tipos de dados suportados no Azure Synapse Analytics, exceto para colunas de string.

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

• O CTAS não suporta a OPENJSON função como parte da SELECT declaração. Em 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 detalhes, veja Comportamento de Bloqueio em CREATE TABLE.

Performance

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

Se estiveres a usar CTAS para criar tabela e o desempenho não for um fator, podes especificar ROUND_ROBIN para evitar ter de escolher uma coluna de distribuição.

Para evitar o movimento de dados em consultas subsequentes, pode especificar REPLICATE , ao custo de um aumento de armazenamento, o carregamento de uma cópia completa da tabela em cada nó de Computação.

Exemplos para copiar uma tabela

A. Use o CTAS para copiar uma tabela

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

Talvez um dos usos mais comuns seja CTAS criar uma cópia de uma tabela para que possas alterar o DDL. Se, por exemplo, originalmente criou a sua tabela como ROUND_ROBIN e agora quer mudá-la para uma tabela distribuída numa coluna, CTAS é assim que mudaria a coluna de distribuição. CTAS pode também ser usado para alterar particionamento, indexação ou tipos de coluna.

Imaginemos que 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 queres criar uma nova cópia desta tabela com um índice clustered columnstore para poderes tirar partido do desempenho das tabelas clustered columnstore. Também deve distribuir esta tabela em ProductKey , pois está a antecipar joins nesta coluna e evitar o movimento de dados durante as joins em ProductKey. Por fim, também queres adicionar particionamento para OrderDateKey poderes apagar rapidamente dados antigos ao eliminar partições antigas. Aqui está a declaração CTAS que copiaria a sua tabela antiga para 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;

Finalmente, podes renomear as tuas mesas para trocar a nova mesa e depois retirar a antiga mesa.

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 os atributos das colunas

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

Este exemplo usa CTAS para alterar tipos de dados, anulabilidade e colação para várias colunas da DimCustomer2 tabela.

-- 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 passo final, podes usar RENAME (Transact-SQL) para trocar os nomes das tabelas. Isto faz do DimCustomer2 a nova tabela.

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

DROP TABLE DimCustomer2_old;

Exemplos para distribuição de tabelas

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

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

Este exemplo simples mostra como alterar o método de distribuição de uma tabela. Para mostrar a mecânica de como fazer isto, muda uma tabela distribuída por hash para round-robin e depois muda a tabela round-robin de volta para distribuição por hash. A tabela final corresponde à mesa original.

Na maioria dos casos, não é necessário mudar uma tabela distribuída por hash para uma tabela round-robin. Mais frequentemente, pode ser necessário mudar uma tabela round-robin para uma tabela distribuída por hash. Por exemplo, podes inicialmente carregar uma nova tabela em round-robin e depois movê-la para uma tabela distribuída por hash para obter melhor desempenho na junção.

Este exemplo utiliza a base de dados de exemplo AdventureWorksDW. Para carregar a versão do Azure Synapse Analytics, consulte Quickstart: Criar e consultar um pool SQL dedicado (anteriormente SQL DW) no Azure Synapse Analytics usando o portal 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];

De seguida, volta a mudar para 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. Use o CTAS para converter uma tabela numa tabela replicada

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

Este exemplo aplica-se para converter tabelas round-robin ou distribuídas por hash numa tabela replicada. Este exemplo em particular leva o método anterior de alterar o tipo de distribuição um passo mais longe. Como DimSalesTerritory é uma dimensão e provavelmente uma tabela mais pequena, pode optar por recriar a tabela como replicada para evitar o movimento de dados ao juntar 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. Use o CTAS para criar uma tabela com menos colunas

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

O exemplo seguinte cria uma tabela distribuída round-robin chamada myTable (c, ln). A nova tabela só tem duas colunas. Utiliza os pseudónimos das colunas 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. Use uma Dica de Consulta com CRIAR TABELA COMO SELEÇÃO (CTAS)

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

Esta consulta mostra a sintaxe básica para usar uma dica de junção de consulta com a instrução CTAS. Após a submissão da consulta, 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 mais informações sobre a dica de consulta de junção de hash, consulte 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 para tabelas externas

G. Use CTAS para importar dados do Azure Blob storage

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

Para importar dados de uma tabela externa, use CRIAR TABELA COMO SELECT para selecionar da tabela externa. A sintaxe para selecionar dados de uma tabela externa no Azure Synapse Analytics é a mesma para selecionar dados de uma tabela regular.

O exemplo seguinte define uma tabela externa sobre dados numa conta Azure Blob Storage. Depois, utiliza CRIAR TABELA COMO SELECT para selecionar da tabela externa. Isto importa os dados dos ficheiros delimitados por texto do Azure Blob Storage e armazena os dados numa nova tabela 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. Use o CTAS para importar dados Hadoop de uma tabela externa

Aplica-se a: Sistema de Plataforma de Análise (PDW)

Para importar dados de uma tabela externa, basta usar CRIAR TABELA COMO SELECT para selecionar a partir da tabela externa. A sintaxe para selecionar dados de uma tabela externa no Sistema de Plataforma de Análise (PDW) é a mesma para selecionar dados de uma tabela regular.

O exemplo seguinte define uma tabela externa num cluster Hadoop. Depois, utiliza CRIAR TABELA COMO SELECT para selecionar da tabela externa. Isto importa os dados de ficheiros Hadoop delimitados por texto e armazena os dados numa nova tabela do Sistema de Plataforma de Análise (PDW).

-- 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 a usar CTAS para substituir código SQL Server

Use o CTAS para contornar algumas funcionalidades não suportadas. Além de conseguir executar o seu código no data warehouse, reescrever código existente para usar CTAS normalmente melhora o desempenho. Isto resulta do seu design totalmente paralelizado.

Observação

Tenta pensar em "CTAS primeiro". Se achas que podes resolver um problema usando isso, CTAS essa é geralmente a melhor forma de o abordar – mesmo que estejas a escrever mais dados como resultado.

I. Use CTAS em vez de SELECT.. PARA

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

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

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

Esta sintaxe não é suportada no Azure Synapse Analytics e no Parallel Data Warehouse. Este exemplo mostra como reescrever o SELECT anterior.. Declaração INTO como declaração CTAS. Pode escolher qualquer uma das opções DISTRIBUTION descritas na sintaxe CTAS. Este exemplo utiliza o método de distribuição ROUND_ROBIN.

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

J. Use o CTAS para simplificar as sentenças de fusão

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

As sentenças merge podem ser substituídas, pelo menos em parte, usando CTAS. Pode consolidar o INSERT e o UPDATE numa única instrução. Quaisquer registos eliminados teriam de ser encerrados numa segunda declaração.

Segue-se um exemplo 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. Estado explícito tipo de dado e anulabilidade da saída

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

Ao migrar código SQL Server para o Azure Synapse Analytics, pode encontrar este tipo de padrão de programaçã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, pode pensar que deveria migrar este código para um CTAS e estaria certo. No entanto, há aqui um problema oculto.

O código seguinte 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
;

Note que a coluna "resultado" transporta adiante os valores do tipo de dado e da anulabilidade da expressão. Isto pode levar a variações subtis nos valores se não tiver cuidado.

Experimente o seguinte exemplo:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

O valor armazenado para o resultado é diferente. À medida que o valor persistente na coluna do resultado é usado noutras expressões, o erro torna-se ainda mais significativo.

Isto é importante para migrações de dados. Embora a segunda pergunta seja, provavelmente, mais precisa, há um problema. Os dados seriam diferentes em comparação com o sistema de origem e isso leva a questões de integridade na migração. Este é um daqueles raros casos em que a resposta "errada" é, na verdade, a correta!

A razão pela qual vemos esta disparidade entre os dois resultados deve-se à combinação implícita de tipos. No primeiro exemplo, a tabela define a definição da coluna. Quando a linha é inserida, ocorre uma conversão implícita de tipo. No segundo exemplo, não há conversão implícita de tipo, pois a expressão define o tipo de dado da coluna. Note também que a coluna no segundo exemplo foi definida como uma coluna NULLable, enquanto no primeiro exemplo não foi. Quando a tabela foi criada no primeiro exemplo da coluna, a nulidade foi explicitamente definida. No segundo exemplo, ficava ao critério da expressão e, por defeito, isso resultaria numa NULL definição.

Para resolver estas questões, deve definir explicitamente a conversão de tipo e a nulidade na SELECT parte da CTAS declaração. Não podes definir estas propriedades na parte de criar tabela.

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

Note o seguinte no exemplo:

• CAST ou CONVERT poderiam ter sido usados.
• O ISNULL é usado para forçar a NULLabilidade, não a COALESCE.
• ISNULL é a função mais externa.
• A segunda parte do ISNULL é uma constante, 0.

Observação

Para que a nulidade seja corretamente definida, é vital usar ISNULL e não COALESCE. COALESCE não é uma função determinística e, por isso, o resultado da expressão será sempre NULLável. ISNULL é diferente. É determinista. Portanto, quando a segunda parte da ISNULL função é uma constante ou literal, o valor resultante NÃO SERÁ NULO.

Esta dica não é apenas útil para garantir a integridade dos seus cálculos. Também é importante para a troca de partições de tabelas. Imagine que tem esta tabela definida como o seu facto:

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 valor é uma expressão calculada e não faz parte dos dados de origem.

Para criar o 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 correria perfeitamente. O problema surge quando tentas fazer a troca de partição. As definições das tabelas não coincidem. Para fazer as definições das tabelas, é necessário modificar a correspondência do CTAS.

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');

Pode ver, portanto, que a consistência do tipo e a manutenção das propriedades de anulabilidade num CTAS são boas práticas de engenharia. Ajuda a manter a integridade nos seus cálculos e também garante que a troca de partições é possível.

L. Criar um índice de coluna de armazenamento agrupado ordenado com MAXDOP 1

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

Conteúdo relacionado

• CRIAR FONTE DE DADOS EXTERNA (Transact-SQL)
• CRIAR FORMATO DE ARQUIVO EXTERNO (Transact-SQL)
• CRIAR TABELA EXTERNA (Transact-SQL)
• CRIAR TABELA EXTERNA COMO SELECT (Transact-SQL)
• CREATE TABLE (Azure Synapse Analytics)
• MESA SUSPENSA (Transact-SQL)
• DROP EXTERNAL TABLE (Transact-SQL)
• ALTERAR TABELA (Transact-SQL)
• ALTERAR TABELA EXTERNA (Transact-SQL)