CREATE TABLE AS SELECT (Azure Synapse Analytics)

Se aplica a: síAzure Synapse Analytics síAlmacenamiento de datos paralelos

CREATE TABLE AS SELECT (CTAS) es una de las características más importantes de T-SQL disponibles. Se trata de una operación de ejecución completamente en paralelo que crea una tabla basada en la salida de una instrucción SELECT. CTAS es la forma más sencilla y rápida de crear una copia de una tabla.

Por ejemplo, use CTAS para lo siguiente:

  • Volver a crear una tabla con una columna de distribución de hash diferente.
  • Volver a crear una tabla como replicada.
  • Crear un índice de almacén de columnas en solo algunas columnas de la tabla.
  • Consultar o importar datos externos.

Nota

Dado que CTAS se agrega a las funcionalidades para crear tablas, en este tema se procura no repetir el tema CREATE TABLE. En su lugar, se describen las diferencias entre las instrucciones CTAS y CREATE TABLE. Para obtener todos los detalles sobre CREATE TABLE, vea la instrucción CREATE TABLE (Azure Synapse Analytics).

Nota

El grupo de SQL sin servidor no admite esta sintaxis en Azure Synapse Analytics.

Icono de vínculo de tema Convenciones de sintaxis de Transact-SQL

Sintaxis

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 = 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 más información, vea la sección Argumentos de CREATE TABLE.

Opciones de columna

column_name [ ,...n ]
Los nombres de columna no admiten las opciones de columna mencionadas en CREATE TABLE. En su lugar, puede proporcionar una lista opcional de uno o más nombres de columna para la nueva tabla. Las columnas de la nueva tabla usarán los nombres que especifique. Al especificar nombres de columna, el número de columnas de la lista de columnas debe coincidir con el número de columnas de los resultados de selección. Si no especifica nombres de columna, la nueva tabla de destino usará los nombres de columna de los resultados de la instrucción de selección.

No se pueden especificar otras opciones de columna, como tipos de datos, intercalación o la nulabilidad. Cada uno de estos atributos se deriva de los resultados de la instrucción SELECT. Aun así, puede usar la instrucción SELECT para cambiar los atributos. Para obtener un ejemplo, vea Usar CTAS para cambiar los atributos de columna.

Opciones de distribución de tabla

DISTRIBUTION = HASH ( distribution_column_name ) | ROUND_ROBIN | REPLICATE
La instrucción CTAS requiere una opción de distribución y no tiene valores predeterminados. Es diferente de CREATE TABLE, que tiene valores predeterminados.

Para obtener más información y entender cómo elegir la mejor columna de distribución, vea la sección Opciones de distribución de tabla en CREATE TABLE.

Opciones de partición de tabla

La instrucción CTAS crea una tabla sin particiones de forma predeterminada, incluso si la tabla de origen tiene particiones. Para crear una tabla con particiones con la instrucción CTAS, debe especificar la opción de partición.

Para más información, vea la sección Opciones de partición de tabla en CREATE TABLE.

Select, instrucción

La instrucción de selección es la diferencia fundamental entre CTAS y CREATE TABLE.

WITH common_table_expression
Especifica un conjunto de resultados temporal con nombre, conocido como expresión de tabla común (CTE). Para más información, vea WITH common_table_expression (Transact-SQL).

SELECT select_criteria
Rellena la nueva tabla con los resultados de una instrucción SELECT. select_criteria es el cuerpo de la instrucción SELECT que determina qué datos se copian en la nueva tabla. Para más información sobre las instrucciones SELECT, vea SELECT (Transact-SQL).

Sugerencia de consulta

Los usuarios pueden establecer MAXDOP en un valor entero para controlar el grado máximo de paralelismo. Cuando MAXDOP se establece en 1, la consulta se ejecuta en un único subproceso.

Permisos

CTAS requiere el permiso SELECT en todos los objetos a los que hace referencia en select_criteria.

Para conocer los permisos para crear una tabla, vea Permisos en CREATE TABLE.

Notas generales

Para más información, vea Notas generales en CREATE TABLE.

Limitaciones y restricciones

Se puede crear un índice de almacén de columnas agrupado y ordenado en columnas de cualquier tipo de datos que se admita en Azure Synapse Analytics, excepto en columnas de cadena.

