{"id":117,"date":"2015-06-30T00:00:00","date_gmt":"2013-01-01T00:00:00","guid":{"rendered":"https:\/\/www.sqlhammer.com\/blog\/writing-repeatable-t-sql"},"modified":"2026-03-18T21:51:53","modified_gmt":"2026-03-18T21:51:53","slug":"writing-repeatable-t-sql","status":"publish","type":"post","link":"https:\/\/sqlhammer.com\/index.php\/2015\/06\/30\/writing-repeatable-t-sql\/","title":{"rendered":"Writing Repeatable T-SQL"},"content":{"rendered":"<p>Writing code for a database is more of a challenge than writing for applications. Before the developers bite my head off, I do not mean that the languages, such as T-SQL, are more difficult or that applications are any less sophisticated than database management systems (DBMS). An important difference, however, is that the state of the database is absolutely critical when running any code while you can simply replace assemblies or script files with most applications. This is because applications only exist while they are being run, otherwise they are simply files. When it comes to data, we are not\u00a0updating a feature in the DBMS we are modifying the data itself.<\/p>\n<h2>Desired state development<\/h2>\n<p>The state sensitive nature of a database means that code you tested five minutes ago might still blow up in production if, during that five minutes, the state of the database changed in a way that is significant to your script. This is why we are seeing products,\u00a0like Microsoft\u2019s <a href=\"http:\/\/blogs.msdn.com\/b\/ssdt\/\">SQL Server Data Tools<\/a>,\u00a0which focus on desired state development. The concept is that the product will compare the database to the desired state and generate the non-breaking script to move from state A to state B, even if you did not know what state A would be until it was executed.<\/p>\n<p>I have a lot of positive comments that I could make\u00a0about this concept but I maintain that it will never be complete. There are state migration scenarios which require custom logic to handle properly and any tool is going to error on the side of caution when coming across these issues. It is safest to put the decision into the developer\u2019s or DBA\u2019s hands and I completely agree with that decision.<\/p>\n<h2>Repeatable code<\/h2>\n<p>Understanding the problem and accepting the fact that we will need to deal with these challenges ourselves for the foreseeable future, now we need to learn to always write repeatable code.<\/p>\n<h3>Objects<\/h3>\n<p>I have often seen, what I refer to as,\u00a0<em>lazy resilience.\u00a0<\/em>The best example of this is when trying to create a table.<\/p>\n<pre><code class=\"language-\">USE [Demo]\n\n<p>DROP TABLE dbo.Demo1<\/p>\n\n<p>CREATE TABLE dbo.Demo1\n(\n\tid INT IDENTITY(1,1) NOT NULL,\n\tmyCol NVARCHAR(10) NULL\n)<\/code><\/pre>\n<\/p>\n<p>This method technically works. Hopefully you are already getting angry with the author of this snippet. Some\u00a0problems with this script.<\/p>\n<ul>\n<li>The DROP TABLE could throw an error if this is a first time creation. Just because SSMS defaults to XACT_ABORT OFF, does not mean that you should rely upon the script continuing and creating your table. Maybe\u00a0this script ends up being run with XACT_ABORT ON.<\/li>\n<\/ul>\n<ul>\n<li>If the table does exist, you have just set yourself up for potential data loss. Read that again,\u00a0<strong>data loss<\/strong>.<\/li>\n<\/ul>\n<p>To be fair, I did choose the worst case scenario here. I can hear whispers in my mind saying, \u201cwe would use an IF NOT EXISTS to prevent the error,\u201d and, \u201cwell of course we would not do this for a table, but a stored procedure or view, that is ok.\u201d<\/p>\n<p>Let us take a look at a stored procedure example with those whispers in mind. Taking an existing stored procedure, dbo.spDemo, we will glance at the current permissions.<\/p>\n<pre><code class=\"language-\">SELECT dpals.name\n\t, OBJECT_NAME(dperms.major_id) [ObjectName]\n\t, dperms.permission_name\n\t, dperms.state_desc\nFROM sys.database_principals dpals\nINNER JOIN sys.database_permissions dperms\n\tON dperms.[grantee_principal_id] = dpals.[principal_id]\nWHERE name = 'DemoUser'\n\tAND OBJECT_NAME(dperms.major_id) = 'spDemo'<\/code><\/pre>\n<p><a href=\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2015\/06\/repeatable-sproc-demo1.png\"><img decoding=\"async\" src=\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2015\/06\/repeatable-sproc-demo1.png\" alt=\"\" \/><\/a><\/p>\n<p>Now we will update our stored procedure with our \u201crepeatable\u201d script. I even included the schema name in the IF statement, which I often see omitted.<\/p>\n<pre><code class=\"language-\">USE [Demo]\n\n<p>IF EXISTS\n\t(\n\t\tSELECT <em>\n\t\tFROM sys.objects o\n\t\tINNER JOIN sys.schemas s\n\t\tON s.schema_id = o.schema_id\n\t\tWHERE [type] = 'P'\n\t\t\tAND s.name = 'dbo'\n\t\t\tAND o.name = 'spDemo'\n\t)\nBEGIN\n\tDROP PROCEDURE dbo.spDemo\nEND\nGO<\/p>\n\n<p>CREATE PROCEDURE dbo.spDemo\nAS\nBEGIN\n\tSELECT 'My updated procedure' [Message]\nEND<\/code><\/pre>\n<\/p>\n<p>Executing our permissions script again shows us that we have just revoked access to this procedure from DemoUser.<\/p>\n<p><a href=\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2015\/06\/repeatable-sproc-demo2.png\"><img decoding=\"async\" src=\"http:\/\/www.sqlhammer.com\/blog\/wp-content\/uploads\/2015\/06\/repeatable-sproc-demo2.png\" alt=\"\" \/><\/a><\/p>\n<p>You could include the permissions at the bottom of your create script each time but that becomes problematic when you are not the person who manages security on all of the\u00a0servers.<\/p>\n<h4>Recommended practice<\/h4>\n<p>My preference is to\u00a0do what you actually intend. If you are looking to create or alter an object then CREATE or ALTER it, do not DROP anything. This is what I use for stored procedures and is equally relevant for other SQL Server object types.<\/p>\n<pre><code class=\"language-\">USE [Demo]\n\n<p>IF NOT EXISTS\n\t(\n\t\tSELECT <\/em>\n\t\tFROM sys.objects o\n\t\tINNER JOIN sys.schemas s\n\t\tON s.schema_id = o.schema_id\n\t\tWHERE [type] = 'P'\n\t\t\tAND s.name = 'dbo'\n\t\t\tAND o.name = 'spDemo'\n\t)\nBEGIN\n\tEXEC ( 'CREATE PROC dbo.spDemo AS SELECT ''Stubbed''' );\nEND\nGO<\/p>\n\n<p>ALTER PROCEDURE dbo.spDemo\nAS\nBEGIN\n\tSELECT 'My updated procedure' [Message]\nEND<\/code><\/pre>\n<\/p>\n<p>Using the CREATE\/ALTER method you preserve metadata such as object level permissions, extended properties, etc. You also have a means of quickly detecting problems, if proper error handling did not already bubble up an exception for you.<\/p>\n<pre><code class=\"language-\">select OBJECT_NAME(id) [ObjectName]\nfrom sys.syscomments\nwhere text like '%stubbed%'<\/code><\/pre>\n<p>This query will find all of your objects which failed to properly create. Remember though, when altering, you never dropped anything. Which means that you failed to update the object, leaving you with no change. <strong>You will never be left with the object dropped and no new version to replace it.<\/strong><\/p>\n<h3>Data<\/h3>\n<p>Creating repeatable DDL scripts is easy compared to DML scripts. With DDL, you can design a pattern, such as the one I recommend above, and reuse it for everything. With DML your code has to be extremely customized to what you are trying to do. To demonstrate this, I will make up a scenario which could exist in reality and then show how I would make the script repeatable.<\/p>\n<h4>Scenario<\/h4>\n<p>You are working for a company who provides\u00a0tax preparation services. This company has\u00a0the following table.<\/p>\n<pre><code class=\"language-\">USE Demo\n\n<p>CREATE TABLE dbo.TaxReturn\n(\n\ttaxReturnId UNIQUEIDENTIFIER NOT NULL DEFAULT(NEWID()),\n\ttaxReturnXml XML NULL,\n\toriginalReturnId UNIQUEIDENTIFIER NULL,\n\tisSupportCopy BIT NOT NULL DEFAULT(0)\n)<\/p>\n\n<p>INSERT INTO dbo.TaxReturn\n(\n\ttaxReturnId,\n\ttaxReturnXml,\n\toriginalReturnId,\n\tisSupportCopy\n)\nVALUES\n('55304D5A-3ED5-4208-853A-2BA90F32FD0E', '&lt;Return&gt;data&lt;\/Return&gt;', NULL, 0),\n('FAD116FF-D269-4C48-BC05-32649E419BC0', '&lt;Return&gt;data&lt;\/Return&gt;', '55304D5A-3ED5-4208-853A-2BA90F32FD0E', 1),\n('88C705B5-0640-4D2B-AEF6-7963C622798F', '&lt;Return&gt;orig&lt;\/Return&gt;', NULL, 0),\n('FFA528DB-0124-4799-A722-AD93EAFDA72C', '&lt;Return&gt;changed&lt;\/Return&gt;', '88C705B5-0640-4D2B-AEF6-7963C622798F', 0),\n('E803EA1F-2C48-4922-ACBE-BCCB57C29480', '&lt;Return&gt;orig&lt;\/Return&gt;', NULL, 0)<\/p>\n\n<p>SELECT <em>\nFROM dbo.TaxReturn<\/code><\/pre>\n<\/p>\n<p>A performance analysis was conducted and it was noticed that the\u00a0<\/em>originalReturnId<em> was only ever being used to determine if the tax return was the original or not. A decision is made to convert the UNIQUEIDENTIFIER column to a BIT.<\/p>\n<h4>Non-repeatable<\/h4>\n<p>Here is the script that you want to use to make the change. It will run but is not repeatable because the ALTER TABLE statement will throw and exception on the second run. The UPDATE statement can be run multiple times but, best case, it would perform unnecessary reads and writes and, worst case, would reset data that you had already made more changes to.<\/p>\n<pre><code class=\"language-\">USE Demo\n\n<p>ALTER TABLE dbo.TaxReturn\nADD isOriginal BIT NULL<\/p>\n\n<p>UPDATE dbo.TaxReturn\nSET isOriginal =\n\tCASE\n\t\tWHEN originalReturnId IS NOT NULL\n\t\t\tTHEN 0\n\t\tELSE 1\n\tEND<\/p>\n\n<p>ALTER TABLE dbo.TaxReturn\nALTER COLUMN isOriginal BIT NOT NULL<\/p>\n\n<p>--Update stored procs and views as well.<\/p>\n\n<p>SELECT <\/em>\nFROM dbo.TaxReturn<\/code><\/pre>\n<\/p>\n<h4>Repeatable<\/h4>\n<p>The below version of the script performs the exact same actions but can be safely executed over and over regardless of the state of the database.<\/p>\n<ul>\n<li>The column is only added if it does not exist already.<\/li>\n<\/ul>\n<ul>\n<li>The UPDATE will only write any data if the\u00a0<em>isOriginal<\/em> column is NULL.<\/li>\n<\/ul>\n<ul>\n<li>The\u00a0<em>isOriginal<\/em> column is altered only if the column is still nullable.<\/li>\n<\/ul>\n<pre><code class=\"language-\">USE Demo\n\n<p>IF NOT EXISTS\n\t(\n\t\tSELECT <em>\n\t\tFROM sys.tables t\n\t\tINNER JOIN sys.schemas s\n\t\t\tON s.schema_id = t.schema_id\n\t\tINNER JOIN sys.columns c\n\t\t\tON c.object_id = t.object_id\n\t\tWHERE s.name = 'dbo'\n\t\t\tAND t.name = 'TaxReturn'\n\t\t\tAND c.name = 'isOriginal'\n\t)\nBEGIN\n\tALTER TABLE dbo.TaxReturn\n\tADD isOriginal BIT NULL\nEND<\/p>\n\n<p>UPDATE dbo.TaxReturn\n\tSET isOriginal =\n\t\tCASE\n\t\t\tWHEN originalReturnId IS NOT NULL\n\t\t\t\tTHEN 0\n\t\t\tELSE 1\n\t\tEND\nWHERE isOriginal IS NULL<\/p>\n\n<p>IF NOT EXISTS\n\t(\n\t\tSELECT <\/em>\n\t\tFROM sys.tables t\n\t\tINNER JOIN sys.schemas s\n\t\t\tON s.schema_id = t.schema_id\n\t\tINNER JOIN sys.columns c\n\t\t\tON c.object_id = t.object_id\n\t\tWHERE s.name = 'dbo'\n\t\t\tAND t.name = 'TaxReturn'\n\t\t\tAND c.name = 'isOriginal'\n\t\t\tAND c.is_nullable = 1\n\t)\nBEGIN\n\tALTER TABLE dbo.TaxReturn\n\tALTER COLUMN isOriginal BIT NOT NULL\nEND<\/p>\n\n<p>--Update stored procs and views as well.\n--With the recommend pattern above.<\/p>\n\n<p>SELECT *\nFROM dbo.TaxReturn<\/code><\/pre>\n<\/p>\n<p>This certainly is a lot more code than the non-repeatable example. The resilience to database state change is very powerful, though. As a final remark, I would like to point out something that is not easy to see in the script. I am creating a NULL column, populating it, then converting to NOT NULL. One could point out\u00a0that I would use less code if I made the column NOT NULL with a default constraint before updating the values to what is true. I did not do this for two reasons. First, I do not like inaccurate data to exist in my database for longer than it absolutely has to. NULL works because we understand that it loosely means unknown and any queries which run looking for valid values would not include these records. The second reason is that you would end up putting all of your code into the first\u00a0IF NOT EXISTS block. This is not bad, assuming you add a transaction around all of the commands inside the IF, but it makes a block of code dependent upon a base condition rather than making each statement flexible to any variations of the database state.<\/p>\n<p>What is important, though, is that you think through all of the possible failure and partial complete scenarios and account for them, even if you believe the script will only ever be executed once. I have spent many off-hours troubleshooting production deployments that went bad. The deployment window can become the Wild West in a blink of an eye and you get to play the part of the Cowboy.<\/p>\n<hr \/>\n<p><img decoding=\"async\" src=\"\/wp-content\/uploads\/hammer-derik-172x140.png\" alt=\"\" \/><strong>Written by Derik Hammer of <a href=\"http:\/\/www.sqlhammer.com\/\">SQL Hammer<\/a><\/strong><\/p>\n<p>Derik is a data professional focusing on Microsoft SQL Server. His passion focuses around <a href=\"http:\/\/www.sqlhammer.com\/blog\/category\/high-availability\/\">high-availability<\/a>, <a href=\"http:\/\/www.sqlhammer.com\/blog\/category\/general\/disaster-recovery\/\">disaster recovery<\/a>, continuous integration, and automated maintenance. his experience has spanned long-term database administration, consulting, and\u00a0entrepreneurial ventures.<\/p>\n<p>Derik gives the SQL community credit for plugging the gaps in his knowledge when he was a junior DBA and, now that his skills have matured, started <a href=\"http:\/\/www.sqlhammer.com\/\">SQLHammer.com<\/a> as one small way to give back and continue the cycle of shared learning.<\/p>\n<p>Derik is the owner and lead author of SQL Hammer, a <a href=\"http:\/\/www.sqlhammer.com\/\">Microsoft SQL Server resource<\/a>.<\/p>\n<p>For more information, visit <a href=\"http:\/\/www.sqlhammer.com\/\">http:\/\/www.sqlhammer.com<\/a>. Follow Derik on <a href=\"http:\/\/twitter.com\/SQLHammer\">Twitter<\/a> for <strong>SQL tips and chat<\/strong><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Writing code for a database is more of a challenge than writing for applications. Before the developers bite my head off, I do not mean that the languages, such as T-SQL, are more difficult or that applications are any less sophisticated than database management systems (DBMS). An important difference, however, is that the state of [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-117","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/117","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=117"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/117\/revisions"}],"predecessor-version":[{"id":379,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/117\/revisions\/379"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=117"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=117"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=117"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}