THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

IF NOT EXISTS ( SELECT ‘thisPost’ ) CREATE POST thisPost AS

T-SQL deployment scripts are tedious to write and they get no love. As a result, they are very often the most horrible, error-prone step-children of scripts*. One basic principle that could help is to develop a habit of using the “IF NOT EXISTS” or “CREATE OR ALTER” patterns in these scripts. The idea is to first check the existing database before creating new objects. This is far better, in my opinion, than the common “DROP and CREATE” pattern, which can lose data and/or permissions if misapplied. Whenever possible, this type of deployment code should use the SQL Server Catalog Views, and not the deprecated ones. INFORMATION_SCHEMA has been demonstrated to be flakey by others.

Make It So

I like to adopt an approach where deployment scripts

  • Are re-runnable without harm. I’m calling this the “F5 F5 F5 F5 F5” principle. DROP AND CREATE is your enemy.
  • Should complete without errors when successful. (Errors should be real; none of this “ok, expect errors from the script and ignore them.” That’s just dangerous.)
  • Take whatever is there on the server, within reason, and change it to what the script says should be there. This is a declarative approach a bit like Picard’s orders to Number 1. Make It So. If an object is missing, then create it. If it’s there already, then move on. If it needs modification, then change it.

The hard part about IF NOT EXISTS is that you need all these weird snippets of code to check system views for objects in the database. These are repetitive, but hard to figure out from scratch, and who has time for that? Well, I’m making time. I have had this post idea in the back of my head for a long time. This page will be a clearing house of code snippets to check for the existence of objects, and I’ll come back and add or adjust it in the future. You should be able to just snag what you need for that ugly deployment script and make a few edits.

Use SQLCMD to Stop on Error and for Script Variables

/* Optional but Recommended: use SQLCMD to set database name and stop on errors */

:on error exit
:setvar dbname myDatabase

Notes: SQLCMD is a great addition to deployment scripts that allows one to avoid hard-coding things like database names, and to stop script execution if an error is encountered. You have to enable SQLCMD mode in SSMS to take advantage of this. Query > SQLCMD Mode

Every Script Needs Database Context

Somewhere you must direct the deployment script to use the correct database. If you are doing that from a calling function like a PowerShell script, it could happen there. If not, your script needs:

USE [$(dbname)]

This should happen one time, near the top, after SQLCMD variable assignments and comments.

Notes: without this, chances are people will accidentally fill master with junk. Truly.

Create a Database if it Doesn’t Exist

/* Create the database only if it's not already present */
USE master

IF NOT EXISTS ( SELECT name FROM sys.databases WHERE name = '$(dbname)' )
BEGIN
    CREATE DATABASE [$(dbname)] ;
    ALTER DATABASE  [$(dbname)] MODIFY FILE
    ( NAME = N'$(dbname)', SIZE = 200MB, MAXSIZE = UNLIMITED, FILEGROWTH = 200MB ) ;
    ALTER DATABASE  [$(dbname)] MODIFY FILE
    ( NAME = N'$(dbname)_log', SIZE = 50MB, MAXSIZE = 2048GB, FILEGROWTH = 50MB ) ;
END
GO

/* Set any important database-level options */
ALTER DATABASE [$(dbname)] SET RECOVERY { FULL or SIMPLE } ;
ALTER DATABASE [$(dbname)] SET AUTO_SHRINK OFF ;
ALTER DATABASE [$(dbname)] SET AUTO_UPDATE_STATISTICS ON ;
ALTER DATABASE [$(dbname)] SET READ_COMMITTED_SNAPSHOT { ON or OFF } ;
ALTER DATABASE [$(dbname)] SET ALLOW_SNAPSHOT_ISOLATION { ON or OFF } ;
/* ... etc ... */

Notes:

Edit here 7 Sept 2013 based on Aaron’s comments:

