Modification des tables à mémoire optimisée

S’applique à :SQL ServerAzure SQL DatabaseAzure SQL Managed Instance

Les modifications de schéma et d’index sur les tables optimisées en mémoire peuvent être effectuées à l’aide de l’instruction ALTER TABLE . Dans SQL Server 2016 et Azure SQL Database, les ALTER TABLE opérations sur les tables optimisées en mémoire sont effectuées hors connexion, ce qui signifie que la table n’est pas disponible pour les requêtes pendant toute la durée de l’opération. Dans le cadre de cette modification, l’application de base de données peut continuer à s’exécuter, mais toute opération accédant à la table est bloquée jusqu’à la fin du processus de modification. Il est possible de combiner plusieurs opérations ADD, DROP ou ALTER dans une seule ALTER TABLE instruction.

ALTER TABLE

La ALTER TABLE syntaxe est utilisée pour apporter des modifications au schéma de table, ainsi que pour ajouter, supprimer et reconstruire des index. Les index sont considérés comme faisant partie intégrante de la définition de table :

• La syntaxe ALTER TABLE ... ADD/DROP/ALTER INDEX est pris en charge uniquement pour les tables optimisées en mémoire.

• Sans utiliser l’instruction ALTER TABLE, les instructions CREATE INDEX, DROP INDEX, ALTER INDEX et PAD_INDEX ne sont pas prises en charge pour les index sur les tables à mémoire optimisée.

Les types de modifications pris en charge sont les suivants :

• Modification du nombre de buckets

• ajout et suppression d’un index ;

• modification, ajout et suppression d’une colonne ;

• ajout et suppression d’une contrainte.

Pour plus d’informations sur ALTER TABLE les fonctionnalités et la syntaxe complète, consultez ALTER TABLE (Transact-SQL)

Dépendance liée au schéma

Les procédures stockées compilées en mode natif doivent être liées au schéma, ce qui signifie qu’elles ont une dépendance liée au schéma vis-à-vis des tables optimisées en mémoire auxquelles elles accèdent et des colonnes qu’elles référencent. Une dépendance liée au schéma est une relation entre deux entités qui empêche l’entité référencée d’être supprimée ou modifiée de façon incompatible tant que l’entité de référence existe.

Par exemple, si une procédure stockée compilée en mode natif et liée au schéma fait référence à une colonne c1 de la table mytable, la colonne c1 ne peut pas être supprimée. De même, s’il existe une telle procédure avec une INSERT instruction sans liste de colonnes (par exemple, INSERT INTO dbo.mytable VALUES (...)), aucune colonne de la table ne peut être supprimée.

Journalisation de ALTER TABLE sur les tables optimisées en mémoire

Dans une table optimisée en mémoire, la plupart des ALTER TABLE scénarios s’exécutent désormais en parallèle et entraînent une optimisation des écritures dans le journal des transactions. L’optimisation est obtenue par la journalisation des seules modifications de métadonnées dans le journal des transactions. Toutefois, les opérations suivantes ALTER TABLE s’exécutent sur un seul thread et ne sont pas optimisées pour la journalisation.

Dans ce cas, une exécution mono-thread consignerait l’intégralité du contenu de la table modifiée dans le journal des transactions. Voici la liste des opérations à thread unique :

• Modifier ou ajouter une colonne pour utiliser un type d’objet volumineux (LOB) : nvarchar(max), varchar(max) ou varbinary(max).

• Ajouter ou supprimer un index COLUMNSTORE.

• Presque tout ce qui affecte une colonne hors ligne.

  • Provoquer le déplacement d’une colonne sur une ligne en mode hors ligne.
  • Provoquer le déplacement d’une colonne en mode hors ligne sur une ligne.
  • Créer une colonne hors ligne.
  • Exception : l’allongement d’une colonne déjà hors ligne est journalisé de la manière optimisée.

Exemples

L’exemple ci-après modifie le nombre de compartiments d’un index de hachage existant. Cette opération régénère l’index de hachage avec le nouveau nombre de compartiments, tandis que les autres propriétés de l’index de hachage restent identiques.

ALTER TABLE Sales.SalesOrderDetail_inmem
       ALTER INDEX imPK_SalesOrderDetail_SalesOrderID_SalesOrderDetailID  
              REBUILD WITH (BUCKET_COUNT=67108864);  
GO

L’exemple suivant ajoute une colonne avec une contrainte NOT NULL et une DEFAULT définition, et utilise WITH VALUES pour fournir des valeurs pour chaque ligne existante de la table. Si l'option WITH VALUES n'est pas utilisée, chaque ligne a la valeur NULL dans la nouvelle colonne.

ALTER TABLE Sales.SalesOrderDetail_inmem  
       ADD Comment NVARCHAR(100) NOT NULL DEFAULT N'' WITH VALUES;  
GO

L’exemple ci-après ajoute une contrainte de clé primaire à une colonne existante.

CREATE TABLE dbo.UserSession (
   SessionId int not null,
   UserId int not null,
   CreatedDate datetime2 not null,
   ShoppingCartId int,
   index ix_UserId nonclustered hash (UserId) with (bucket_count=400000)
)
WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY) ;  
GO  
  
ALTER TABLE dbo.UserSession  
       ADD CONSTRAINT PK_UserSession PRIMARY KEY NONCLUSTERED (SessionId);  
GO

L’exemple suivant supprime un index.

ALTER TABLE Sales.SalesOrderDetail_inmem  
       DROP INDEX ix_ModifiedDate;  
GO

L’exemple suivant ajoute un index.

ALTER TABLE Sales.SalesOrderDetail_inmem  
       ADD INDEX ix_ModifiedDate (ModifiedDate);  
GO  

L’exemple suivant ajoute plusieurs colonnes, avec un index et des contraintes.

ALTER TABLE Sales.SalesOrderDetail_inmem  
       ADD    CustomerID int NOT NULL DEFAULT -1 WITH VALUES,  
              ShipMethodID int NOT NULL DEFAULT -1 WITH VALUES,  
              INDEX ix_Customer (CustomerID);  
GO  

Voir aussi

Tables à mémoire optimisée