SET ROWCOUNT (Transact-SQL) no tiene ningún efecto en CTAS. Para lograr un comportamiento similar, use TOP (Transact-SQL).

Para más información, vea Limitaciones y restricciones en CREATE TABLE.

Comportamiento del bloqueo

Para más información, vea Comportamiento de bloqueo en CREATE TABLE.

Rendimiento

Para una tabla distribuida de hash, puede usar CTAS para seleccionar una columna de distribución diferente a fin de obtener un mejor rendimiento de las combinaciones y las agregaciones. Si su objetivo no es seleccionar una columna de distribución diferente, tendrá el mejor rendimiento de CTAS si especifica la misma columna de distribución, ya que de este modo evitará que se vuelvan a distribuir las filas.

Si usa CTAS para crear una tabla y el rendimiento no es un factor importante, puede especificar ROUND_ROBIN para evitar tener que elegir una columna de distribución.

Para evitar el movimiento de datos en las consultas posteriores, puede especificar REPLICATE a costa de que aumente el almacenamiento para cargar una copia completa de la tabla en cada nodo de ejecución.

Ejemplos para copiar una tabla

A. Uso de CTAS para copiar una tabla

Se aplica a: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

Quizás uno de los usos más comunes de CTAS es crear una copia de una tabla para poder cambiar el DDL. Si, por ejemplo, creó inicialmente la tabla como ROUND_ROBIN y ahora quiere cambiarla a una tabla distribuida en una columna, cambiaría la columna de distribución con CTAS. CTAS también se puede usar para cambiar los tipos de particiones, indexación o columna.

Supongamos que ha creado esta tabla con el tipo de distribución predeterminado de ROUND_ROBIN distribuido, ya que no se ha especificado ninguna columna de distribución en CREATE TABLE.

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

Ahora quiere crear una copia de esta tabla con un índice de almacén de columnas agrupado para poder aprovechar el rendimiento de las tablas del almacén de columnas agrupado. También quiere distribuir esta tabla en ProductKey, ya que prevé que se producirán combinaciones con esta columna y le interesa impedir el movimiento de datos durante las combinaciones en ProductKey. Por último, también quiere agregar la creación de particiones en OrderDateKey para poder eliminar rápidamente datos antiguos mediante la anulación de particiones antiguas. A continuación se muestra la instrucción CTAS que copiaría la tabla antigua en una nueva tabla.

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 último, puede cambiar el nombre de las tablas para intercambiar la tabla nueva y, después, anular la tabla antigua.

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

DROP TABLE FactInternetSales_old;

Ejemplos de opciones de columna

B. Usar CTAS para cambiar los atributos de columna

Se aplica a: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

En este ejemplo se usa CTAS para cambiar los tipos de datos, la nulabilidad y la intercalación de varias columnas en la tabla 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 paso final, puede usar RENAME (Transact-SQL) para cambiar los nombres de tabla. Esto hace que DimCustomer2 sea la nueva tabla.

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

DROP TABLE DimCustomer2_old;

Ejemplos de distribución de la tabla

C. Usar CTAS para cambiar el método de distribución de una tabla

Se aplica a: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

En este sencillo ejemplo se muestra cómo cambiar el método de distribución de una tabla. Para mostrar la mecánica de cómo se hace, cambia una tabla distribuida de hash a round robin y, después, cambia la tabla round robin de nuevo a distribuida de hash. La tabla final coincide con la tabla original.

En la mayoría de los casos no es necesario convertir una tabla distribuida de hash en una tabla round robin. A menudo, deberá cambiar una tabla round robin a una tabla distribuida de hash. Por ejemplo, es posible que cargue inicialmente una tabla nueva como round robin y, después, la mueva a una tabla distribuida de hash para obtener un mejor rendimiento de combinación.

En este ejemplo se usa la base de datos de ejemplo AdventureWorksDW. Para cargar la versión Azure Synapse Analytics, vea Carga de datos de ejemplo en Azure Synapse Analytics.

-- 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];

Después, cámbiela de nuevo a una tabla distribuida de 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 convertir una tabla en una tabla replicada

Se aplica a: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