This snippet works for small, simple databases. Edit the file size and growth settings to appropriate values, but please don’t use the truly awful default values from SQL Server. Avoid relying only on auto grow to size your databases, instead, when possible, allocating a plausible size and growth increment for files. If you can’t predict the size, let the system run for a time, and monitor, until it reaches a steady state for file size. If you control the model DB (if you’re creating in-house databases, for example) then you can adjust that. If you are deploying on unknown servers, then you can’t rely on reasonable defaults. That said, auto growing at a suitable increment is a reasonable fail-safe to prevent running out of room with a hard size limit.

This method has the advantage of using the default locations for files at the server level instead of hard-coding file paths – especially the cringe-worthy
N'C:\Program Files\Microsoft SQL Server\…

If your database requires more files and file groups, you might need to provide a file path, but ideally use SQLCMD variables so that it can be easily repointed.

Create a Table if it Doesn’t Exist

Preferred:

IF NOT EXISTS ( 
    SELECT * FROM sys.tables t
    INNER JOIN sys.schemas s on t.schema_id = s.schema_id
    WHERE s.name = 'dbo' and t.name = 'myTable' 
)
    CREATE TABLE dbo.myTable (
        col1 int not null,
        col2 nvarchar(50) not null
    );

Also works:

IF NOT EXISTS (
    SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[myTable]') AND type in (N'U')
)
    CREATE TABLE dbo.myTable (
        col1 int not null,
        col2 nvarchar(50) not null
    );

Notes:

Don’t forget the schema. Use two-part names everywhere unless there is a real and compelling requirement not to.

Management Studio can help generate these IF NOT EXISTS clauses, but the quality varies and some of the code behind that scripting is very old. Still, it can give you a working starting point. In SSMS, go to Tools > Options > SQL Server Object Explorer > Scripting and look for the setting “Check for Object Existence” or “Include IF NOT EXISTS clause,” then script out some objects with the GUI and examine what you get back. Except for procedures, these are sort of OK.

Create a View if it Doesn’t Exist

Preferred:

IF NOT EXISTS (
    SELECT * FROM sys.views v
    INNER JOIN sys.schemas s on v.schema_id = s.schema_id
    WHERE s.name = 'dbo' and v.name = 'myView'
)
    EXEC sp_executesql @statement = N'CREATE VIEW dbo.myView AS SELECT ''Placeholder'' AS Placeholder' ;
GO
ALTER VIEW dbo.myView AS
    SELECT col1 
    FROM dbo.myTable ;
GO

Also works:

IF NOT EXISTS (
    SELECT * FROM sys.views 
    WHERE object_id = OBJECT_ID(N'[dbo].[myView]')
)
    EXEC sp_executesql @statement = N'CREATE VIEW dbo.myView AS SELECT ''Placeholder'' AS Placeholder' ;
GO
ALTER VIEW dbo.myView AS
    SELECT col1 
    FROM dbo.myTable ;
GO

Notes:

Don’t forget the schema.

This looks odd at first, but is wonderful to use. It mimics the “CREATE OR ALTER” feature in some other dialects of SQL.

Create a Procedure if it Doesn’t Exist

Preferred:

IF NOT EXISTS (
    SELECT * FROM sys.procedures p
    INNER JOIN sys.schemas s on p.schema_id = s.schema_id
    WHERE s.name = 'dbo' and p.name = 'myProc'
)
    EXEC sp_executesql @statement = N'CREATE PROCEDURE dbo.myProc AS SELECT ''Placeholder'' AS Placeholder' ;
GO
ALTER PROCEDURE dbo.myProc AS
    SELECT col1 
    FROM dbo.myTable ;
GO

Also works:

IF NOT EXISTS (
    SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[myProc]') AND type in (N'P', N'PC')
)
    EXEC sp_executesql @statement = N'CREATE PROCEDURE dbo.myProc AS SELECT ''Placeholder'' AS Placeholder' ;
GO
ALTER PROCEDURE dbo.myProc AS
    SELECT col1 
    FROM dbo.myTable ;
GO

Notes:

Don’t forget the schema.

Like views, this rather strange one imitates “CREATE OR ALTER.” That allows you to keep one source file, in source control, for example, and update it without changing from CREATE to ALTER or even worrying about it.

