Implémentation d’UPDATE avec FROM ou des sous-requêtes

S’applique à : SQL Server Azure SQL Database Azure SQL Managed Instance

Dans l’instruction Transact-SQL UPDATE, dans un module T-SQL compilé en mode natif, les éléments syntaxiques suivants ne sont pas pris en charge :

• la Clause FROM
• Sous-requêtes

En revanche, les éléments précédents sont pris en charge dans les modules compilés en mode natif sur l’instruction SELECT.

Les instructions UPDATE avec la clause FROM sont généralement utilisées pour mettre à jour les informations contenues dans une table basée sur un paramètre table (TVP), ou pour mettre à jour les colonnes d’une table dans un déclencheur AFTER.

Pour le scénario de mise à jour basée sur un paramètre table, consultez Implémentation de la fonctionnalité MERGE dans une procédure stockée compilée en mode natif.

L’exemple suivant illustre une mise à jour effectuée dans un déclencheur. Dans la table, la colonne nommée LastUpdated a la valeur date-heure en cours APRÈS les mises à jour. La solution de contournement procède à des mises à jour individuelles à l’aide des éléments suivants :

• Une variable de table qui a une colonne IDENTITY.
• Une boucle WHILE pour itérer sur les lignes dans la variable de table.

Voici l’instruction originale T-SQL UPDATE :

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

L’exemple de code T-SQL dans le bloc suivant illustre une solution de contournement qui fournit de bonnes performances. La solution de contournement est implémentée dans un déclencheur compilé en mode natif. Les principaux éléments à remarquer dans le code sont les suivants :

• Le type nommé dbo.Type1, qui est un type de table optimisé en mémoire.
• La boucle WHILE dans le déclencheur.
  • La boucle extrait les lignes de Inserted une à la fois.

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

Voici le jeu de résultats.

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