DROP … CRÉER vs ALTER

ALTER forcera également une recompilation de toute la procédure. Recomstack niveau d'instruction s'applique aux instructions à l'intérieur des procédures, par exemple. un seul SELECT, qui sont recompilés car les tables sous-jacentes changent, sans aucune modification de la procédure. Il ne serait même pas possible de recomstackr sélectivement seulement certaines instructions sur la procédure ALTER, afin de comprendre ce qui a changé dans le text SQL après une procédure ALTER que le server devrait … comstackr.

Pour tous les objects, ALTER est toujours meilleur car il préserve toute security, toutes les propriétés étendues, toutes les dependencies et toutes les contraintes.

C'est comme ça qu'on fait:

if object_id('YourSP') is null exec ('create procedure dbo.YourSP as select 1') go alter procedure dbo.YourSP as ... 

Le code crée une procédure stockée "stub" si elle n'existe pas encore, sinon elle fait un alter. De cette manière, toutes les permissions existantes sur la procédure sont conservées, même si vous exécutez le script à plusieurs resockets.

La modification est généralement meilleure. Si vous supprimez et créez, vous pouvez perdre les permissions associées à cet object.

Si vous avez une fonction / proc stockée qui est appelée très fréquemment à partir d'un site web par exemple, cela peut causer des problèmes.

Le proc stocké sera supprimé pendant quelques millisecondes / secondes, et pendant ce time, toutes les requêtes échoueront.

Si vous faites un changement, vous n'avez pas ce problème.

Les templates de proc stockés nouvellement créés sont généralement ce formulaire:

 IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = '<name>') BEGIN DROP PROCEDURE <name> END GO CREATE PROCEDURE <name> ...... 

Cependant, le contraire est meilleur, imo:

Si la fonction storedproc / function / etc n'existe pas, créez-la avec une instruction select fictive. Ensuite, l'alter fonctionnera toujours – il ne sera jamais abandonné.

Nous avons un proc stocké pour cela, donc nos procs / fonctions stockées ressemblent généralement à ceci:

 EXEC Utils.pAssureExistance 'Schema.pStoredProc' GO ALTER PROCECURE Schema.pStoredProc ... 

et nous utilisons le même proc stocké pour les fonctions:

 EXEC Utils.pAssureExistance 'Schema.fFunction' GO ALTER FUNCTION Schema.fFunction ... 

Dans Utils.pAssureExistance nous faisons un IF et regardons le premier caractère après le ".": Si c'est un "f", nous créons une fonction fictive, si c'est "p", nous créons un proc stocké fictif.

Attention cependant, si vous créez une fonction scalaire fictive et que votre ALTER est sur une fonction table, ALTER FUNCTION échouera en disant que ce n'est pas compatible.

Encore une fois, Utils.pAssureExistance peut être utile, avec un paramètre facultatif supplémentaire

 EXEC Utils.pAssureExistance 'Schema.fFunction', 'TableValuedFunction' 

va créer une fonction factice de table,

De plus, je peux me tromper, mais je pense que si vous faites une procédure d'abandon et qu'une requête utilise actuellement la procédure stockée, elle échouera.

Cependant, une procédure alter attendra que toutes les requêtes arrêtent d'utiliser le proc stocké, puis le modifieront. Si les requêtes "verrouillent" trop longtime le proc stocké (disons quelques secondes), ALTER arrêtera d'attendre le verrou, et modifiera quand même le proc mémorisé: les requêtes utilisant le proc stocké échoueront probablement à ce point.

Je ne sais pas s'il est possible de faire un tel commentaire général et de dire "ALTER est mieux". Je pense que tout dépend de la situation. Si vous avez besoin de ce type de permission granulaire jusqu'au niveau de la procédure, vous devriez probablement gérer cela dans une procédure séparée. Il y a des avantages à devoir laisser tomber et recréer. Il nettoie la security existante et la réinitialise, ce qui est prévisible.

J'ai toujours préféré utiliser drop / recréer. J'ai également trouvé plus facile de les stocker dans le contrôle de la source. Au lieu de faire …. si existe, change et si n'existe pas, crée.

Cela dit … si vous savez ce que vous faites … je ne pense pas que cela ait une importance.

Vous avez posé une question spécifique concernant les objects de database qui ne contiennent aucune donnée et qui, théoriquement, ne devraient pas être changés aussi souvent.

Il est probable que vous ayez besoin de modifier ces objects mais pas toutes les 5 minutes. Pour cette raison, je pense que vous avez déjà frappé le marteau sur la tête – les permissions.

Réponse courte, pas vraiment un problème, tant que les permissions ne sont pas un problème

DROP perd généralement les permissions ET toutes les propriétés étendues.

Sur certaines fonctions UDF, ALTER perd également des propriétés étendues (définitivement sur les fonctions table multi-instructions SQL Server 2005).

En général, je ne DROP et CREATE sauf si je recréer ces choses (ou savoir que je veux les perdre).

Nous avions l'habitude d'utiliser alter pendant que nous travaillions en développement soit en créant de nouvelles fonctionnalités ou en modifiant les fonctionnalités. Quand nous aurions fini notre développement et nos tests, nous ferions une chute et créerions. Cela modifie l'horodatage des process afin que vous puissiez les sortinger par date / heure.

Cela nous a aussi permis de voir ce qui était groupé par date pour chaque livrable que nous avons envoyé.

Ajouter avec une goutte si existe est mieux parce que si vous avez plusieurs environnements lorsque vous déplacez le script à QA ou de test ou prod vous ne savez pas si le script existe déjà dans cet environnement. En ajoutant une baisse (si elle existe déjà) et puis ajoutez vous serez couvert indépendamment de si elle existe ou non. Vous devez ensuite réappliquer les permissions, mais c'est mieux d'entendre votre script d'installation avec des erreurs.

À partir de SQL Server 2016 SP1, vous avez désormais la possibilité d'utiliser la syntaxe CREATE OR ALTER pour les procédures stockées, les fonctions, les triggersurs et les vues. Voir CREATE OR ALTER – une autre excellente amélioration du langage dans SQL Server 2016 SP1 sur le Blog du moteur de database SQL Server. Par exemple:

 CREATE OR ALTER PROCEDURE dbo.MyProc AS BEGIN SELECT * FROM dbo.MyTable END; 

D'un sharepoint vue de l'utilisabilité, une goutte et créer est mieux qu'un alter. Alter échouera dans une database qui ne contient pas cet object, mais ayant un IF EXISTS DROP puis un CREATE fonctionneront dans une database avec l'object déjà existant ou dans une database où l'object n'existe pas. Dans Oracle et PostgreSQL, vous créez normalement des fonctions et des procédures avec l'instruction CREATE OR REPLACE qui fait la même chose qu'un SQL SERVER IF EXISTS DROP puis un CREATE. Ce serait bien si SQL Server prenait cette syntaxe petite mais très pratique.

C'est comme ça que je le ferais. Mettez tout cela dans un script pour un object donné.

 IF EXISTS ( SELECT 1 FROM information_schema.routines WHERE routine_schema = 'dbo' AND routine_name = '<PROCNAME' AND routine_type = 'PROCEDURE' ) BEGIN DROP PROCEDURE <PROCNAME> END GO CREATE PROCEDURE <PROCNAME> AS BEGIN END GO GRANT EXECUTE ON <PROCNAME> TO <ROLE> GO