Create a Function if it Doesn’t Exist

IF NOT EXISTS (
    SELECT * FROM sys.objects 
    WHERE object_id = OBJECT_ID(N'[dbo].[getFunky]') AND type in (N'FN', N'IF', N'TF', N'FS', N'FT')
)
    EXEC sp_executesql 
    @statement = N'CREATE FUNCTION dbo.getFunky () RETURNS TABLE AS RETURN SELECT ''Placeholder'' AS Placeholder' ;
GO
ALTER FUNCTION dbo.getFunky ( @someParameter int )
RETURNS TABLE
AS
    RETURN
        SELECT col1 
        FROM dbo.myTable
        WHERE col1 = @someParameter ;
GO

If a Primary Key is Missing, Add it

IF NOT EXISTS (
    SELECT * FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.indexes i on i.object_id = t.object_id
    WHERE i.is_primary_key = 1 
    AND s.name = 'dbo' AND t.name = 'myTable'
) 
    ALTER TABLE dbo.myTable
    ADD CONSTRAINT PK_myTable PRIMARY KEY /* { maybe CLUSTERED } */ (
        col1
    ) WITH ( ONLINE = ON /* or OFF */ )
    /* perhaps ON [myFileGroup] */ ;

Notes:

This snippet looks for any primary key at all. That’s because you often cannot trust the name of the primary key, if it was auto-generated by the server at some point in the past. It’s good practice to name constraints, but not everyone does. I actually have some code that says “if the primary key is present but not named {x} then sp_rename it to {x}.” But that’s a little DBA-nuts.

If a NonClustered Index is Missing (by Name), Add it

Preferred:

IF NOT EXISTS (
    SELECT * FROM sys.tables t
    INNER JOIN sys.schemas s ON t.schema_id = s.schema_id
    INNER JOIN sys.indexes i on i.object_id = t.object_id
    WHERE s.name = 'dbo' AND t.name = 'myTable' and i.name = 'myNCI'
) 
    CREATE NONCLUSTERED INDEX myNCI 
    on dbo.myTable ( 
        col2 
    ) WITH ( ONLINE = ON /* or OFF */ ) ;

Also works:

IF NOT EXISTS (
    SELECT * FROM sys.indexes 
    WHERE object_id = OBJECT_ID(N'[dbo].[myTable]') AND name = N'myNCI'
)
    CREATE NONCLUSTERED INDEX myNCI 
    on dbo.myTable ( 
        col2 
    ) WITH ( ONLINE = ON /* or OFF */ ) ;
GO

Notes: this assumes that you trust the name of the target index, and that if present the structure of that index is assumed to be correct. It doesn’t check the columns in the index.

Conclusion

I do wish SSMS had better and more sophisticated support for scripting these clauses, but, lacking that, I resort to a small library of these and look them up. I’ll add to this page as I locate more of these – but contributions are certainly welcome!

* Some people and organizations use various tools to help automate this, but my real-world experience tells me that old-fashioned scripts are still a huge proportion of database installation and patch procedures.

Published Tuesday, August 20, 2013 12:46 PM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Tony said:

I use the "IF NOT EXISTS...CREATE; ALTER..." for all of my deployment scripts, and I'm trying to get others I work with to adopt this style...but it's slow going.

August 20, 2013 3:13 PM
 

WayneS said:

FWIW, I prefer using the OBJECT_ID() function (using two-part names and the optional object_type to specify table/view/procedure/etc.), and the DB_ID() function for checking the database. Just check both for IS NULL. It's simpler, shorter, and easier to read.

August 20, 2013 4:18 PM
 

Koen Verbeeck said:

When you use schema compare within database projects (SSDT for SQL Server 2012 or the right version of Visual Studio for earlier versions), the resulting script is similar to those described in this blog. It also uses the SQLCMD variables and doesn't use DROP/CREATE (at least in the scripts I generated), so this tool is very useful to create decent deployment scripts.

August 21, 2013 3:07 AM
 

PatrickE said:

SSMS has a templates panel in which one may add their own templates and use ctrl-shift M

