CREATE TABLE AS SELECT

  • Artigo

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

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

Por exemplo, utilize o CTAS para:

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

Nota

Uma vez que o CTAS adiciona às capacidades de criação de uma tabela, este tópico tenta não repetir o tópico CRIAR TABELA. Em vez disso, descreve as diferenças entre as instruções CTAS e CREATE TABLE. Para obter os detalhes CREATE TABLE, consulte a instrução CREATE TABLE (Azure Synapse Analytics).

  • Esta sintaxe não é suportada pelo conjunto de SQL sem servidor no Azure Synapse Analytics.
  • O CTAS é suportado no Armazém no Microsoft Fabric.

Convenções de sintaxe 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, veja a secção Argumentos em CRIAR TABELA.

Opções de coluna

column_name [ ,...n ]
Os nomes das colunas não permitem as opções de coluna mencionadas em CRIAR TABELA. Em vez disso, pode fornecer uma lista opcional de um ou mais nomes de coluna para a nova tabela. As colunas na nova tabela utilizam os nomes que especificar. Quando especificar nomes de colunas, o número de colunas na lista de colunas tem de corresponder ao número de colunas nos resultados selecionados. Se não especificar nomes de colunas, a nova tabela de destino utiliza os nomes das colunas nos resultados da instrução select.

Não pode especificar outras opções de coluna, como tipos de dados, agrupamento ou nulo. Cada um destes atributos é derivado dos resultados da SELECT instrução . No entanto, pode utilizar a instrução SELECT para alterar os atributos. Por exemplo, veja Utilizar CTAS para alterar atributos de coluna.

Opções de distribuição de tabelas

Para obter detalhes e compreender como escolher a melhor coluna de distribuição, consulte a secção Opções de distribuição de tabelas em CREATE TABLE. Para obter recomendações sobre a distribuição a escolher para uma tabela com base na utilização real ou em consultas de exemplo, veja Distribution Advisor in Azure Synapse SQL (Assistente de Distribuição no SQL do Azure Synapse).

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 de CREATE TABLE, que tem predefinições.

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

Nota

  • Para ativar a funcionalidade, altere o nível de compatibilidade da base de dados para 50 com este comando. Para obter mais informações sobre como definir o nível de compatibilidade da base de dados, veja ALTER DATABASE SCOPED CONFIGURATION (ALTERAR CONFIGURAÇÃO DO ÂMBITO DA BASE DE DADOS). Por exemplo: ALTER DATABASE SCOPED CONFIGURATION SET DW_COMPATIBILITY_LEVEL = 50;
  • Para desativar a funcionalidade de distribuição de várias 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 permanecerão, mas tornar-se-ão ilegíveis. As consultas em tabelas MCD irão devolver 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 para uma tabela MCD, utilize a instrução CTAS e a origem de dados tem de ser tabelas SQL do Synapse.
    • O CTAS nas tabelas de destino HEAP do MCD não é suportado. Em vez disso, utilize INSERT SELECT como solução para carregar dados para tabelas HEAP mcD.
  • A utilização do SSMS para gerar um script para criar tabelas MCD é atualmente suportada para além da versão 19 do SSMS.

Para obter detalhes e compreender como escolher a melhor coluna de distribuição, consulte a secção Opções de distribuição de tabelas em CREATE TABLE.

Opções de partição de tabela

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

Para obter detalhes, consulte a secção Opções de partição de tabela em CRIAR TABELA.

Instrução SELECT

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

WITHcommon_table_expression

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

SELECTselect_criteria

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

Sugestão de consulta

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

Permissões

O CTAS requer SELECT permissão em quaisquer objetos referenciados no select_criteria.

Para obter permissões para criar uma tabela, veja Permissões em CRIAR TABELA.

Observações

Para obter detalhes, consulte Observações Gerais em CRIAR TABELA.

Limitações e restrições

Um índice columnstore agrupado ordenado pode ser criado em colunas de quaisquer tipos de dados suportados no Azure Synapse Analytics, exceto nas colunas de cadeia.

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

Para obter detalhes, veja Limitações e Restrições em CRIAR TABELA.

Comportamento de bloqueio

Para obter detalhes, veja Comportamento de Bloqueio em CRIAR TABELA.

Desempenho

Para uma tabela distribuída por hash, pode utilizar o CTAS para escolher uma coluna de distribuição diferente para obter um melhor desempenho para associações e agregações. Se escolher uma coluna de distribuição diferente não for o seu objetivo, terá o melhor desempenho de CTAS se especificar a mesma coluna de distribuição, uma vez que evitará redistribuir as linhas.

Se estiver a utilizar o CTAS para criar uma tabela e o desempenho não for um fator, pode especificar ROUND_ROBIN para evitar ter de decidir sobre uma coluna de distribuição.

Para evitar o movimento de dados em consultas subsequentes, pode especificar REPLICATE à custa do aumento do armazenamento para carregar uma cópia completa da tabela em cada nó de Computação.

Exemplos para copiar uma tabela

A. Utilizar o CTAS para copiar uma tabela

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

Talvez uma das utilizações mais comuns de CTAS seja criar uma cópia de uma tabela para que possa alterar o DDL. Se, por exemplo, tiver criado originalmente a tabela como ROUND_ROBIN e quiser alterá-la para uma tabela distribuída numa coluna, CTAS é assim que alteraria a coluna de distribuição. CTAS também pode ser utilizado para alterar a criação de partições, indexação ou tipos de coluna.

Suponhamos que criou esta tabela ao especificar HEAP e utilizar o tipo de distribuição predefinido 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, quer criar uma nova cópia desta tabela com um índice columnstore em cluster para que possa tirar partido do desempenho das tabelas columnstore em cluster. Também quer distribuir esta tabela, ProductKey uma vez que está a antecipar associações nesta coluna e quer evitar o movimento de dados durante as associações no ProductKey. Por último, também quer adicionar a OrderDateKey criação de partições para que possa eliminar rapidamente dados antigos ao remover partições antigas. Eis a instruçã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;

Por fim, pode mudar o nome das tabelas para trocar na nova tabela e, em seguida, largar 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. Utilizar o CTAS para alterar atributos de coluna

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

Este exemplo utiliza o CTAS para alterar tipos de dados, nullability e agrupamento para várias colunas na 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, pode utilizar o rename (Transact-SQL) para mudar os nomes das tabelas. Isto faz com que DimCustomer2 seja a nova tabela.

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

DROP TABLE DimCustomer2_old;

Exemplos de distribuição de tabelas

C. Utilizar 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 fazê-lo, altera uma tabela distribuída por hash para round robin e, em seguida, altera a tabela round robin novamente para distribuída por hash. A tabela final corresponde à tabela original.

Na maioria dos casos, não precisa de alterar uma tabela distribuída por hash para uma tabela round robin. Mais frequentemente, poderá ter de alterar uma tabela round robin para uma tabela distribuída por hash. Por exemplo, pode carregar inicialmente uma nova tabela como round robin e, mais tarde, movê-la para uma tabela distribuída por hash para obter um melhor desempenho de associação.

Este exemplo utiliza a base de dados de exemplo AdventureWorksDW. Para carregar a versão do Azure Synapse Analytics, veja Início Rápido: Criar e consultar um conjunto de SQL dedicado (anteriormente SQL DW) no Azure Synapse Analytics com 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, altere-a novamente 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. Utilizar o CTAS para converter uma tabela numa tabela replicada

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

Este exemplo aplica-se à conversão de tabelas distribuídas por round robin ou hash numa tabela replicada. Este exemplo específico leva o método anterior de alterar o tipo de distribuição um passo mais além. Uma DimSalesTerritory vez que é uma dimensão e, provavelmente, uma tabela mais pequena, pode optar por recriar a tabela como replicada para evitar o movimento de dados ao associar 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. Utilizar 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 com o nome myTable (c, ln). A nova tabela tem apenas duas colunas. Utiliza 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 sugestões de consulta

F. Utilizar uma Sugestão de Consulta com CREATE TABLE AS SELECT (CTAS)

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

Esta consulta mostra a sintaxe básica para utilizar uma sugestão de associação de consulta com a instrução CTAS. Depois de a consulta ser submetida, o Azure Synapse Analytics aplica a estratégia de associação hash quando gera o plano de consulta para cada distribuição individual. Para obter mais informações sobre a sugestão de consulta de associação hash, veja 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. Utilizar o CTAS para importar dados do armazenamento de Blobs do Azure

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

