Idempotent DDL Scripts That Always Achieve The Same Result - Making Changes Only Once: Stairway to Exploring Database Metadata Level 6

  • Phil Factor

    SSCoach

    Points: 19903

    Comments posted to this topic are about the item Idempotent DDL Scripts That Always Achieve The Same Result - Making Changes Only Once: Stairway to Exploring Database Metadata Level 6

    Best wishes,
    Phil Factor
    Simple Talk

  • roworthm

    Old Hand

    Points: 313

    I agree with this. A deployment script should always take a database to a known destination state, irrespective of what was there before. Just as an aside, OBJECT_ID can take a second parameter, which is the type of object being tested for, which would simplify the scripting for some of the tests. e.g.:

    [font="Courier New"]OBJECT_ID('dbo.TableName', 'U') -- tests for the existence of a user table

    OBJECT_ID('dbo.ViewName', 'V') -- tests for the existence of a view

    OBJECT_ID('dbo.FunctionName', 'FN') -- tests for the existence of a function

    OBJECT_ID('dbo.ProcedureName', 'P') -- tests for the existence of a sproc

    [/font]

  • Dan Meenan

    SSC Enthusiast

    Points: 159

    Great article. A few points:

    1. remake your idempotent statements to "if not exist create, then alter", rather than drop/create.

    Example:

    if object_id(N'[schema].[sp_proc]',N'P') is null

    exec ('create proc [schema].[sp_proc] as Select X = 1')

    go

    alter proc [schema].[sp_proc] (@some_variable bit = 1)

    as

    set nocount on

    if @bit = 1

    do stuff

    go

    This technique (if not exists/create/alter) is important to preserve user and role permissions. Drop/create idempotent scripts wreak havoc on security, requiring a follow-up security script if you have anything other than a vanilla security schema.

    2. Indexes - where do I begin? I have implemented a deterministic index/constraint naming convention at my company, modeled after the Microsoft naming conventions. I published the standard, and the developers (for the most part) adhere to it. In any case a job runs daily in staging/prod to locate and email incorrectly named objects, so we can fix the code long before it hits production. What does this buy us? We don't drop/create indices, ever. Since every index has a unique, deterministic name, the idempotent script is simply (for example):

    if not exists (select top 1 1 from sys.indexes where name = 'IX_SCHEMA_table_K1_K3_i4')

    create nonclustered index on [schema].

    (column1, column3) include (column4)

    Note the lower-case "i" in the convention -- makes it easily distinguishable from lower-case L. I'll grant that not having the column names in the index/constraint name will involve an extra step sometimes, but it removes the need to drop/create an index on a deep table AND allows easy checking for overlapping indexes, etc. Idempotency using drop/create for indexing can be quite expensive and time consuming. Further, it removes the very likely scenario where two developers would otherwise name a covering index with different names - resulting in index duplication. Just my thoughts - I'd be happy to post my naming convention documentation and code for sending out email for misnamed objects if anyone thinks these thoughts have any merit.

  • qbrt

    SSCrazy

    Points: 2422

    Agree that having idempotent scripts are very important. The T-SQL implementation of having to use EXISTS() function to craft idempotent scripts is what the developers on our team dislike the most. It takes three or four times as long to come up with a script for a simple create/alter step.

    Finally MS is making it simpler via new T-SQL changes in SQL Server 2016.

    DROP IF EXISTS – new thing in SQL Server 2016

    [/url]

    I can't wait until this becomes part of all their objects DDL scripts.

  • paul.kohlmiller

    Valued Member

    Points: 68

    I liked this article. I thought I had stolen the concept of "idempotent" from matrices.

    We developed a kind of index management so we have a record of what we "intended" for indexes and can compare to what is actually deployed. But we extend the idea of idempotent procedures to all data manipulations. That is, you can populate a table in some stored procedure and then you can run that procedure again with the same parameters and we have to guarantee that is safe (even if a waste of time).

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the great information and concepts.

  • roworthm

    Old Hand

    Points: 313

    We have a similar system here referred to as KIX (Keys and Indexes) and another for permissions. The reference tables contain what should be the case.

    The foreign keys, table indexes (and permissions, but separate component) are kept in a set of reference tables for the entire server, in a database called lib. When a table needs to be modified, we drop all referential integrity to it, back it up, recreate it, repopulate it and then call three stored procedures that restore the indexes, foreign keys and permissions. Rather than setup the indexes in the DDL for the table, we setup the reference data for it (again stored procedures to create the indexes etc). Then a single call to setup each of them.

    Makes life a lot easier. We know the sprocs work and have been tested, so to restore the indexes, foreign keys and permissions for a table would look like this:

    [font="Courier New"]EXEC lib.dbo.KIX_DropTargetTableIndexes 'symph', 'dbo', 'Attendance_Details'

    EXEC lib.dbo.KIX_CreateTableForeignKeys 'DW', 'dbo', 'PE_WaitingListEntry'

    EXEC lib.dbo.PERM_CreatePermissionsForDB 'DW'[/font]

    Edit: This was a response to paul.kohlmiller above.

  • Phil Factor

    SSCoach

    Points: 19903

    @roworthm,

    Yes, I rather forgot that and the Tech reviewer reminded me. I ought to change those checks. Sadly, I've been using these routines a long time! On your other note, it sounds like a really good system, particularly where you can't always put the data in the tables in the right table order. (sometimes we need keyhole surgery)

    Best wishes,
    Phil Factor
    Simple Talk

  • Phil Factor

    SSCoach

    Points: 19903

    @Paul.kohlmiller

    The idea of idempotency in build scripts has been around a long time. It is a useful word, and there isn't anything else that quite captures the principle. Oh heavens it is useful: I've been caught out in the past with a deployment when I forget to make a script idempotent. I agree with you that it should be a general check. It is generally impossible to run any deployment script more than once, but it certainly happen, particularly whenever you fail to make the script idempotent. The results can be unpleasant.

    Best wishes,
    Phil Factor
    Simple Talk

  • Phil Factor

    SSCoach

    Points: 19903

    @Dan Meenan

    Very good point. I generally do it that way to preserve any permissions or extended properties. In this article I did it the simpler way just because doing it properly is a bit messier!

    Best wishes,
    Phil Factor
    Simple Talk

  • vliet

    SSCommitted

    Points: 1986

    Maybe few of us did notice this enhancement that was somehow secretly added in a service pack, but since SQL 2016 SP1 we may use CREATE OR ALTER and it does not change permissions on existing objects. Available for all SQL 'modules', thus for every STORED PROCEDURE, FUNCTION, TRIGGER and VIEW. Great for work on newer SQL instances and in my case mainly for SQL Azure!

  • anacap

    Old Hand

    Points: 321

    I always explain the concept of idempotency through the analogy of a vaccination.  Essentially an agent can only affect the immune system once since it is affected permanently.

    I think it's also important to also cover DML change scripts for tables that contain reference data.  Since version controlling is becoming in vogue, it's now desirable to stand up an entire skeleton database from scratch with all the reference data in it as well.  Things like using MERGE vs TRUNCATE then INSERT and whether or not IDENTITY columns should match across environments and the implications of whether to use IDENTITY INSERTS or not.

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply