Remplacer en toute security la table par de nouveldatatables et schémas

J'essaye de créer une procédure stockée pour recréer une table à partir de zéro, avec un changement possible de schéma (y compris les additions / suppressions possibles de colonnes), en utilisant un DROP TABLE suivi d'un SELECT INTO , comme ceci:

 BEGIN TRAN DROP TABLE [MyTable] SELECT (...) INTO [MyTable] FROM (...) COMMIT 

Mon souci est que des erreurs pourraient être générées si quelqu'un essaie d'accéder à la table après qu'elle a été abandonnée mais avant que le SELECT INTO soit terminé. Y at-il un moyen de verrouiller [MyTable] d'une manière qui persistera à travers le DROP? Au lieu de DROP/SELECT INTO , je pourrais TRUNCATE/INSERT INTO , mais cela ne permettrait pas de changer le schéma. SELECT INTO est pratique dans ma situation car il permet de déterminer automatiquement le nouveau schéma. Y a-t-il un moyen de faire ce travail en toute security?

Aussi, je voudrais être sûr que les tables sources dans "FROM (…)" ne sont pas bloquées pendant ce process.

Si vous essayez d'apporter une modification significative à la table (comme append une colonne au milieu de colonnes existantes, pas à la fin) en utilisant SSMS et voir quel script il génère, vous verrez que SSMS utilise sp_rename .

La structure générale du script de SSMS:

  • créer une nouvelle table avec un nom temporaire
  • remplir la nouvelle table avec des données
  • déposez l'ancienne table
  • Renommez la nouvelle table au nom correct.

Tout cela dans une transaction.

Cela devrait garder le time quand les tables sont verrouillées au minimum.

 BEGIN TRANSACTION SELECT (...) INTO dbo.Temp_MyTable FROM (...) DROP TABLE dbo.MyTable EXECUTE sp_rename N'dbo.Temp_MyTable', N'dbo.MyTable', 'OBJECT' COMMIT 

DROP TABLE MyTable acquiert un locking de modification de schéma (Sch-M) jusqu'à la fin de la transaction, de sorte que toutes les autres requêtes utilisant MyTable attente. Même si d'autres requêtes utilisent le niveau d'isolation READ UNCOMMITTED (ou le fameux indice WITH (NOLOCK) ).

Voir aussi Modes de locking MSDN:

Verrous de schéma

Le moteur de database utilise des verrous de modification de schéma (Sch-M) lors d'une opération de langage de définition de données de table (DDL), telle que l'ajout d'une colonne ou la suppression d'une table. Pendant le time qu'il est tenu, le verrou Sch-M empêche l'access simultané à la table. Cela signifie que le verrou Sch-M bloque toutes les opérations extérieures jusqu'à ce que le verrou soit libéré.