Para importar dados de uma tabela externa, utilize CREATE TABLE AS SELECT para selecionar a partir da tabela externa. A sintaxe para selecionar dados de uma tabela externa para o Azure Synapse Analytics é igual à sintaxe para selecionar dados de uma tabela normal.

O exemplo seguinte define uma tabela externa sobre dados numa conta Armazenamento de Blobs do Azure. Em seguida, utiliza CREATE TABLE AS SELECT para selecionar a partir da tabela externa. Isto importa os dados de Armazenamento de Blobs do Azure ficheiros delimitados por texto e armazena os dados numa 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. Utilizar o CTAS para importar dados do Hadoop a partir de uma tabela externa

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

Para importar dados de uma tabela externa, basta utilizar CREATE TABLE AS 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 que a sintaxe para selecionar dados de uma tabela normal.

O exemplo seguinte define uma tabela externa num cluster do Hadoop. Em seguida, utiliza CREATE TABLE AS SELECT para selecionar a partir da tabela externa. Isto importa os dados de ficheiros delimitados por texto do Hadoop 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 de utilização do CTAS para substituir SQL Server código

Utilize o CTAS para contornar algumas funcionalidades não suportadas. Além de poder executar o código no armazém de dados, reescrever o código existente para utilizar o CTAS irá, normalmente, melhorar o desempenho. Isto é o resultado do design totalmente paralelizado.

Nota

Tente pensar em "CTAS em primeiro lugar". Se acha que pode resolver um problema com CTAS essa é geralmente a melhor forma de o abordar, mesmo que esteja a escrever mais dados como resultado.

I. Utilize CTAS em vez de SELECT.. INTO

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

SQL Server código normalmente utiliza SELECT.. INTO para preencher uma tabela com os resultados de uma instrução SELECT. Este é um exemplo de um SQL Server SELECT.. Instrução 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. Instrução INTO como uma instrução CTAS. Pode escolher qualquer uma das opções de DISTRIBUIÇÃO 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. Utilizar o CTAS para simplificar as instruções de intercalação

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

As instruções de intercalação podem ser substituídas, pelo menos em parte, com CTAS. Pode consolidar o INSERT e o UPDATE numa única instrução. Todos os registos eliminados teriam de ser encerrados numa segunda instrução.

Um exemplo do UPSERT seguinte:

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. Indicar explicitamente o tipo de dados e a nulobilidade da saída

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

Ao migrar SQL Server código para o Azure Synapse Analytics, poderá descobrir que executa 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, pode pensar que deve migrar este código para um CTAS e estaria correto. No entanto, existe um problema oculto aqui.

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
;

Repare que a coluna "result" encaminha os valores de tipo de dados e de nulo da expressão. Isto pode levar a desvios subtis em valores se não tiver cuidado.

Experimente o seguinte como 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 de resultados é utilizado noutras expressões, o erro torna-se ainda mais significativo.

Uma captura de ecrã do SQL Server Management Studio (SSMS) dos resultados CREATE TABLE AS SELECT.

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

A razão pela qual vemos esta disparidade entre os dois resultados deve-se à conversão de tipo implícito. 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 existe nenhuma conversão implícita de tipo, uma vez que a expressão define o tipo de dados da coluna. Repare também que a coluna no segundo exemplo foi definida como uma coluna NULLable, ao passo que, no primeiro exemplo, não foi. Quando a tabela foi criada no primeiro exemplo, a nullability da coluna foi explicitamente definida. No segundo exemplo, foi deixado para a expressão e, por predefinição, tal resultaria numa NULL definição.

Para resolver estes problemas, tem de definir explicitamente a conversão de tipo e a SELECT nullabilidade na parte da CTAS instrução. Não pode definir estas propriedades na parte 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

Tenha em atenção o seguinte no exemplo:

  • A função CAST ou CONVERT poderia ter sido utilizada.
  • A ISULL é utilizada para forçar a NULabilidade e não a COALESCE.
  • ISNULL é a função mais externa.
  • A segunda parte da ISULL é uma constante, 0.

Nota

Para que a nullability seja definida corretamente, é essencial utilizar ISNULL e não COALESCE. COALESCE não é uma função determinista, pelo que o resultado da expressão será sempre NULável. ISNULL é diferente. É determinista. Portanto, quando a segunda parte da ISNULL função é uma constante ou um literal, o valor resultante não será NULO.

Esta sugestão não é apenas útil para garantir a integridade dos seus cálculos. Também é importante para a mudança de partições de tabelas. Imagine que tem esta tabela definida como 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 de valor é uma expressão calculada que não faz parte dos dados de origem.

Para criar o 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 perfeitamente executada. O problema surge quando tenta executar o comutador de partição. As definições da tabela não correspondem. Para fazer as definições da tabela, corresponda ao CTAS tem de 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');

Por conseguinte, pode ver que a consistência do tipo e a manutenção das propriedades de nullability num CTAS é uma boa prática de engenharia. Ajuda a manter a integridade nos seus cálculos e também garante que a mudança de partições é possível.

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

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

Passos seguintes

Aplica-se a:Armazém no Microsoft Fabric

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

Por exemplo, utilize o CTAS no Warehouse no Microsoft Fabric para:

  • Crie uma cópia de uma tabela com algumas das colunas da tabela de origem.
  • Crie uma tabela que seja o resultado de uma consulta que associe outras tabelas.

Para obter mais informações sobre como utilizar o CTAS no seu Armazém no Microsoft Fabric, veja Ingerir dados no seu Armazém com o TSQL.

Nota

Uma vez que o CTAS adiciona às capacidades de criação de uma tabela, este tópico tenta não repetir o tópico CRIAR TABELA. Em vez disso, descreve as diferenças entre as instruções CTAS e CREATE TABLE. Para obter os detalhes DE CREATE TABLE, consulte a instrução CREATE TABLE .

Convenções de sintaxe Transact-SQL

Sintaxe

CREATE TABLE { database_name.schema_name.table_name | schema_name.table_name | table_name }
    AS <select_statement>  
[;]  

<select_statement> ::=  
    SELECT select_criteria

Argumentos

Para obter detalhes, consulte os Argumentos em CRIAR TABELA para o Microsoft Fabric.

Opções de coluna

column_name [ ,...n ]
Os nomes das colunas não permitem as opções de coluna mencionadas em CRIAR TABELA. Em vez disso, pode fornecer uma lista opcional de um ou mais nomes de coluna para a nova tabela. As colunas na nova tabela utilizam os nomes que especificar. Quando especificar nomes de colunas, o número de colunas na lista de colunas tem de corresponder ao número de colunas nos resultados selecionados. Se não especificar nomes de colunas, a nova tabela de destino utiliza os nomes das colunas nos resultados da instrução select.

Não pode especificar outras opções de coluna, como tipos de dados, agrupamento ou nulo. Cada um destes atributos é derivado dos resultados da SELECT instrução . No entanto, pode utilizar a instrução SELECT para alterar os atributos.

Instrução SELECT

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

SELECTselect_criteria

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

Permissões

O CTAS requer SELECT permissão em quaisquer objetos referenciados no select_criteria.

Para obter permissões para criar uma tabela, veja Permissões em CRIAR TABELA.

Observações

Para obter detalhes, consulte Observações Gerais em CRIAR TABELA.

Limitações e restrições

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

Para obter detalhes, veja Limitações e Restrições em CRIAR TABELA.

Comportamento de bloqueio

Para obter detalhes, veja Comportamento de Bloqueio em CRIAR TABELA.

Exemplos para copiar uma tabela

Para obter mais informações sobre como utilizar o CTAS no seu Armazém no Microsoft Fabric, veja Ingerir dados no seu Armazém com o TSQL.

A. Utilizar o CTAS para alterar atributos de coluna

Este exemplo utiliza o CTAS para alterar os tipos de dados e a nullabilidade de várias colunas na DimCustomer2 tabela.

-- Original table 
CREATE TABLE [dbo].[DimCustomer2] (  
    [CustomerKey] INT NOT NULL,  
    [GeographyKey] INT NULL,  
    [CustomerAlternateKey] VARCHAR(15)NOT NULL  
)  

-- CTAS example to change data types and nullability of columns
CREATE TABLE test  
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,  
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] VARCHAR(15) NOT NULL, 
    [CustomerAlternateKeyNullable] VARCHAR(15) NULL, 
NOT NULL
)

B. Utilizar o CTAS para criar uma tabela com menos colunas

O exemplo seguinte cria uma tabela com o nome myTable (c, ln). A nova tabela tem apenas duas colunas. Utiliza os aliases de coluna na instrução SELECT para os nomes das colunas.

CREATE TABLE myTable  
AS SELECT CustomerKey AS c, LastName AS ln  
    FROM dimCustomer;

C. Utilize CTAS em vez de SELECT.. INTO

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

SELECT *
INTO    NewFactTable
FROM    [dbo].[FactInternetSales]

Este exemplo mostra como reescrever o SELECT anterior. Instrução INTO como uma instrução CTAS.

CREATE TABLE NewFactTable
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
;

D. Utilizar o CTAS para simplificar as instruções de intercalação

As instruções de intercalação podem ser substituídas, pelo menos em parte, com CTAS. Pode consolidar o INSERT e o UPDATE numa única instrução. Todos os registos eliminados teriam de ser encerrados numa segunda instrução.

Um exemplo do UPSERT seguinte:

CREATE TABLE dbo.[DimProduct_upsert]
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]
)
;

E. Indicar explicitamente o tipo de dados e a nullabilidade da saída

Ao migrar SQL Server código para o Armazém, poderá descobrir que executa 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)

INSERT INTO result
SELECT @d*@f
;

Instintivamente, poderá pensar que deve migrar este código para um CTAS e estaria correto. No entanto, existe um problema oculto aqui.

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
AS
SELECT @d*@f as result
;

Repare que a coluna "resultado" encaminha o tipo de dados e os valores de nullabilidade da expressão. Isto pode levar a variâncias subtis em valores se não tiver cuidado.

Experimente o seguinte como 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 de resultados é utilizado noutras expressões, o erro torna-se ainda mais significativo.

Uma captura de ecrã a partir de SQL Server Management Studio (SSMS) dos resultados CREATE TABLE AS SELECT.

Isto é importante 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 e isso levaria a questões de integridade na migração. Este é um daqueles casos raros em que a resposta "errada" é, na verdade, a certa!

A razão pela qual vemos esta disparidade entre os dois resultados deve-se à função de fundição de tipo implícito. No primeiro exemplo, a tabela define a definição da coluna. Quando a linha é inserida, ocorre uma conversão de tipo implícito. No segundo exemplo, não existe nenhuma conversão de tipo implícito, uma vez que a expressão define o tipo de dados da coluna. Repare também que a coluna no segundo exemplo foi definida como uma coluna NULLável, ao passo que, no primeiro exemplo, não o fez. Quando a tabela foi criada no primeiro exemplo, a nullabilidade da coluna foi explicitamente definida. No segundo exemplo, ficou na expressão e, por predefinição, tal resultaria numa NULL definição.

Para resolver estes problemas, tem de definir explicitamente o tipo de conversão e a nullabilidade na SELECT parte da CTAS instrução. Não pode definir estas propriedades na parte 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
AS
SELECT ISNULL(CAST(@d*@f AS DECIMAL(7,2)),0) as result

Tenha em atenção o seguinte no exemplo:

  • A função CAST ou CONVERT poderia ter sido utilizada.
  • A ISULL é utilizada para forçar a NULabilidade e não a COALESCE.
  • ISNULL é a função mais externa.
  • A segunda parte do ISULL é uma constante, 0.

Nota

Para que a nullabilidade seja definida corretamente, é vital utilizar ISNULL e não COALESCE. COALESCE não é uma função determinista, pelo que o resultado da expressão será sempre NULável. ISNULL é diferente. É determinista. Portanto, quando a segunda parte da ISNULL função é uma constante ou um literal, o valor resultante não será NULO.

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

CREATE TABLE [dbo].[Sales]
(
    [date]      INT     NOT NULL
,   [product]   INT     NOT NULL
,   [store]     INT     NOT NULL
,   [quantity]  INT     NOT NULL
,   [price]     DECIMAL(7,2)   NOT NULL
,   [amount]    DECIMAL(7,2)   NOT NULL
)
;

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

Por conseguinte, pode ver que a consistência do tipo e a manutenção de propriedades de nullabilidade num CTAS é uma boa prática de engenharia. Ajuda a manter a integridade nos seus cálculos.

Passos seguintes