Quelle est la différence entre ces deux instructions T-SQL?

Question

Dans un package SSIS au travail, il y a des tâches SQL qui créent des tables de mise en scène pour stocker les données d'importation. Toutes les instructions prennent cette forme :

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'dbo.tbNewTable') AND type in (N'U'))
BEGIN
    TRUNCATE TABLE dbo.tbNewTable
END
ELSE
BEGIN
    CREATE TABLE dbo.tbNewTable (
        ColumnA VARCHAR(10) NULL,
        ColumnB VARCHAR(10) NULL,
        ColumnC INT NULL
    ) ON PRIMARY
END

Dans le livre d'Itzik Ben-Gan Principes de base de T-SQL je vois une forme différente d'instruction pour créer une table :

IF OBJECT_ID('dbo.tbNewTable', 'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.tbNewTable
END

CREATE TABLE dbo.tbNewTable (
    ColumnA VARCHAR(10) NULL,
    ColumnB VARCHAR(10) NULL,
    ColumnC INT NULL
) ON PRIMARY

Chacun de ces codes semble faire la même chose. Après l'exécution, il y aura une table vide appelée tbNewTable dans le schéma dbo.

Y a-t-il des différences pratiques ou théoriques entre les deux ? Quelles implications pourraient-elles avoir ?

Answer 1

Le premier suppose que si la table existe, elle a les mêmes colonnes que celles qu'elle créerait. Le deuxième ne fait pas cette hypothèse. Donc, si une table avec ce nom existe et a un ensemble différent de colonnes, les deux auraient des résultats très différents.

Answer 2

Le premier ne supprimera pas réellement la table - il vide simplement TRUNCATES toutes les données de ladite table. C'est pourquoi le CREATE est protégé.

Ainsi, le formulaire avec le DROP permettra au CREATE ultérieur de modifier le schéma (lorsque la nouvelle table est créée) même si tbNewTable existait auparavant.

Parce que le DROP/CREATE modifie le schéma de la base de données, il se peut que cela ne soit pas autorisé dans tous les cas. Par exemple, une vue créée avec un SCHEMABINDING empêchera la table d'être supprimée. (Cela s'applique également aux relations FK plus générales, si elles existent.)

...lorsque SCHEMABINDING est spécifié, la table de base ne peut pas être modifiée de manière à affecter la définition de la vue.

Le TRUNCATE devrait être marginalement plus rapide dans l'une de ces constantes "ne pas prendre en compte" : il ne devrait pas y avoir de considération de performance donnée à l'un par rapport à l'autre.

Il y a aussi des différences de permission. TRUNCATE ne nécessite que la permission ALTER.

La permission minimale requise est ALTER sur table_name. Les permissions TRUNCATE TABLE sont par défaut pour le propriétaire de la table...

Bon codage.

Answer 3

Ce sont des approches très différentes...

Le premier fait une vérification d'égalité sur la table système sys.objects et vérifie s'il y a un nom de table correspondant. Si c'est le cas, il tronque la table. Fondamentalement, il supprime toutes les lignes tout en maintenant la structure de la table elle-même - c'est-à-dire que la table réelle n'est jamais supprimée.

Dans le second, la vérification de l'existence de la table est implicitement effectuée en utilisant la méthode OBJECT_ID(). Si c'est le cas, la table est complètement supprimée - lignes et structure.

Si vous avez une contrainte de clé primaire et étrangère sur la table, vous aurez certainement des problèmes pour la supprimer complètement... et si vous avez d'autres tables liées à la table que vous essayez de 'tronquer', vous rencontrerez également des problèmes, sauf si vous avez activé la suppression en cascade.

Answer 4

Je tends à ne pas aimer l'une ou l'autre construction dans un package SSIS. Je crée les tables dans un script de déploiement et je veux que le package échoue si l'une des tables que j'utilise est manquante plus tard car alors quelque chose de gravement incorrect s'est produit et je veux enquêter avant d'essayer de mettre des données n'importe où.