En este ejemplo se convierten tablas round robin o distribuidas de hash en una tabla replicada. En este ejemplo en concreto, el método anterior de cambiar el tipo de distribución va un paso más allá. Dado que DimSalesTerritory es una dimensión y es probable que sea una tabla menor, puede volver a crear la tabla como replicada para evitar el movimiento de datos al combinarla con otras tablas.

-- 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 crear una tabla con menos columnas

Se aplica a: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

En el ejemplo siguiente se crea una tabla distribuida round robin denominada myTable (c, ln). La nueva tabla solo tiene dos columnas. Usa los alias de columna en la instrucción SELECT para los nombres de las columnas.

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

Ejemplos de sugerencias de consulta

F. Usar una sugerencia de consulta con CREATE TABLE AS SELECT (CTAS)

Se aplica a: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

Esta consulta muestra la sintaxis básica para usar una sugerencia de combinación de consulta con la instrucción CTAS. Una vez enviada la consulta, Azure Synapse Analytics aplica la estrategia de combinación hash al generar el plan de consulta para cada distribución individual. Para más información sobre la sugerencia de consulta de combinación hash, vea 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 );  

Ejemplos de tablas externas

G. Usar CTAS para importar datos de Azure Blob Storage

Se aplica a: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

Para importar datos de una tabla externa, basta con que use CREATE TABLE AS SELECT para hacer una selección en la tabla externa. La sintaxis para seleccionar datos de una tabla externa en Azure Synapse Analytics es igual que la sintaxis para seleccionar datos de una tabla normal.

En el ejemplo siguiente se define una tabla externa en los datos de una cuenta de Azure Blob Storage. Después, se usa CREATE TABLE AS SELECT para hacer una selección en la tabla externa. De este modo se importan los datos de los archivos delimitados de texto de Azure Blob Storage y se almacenan los datos en una nueva tabla de 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 datos de Hadoop de una tabla externa

Se aplica a: Sistema de la plataforma de análisis (PDW)

Para importar datos de una tabla externa, basta con que use CREATE TABLE AS SELECT para hacer una selección en la tabla externa. La sintaxis para seleccionar datos de una tabla externa en Sistema de la plataforma de análisis (PDW) es igual que la sintaxis para seleccionar datos de una tabla normal.

En el ejemplo siguiente se define una tabla externa en un clúster de Hadoop. Después, se usa CREATE TABLE AS SELECT para hacer una selección en la tabla externa. De este modo se importan los datos de los archivos delimitados de texto de Hadoop y se almacenan los datos en una nueva tabla de Sistema de la plataforma de análisis (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  
;   

Ejemplos del uso de CTAS para reemplazar código de SQL Server

Use CTAS para hallar soluciones temporales para algunas características no admitidas. Además de permitirle ejecutar el código en el almacén de datos, la reescritura del código existente para usar CTAS suele mejorar el rendimiento. Esto se debe a su diseño totalmente paralelizado.

Nota

Intente dar prioridad a CTAS. Lo más recomendable es resolver los problemas mediante CTAS, incluso si como resultado debe escribir más datos.

I. Usar CTAS en lugar de SELECT..EN

Se aplica a: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

El código de SQL Server suele usar SELECT..INTO para rellenar una tabla con los resultados de una instrucción SELECT. Este es un ejemplo de una instrucción SELECT..INTO de SQL Server.

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

Esta sintaxis no se admite en Azure Synapse Analytics ni en Almacenamiento de datos paralelos. En este ejemplo se muestra cómo reescribir la anterior instrucción SELECT..INTO como una instrucción CTAS. Puede elegir cualquiera de las opciones DISTRIBUTION que se describen en la sintaxis CTAS. En este ejemplo se usa el método de distribución ROUND_ROBIN.

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

J. Usar CTAS y combinaciones implícitas para reemplazar combinaciones ANSI en la cláusula FROM de una instrucción UPDATE

Se aplica a: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

Es posible que se encuentre con una actualización compleja que combina más de dos tablas mediante la sintaxis de combinación de ANSI para llevar a cabo UPDATE o DELETE.

Imagine que tiene que actualizar esta tabla:

CREATE TABLE [dbo].[AnnualCategorySales]
(   [EnglishProductCategoryName]    NVARCHAR(50)    NOT NULL
,   [CalendarYear]          SMALLINT    NOT NULL
,   [TotalSalesAmount]      MONEY       NOT NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN
)
;

La consulta original podría haber tenido un aspecto como este:

UPDATE  acs
SET     [TotalSalesAmount] = [fis].[TotalSalesAmount]
FROM    [dbo].[AnnualCategorySales]     AS acs
JOIN    (
        SELECT  [EnglishProductCategoryName]
        ,       [CalendarYear]
        ,       SUM([SalesAmount])              AS [TotalSalesAmount]
        FROM    [dbo].[FactInternetSales]       AS s
        JOIN    [dbo].[DimDate]                 AS d    ON s.[OrderDateKey]             = d.[DateKey]
        JOIN    [dbo].[DimProduct]              AS p    ON s.[ProductKey]               = p.[ProductKey]
        JOIN    [dbo].[DimProductSubCategory]   AS u    ON p.[ProductSubcategoryKey]    = u.[ProductSubcategoryKey]
        JOIN    [dbo].[DimProductCategory]      AS c    ON u.[ProductCategoryKey]       = c.[ProductCategoryKey]
        WHERE   [CalendarYear] = 2004
        GROUP BY
                [EnglishProductCategoryName]
        ,       [CalendarYear]
        ) AS fis
ON  [acs].[EnglishProductCategoryName]  = [fis].[EnglishProductCategoryName]
AND [acs].[CalendarYear]                = [fis].[CalendarYear]
;

Dado que Azure Synapse Analytics no admite combinaciones ANSI en la cláusula FROM de una instrucción UPDATE, no se puede usar este código de SQL Server sin cambiarlo ligeramente.

Puede usar una combinación de una instrucción CTAS y una combinación implícita para reemplazar este código:

-- Create an interim table
CREATE TABLE CTAS_acs
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT  ISNULL(CAST([EnglishProductCategoryName] AS NVARCHAR(50)),0)    AS [EnglishProductCategoryName]
,       ISNULL(CAST([CalendarYear] AS SMALLINT),0)                      AS [CalendarYear]
,       ISNULL(CAST(SUM([SalesAmount]) AS MONEY),0)                     AS [TotalSalesAmount]
FROM    [dbo].[FactInternetSales]       AS s
JOIN    [dbo].[DimDate]                 AS d    ON s.[OrderDateKey]             = d.[DateKey]
JOIN    [dbo].[DimProduct]              AS p    ON s.[ProductKey]               = p.[ProductKey]
JOIN    [dbo].[DimProductSubCategory]   AS u    ON p.[ProductSubcategoryKey]    = u.[ProductSubcategoryKey]
JOIN    [dbo].[DimProductCategory]      AS c    ON u.[ProductCategoryKey]       = c.[ProductCategoryKey]
WHERE   [CalendarYear] = 2004
GROUP BY
        [EnglishProductCategoryName]
,       [CalendarYear]
;

-- Use an implicit join to perform the update
UPDATE  AnnualCategorySales
SET     AnnualCategorySales.TotalSalesAmount = CTAS_ACS.TotalSalesAmount
FROM    CTAS_acs
WHERE   CTAS_acs.[EnglishProductCategoryName] = AnnualCategorySales.[EnglishProductCategoryName]
AND     CTAS_acs.[CalendarYear]               = AnnualCategorySales.[CalendarYear]
;

--Drop the interim table
DROP TABLE CTAS_acs
;

K. Usar CTAS para especificar qué datos se deben conservar en lugar de usar combinaciones ANSI en la cláusula FROM de una instrucción DELETE

Se aplica a: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

A veces, lo más recomendable para eliminar datos es usar CTAS. En lugar de eliminar los datos, simplemente seleccione los datos que quiere conservar. Esto es especialmente aconsejable en el caso de las instrucciones DELETE que usan sintaxis de combinación ANSI, ya que Azure Synapse Analytics no admite combinaciones ANSI en la cláusula FROM de una instrucción DELETE.

A continuación se muestra un ejemplo de una instrucción DELETE convertida:

CREATE TABLE dbo.DimProduct_upsert
WITH
(   Distribution=HASH(ProductKey)
,   CLUSTERED INDEX (ProductKey)
)
AS -- Select Data you wish to keep
SELECT     p.ProductKey
,          p.EnglishProductName
,          p.Color
FROM       dbo.DimProduct p
RIGHT JOIN dbo.stg_DimProduct s
ON         p.ProductKey = s.ProductKey
;

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

L. Usar CTAS para simplificar las instrucciones de fusión

Se aplica a: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

Las instrucciones de fusión se pueden sustituir, al menos en parte, mediante CTAS. Es posible consolidar INSERT y UPDATE en una única instrucción. Los registros eliminados deben cerrarse en una segunda instrucción.

A continuación se muestra un ejemplo 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];

M. establezca explícitamente el tipo de datos y la nulabilidad de salida

Se aplica a: Azure Synapse Analytics y Sistema de la plataforma de análisis (PDW)

Al migrar código de SQL Server a Azure Synapse Analytics, puede encontrarse con este tipo de patrón de codificación:

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 pensará que debe migrar este código a una instrucción CTAS, y tendría razón. Lo que pasa es que hay un problema oculto.

El código siguiente NO produce el mismo 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 la columna "result" traslada el tipo de datos y los valores de nulabilidad de la expresión. Esto puede provocar sutiles variaciones en los valores si no tiene cuidado.

Pruebe por ejemplo lo siguiente:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

El valor almacenado para el resultado es diferente. Dado que el valor almacenado en la columna de resultados se usa en otras expresiones, el error es todavía mayor.

Resultados de CREATE TABLE AS SELECT

Esto es especialmente importante para las migraciones de datos. Aunque podría decirse que la segunda consulta es más precisa, hay un problema. Los datos serían diferentes en comparación con el sistema de origen, lo que produce problemas de integridad en la migración. Este es uno de los pocos casos en los que la respuesta "incorrecta" es en realidad la correcta.

El motivo por el que vemos esta discrepancia entre los dos resultados se debe a la conversión de tipos implícita. En el primer ejemplo, la tabla establece la definición de columna. Cuando se inserta la fila, se realiza una conversión de tipos implícita. En el segundo ejemplo no hay ninguna conversión de tipos implícita, ya que la expresión define el tipo de datos de la columna. Observe también que la columna del segundo ejemplo se ha definido como una columna que acepta valores NULL, al contrario que en el primer ejemplo. Al crear la tabla en el primer ejemplo, se definió explícitamente la nulabilidad de la columna. En el segundo ejemplo, esto le correspondía a la expresión, lo que conllevaba una definición NULL de forma predeterminada.

Para resolver estos problemas, debe establecer explícitamente la conversión de tipos y la nulabilidad en la parte SELECT de la instrucción CTAS. No pueden establecer estas propiedades en la parte de creación de la tabla.

En el ejemplo siguiente se muestra cómo corregir el 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

Tenga en cuenta lo siguiente:

  • Podían haberse usado CAST o CONVERT.
  • Se usa ISNULL para forzar la nulabilidad, no COALESCE.
  • ISNULL es la función más externa.
  • La segunda parte de ISNULL es una constante, es decir, 0.

Nota

Para que la nulabilidad se establezca correctamente, es fundamental usar ISNULL, no COALESCE. COALESCE no es una función determinista, por lo que el resultado de la expresión siempre aceptará valores NULL. ISNULL es diferente, ya que es determinista. Por lo tanto, cuando la segunda parte de la función ISNULL es una constante o un literal, el valor resultante será NOT NULL.

Esta sugerencia no solo es útil para garantizar la integridad de los cálculos, sino que también es importante para la modificación de particiones de tabla. Imagine que ha definido como hecho esta tabla:

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

Pero el campo de valor es una expresión calculada, no forma parte del origen de datos.

Para crear el conjunto de datos con particiones, tal vez haría algo como esto:

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

La consulta se ejecutaría perfectamente. El problema se produce al intentar realizar la modificación de particiones. Las definiciones de tabla no coinciden. Para que las definiciones de tabla coincidan, es necesario modificar la instrucción 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');

Por lo tanto, puede ver que la coherencia de los tipos y el mantenimiento de las propiedades de nulabilidad en una instrucción CTAS es un procedimiento recomendado de ingeniería. Ayuda a mantener la integridad de los cálculos y también garantiza que la modificación de particiones sea posible.

Hora Creación de un índice ordenado de almacén de columnas agrupado con MAXDOP 1

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

Consulte también

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)