Es muss nicht immer DROP…CREATE sein

Hallo zusammen,

dieser kleine Beitrag schwirrte schon lange in meinem Kopf herum und nun mache ich mich endlich mal daran, diesen auch umzusetzen. Anlass ist der letzte Blog Post von einem unserer #sqlpass_de MVPs und Leiter der Regionalgruppe Ruhrgebiet: Frank Geisler (B|T). Frank hatte das, in der CTP 3 des SQL Servers 2016, neue DROP…IF EXISTS kurz vorgestellt.

Und dann ist mir wieder eingefallen, wie oft ich schon sogenannte Update Skripte gesehen habe, wo Funktionen, Prozeduren oder Views “überarbeitet” wurden. Die fingen dann nämlich so an:

   1: DROP PROCEDURE blabla
   2:
   3: CREATE PROCEDURE blabla
   4: AS
   5: ....
(das blabla stand natürlich nicht so in den Skripten)

An und für sich mag der Ansatz ja ok sein, auch wenn der schon nicht so richtig elegant ist. Was ist, wenn das entsprechende Objekt gar nicht vorhanden ist? Dann kommt sofort

Msg 3701, Level 11, State 5, Line 1
Cannot drop the procedure 'blabla', because it does not exist or you do not have permission.

Wie man es richtig macht, hat Frank in seinem BlogPost ja aufgezeigt, nämlich:

   1: IF OBJECT_ID('blabla','P') IS NOT NULL
   2:     DROP PROCEDURE blabla

oder dann demnächst in SQL Server 2016:

   1: DROP PROCEDURE IF EXISTS dbo.blabla
   2: GO
   3: CREATE PROCEDURE dbo.blabla
   4: AS

Nun aber Folgendes: Was ist denn, wenn man tatsächlich eine Datenbank hat, wo granular Berechtigungen vergeben worden sind und es ggf. kein dediziertes Schema gibt, auf dem ich die entsprechenden Berechtigungen vergeben kann? Wo also zum Beispiel ein bestimmtes Servicekonto nur eine Prozedur ausführen oder eine View abrufen soll? Mit dem DROP ist das Objekt weg und damit auch die granulare Berechtigung auf eben dieses.

Von daher hab ich nun schon einige Male folgende Idee mit auf den Weg gegeben (und es ist nichts Neues): Prüft, ob das entsprechende Objekt da ist oder nicht. Wenn nicht, dann einach einen Dummy anlegen und nachgelagert dann das neue Objekt mit ALTER begegnen. Wenn es schon da ist, dann greift direkt Euer ALTER Befehl. Also quasi so:

   1: IF OBJECT_ID('dbo.blabla','P') IS  NULL
   2:     EXEC ('CREATE PROCEDURE blabla
   3:     AS
   4:     SET NOCOUNT ON;')
   5: GO
   6: ALTER PROCEDURE blabla
   7: AS
   8: BEGIN
   9: PRINT 'So ist es doch besser....'
  10: END

Dies hat den Charme, dass Ihr nicht bereits gesetzte Berechtigungen verliert. Erspart in mancher heißen Testphase das eine oder andere Telefonat mit dem DBA eures Vertrauens.

Advertisements

Über Dirk Hondong

A MS server and ms sql server admin guy from germany. want to improve my skills a little bit, sharing my daily experience
Dieser Beitrag wurde unter SQL Server abgelegt und mit , verschlagwortet. Setze ein Lesezeichen auf den Permalink.

7 Antworten zu Es muss nicht immer DROP…CREATE sein

  1. milosradivojevic schreibt:

    Hi Dirk,

    Da CREATE PROC die erste Anweisung in einem Batch sein muss, ist von dir präsentierte Lösung, die einzige Möglichkeit, einen DROP PROC zu vermeiden und die Business Logic immer in der ALTER-Teil zu beschreiben.
    Umso schöner wäre, wenn sie in SQL Server 2016 eine CREATE OR REPLACE Anweisung unterstützten würden. Damit wäre Dynamic SQL überhaupt nicht notwendig und noch der StorProc Name würde nur einmal kommen. Jetzt muss man den Name dreimal schreiben, üblicherweise mit einem Copy/Paste, was fehleranfällig uns mühsam ist.

    So, Daumen hoch für die DROP IF EXISTS und bitte, bitte, bitte für die CREATE OR REPLACE :)

    LG aus Wien,
    Milos

  2. Frank Geisler schreibt:

    Hallo Dirk, Hallo Milos!

    Erstmal finde ich es super Dirk, dass Dich mein Blog-Post selbst zu einem Blog-Post veranlasst hat (Thumbs Up!) :-). Ich denke die DROP IF EXISTS und ALTER IF EXISTS sind auf zwei unterschiedliche Anwendungsbereiche gemünzt und MIcrosoft hat momenten leider nur den einen Anwendungsbereich, der auch über SSDT abgebildet wird, im Blick. DROP IF EXISTS braucht man, wenn sämtliche Datenbankobjekte gescriptet sind und man bei Änderungen das entsprechende Script ausführt, dass für die entsprechende Änderung zuständig ist. Wichtig hierbei ist aber auch, dass die Security, sofern sie direkt auf die Stored Procedure gesetzt ist, auch in dem Script vorhanden ist, d.h. dort müssen auch die entsprechenden ALTER Befehle vorhanden sein. Aus meiner Sicht ist das die „saubere“ Methode, da so alles was irgendwie mit den Datenbankobjekten zu tun hat auch entsprechend in der Versionskontrolle drin ist. Bei SSDT benötigt man übrigens kein DROP … IF EXISTS, da das Anlegen bzw. Ändern der Stored Procedure ja automatisch durch den Deployment-Prozess vorgenommen wird, d.h. SSDT entscheidet selbstständig, ob im Deployment Script ein CREATE PROCEDURE oder ein ALTER PROCEDURE (wenn es die Prozedur schon gibt) eingefügt wird. An dieser Stelle wäre es sicherlich mal interessant herauszufinden wie SSDT mit Security auf Objektebene umgeht. Da SSDT beim Vorhandensein des Objektes ein ALTER produziert würde ich davon ausgehen, dass das funktioniert, müsste man aber sicherlich noch mal prüfen. In SSDT muss bei DDL-Scripten übrigens zwingend am Anfagen ein CREATE stehen, d.h. DROP IF EXISTS kommt nur dann zum Tragen wenn man seine Datenbank mittelst SSMS-Projekten aufgebaut hat. Hier wird ja kein automatisches Deployment durchgeführt und man muss das Objekt selbst löschen und neu anlegen.

  3. retracement schreibt:

    Hey Dirk, nice post. I can say this because I wrote about the same thing when I was a mere babe in 2010. A now rather badly written http://bit.ly/1kJQlfm . This may have been coincidence, but several years after writing it, I noticed that sp_whoisactive uses the same technique (and even uses the „stub“ terminology). Either way I’m gonna assume Adam incorporated my idea until proven otherwise ;)

  4. Pingback: CREATE OR ALTER mit SQL 2016 SP1 | Dirk Hondong's blog

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden / Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden / Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden / Ändern )

Google+ Foto

Du kommentierst mit Deinem Google+-Konto. Abmelden / Ändern )

Verbinde mit %s