December 21, 2016 at 12:00 am
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
December 21, 2016 at 1:08 am
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]
December 21, 2016 at 6:01 am
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.
December 21, 2016 at 8:17 am
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.
December 21, 2016 at 8:43 am
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).
December 22, 2016 at 5:59 am
Thanks for the great information and concepts.
December 22, 2016 at 7:22 am
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.
December 22, 2016 at 7:36 am
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
December 22, 2016 at 7:42 am
@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
November 6, 2019 at 10:44 am
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!
November 6, 2019 at 1:48 pm
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.
February 18, 2021 at 9:58 pm
I work to have scripts be idempotent where feasible. However, it is not always feasible. So.... what do we call those scripts that are not idempotent? That is, what is the antonym of idempotent? My dictionary did not list any, but it listed the entomology for idempotent as:
Scripts that are not idempotent have their power only once, so I looked up Latin translations of "once," and I got these:
To me, semel looks the best with "once," "first," and "single time." I propose that semelpotent is a good word to use to categorize scripts that are not idempotent.
Sincerely,
Daniel
February 19, 2021 at 7:02 am
So.... what do we call those scripts that are not idempotent?
Impotent? ... I'll get my coat ...
In my rollout scripts (mechanically generated) I have a #TempTable for anything that couldn't be completed.
e.g. if modify of a table has to drop a VIEW/similar, because of Dependency, that VIEW name is added to #TempTable and reported on at the end of the script (only if the view is still missing - the script might subsequently include the CREATE of that VIEW)
Similarly a Foreign Key Create will check that the corresponding data is actually valid and that the table exist 🙂 so that the create will not fail. Again, add to #TempTable if FKey cannot be created. For us typically that exact same FKey create statement will also occur later in the script - e.g. we are creating two tables, the FKey create will be attempted after the first table create, that will be skipped on detection that "Other table does not yet exist", and then the same FKey create statement will be scripted after the second table create - at which point it will be successful.
Similarly for Unique Indexes, we check that the Keys are indeed unique before attempting to create the index.
October 4, 2023 at 10:34 am
When you use "IF NOT EXISTS", you should also check and ensure the table exists. E.g., this demo code:
IF NOT EXISTS(
SELECT * FROM sys.columns
WHERE object_ID=object_id('HumanResources.Employee')
AND col_name(object_ID,column_Id)='BusinessEntityID'
)
BEGIN
--it doesn’t exist so do something
END;
Should be something like:
IF NOT EXISTS(
SELECT * FROM sys.columns
WHERE object_ID=object_id('HumanResources.Employee')
AND col_name(object_ID,column_Id)='BusinessEntityID'
) AND EXISTS (SELECT * FROM sys.tables WHERE object_ID=object_id('HumanResources.Employee')
BEGIN
--it doesn’t exist so do something
END;
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply