Implementando UPDATE com FROM ou subconsultas

Aplica-se a: SQL Server Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure

Na instrução UPDATE Transact-SQL, em um módulo do T-SQL compilado nativamente, não há suporte para os seguintes elementos de sintaxe:

• A cláusula FROM
• Subconsultas

Por outro lado, os elementos anteriores têm suporte em módulos compilados nativamente na instrução SELECT.

Instruções UPDATE com uma cláusula FROM muitas vezes são usadas para atualizar informações em uma tabela baseada em um TVP (parâmetro com valor de tabela) ou para atualizar colunas em uma tabela em um gatilho AFTER.

Para o cenário da atualização baseada em um TVP, consulte Implementando a funcionalidade MERGE em um procedimento armazenado compilado de forma nativa.

O exemplo a seguir ilustra uma atualização executada em um gatilho. Na tabela, a coluna denominada LastUpdated é definida como a data e hora atual após as atualizações. A solução alternativa executa atualizações individuais usando os seguintes itens:

• Uma variável de tabela que tem uma coluna IDENTITY.
• Um loop WHILE para fazer a iteração das linhas na variável de tabela.

Aqui está a instrução T-SQL UPDATE original:

 UPDATE dbo.Table1  
     SET LastUpdated = SysDateTime()  
     FROM  
         dbo.Table1 t  
         JOIN Inserted i ON t.Id = i.Id;  

O código T-SQL de exemplo no seguinte bloco demonstra uma solução alternativa que fornece um bom desempenho. A solução alternativa é implementada em um gatilho compilado de forma nativa. Os itens cruciais para observar no código são:

• O tipo chamado dbo.Type1, que é um tipo de tabela com otimização de memória.
• O loop WHILE no gatilho.
  • O loop recupera as linhas de Inserted uma de cada vez.

DROP TABLE IF EXISTS dbo.Table1;  
GO

DROP TYPE IF EXISTS dbo.Type1;  
GO

-----------------------------
-- Table and table type.
-----------------------------
CREATE TABLE dbo.Table1 (
   Id INT NOT NULL PRIMARY KEY NONCLUSTERED,
   Column2 INT NOT NULL,
   LastUpdated DATETIME2 NOT NULL DEFAULT(SYSDATETIME())
)
WITH (MEMORY_OPTIMIZED = ON);
GO

CREATE TYPE dbo.Type1 AS TABLE (
   Id INT NOT NULL,
   RowID INT NOT NULL IDENTITY,
   INDEX ix_RowID HASH (RowID) WITH (BUCKET_COUNT = 1024)
)
WITH (MEMORY_OPTIMIZED = ON);
GO

----------------------------------------
-- Trigger that contains the workaround
-- for UPDATE with FROM.
----------------------------------------
CREATE TRIGGER dbo.tr_a_u_Table1 ON dbo.Table1
   WITH NATIVE_COMPILATION, SCHEMABINDING
   AFTER UPDATE
AS
BEGIN
   ATOMIC
   WITH (
       TRANSACTION ISOLATION LEVEL = SNAPSHOT,
       LANGUAGE = N'us_english'
   )

   DECLARE @tabvar1 dbo.Type1;

   INSERT @tabvar1 (Id)
   SELECT Id
   FROM Inserted;

   DECLARE @i INT = 1,
       @Id INT,
       @max INT = SCOPE_IDENTITY();

   ---- Loop as a workaround to simulate a cursor.
   ---- Iterate over the rows in the memory-optimized table  
   ----   variable and perform an update for each row.  
   WHILE @i <= @max
   BEGIN
       SELECT @Id = Id
       FROM @tabvar1
       WHERE RowID = @i;

       UPDATE dbo.Table1
       SET LastUpdated = SysDateTime()
       WHERE Id = @Id;

       SET @i += 1;
   END
END
GO

---------------------------------
-- Test to verify functionality.
---------------------------------
SET NOCOUNT ON;

INSERT dbo.Table1 (Id, Column2)
VALUES (1, 9), (2, 9), (3, 600);

SELECT N'BEFORE-Update' AS [BEFORE-Update], *
FROM dbo.Table1
ORDER BY Id;

WAITFOR DELAY '00:00:01';

UPDATE dbo.Table1
SET Column2 += 1
WHERE Column2 <= 99;

SELECT N'AFTER--Update' AS [AFTER--Update], *
FROM dbo.Table1
ORDER BY Id;
GO

Veja a seguir o conjunto de resultados.

BEFORE-Update   Id   Column2   LastUpdated  
BEFORE-Update   1       9      2016-04-20 21:18:42.8394659  
BEFORE-Update   2       9      2016-04-20 21:18:42.8394659  
BEFORE-Update   3     600      2016-04-20 21:18:42.8394659  

AFTER--Update   Id   Column2   LastUpdated  
AFTER--Update   1      10      2016-04-20 21:18:43.8529692  
AFTER--Update   2      10      2016-04-20 21:18:43.8529692  
AFTER--Update   3     600      2016-04-20 21:18:42.8394659