August 21, 2013 8:25 AM
 

pmbAustin said:

Is there a reason to avoid the DROP/CREATE pattern for Stored Procedures, Views, and Functions?  I understand you want to avoid it with tables, certainly... but I guess I'm missing the harm for Stored Procedures, (non-schema-bound-or-indexed) views, and functions.

At least in a world where you don't set up complicated permissions on objects, that is... and the above script snippets indicate that granting permissions isn't an issue in your examples.

Is it just a performance thing, or is there real harm done somewhere?  I know in pre-SQL2008 worlds, views had dependency issues, but that all seems resolved now.

August 21, 2013 11:33 AM
 

merrillaldrich said:

Great comments - thank you all for reading and responding.

Tony - keep pushing

WayneS - valid point. I am fond of names, but many people aren't. Both seem to work OK.

Koen - yes! That's no accident. I really like those tools, but there are cases where they aren't available or cannot be used (ISV delivery, for example) or people just don't know about them.

PatrickE - I tried the SSMS template feature but personally didn't love that. I do like SQL Prompt snippets

August 21, 2013 12:02 PM
 

merrillaldrich said:

pbmAustin - for me it is mostly related to perms, but also using a consistent approach. I don't feel like it's safe to assume there are no special permissions assigned and just pave over those objects, even if there's a policy like that in place.

August 21, 2013 12:04 PM
 

John Hennesey said:

Love the post!

One thing to consider with SQLCmd mode - if you get overzealous and enable it by default it will kill intellisense.  It's an unfortunate limitation of SSMS that may cost hours to figure out.  Some third party products are not susceptible to this (ie SqlPrompt).  There is a connect item for it... http://connect.microsoft.com/SQLServer/feedback/details/476513/intellisense-in-sqlcmd-mode

August 21, 2013 5:22 PM
 

merrillaldrich said:

Ooo, really interesting problem. I do use SQL Prompt so I was not even aware of that. Thanks!

August 21, 2013 7:05 PM
 

Mike Good said:

Good article.  

I agree with WayneS, for same reasons: simpler, shorter, easier to read.  Otherwise these existence checks can consume more space/lines-of-code than the actual object definitions.

And as long as MS sticks with underlying sysobjects paradigm, checking anything more than existence of object name with OBJECT_ID() is a potential failure point for scripts like this.  We can only have one object of a given name, regardless of the type.  

I also use COLUMNPROPERTY(OBJECT_ID(<table>), <column>, 'ColumnId') to check if column exists, and use other column properties when purpose of script is to alter column in some way.

August 26, 2013 11:47 AM
 

John said:

IF Exists #TableName  ?? Seems like that would come up as often or more often than database or normal table

August 27, 2013 7:34 AM
 

Ranga said:

I also have set xact_abort on on my data scripts.

August 27, 2013 11:49 AM
 

Aaron Bertrand said:

Great pos Merrill!

While I agree about not accepting SQL Server's horrible defaults (and you can change model so that even if you forget, you don't forget), and that you should always try to anticipate growth so that auto grows are never necessary, I disagree about disabling auto grow as a general principle. Even with the best predictive abilities, we can't always anticipate everything. I wouldn't let any of my customers disable auto grow because it can be a vital safety net in those freak scenarios you can't foresee. Having a growth event is a better alternative than ceasing all activity until, what, you manually grow the file yourself, or add a file somewhere else.

September 7, 2013 12:08 AM
 

Aaron Bertrand said:

I should never "type" on an iPad

September 7, 2013 12:09 AM
 

merrillaldrich said:

Hi Aaron - I agree completely. Rereading I see I did a poor job wording that note about auto grow. What I intended to say was, leave it on but don't rely on it as a norm. That is, don't ignore the size of files and just always rely on auto grow. Fixing now. Thanks for taking the time to read and comment! (POS or not :D)

September 7, 2013 10:34 AM
 

DevBio @http://blog.yvoz.net said:

You forget test for create USER and LOGIN.

October 30, 2013 7:21 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement