{"id":847,"date":"2010-03-31T00:00:00","date_gmt":"2010-03-31T00:00:00","guid":{"rendered":"https:\/\/test.simple-talk.com\/uncategorized\/basic-defensive-database-programming-techniques\/"},"modified":"2021-09-29T16:22:00","modified_gmt":"2021-09-29T16:22:00","slug":"basic-defensive-database-programming-techniques","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/sql-server\/t-sql-programming-sql-server\/basic-defensive-database-programming-techniques\/","title":{"rendered":"Basic Defensive Database Programming Techniques"},"content":{"rendered":"<div class=\"spaced-bottom padded--tight scheme--lightest-grey\">\n<img decoding=\"async\" class=\"media__image\" style=\"float: left; vertical-align: middle;\" src=\"https:\/\/assets.red-gate.com\/external\/SimpleTalk\/database-devops-cog-92.png\" \/> <\/p>\n<p class=\"padded-top--tight\"><strong>DevOps, Continuous Delivery & Database Lifecycle Management<\/strong><br \/>Continuous Integration<\/p>\n<\/div>\n\n<p>The goal of defensive database programming is to produce resilient database code; in other words code that does not contain bugs and is not susceptible to being broken by unexpected use cases, small modifications to the underlying database schema, changes in SQL Server settings, and so on.<\/p>\n<p>If you fail to program defensively, then code that runs as expected on a given standalone server, with a specific configuration, may run very differently in a different environment, under different SQL Server settings, against different data, or under conditions of concurrent access. When this happens, you will be susceptible to erratic behavior in your applications, performance problems, data integrity issues and unhappy users.<\/p>\n<p>The process of reducing the number of vulnerabilities in your code, and so increasing its resilience, is one of constantly questioning the assumptions on which your implementation depends, ensuring they are always enforced if they are valid, and removing them if not. It is a process of constantly testing your code, breaking it, and then refining it based on what you have learned.<\/p>\n<p>The best way to get a feel for this process, and for how to expose vulnerabilities in your code and fix them using defensive programming techniques, is to take a look at a few common areas where I see that code is routinely broken by unintended use cases or erroneous assumptions:<\/p>\n<ul>\n<li>Unreliable search patterns<\/li>\n<li>Reliance on specific SQL Server environment settings<\/li>\n<li>Mistakes and ambiguity during data modifications<\/li>\n<\/ul>\n<p>In each case, we&#8217;ll identify the assumptions that lead to code vulnerability, and show how to fix them. All the examples in this article are as simple as possible in that there is no concurrency and the underlying database schema is fixed.<\/p>\n<p>My <a href=\"http:\/\/www.simple-talk.com\/books\/sql-books\/defensive-database-programming\/\">forthcoming book<\/a> on this subject introduces many of the additional dangers that can arise when exposing the code to changes in the database schema, running it under high concurrency, and so on.<\/p>\n<h2>Reducing Code Vulnerability<\/h2>\n<p>There are four key elements to defensive database programming that, when applied, will allow you to eliminate bugs and make your code less vulnerable to be being subsequently broken by cases of unintended use:<\/p>\n<ol>\n<li>Define and understand your assumptions<\/li>\n<li>Test as many use cases as possible<\/li>\n<li>Lay out your code in short, fully testable, and fully tested modules<\/li>\n<li>Reuse your code whenever feasible, so that the code to solve a given problem is implemented in one place only<\/li>\n<\/ol>\n<p>While I will occasionally make brief mention of the sort of checks and tests that ought to be included in your unit tests (steps 2 and 3), this article is focused on defensive programming, and so on the rigorous application of the first two principles.<\/p>\n<h2>Define your Assumptions<\/h2>\n<p>One of the most damaging mistakes made during the development of SQL code, and any other code, is a failure to explicitly define the assumptions that have been made regarding how the code should operate, and how it should respond to various inputs. Specifically, we must:<\/p>\n<ul>\n<li>Explicitly list the assumptions that have been made<\/li>\n<li>Ensure that the these assumptions always hold<\/li>\n<li>Systematically remove assumptions that are not essential, or are incorrect<\/li>\n<\/ul>\n<p>When identifying these assumptions, there can be one of three possible outcomes. Firstly, if an assumption is deemed essential, it must be documented, and then tested rigorously to ensure it always holds; I prefer to use unit tests to document such assumptions. Failure to do so will mean that when the code makes it into production it will inevitably be broken as a result of usage that conflicts with the assumption.<\/p>\n<p>Secondly, if the assumption is deemed non-essential, it should, if possible, be removed. Finally, in the worst case, the code may contain assumptions that are simply wrong, and can threaten the integrity of any data that the code modifies. Such assumptions must be eliminated from the code.<\/p>\n<h2>Rigorous Testing<\/h2>\n<p>As we develop code, we must use all our imagination to come up with cases of unintended use, trying to break our modules. We should incorporate these cases into our testing suites.<\/p>\n<p>As we test, we will find out how different changes affect code execution and learn how to develop code that does not break when &#8220;something&#8221;, for example a language setting or the value of <span class=\"STCodeinTextChar\">ROWCOUNT<\/span>, changes<\/p>\n<p>Having identified a setting that breaks one of our code modules, we should fix it and then identify and fix all other similar problems in our code. We should not stop at that. The defensive programmer must investigate all other database settings that may affect the way the code runs and then review and amend the code again and again, fixing potential problems before they occur. This process usually takes a lot of iterations, but every time we end up with better, more robust code and we will save a lot of potential wasted time in troubleshooting problems, as well as expensive retesting and redeployment, when the code is deployed to production.<\/p>\n<p>Throughout the rest of this article, we&#8217;ll discuss how this basic defensive coding philosophy is applied in practice, by way of some simple practical examples.<\/p>\n<h2>Defending Against Cases of Unintended Use<\/h2>\n<p>All-too-often, we consider our code to be finished as soon as it passes a few simple tests. We do not take enough time to identify and test all possible, reasonable use cases for our code. When the inevitable happens, and our code is used in a way we failed to consider, it does not work as expected.<\/p>\n<p>To demonstrate these points, we&#8217;ll consider an example that shows how (and how not) to use string patterns in searching. We&#8217;ll analyze a seemingly working stored procedure that searches a <span class=\"STCodeinTextChar\">Messages<\/span> table, construct cases of unintended use, and identify an implicit assumption on which the implementation of this procedure relies. We will then need to decide whether to eliminate the assumption or to guarantee that it always holds. Either way, we will end up with a more robust procedure.<\/p>\n<p>Listing 1 contains the code needed to create a sample <span class=\"STCodeinTextChar\">Messages<\/span> table, which holds the subject and body of various text messages, and load it with two sample messages. It then creates the stored procedure, <span class=\"STCodeinTextChar\">SelectMessagesBySubjectBeginning<\/span>, which will search the messages using a search pattern based on the <span class=\"STCodeinTextChar\">LIKE<\/span> keyword. The stored procedure takes one parameter, <span class=\"STCodeinTextChar\">SubjectBeginning<\/span>, and is supposed to return every message whose subject starts with the specified text.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE TABLE dbo.Messages  \r\n\u00a0\u00a0\u00a0 (\r\n\u00a0 \u00a0\u00a0\u00a0\u00a0MessageID INT NOT IDENTITY(1,1) NOT NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PRIMARY KEY,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Subject VARCHAR(30) NOT NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Body VARCHAR(100) NOT NULL\r\n\u00a0\u00a0\u00a0 ) ;\r\nGO\r\n\u00a0\r\nINSERT\u00a0 INTO dbo.Messages\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( Subject ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Body \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 'Next release delayed' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Still fixing bugs'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNION ALL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 'New printer arrived' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'By the kitchen area' ;\r\nGO\r\n\u00a0\r\nCREATE PROCEDURE dbo.SelectMessagesBySubjectBeginning\r\n\u00a0\u00a0\u00a0 @SubjectBeginning VARCHAR(30)\r\nAS \r\n\u00a0\u00a0\u00a0 SET NOCOUNT ON ; \r\n\u00a0\u00a0\u00a0 SELECT\u00a0 Subject ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Body\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 dbo.Messages\r\n\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 Subject LIKE @SubjectBeginning + '%' ;\r\n<\/pre>\n<p class=\"caption\">Listing 1: Creating and populating the <span class=\"STCodeinTextChar\">Messages<\/span> table along with the stored procedure to search the messages<\/p>\n<p>Some preliminary testing against this small set of test data, as shown in Listing 2, does not reveal any problems.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">-- must return one row\r\nEXEC dbo.SelectMessagesBySubjectBeginning\r\n\u00a0 @SubjectBeginning='Next';\r\n\u00a0\r\nSubject\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Body\r\n------------------------------ -------------------\r\nNext release delayed\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Still fixing bugs\r\n\r\n\u00a0\r\n-- must return one row\r\nEXEC dbo.SelectMessagesBySubjectBeginning\r\n\u00a0 @SubjectBeginning='New';\r\n\u00a0\r\nSubject\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Body\r\n------------------------------ -------------------\r\nNew printer arrived\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 By the kitchen area\r\n\u00a0\r\n-- must return two rows\r\nEXEC dbo.SelectMessagesBySubjectBeginning\r\n\u00a0 @SubjectBeginning='Ne';\r\n\u00a0\r\nSubject\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Body\r\n------------------------------ -------------------\r\nNext release delayed\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Still fixing bugs\r\nNew printer arrived\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 By the kitchen area\r\n\u00a0\r\n-- must return nothing\r\nEXEC dbo.SelectMessagesBySubjectBeginning\r\n\u00a0 @SubjectBeginning='No Such Subject';\r\n\u00a0\r\nSubject\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Body\r\n------------------------------ -------------------\r\n<\/pre>\n<p class=\"caption\">Listing 2: A few simple tests against the provided test data demonstrate that results match expectations<\/p>\n<h2>Handling Special Characters in Searching<\/h2>\n<p>In defensive database programming, it is essential to construct cases of unintended use with which to break our code. The test data in Listing 1 and the stored procedure calls in Listing 2 demonstrate the cases of <span class=\"STBold\">intended<\/span> use, and clearly the procedure works, when it is used as intended.<\/p>\n<p class=\"MsoNormal\">However, have we considered all the possible cases? Will the procedure continue to work as expected in cases of <span class=\"STBold\">unintended<\/span> use? Can we find any hidden bugs in this procedure? In fact, it is embarrassingly easy to break this stored procedure, simply by adding a few &#8220;off topic&#8221; messages to our table, as shown in Listing 3.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">INSERT\u00a0 INTO dbo.Messages\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( Subject ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Body\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 '[OT] Great vacation in Norway!' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Pictures already uploaded'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNION ALL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 '[OT] Great new camera' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Used it on my vacation' ;\r\nGO\r\n-- must return two rows\r\nEXEC dbo.SelectMessagesBySubjectBeginning\r\n\u00a0\u00a0\u00a0 @SubjectBeginning = '[OT]' ;\r\n\u00a0\r\nSubject\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Body\r\n------------------------------ -------------------\r\n<\/pre>\n<p class=\"caption\">Listing 3: Our procedure fails to return &#8220;off topic&#8221; messages<\/p>\n<p>Our procedure fails to return the expected messages. In fact, by loading one more message, as shown in Listing 4, we can demonstrate that this procedure can also return incorrect data.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">INSERT\u00a0 INTO dbo.Messages\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( Subject ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Body\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 'Ordered new water cooler' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Ordered new water cooler' ;\r\nEXEC dbo.SelectMessagesBySubjectBeginning\r\n\u00a0\u00a0 \u00a0@SubjectBeginning = '[OT]' ;\r\n\u00a0\r\nSubject\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0Body\r\n------------------------------ -------------------\r\nOrdered new water cooler\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Ordered new water cooler\r\n<\/pre>\n<p class=\"caption\">Listing 4: Our procedure returns the wrong messages when the search pattern contains [OT]<\/p>\n<p>When using the <span class=\"STCodeinTextChar\">LIKE<\/span> keyword, square brackets (&#8220;<span class=\"STCodeinTextChar\">[<\/span>&#8221; and &#8220;<span class=\"STCodeinTextChar\">]<\/span>&#8220;), are treated as wildcard characters, denoting a single character within a given range or set. As a result, while the search was intended to be one for off-topic posts, it in fact searched for &#8220;<span class=\"STItalic\">any messages whose subject starts with O or T<\/span>&#8220;. Therefore Listing 3 returns no rows, since no such messages existed at that point, whereas Listing 4 &#8220;unexpectedly&#8221; returns the message starting with &#8220;O&#8221;, rather than the off-topic messages.<\/p>\n<p>In a similar vein, we can also prove that the procedure fails for messages with the <span class=\"STCodeinTextChar\">%<\/span> sign in subject lines, as shown in Listing 5.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">INSERT\u00a0 INTO dbo.Messages\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( Subject ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Body\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 '50% bugs fixed for V2' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Congrats to the developers!'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNION ALL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 '500 new customers in Q1' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Congrats to all sales!' ;\r\nGO\r\n\u00a0\u00a0\u00a0 \r\nEXEC dbo.SelectMessagesBySubjectBeginning\r\n\u00a0\u00a0 \u00a0@SubjectBeginning = '50%' ;\r\n\u00a0\r\nSubject\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Body\r\n------------------------------ ----------------\r\n50% bugs fixed for V2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Congrats to the developers!\r\n500 new customers in Q1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Congrats to all sales!\r\n\u00a0\r\n<\/pre>\n<p class=\"caption\">Listing 5: Our stored procedure returns the wrong messages, along with the correct ones, if the pattern contains <span class=\"STCodeinTextChar\">%<\/span><\/p>\n<p>The problem is basically the same: the <span class=\"STCodeinTextChar\">%<\/span> sign is a wildcard character denoting &#8220;any string of zero or more characters&#8221;. Therefore, the search returns the &#8220;<span class=\"STItalic\">500 new customers&#8230;<\/span>&#8221; row in addition to the desired &#8220;<span class=\"STItalic\">50% bugs fixed&#8230;<\/span>&#8221; row.<\/p>\n<p>Our testing has revealed an implicit assumption that underpins the implementation of the <span class=\"STCodeinTextChar\">SelectMessagesBySubjectBeginning<\/span> stored procedure: the author of this stored procedure did not anticipate or expect that message subject lines could contain special characters, such as square brackets and percent signs. As a result, the search only works if the specified <span class=\"STCodeinTextChar\">SubjectBeginning<\/span> does not contain special characters.<\/p>\n<p>Having identified this assumption, we have a choice: we can either change our stored procedure so that it does not rely on this assumption, or we can enforce it.<\/p>\n<h2>Enforcing or Eliminating the Special Characters Assumption<\/h2>\n<p>Our first option is to fix our data by enforcing the assumption that messages will not contain special characters in their subject line. We can delete all the rows with special characters in their subject line, and then add a <span class=\"STCodeinTextChar\">CHECK<\/span> constraint that forbids their future use, as shown in Listing 6. The patterns used in the <span class=\"STCodeinTextChar\">DELETE<\/span> command and in the <span class=\"STCodeinTextChar\">CHECK<\/span> constraint are advanced and need some explanation. The first pattern, <span class=\"STCodeinTextChar\">%[[]%,<\/span> means the following:<\/p>\n<ul>\n<li>Both percent signs denote &#8220;any string of zero or more characters&#8221;<\/li>\n<li>[[] in this case denotes &#8220;opening square bracket, [&#8220;<\/li>\n<li>The whole pattern means\u00a0 &#8220;any string of zero or more characters, followed by an opening square bracket, followed by another string of zero or more characters&#8221;, which is equivalent to &#8220;any string containing at least one opening square bracket&#8221;<\/li>\n<\/ul>\n<p>Similarly, the second pattern<span class=\"STCodeinTextChar\">, %[%]%,<\/span> means &#8220;any string containing at least one percent sign&#8221;.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">BEGIN TRAN ;\r\nDELETE\u00a0 FROM dbo.Messages\r\nWHERE\u00a0\u00a0 Subject LIKE '%[[]%'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OR Subject LIKE '%[%]%' ;\r\n\u00a0\r\nALTER TABLE dbo.Messages\r\nADD CONSTRAINT Messages_NoSpecialsInSubject\r\n\u00a0\u00a0\u00a0 CHECK(Subject NOT LIKE '%[[]%' \r\n\u00a0\u00a0\u00a0\u00a0\u00a0 AND Subject NOT LIKE '%[%]%') ;\r\n\u00a0\r\nROLLBACK TRAN ; \r\n<\/pre>\n<p class=\"caption\">Listing 6: Enforcing the &#8220;no special characters&#8221; assumption<\/p>\n<p>Although enforcing the assumption is easy, does it make practical sense? It depends. I would say that under most circumstances special characters in subject lines should be allowed, so let&#8217;s consider a second, better option: eliminating the assumption. Note that Listing 6 rolls back the transaction, so that our changes are not persisted in the database.<\/p>\n<p>Listing 7 shows how to alter the stored procedure so that it can handle special characters. To better demonstrate how the procedure escapes special characters, I included some debugging output. Always remember to remove such debugging code before handing over the code for QA and deployment!<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">ALTER PROCEDURE dbo.SelectMessagesBySubjectBeginning\r\n\u00a0\u00a0\u00a0 @SubjectBeginning ng VARCHAR(50)\r\nAS \r\n\u00a0\u00a0\u00a0 SET NOCOUNT ON ;\r\n\u00a0\u00a0\u00a0 DECLARE @ModifiedSubjectBeginning VARCHAR(150) ;\r\n\u00a0 SET @ModifiedSubjectBeginning = \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REPLACE(REPLACE(@SubjectBeginning,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 '[',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 '[[]'),\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 '%',\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 '[%]') ;\r\n\u00a0\u00a0\u00a0 SELECT\u00a0 @SubjectBeginning AS [@SubjectBeginning] ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @ModifiedSubjectBeginning AS \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [@ModifiedSubjectBeginning] ;\r\n\u00a0\u00a0\u00a0 SELECT\u00a0 Subject ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Body\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 dbo.Messages\r\n\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 Subject LIKE @ModifiedSubjectBeginning + '%' ;\r\nGO\r\n<\/pre>\n<p class=\"caption\">Listing 7: Eliminating the &#8220;no special characters&#8221; assumption<\/p>\n<p>Listing 8 demonstrates that our stored procedure now correctly handles special characters. Of course, in a real world situation, all previous test cases have to be rerun to check that we didn&#8217;t break them in the process of fixing the bug.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">-- must return two rows\r\nEXEC dbo.SelectMessagesBySubjectBeginning\r\n\u00a0 @SubjectBeginning = '[OT]' ;\r\n\r\n@SubjectBeginning\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0@ModifiedSubjectBeginning\r\n----------------------------------------------------------\r\n[OT]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [[]OT]\r\n\u00a0\r\nSubject\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Body\r\n------------------------------ ----------------------------\r\n[OT] Great vacation in Norway! Pictures already uploaded \r\n[OT] Great new camera\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Used it on my vacation\r\n\r\n\u00a0\r\n-- must return one row\r\nEXEC dbo.SelectMessagesBySubjectBeginning\r\n\u00a0 @SubjectBeginning='50%';\r\n\u00a0\r\n@SubjectBeginning\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @ModifiedSubjectBeginning\r\n------------------------------------------------------------------\r\n50%\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 50[%]\r\n\u00a0\r\nSubject\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Body\r\n------------------------------ ----------------------\r\n50% bugs fixed for V2\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Congrats to the developers!\r\n<\/pre>\n<p class=\"caption\">Listing 8: Our search now correctly handles [ ] and %<\/p>\n<p>Whether we ultimately decide to enforce or eliminate the assumption, we have created a more robust search procedure as a result.<\/p>\n<h2>Defending Against Changes in SQL Server Settings<\/h2>\n<p>A common mistake made by developers is to develop SQL code on a given SQL Server, with a defined set of properties and settings, and then fail to consider how their code will respond when executed on instances with different settings, or when users change settings at the session level.<\/p>\n<p>Let&#8217;s examine a few simple cases of how hidden assumptions with regard to server settings can result in vulnerable code.<\/p>\n<h2>How SET ROWCOUNT can break a Trigger<\/h2>\n<p>Traditionally, developers have relied on the <span class=\"STCodeinTextChar\">SET<\/span> <span class=\"STCodeinTextChar\">ROWCOUNT<\/span> command to limit the number of rows returned to a client for a given query, or to limit the number of rows on which a data modification statement (<span class=\"STCodeinTextChar\">UPDATE<\/span>, <span class=\"STCodeinTextChar\">DELETE,<\/span> <span class=\"STCodeinTextChar\">MERGE<\/span> or <span class=\"STCodeinTextChar\">INSERT<\/span>) acts. In either case, <span class=\"STCodeinTextChar\">SET<\/span> <span class=\"STCodeinTextChar\">ROWCOUNT<\/span> works by instructing SQL Server to stop processing after a specified number of rows.<\/p>\n<p>However, use of <span class=\"STCodeinTextChar\">SET<\/span> <span class=\"STCodeinTextChar\">ROWCOUNT<\/span> can have some unexpected consequences for the unwary developer. Consider a very simple table, <span class=\"STCodeinTextChar\">Objects<\/span>, which stores basic size and weight information about objects, as shown in Listing 9.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE TABLE dbo.Objects\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 ObjectID INT NOT NULL PRIMARY KEY ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 SizeInInches FLOAT NOT NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 WeightInPounds FLOAT NOT NULL\r\n\u00a0\u00a0\u00a0 ) ;\r\nGO\r\nINSERT\u00a0 INTO dbo.Objects\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( ObjectID ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SizeInInches ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WeightInPounds\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 1 ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10 ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNION ALL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 2 ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12 ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNION ALL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 3 ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 20 ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 22 ;\r\nGO\r\n<\/pre>\n<p class=\"caption\">Listing 9: Creating and populating the <span class=\"STCodeinTextChar\">Objects<\/span> table<\/p>\n<p>We are required to start logging all updates of existing rows in this table, so we create a second table, <span class=\"STCodeinTextChar\">ObjectsChangeLog<\/span>, in which to record the changes made, and a trigger that will fire whenever data in the <span class=\"STCodeinTextChar\">Objects<\/span> table is updated, record details of the changes made, and insert them into <span class=\"STCodeinTextChar\">ObjectsChangeLog<\/span>.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE TABLE dbo.ObjectsChangeLog\r\n\u00a0 (\r\n\u00a0\u00a0\u00a0 ObjectsChangeLogID INT NOT NULL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 IDENTITY ,\r\n\u00a0\u00a0\u00a0 ObjectID INT NOT NULL ,\r\n\u00a0\u00a0\u00a0 ChangedColumnName VARCHAR(20) NOT NULL ,\r\n\u00a0\u00a0\u00a0 ChangedAt DATETIME NOT NULL ,\r\n\u00a0\u00a0\u00a0 OldValue FLOAT NOT NULL ,\r\n\u00a0\u00a0\u00a0 CONSTRAINT PK_ObjectsChangeLog PRIMARY KEY \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( ObjectsChangeLogID )\r\n\u00a0 ) ;\r\n\u00a0GO\r\n\u00a0\r\nCREATE TRIGGER Objects_UpdTrigger ON dbo.Objects\r\n\u00a0 FOR UPDATE\r\nAS\r\n\u00a0 BEGIN; \r\n\u00a0\u00a0\u00a0 INSERT\u00a0 INTO dbo.ObjectsChangeLog\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( ObjectID ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ChangedColumnName ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ChangedAt ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OldValue\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 i.ObjectID ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'SizeInInches' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CURRENT_TIMESTAMP ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 d.SizeInInches\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 inserted AS i\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN deleted AS d ON \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i.ObjectID = d.ObjectID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 i.SizeInInches &lt;&gt; d.SizeInInches\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNION ALL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 i.ObjectID ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'WeightInPounds' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CURRENT_TIMESTAMP ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 d.WeightInPounds\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 inserted AS i\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN deleted AS d ON \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i.ObjectID = d.ObjectID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE i.WeightInPounds &lt;&gt; d.WeightInPounds ;\r\n\u00a0 END ; \r\n<\/pre>\n<p class=\"caption\">Listing 10: Logging updates to the <span class=\"STCodeinTextChar\">Objects<\/span> table<\/p>\n<p class=\"MsoNormal\">Please note that my approach to all example in this book is to keep them as simple as they can be while still providing a realistic demonstration of the point, which here is the effect of <span class=\"STCodeinTextChar\">SET<\/span> <span class=\"STCodeinTextChar\">ROWCOUNT<\/span>. So, in this case, I have omitted:<\/p>\n<ul>\n<li>A &#8220;real&#8221; key on the ObjectsChangeLog table, enforced by a <span class=\"STCodeinTextChar\">UNIQUE<\/span> constraint (<span class=\"STCodeinTextChar\">ObjectID<\/span>, <span class=\"STCodeinTextChar\">ChangedColumnName<\/span>, <span class=\"STCodeinTextChar\">ChangedAt<\/span>), in addition to the surrogate key on <span class=\"STCodeinTextChar\">ObjectsChangeLogID<\/span><\/li>\n<li>The equivalent insert and delete triggers to log <span class=\"STCodeinTextChar\">INSERT<\/span> and <span class=\"STCodeinTextChar\">DELETE<\/span> modifications, as well as <span class=\"STCodeinTextChar\">UPDATE<\/span>s<\/li>\n<\/ul>\n<p>Likewise, there are several ways of logging changes and the one I chose here may not be the best approach; again my goal was to keep the example focused and simple. Listing 11 shows the code that tests how our trigger logs changes against the <span class=\"STCodeinTextChar\">Objects<\/span> table.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">BEGIN TRAN ;\r\n\u00a0\r\n-- TRUNCATE TABLE can also be used here\r\nDELETE\u00a0 FROM dbo.ObjectsChangeLog ;\r\n\u00a0\r\nUPDATE\u00a0 dbo.Objects\r\nSET\u00a0\u00a0\u00a0\u00a0 SizeInInches = 12 ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WeightInPounds = 14\r\nWHERE\u00a0\u00a0 ObjectID = 1 ;\r\n\u00a0\r\n-- we are selecting just enough columns \r\n-- to demonstrate that the trigger works\r\n\u00a0\r\nSELECT\u00a0 ObjectID ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ChangedColumnName ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OldValue\r\nFROM\u00a0\u00a0\u00a0 dbo.ObjectsChangeLog ;\r\n\u00a0\r\n-- we do not want to change the data,\r\n-- only to demonstrate how the trigger works\r\nROLLBACK ;\r\n-- the data has not been modified by this script\r\n\u00a0\r\nObjectID\u00a0\u00a0\u00a0 ChangedColumnName\u00a0\u00a0\u00a0 OldValue\r\n----------- -------------------- ------\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SizeInInches\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WeightInPounds\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10\r\n<\/pre>\n<p class=\"caption\">Listing 11: Testing the trigger<\/p>\n<p>Apparently, our trigger works as expected! However, with a little further testing, we can prove that the trigger will sometimes fail to log <span class=\"STCodeinTextChar\">UPDATE<\/span>s made to the <span class=\"STCodeinTextChar\">Objects<\/span> table, due to an underlying assumption in the trigger code, of which the developer may not even have been aware!<\/p>\n<h2>The ROWCOUNT Assumption<\/h2>\n<p>Let&#8217;s consider what might happen if, within a given session, a user changed the default value for <span class=\"STCodeinTextChar\">ROWCOUNT<\/span> and then updated the <span class=\"STCodeinTextChar\">Objects<\/span> table, without resetting <span class=\"STCodeinTextChar\">ROWCOUNT<\/span>, as shown in Listing 12.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">DELETE\u00a0 FROM dbo.ObjectsChangeLog ;\r\n\u00a0\r\nSET ROWCOUNT 1 ;\r\n-- do some other operation(s) \r\n-- for which we needed to set rowcount to 1\r\n-- do not restore ROWCOUNT setting\r\n-- to its default value\r\nBEGIN TRAN ;\r\n\u00a0\r\nUPDATE\u00a0 dbo.Objects\r\nSET\u00a0\u00a0\u00a0\u00a0 SizeInInches = 12 ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WeightInPounds = 14\r\nWHERE\u00a0\u00a0 ObjectID = 1 ;\r\n\u00a0\r\n-- make sure to restore ROWCOUNT setting\r\n-- to its default value so that it does not affect the\r\n-- following SELECT\r\n\u00a0\r\nSET ROWCOUNT 0 ;\r\n\u00a0\r\nSELECT\u00a0 ObjectID ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ChangedColumnName ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OldValue\r\nFROM\u00a0\u00a0\u00a0 dbo.ObjectsChangeLog ;\r\n\u00a0\r\nROLLBACK ;\r\n\u00a0\r\nObjectID\u00a0\u00a0\u00a0 ChangedColumnName\u00a0\u00a0\u00a0 OldValue\r\n----------- -------------------- ---------\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SizeInInches\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 10\r\n<\/pre>\n<p class=\"caption\">Listing 12: Breaking the trigger by changing the value of <span class=\"STCodeinTextChar\">ROWCOUNT<\/span><\/p>\n<p>As a result of the change to the <span class=\"STCodeinTextChar\">ROWCOUNT<\/span> value, our trigger processes the query that logs changes to the <span class=\"STCodeinTextChar\">SizeInInches<\/span> column, returns one row, and then ceases processing. This means that it fails to log the change to <span class=\"STCodeinTextChar\">WeightInPounds<\/span> column. Of course, there is no guarantee that the trigger will log the change to the <span class=\"STCodeinTextChar\">SizeInInches<\/span> column. On your server, the trigger may log only the change of <span class=\"STCodeinTextChar\">WeightInPounds<\/span> but fail to log the change in <span class=\"STCodeinTextChar\">SizeInInches<\/span>. Which column will be logged depends on the execution plan chosen by the optimizer, and we cannot assume that the optimizer will always choose one and the same plan for a query.<\/p>\n<p>Although the developer of the trigger may not have realized it, the implied assumption regarding its implementation is that <span class=\"STCodeinTextChar\">ROWCOUNT<\/span> is set to its default value. Listing 12 proves that that when this assumption is not true, the trigger will not work as expected.<\/p>\n<h2>Enforcing and Eliminating the ROWCOUNT Assumption<\/h2>\n<p>Once we understand the problem, we can fix the trigger very easily, by resetting <span class=\"STCodeinTextChar\">ROWCOUNT<\/span> to its default value at the very beginning of the body of the trigger, as shown in Listing 13.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">ALTER TRIGGER dbo.Objects_UpdTrigger ON dbo.Objects\r\n\u00a0\u00a0\u00a0 FOR UPDATE\r\nAS\r\n\u00a0\u00a0\u00a0 BEGIN;\r\n-- the scope of this setting is the body of the trigger\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET ROWCOUNT 0 ;\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INSERT\u00a0 INTO dbo.ObjectsChangeLog\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( ObjectID ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ChangedColumnName ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ChangedAt ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OldValue\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 i.ObjectID ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'SizeInInches' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CURRENT_TIMESTAMP ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 d.SizeInInches\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 inserted AS i\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN deleted AS d ON\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i.ObjectID = d.ObjectID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 i.SizeInInches &lt;&gt; d.SizeInInches\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNION ALL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 i.ObjectID ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'WeightInPounds' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 CURRENT_TIMESTAMP ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 d.WeightInPounds\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 inserted AS i\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN deleted AS d ON\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 i.ObjectID = d.ObjectID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 i.WeightInPounds &lt;&gt; \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 d.WeightInPounds ;\r\n\u00a0\u00a0\u00a0 END ;\r\n-- after the body of the trigger completes,\r\n-- the original value of ROWCOUNT is restored\r\n-- by the database engine\r\n<\/pre>\n<p class=\"caption\">Listing 13: Resetting <span class=\"STCodeinTextChar\">ROWCOUNT<\/span> at the start of the trigger<\/p>\n<p>We can rerun the test from Listing 12 and this time the trigger will work as required, logging both changes. Note that the scope of our <span class=\"STCodeinTextChar\">SET ROWCOUNT<\/span> is the trigger, so our change will not affect the setting valid at the time when the trigger was fired.<\/p>\n<div class=\"note\">\n<p class=\"note\">SET ROWCOUNT is deprecated in SQL Server 2008&#8230;<\/p>\n<p>&#8230;and eventually, in some future version, will have no effect on <span class=\"STCodeinTextChar\">INSERT<\/span>, <span class=\"STCodeinTextChar\">UPDATE<\/span> or <span class=\"STCodeinTextChar\">DELETE<\/span> statements. Microsoft advises rewriting any such statements that rely on <span class=\"STCodeinTextChar\">ROWCOUNT<\/span> to use <span class=\"STCodeinTextChar\">TOP<\/span> instead. As such, this example may be somewhat less relevant for future versions of SQL Server; the trigger might be less vulnerable to being broken, although still not immune. However, at the time of writing, this example is very relevant.<\/p>\n<\/div>\n<p>In this case, one simple step both enforces the underlying assumption, by ensuring that it is always valid, and eliminates it, by ensuring that the code continues to work in cases where <span class=\"STCodeinTextChar\">ROWCOUNT<\/span> is not at its default value.<\/p>\n<h2>Proactively Fixing SET ROWCOUNT Vulnerabilities<\/h2>\n<p>We have fixed the <span class=\"STCodeinTextChar\">ROWCOUNT<\/span> vulnerability in our trigger, but our job is not done. What about other modules in our system? Might they not have the same vulnerability?<\/p>\n<p>Having learned of the potential side effects of <span class=\"STCodeinTextChar\">SET<\/span> <span class=\"STCodeinTextChar\">ROWCOUNT<\/span>, we can now analyze all the other modules in our system, determine if they have the same problem, and fix them if they do. For example, our stored procedure <span class=\"STCodeinTextChar\">SelectMessagesBySubjectBeginning<\/span> (Listing 1) has the same vulnerability, as demonstrated by the test in Listing 14.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">SET ROWCOUNT 1 ;\r\n-- must return two rows\r\nEXEC dbo.SelectMessagesBySubjectBeginning\r\n\u00a0\u00a0 \u00a0@SubjectBeginning = 'Ne' ;\r\n\r\n...(Snip)...\r\n\u00a0\r\nSubject\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Body\r\n------------------------------ -------------------\r\nNext release delayed\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Still fixing bugs\r\n<\/pre>\n<p class=\"caption\">Listing 14: <span class=\"STCodeinTextChar\">SET ROWCOUNT<\/span> can break a stored procedure just as easily as it can break a trigger<\/p>\n<p>We can apply the same fix, adding <span class=\"STCodeinTextChar\">SET ROWCOUNT 0;<\/span> to the very beginning of this stored procedure. Similarly, we should apply this fix to all other modules that need it.<\/p>\n<p>If your code is supposed to exist for a considerable time, then it makes perfect sense to fix problems proactively. It is usually faster and easier to do so than to wait until the problem occurs, spend considerable time troubleshooting, and then eventually implement the same fix.<\/p>\n<h2>How SET LANGUAGE can break a Query<\/h2>\n<p>Just as the value of <span class=\"STCodeinTextChar\">ROWCOUNT<\/span> can be changed at the session level, so can other settings, such as the default language. Many developers test their code only under the default language setting of their server and do not test how their code will respond if executed on a server with a different language setting, or to a change in the setting at the session level.<\/p>\n<p>This practice is perfectly correct as long as our code always runs under the same settings as those under which we develop and test it. However, if or when the code runs under different settings, this practice will often result in code that is vulnerable to errors, especially when dealing with dates.<\/p>\n<p>Consider the case of a stored procedure that is supposed to retrieve from our <span class=\"STCodeinTextChar\">ObjectsChangeLog<\/span> table (Listing 10) a listing of all changes made to the <span class=\"STCodeinTextChar\">Objects<\/span> table over a given date range. According to the requirements, only the beginning of the range is required; the end of the range is an optional parameter. If an upper bound for the date range is not provided, we are required to use a date far in the future, December 31st, 2099, as the end of our range.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE PROCEDURE dbo.SelectObjectsChangeLogForDateRange\r\n\u00a0\u00a0\u00a0 @DateFrom DATETIME ,\r\n\u00a0\u00a0\u00a0 @DateTo DATETIME = NULL\r\nAS \r\n\u00a0\u00a0\u00a0 SET ROWCOUNT 0 ;\r\n\u00a0\u00a0\u00a0 SELECT\u00a0 ObjectID ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ChangedColumnName ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ChangedAt ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OldValue\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 dbo.ObjectsChangeLog\r\n\u00a0\u00a0\u00a0 WHERE ChangedAt BETWEEN @DateFrom\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND\u00a0 COALESCE(@DateTo, '12\/31\/2099') ;\r\nGO\r\n<\/pre>\n<p class=\"caption\">Listing 15: Creating the <span class=\"STCodeinTextChar\">SelectObjectsChangeLogForDateRange<\/span> stored procedure<\/p>\n<p>Note that this stored procedure uses a string literal, <span class=\"STCodeinTextChar\">12\/31\/2099<\/span>, to denote December 31st, 2099. Although <span class=\"STCodeinTextChar\">12\/31\/2099<\/span> does represent December 31st, 2099 in many languages, such as US English, in many other cultures, such as Norwegian, this string does not represent a valid date. This means that the author of this stored procedure has made an implicit assumption: the code will always run under language settings where <span class=\"STCodeinTextChar\">12\/31\/2099<\/span> represents December 31st, 2099.<\/p>\n<p>When we convert string literals to <span class=\"STCodeinTextChar\">DATETIME<\/span> values, we do not have to make assumptions about language settings. Instead, we can explicitly specify the <span class=\"STCodeinTextChar\">DATETIME<\/span> format from which we are converting.<\/p>\n<p>The following scripts demonstrate both the safe way to convert character strings to <span class=\"STCodeinTextChar\">DATETIME<\/span> values, and the vulnerability of our stored procedure to changes in language settings. The script shown in Listing1.18 populates the <span class=\"STCodeinTextChar\">ObjectsChangeLog<\/span> table and calls the <span class=\"STCodeinTextChar\">SelectObjectsChangeLogForDateRange<\/span> stored procedure under two different language settings, US English and Norwegian.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">-- we can populate this table via our trigger, but\r\n-- I used INSERTs,to keep the example simple\r\nINSERT\u00a0 INTO dbo.ObjectsChangeLog\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( ObjectID ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ChangedColumnName ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ChangedAt ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OldValue\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 1 ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'SizeInInches' ,\r\n-- the safe way to provide July 7th, 2009 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 '20090707', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12.34 ;\r\n\u00a0GO\r\n\u00a0\r\nSET LANGUAGE 'us_english' ;\r\n-- this convertion always works in the same way,\r\n-- regardless of the language settings,\r\n-- because the format is explicitly specified\r\nEXEC dbo.SelectObjectsChangeLogForDateRange\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 @DateFrom = '20090101';\r\n\u00a0\r\nSET LANGUAGE 'Norsk' ;\r\n\u00a0\r\nEXEC dbo.SelectObjectsChangeLogForDateRange\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @DateFrom = '20090101';\r\n\u00a0\r\n-- your actual error message may be different from mine,\r\n-- depending on the version of SQL Server\r\n\r\n\u00a0\r\nChanged language setting to us_english.\r\n(successful output skipped)\r\n\u00a0\r\nChanged language setting to Norsk.\r\nObjectID\u00a0\u00a0\u00a0 ChangedColumnName\u00a0\u00a0\u00a0 ChangedAt\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OldValue\r\n----------- -------------------- ----------------------- --------------\r\nMsg 242, Level 16, State 3, Procedure SelectObjectsChangeLogForDateRange, Line 6\r\nThe conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.\r\n<\/pre>\n<p class=\"caption\">Listing 16: Our stored procedure breaks under Norwegian language settings<\/p>\n<p>Under the Norwegian language settings we receive an error at the point where it attempts to convert <span class=\"STCodeinTextChar\">12\/31\/2099<\/span> into a <span class=\"STCodeinTextChar\">DATETIME<\/span> string.<\/p>\n<p>Note that we are, in fact, quite fortunate to receive an error message right away. Should we, in some other script or procedure, convert <span class=\"STCodeinTextChar\">&#8217;10\/12\/2008&#8242;<\/span> to <span class=\"STCodeinTextChar\">DATETIME<\/span>, SQL Server would silently convert this constant to a wrong value and we&#8217;d get incorrect results. Listing 17 shows how our stored procedure can return unexpected results without raising errors; such silent bugs may be very different to troubleshoot.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">INSERT\u00a0 INTO dbo.ObjectsChangeLog\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( ObjectID ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ChangedColumnName ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ChangedAt ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OldValue\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 1 ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'SizeInInches' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- this means June 15th, 2009\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 '20090615', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12.3\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNION ALL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 1 ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'SizeInInches' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 -- this means September 15th, 2009 \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 '20090915', \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12.5 \r\n\u00a0\r\nSET LANGUAGE 'us_english' ;\r\n\u00a0\r\n-- this call returns rows from Jul 6th to Sep 10th, 2009\r\n-- one log entry meets the criteria\r\nEXEC SelectObjectsChangeLogForDateRange \r\n\u00a0 @DateFrom = '07\/06\/2009',\r\n\u00a0 @DateTo = '09\/10\/2009' ;\r\n\u00a0\r\nSET LANGUAGE 'Norsk' ;\r\n\u00a0\r\n-- this call returns rows from Jun 7th to Oct 9th, 2009\r\n-- three log entries meet the criteria\r\nEXEC SelectObjectsChangeLogForDateRange\r\n\u00a0 @DateFrom = '07\/06\/2009',\r\n\u00a0 @DateTo = '09\/10\/2009' ;\r\n\u00a0\r\nChanged language setting to us_english.\r\nObjectID\u00a0\u00a0\u00a0 ChangedColumnName\u00a0\u00a0\u00a0 ChangedAt\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OldValue\r\n----------- -------------------- ----------------------- \r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0SizeInInches\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-07-07\u00a0\u00a0\u00a0\u00a0\u00a0 12.34\r\n\r\n-- because the stored procedure does not have an ORDER BY\r\n-- clause, your results may show up in a different\r\n-- order\r\n\u00a0\r\nChanged language setting to Norsk.\r\nObjectID\u00a0\u00a0\u00a0 ChangedColumnName\u00a0\u00a0\u00a0 ChangedAt\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OldValue\r\n----------- -------------------- ----------------------- \r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SizeInInches\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-07-07\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12.34\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SizeInInches\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-06-15\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12.3\r\n1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SizeInInches\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 2009-09-15\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 12.5\r\n<\/pre>\n<p class=\"caption\">Listing 17: Our stored procedure call returns different results, depending on language settings<\/p>\n<p>To fix the stored procedure, as shown in Listing 18, we need to explicitly specify the format from which we convert the <span class=\"STCodeinTextChar\">VARCHAR<\/span> values provided when the stored procedure is executed.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">ALTER PROCEDURE dbo.SelectObjectsChangeLogForDateRange\r\n\u00a0\u00a0\u00a0 @DateFrom DATETIME ,\r\n\u00a0\u00a0\u00a0 @DateTo DATETIME = NULL\r\nAS \r\n\u00a0\u00a0\u00a0 SET ROWCOUNT 0 ; \r\n\u00a0\u00a0\u00a0 SELECT\u00a0 ObjectID ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ChangedColumnName ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ChangedAt ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 OldValue\r\n\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 dbo.ObjectsChangeLog\r\n\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 ChangedAt BETWEEN @DateFrom\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND\u00a0\u00a0\u00a0\u00a0 COALESCE(@DateTo,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 '20991231') ;\r\n<\/pre>\n<p class=\"caption\">Listing 18: Fixing the stored procedure<\/p>\n<p>The stored procedure will now run correctly, regardless of the language settings. In this case, we chose to fix the problem by eliminating the assumption. Alternatively, in some cases, we might choose to enforce it by setting the language at the beginning of the stored procedure, just as we did with the <span class=\"STCodeinTextChar\">ROWCOUNT<\/span> setting.<\/p>\n<p>Of course, there are situations when our code will always run under one and the same settings, in which case there is no need to do anything. For example, if a module implements business rules specific to the state of Minnesota, it is reasonable to assume that it will always run under the same language settings.<\/p>\n<h2>Defensive Data Modification<\/h2>\n<p>Data modification is, in general, an area in which I see developers getting into trouble time and again. We&#8217;ll start with a case that demonstrates how data can be erroneously updated as a result of a false assumption in the stored procedure that modifies it. It is a simple example, but the underlying problem is a very common one: <span class=\"STItalic\">using search criteria that affect more rows than intended<\/span>.<\/p>\n<p>We&#8217;ll then discuss a second, somewhat more complex case, where an <span class=\"STCodeinTextChar\">UPDATE<\/span> can go wrong because it fails to unambiguously identify the row(s) to be modified, perhaps falsely assuming that the underlying data structures will ensure that no such ambiguity exists.<\/p>\n<h2>Updating more rows than intended<\/h2>\n<p>Listing 19 creates a simple <span class=\"STCodeinTextChar\">Employee<\/span> table, and a <span class=\"STCodeinTextChar\">SetEmployeeManager<\/span> stored procedure that assigns a manager to a given employee.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE TABLE dbo.Employee\r\n\u00a0 (\r\n\u00a0\u00a0\u00a0 EmployeeID INT NOT NULL ,\r\n\u00a0\u00a0\u00a0 ManagerID INT NULL ,\r\n\u00a0\u00a0\u00a0 FirstName VARCHAR(50) NULL ,\r\n\u00a0\u00a0\u00a0 LastName VARCHAR(50) NULL ,\r\n\u00a0\u00a0\u00a0 CONSTRAINT PK_Employee_EmployeeID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 PRIMARY KEY CLUSTERED ( EmployeeID ASC ) ,\r\n\u00a0\u00a0\u00a0 CONSTRAINT FK_Employee_EmployeeID_ManagerID\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FOREIGN KEY ( ManagerID )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 REFERENCES dbo.Employee ( EmployeeID )\r\n\u00a0 ) ;\r\nGO\r\n\u00a0\r\nCREATE PROCEDURE dbo.SetEmployeeManager\r\n\u00a0 @FirstName VARCHAR(50) ,\r\n\u00a0 @LastName VARCHAR(50) ,\r\n\u00a0 @ManagerID INT\r\nAS \r\n\u00a0 SET NOCOUNT ON ;\r\n\u00a0 UPDATE\u00a0 dbo.Employee\r\n\u00a0 SET\u00a0\u00a0\u00a0\u00a0 ManagerID = @ManagerID\r\n\u00a0 WHERE\u00a0\u00a0 FirstName = @FirstName\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND LastName = @LastName ;\r\n<\/pre>\n<p class=\"caption\">Listing 19: The <span class=\"STCodeinTextBold\">Employee<\/span> table and <span class=\"STCodeinTextBold\">SetEmployeeManager<\/span> stored procedure<\/p>\n<p>Clearly, the person who developed the stored procedure assumed that, at most, one employee may have the provided first and last name. If there happens to be two people in the organization with the same name then this stored procedure will assign them both to the same manager.<\/p>\n<p>Again, having uncovered the assumption, we need to decide whether to enforce it or eliminate it. We could enforce it simply by placing a <span class=\"STCodeinTextChar\">UNIQUE<\/span> constraint on the <span class=\"STCodeinTextChar\">FirstName<\/span> and <span class=\"STCodeinTextChar\">LastName<\/span> columns. However, in this case, it seems much more reasonable to assume that there may well be more than one employee with the same first and last name, and that these namesake employees may report to different managers. Therefore, we need to eliminate the incorrect assumption. There are many ways to do this, the simplest being to ensure that the parameter supplied to the stored procedure, and used in the search criteria, identifies a unique row, as shown in Listing 20.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">ALTER PROCEDURE dbo.SetEmployeeManager\r\n\u00a0\u00a0\u00a0 @EmployeeID INT ,\r\n\u00a0\u00a0\u00a0 @ManagerID INT\r\nAS \r\n\u00a0\u00a0\u00a0 SET NOCOUNT ON ;\r\n\u00a0\u00a0\u00a0 UPDATE\u00a0 dbo.Employee\r\n\u00a0\u00a0\u00a0 SET\u00a0\u00a0\u00a0\u00a0 ManagerID = @ManagerID\r\n\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 EmployeeID = @EmployeeID ;\r\n<\/pre>\n<p class=\"caption\">Listing 20: Using unambiguous search criteria<\/p>\n<p class=\"MsoNormal\">As long as <span class=\"STCodeinTextChar\">EmployeeID<\/span> is the primary key on the <span class=\"STCodeinTextChar\">dbo.Employee<\/span> table, this procedure will work correctly.<\/p>\n<h2>The Problem of Ambiguous Updates<\/h2>\n<p>The results of data modifications may be unpredictable in the hands of the careless programmer. Let&#8217;s consider a very common requirement: populating a permanent table from a staging table. First of all, let&#8217;s create our permanent table, <span class=\"STCodeinTextChar\">Codes<\/span>, and a staging table, <span class=\"STCodeinTextChar\">CodesStaging<\/span>, as shown in Listing 21. Note that <span class=\"STCodeinTextChar\">CodesStaging<\/span> does not have a primary key. This is very common for staging tables, because data is often loaded into such tables before detecting duplicates and other data integrity violations.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">CREATE TABLE dbo.Codes\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Code VARCHAR(5) NOT NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Description VARCHAR(40) NOT NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 CONSTRAINT PK_Codes PRIMARY KEY ( Code )\r\n\u00a0\u00a0\u00a0 ) ;\r\nGO\r\n\u00a0\r\nCREATE TABLE dbo.CodesStaging\r\n\u00a0\u00a0\u00a0 (\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Code VARCHAR(10) NOT NULL ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0 Description VARCHAR(40) NOT NULL\r\n\u00a0\u00a0\u00a0 ) ; \r\nGO\r\n<\/pre>\n<p class=\"caption\">Listing 21: Creating the <span class=\"STCodeinTextChar\">Codes<\/span> and <span class=\"STCodeinTextChar\">CodesStaging<\/span> tables<\/p>\n<p>Now, let&#8217;s populate each table with some sample data, as shown in Listing 22.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">DELETE\u00a0 FROM dbo.Codes ;\r\nINSERT\u00a0 INTO dbo.Codes\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( Code ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Description\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 'AR' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Old description for Arkansas'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNION ALL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 'IN' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'Old description for Indiana' ;\r\n\u00a0\r\nDELETE\u00a0 FROM dbo.CodesStaging ;\r\nINSERT\u00a0 INTO dbo.CodesStaging\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( Code ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Description\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 'AR' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'description for Argentina'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNION ALL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 'AR' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'new description for Arkansas'\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 UNION ALL\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SELECT\u00a0 'IN' ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 'new description for Indiana ' ;\r\n<\/pre>\n<p class=\"caption\">Listing 22: Populating the <span class=\"STCodeinTextChar\">Codes<\/span> and <span class=\"STCodeinTextChar\">CodesStaging<\/span> tables<\/p>\n<p>Now, we&#8217;ll examine two different ways of updating data in the permanent table, based on data in the staging table, both of which are subject to ambiguities if care is not taken:<\/p>\n<ul>\n<li>Using <span class=\"STCodeinTextChar\">UPDATE&#8230;FROM<\/span><\/li>\n<li>Updating an inline view<\/li>\n<\/ul>\n<p>We&#8217;ll then discuss strategies for avoiding such ambiguities.<\/p>\n<h2>Using UPDATE&#8230;FROM<\/h2>\n<p>Notice in Listing 22 that the incoming data in our staging table has a duplicate: the code <span class=\"STCodeinTextChar\">AR<\/span> occurs twice, with different descriptions. Suppose that we have not detected or resolved this duplicate, and that we are updating our <span class=\"STCodeinTextChar\">Codes<\/span> table from the staging table.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">UPDATE\u00a0 dbo.Codes\r\nSET Description = s.Description\r\nFROM\u00a0\u00a0\u00a0 dbo.Codes AS c INNER JOIN dbo.CodesStaging AS s\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON c.Code = s.Code ;\r\n\u00a0\r\nSELECT\u00a0 Code ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Description\r\nFROM\u00a0\u00a0\u00a0 dbo.Codes ;\r\n\u00a0\r\nCode\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Description\r\n---------- ----------------------------------------\r\nAR\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 description for Argentina\r\nIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 new description for Indiana \r\n\u00a0\r\n(2 row(s) affected)\r\n<\/pre>\n<p class=\"caption\">Listing 23: An ambiguous <span class=\"STCodeinTextChar\">UPDATE&#8230;FROM<\/span>, when loading data from a staging table (<span class=\"STCodeinTextChar\">CodesStaging<\/span>) into a target table (<span class=\"STCodeinTextChar\">Codes<\/span>)<\/p>\n<p class=\"MsoNormal\">Although two descriptions were provided for the <span class=\"STCodeinTextChar\">AR<\/span> code, the <span class=\"STCodeinTextChar\">UPDATE&#8230;FROM<\/span> command did not raise an error; it just silently updated the corresponding row in <span class=\"STCodeinTextChar\">Codes<\/span> table with one of the two provided values. In this case, the &#8216;old description for Arkansas&#8217; has been overwritten with the &#8216;description for Argentina&#8217;.<\/p>\n<h2>Updating Inline Views<\/h2>\n<p>When we update inline views, we may encounter exactly the same problem. First, repopulate each of the tables with the original data, using the code from Listing 22. Next, create an inline view, and then use it to implement exactly the same functionality as the previous <span class=\"STCodeinTextChar\">UPDATE&#8230;FROM<\/span> commands, as shown in Listing 24.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">WITH\u00a0\u00a0\u00a0 c AS ( SELECT\u00a0\u00a0 c.Code ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c.Description ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 s.Description AS NewDescription\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0\u00a0 dbo.Codes AS c\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN dbo.CodesStaging AS s\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON c.Code = s.Code\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0\u00a0\u00a0 UPDATE\u00a0 c\r\n\u00a0\u00a0\u00a0 SET\u00a0\u00a0\u00a0\u00a0 Description = NewDescription ;\r\n\u00a0\r\nSELECT\u00a0 Code ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Description\r\nFROM\u00a0\u00a0\u00a0 dbo.Codes ;\r\n\u00a0\r\nCode\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Description\r\n---------- ----------------------------------------\r\nAR\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 description for Argentina\r\nIN\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 new description for Indiana \r\n<\/pre>\n<p class=\"caption\">Listing 24: An ambiguous update of an inline view<\/p>\n<p class=\"MsoNormal\">Note that neither in this example nor the previous <span class=\"STCodeinTextChar\">UPDATE&#8230;FROM<\/span> example, can we predict which of these two values will end up in the target table &#8211; that, as usual, depends on the execution plan and as such is completely unpredictable. It is by pure chance that, in my examples, Argentina was chosen over Arkansas in both cases. I was able to get different results, with the description of Arkansas rather than Argentina inserted into <span class=\"STCodeinTextChar\">Codes<\/span>, just by changing the order in which the rows are inserted into <span class=\"STCodeinTextChar\">CodesStaging<\/span>. However, again, there is no guarantee that you will get the same results on your box. Also, bear in mind that if we ever did add an index to the staging table, this would almost certainly affect the result as well.<\/p>\n<h2>How to Avoid Ambiguous Updates<\/h2>\n<p class=\"MsoNormal\">In both previous examples, the developer has written the <span class=\"STCodeinTextChar\">UPDATE<\/span> command apparently under the assumption that there can be no duplicate data in the <span class=\"STCodeinTextChar\">CodesStaging<\/span> &#8211; which cannot be guaranteed in the absence of a <span class=\"STCodeinTextChar\">UNIQUE<\/span> or <span class=\"STCodeinTextChar\">PRIMARY<\/span> <span class=\"STCodeinTextChar\">KEY<\/span> constraint on the <span class=\"STCodeinTextChar\">Code<\/span> column &#8211; or that any duplicate data should have been removed before updating the permanent table.<\/p>\n<p class=\"MsoNormal\">Generally, performing this sort of ambiguous update is unacceptable. In some cases, we might want to refine the query to make sure it never yields ambiguous results. However, typically we want either to raise an error when an ambiguity is detected, or to update only what is unambiguous.<\/p>\n<p class=\"MsoNormal\">In SQL Server 2008, we can circumvent such problems with <span class=\"STCodeinTextChar\">UPDATE&#8230;FROM<\/span> or CTE-based updates, by use of the <span class=\"STCodeinTextChar\">MERGE<\/span> command. However, prior SQL Server 2008, we have to detect these ambiguities.<\/p>\n<h2>Using MERGE to Detect Ambiguity (SQL Server 2008 only)<\/h2>\n<p class=\"MsoNormal\">If you are working with SQL Server 2008, then easily the best option is to use the <span class=\"STCodeinTextChar\">MERGE<\/span> command. In Listing 25, we use the <span class=\"STCodeinTextChar\">MERGE<\/span> command to update our primary table from our staging table and immediately encounter the expected error.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">MERGE INTO dbo.Codes AS c\r\n\u00a0\u00a0\u00a0 USING dbo.CodesStaging AS s\r\n\u00a0\u00a0\u00a0 ON c.Code = s.Code\r\n\u00a0\u00a0\u00a0 WHEN MATCHED \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN UPDATE\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SET\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c.Description = s.Description ;\r\n\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \r\nMsg 8672, Level 16, State 1, Line 1\r\nThe MERGE statement attempted to UPDATE or DELETE the same row more than once. This happens when a target row matches more than one source row. A MERGE statement cannot UPDATE\/DELETE the same row of the target table multiple times. Refine the ON clause to ensure a target row matches at most one source row, or use the GROUP BY clause to group the source rows.\r\n<\/pre>\n<p class=\"caption\">Listing 25: <span class=\"STCodeinTextChar\">MERGE<\/span> detects an ambiguity in incoming data<\/p>\n<h2>An ANSI-standard Method<\/h2>\n<p class=\"MsoNormal\">Pre-SQL Server 2008, we are forced to seek alternative ways to raise an error whenever there is an ambiguity. The code in Listing 26 is ANSI-standard SQL and accomplishes that goal.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">-- rerun the code from Listing 22 \r\n-- before executing this code\r\nUPDATE\u00a0 dbo.Codes \r\nSET\u00a0\u00a0\u00a0\u00a0 Description =\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ( SELECT\u00a0 Description\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0\u00a0 dbo.CodesStaging \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0\u00a0 Codes.Code = CodesStaging.Code\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\nWHERE\u00a0\u00a0 EXISTS ( SELECT *\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 dbo.CodesStaging AS s\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0 \u00a0Codes.Code = s.Code\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) ;\r\nMsg 512, Level 16, State 1, Line 3\r\nSubquery returned more than 1 value. This is not permitted when the subquery follows =, !=, &lt;, &lt;= , &gt;, &gt;= or when the subquery is used as an expression.\r\nThe statement has been terminated.\r\n<\/pre>\n<p class=\"caption\">Listing 26: An ANSI Standard <span class=\"STCodeinTextChar\">UPDATE<\/span> command, which raises an error when there is an ambiguity<\/p>\n<p class=\"MsoNormal\">Note that in order to update just one column we had to use two almost identical subqueries in this command. This is definitely not a good practice. Should we need to update ten columns, we would have to repeat almost the same code eleven times! If, at some later time, we need to modify the subquery, we will have to make one and the same change in eleven places, which is very prone to errors.<\/p>\n<h2>Defensive Inline View Updates<\/h2>\n<p class=\"MsoNormal\">Fortunately, there are several ways to improve the robustness of inline view updates, as well as <span class=\"STCodeinTextChar\">UPDATE&#8230;FROM<\/span> updates (covered in the next section), which work with SQL 2005.<\/p>\n<p class=\"MsoNormal\">In the previous two examples, an error was raised when ambiguity was detected. This is usually preferable but, if your business rules allow you to ignore ambiguities, and only update that which is unambiguous, then the solution shown in Listing 27 will work.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">-- rerun the code from Listing 22 \r\n-- before executing this code\r\nBEGIN TRAN ;\r\n\u00a0\r\nWITH\u00a0 c AS ( SELECT c.Code ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c.Description ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 s.Description AS NewDescription\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 dbo.Codes AS c\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN dbo.CodesStaging AS s\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON c.Code = s.Code\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND ( SELECT COUNT(*)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0 dbo.CodesStaging AS s1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE c.Code = s1.Code\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) = 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0 UPDATE\u00a0 c\r\n\u00a0 SET\u00a0\u00a0\u00a0\u00a0 Description = NewDescription ;\r\n\u00a0\r\nROLLBACK ;\r\n<\/pre>\n<p class=\"caption\">Listing 27: Using a subquery to ignore ambiguities when updating an inline view<\/p>\n<p class=\"MsoNormal\">This time, only the description of Indiana is updated. In a similar fashion, we could filter out (i.e. ignore) ambiguities with the help of an analytical function, as shown in Listing 28.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">-- rerun the code from Listing 22 \r\n-- before executing this code\r\nBEGIN TRAN ;\r\n\u00a0\r\nWITH c AS ( SELECT c.Code ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c.Description ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 s.Description AS NewDescription ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(*) OVER ( PARTITION BY s.Code )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0AS NumVersions\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 dbo.Codes AS c\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN dbo.CodesStaging AS s\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON c.Code = s.Code\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0 UPDATE\u00a0 c\r\n\u00a0 SET\u00a0\u00a0\u00a0\u00a0 Description = NewDescription\r\n\u00a0 WHERE\u00a0\u00a0 NumVersions = 1 ;\r\n\u00a0\r\nROLLBACK ;\r\n<\/pre>\n<p class=\"caption\">Listing 28: Using <span class=\"STCodeinTextChar\">PARTITION BY<\/span> to ignore ambiguities when updating an inline view<\/p>\n<p class=\"MsoNormal\">In some cases, the approach of only performing unambiguous updates, and silently ignoring ambiguous ones, is unacceptable. In the absence of built in methods, we can use tricky workarounds to reuse the code as much as possible and still raise an error if there is an ambiguity. Consider the example shown in Listing 29, in which a divide by zero occurs if there is an ambiguity.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">-- rerun the code from Listing 22 \r\n-- before executing this code\r\nDECLARE @ambiguityDetector INT ;\r\nWITH c AS ( SELECT c.Code ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c.Description ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 s.Description AS NewDescription ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(*) OVER ( PARTITION BY s.Code )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0AS NumVersions\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 dbo.Codes AS c\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN dbo.CodesStaging AS s\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON c.Code = s.Code\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 )\r\n\u00a0 UPDATE\u00a0 c\r\n\u00a0 SET\u00a0\u00a0\u00a0\u00a0 Description = NewDescription ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 @ambiguityDetector = CASE WHEN NumVersions = 1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 THEN 1\r\n-- if we have ambiguities, the following branch executes\r\n-- and raises the following error:\r\n-- Divide by zero error encountered. \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ELSE 1 \/ 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 END ;\r\n\r\nMsg 8134, Level 16, State 1, Line 4\r\nDivide by zero error encountered.\r\nThe statement has been terminated.\r\n<\/pre>\n<p class=\"caption\">Listing 29: An <span class=\"STCodeinTextChar\">UPDATE<\/span> command using an inline view and raising a divide by zero error when there is an ambiguity<\/p>\n<p class=\"MsoNormal\">Of course, the error message raised by this code (divide by zero) is misleading, so we should only use this approach when any of the previous alternatives are not viable.<\/p>\n<h2>Defensive UPDATE&#8230;FROM<\/h2>\n<p class=\"MsoNormal\">Some of the approaches just outlined for improving the robustness of inline view updates, apply equally as well to improving the <span class=\"STCodeinTextChar\">UPDATE&#8230;FROM<\/span> command.<\/p>\n<p class=\"MsoNormal\">For example, we can use a sub-query to ignore ambiguities, as shown in Listing 30.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">-- rerun the code from Listing 22\r\n-- before executing this code\r\nBEGIN TRAN ;\r\nUPDATE\u00a0 dbo.Codes\r\nSET\u00a0\u00a0\u00a0\u00a0 Description = 'Old Description' ;\r\n\u00a0\r\nUPDATE\u00a0 dbo.Codes\r\nSET\u00a0\u00a0\u00a0\u00a0 Description = s.Description\r\nFROM\u00a0\u00a0\u00a0 dbo.Codes AS c\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN dbo.CodesStaging AS s\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON c.Code = s.Code\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND ( SELECT COUNT(*)\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 dbo.CodesStaging AS s1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 WHERE\u00a0 s.Code = s1.Code\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) = 1 ;\r\nSELECT\u00a0 Code ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Description\r\nFROM\u00a0\u00a0\u00a0 dbo.Codes ;\r\nROLLBACK ;\r\n<\/pre>\n<p class=\"caption\">Listing 30: Using a subquery to ignore ambiguities when using <span class=\"STCodeinTextChar\">UPDATE&#8230;FROM<\/span><\/p>\n<p class=\"MsoNormal\">Likewise, we can use an analytical function for detecting and ignoring ambiguities, as shown in Listing 31.<\/p>\n<pre class=\"theme:ssms2012 lang:tsql\">-- rerun the code from Listing 22 \r\n-- before executing this code\r\nBEGIN TRAN ;\r\nUPDATE\u00a0 dbo.Codes\r\nSET\u00a0\u00a0\u00a0\u00a0 Description = 'Old Description' ;\r\n\u00a0\r\nUPDATE dbo.Codes\r\nSET\u00a0\u00a0\u00a0 Description = s.Description\r\nFROM\u00a0\u00a0 dbo.Codes AS c\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INNER JOIN ( SELECT Code ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Description ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 COUNT(*) OVER ( PARTITION BY Code )\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0AS NumValues\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 FROM\u00a0\u00a0 dbo.CodesStaging\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ) AS s\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ON c.Code = s.Code\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 AND NumValues = 1 ;\r\nSELECT\u00a0 Code ,\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Description\r\nFROM\u00a0\u00a0\u00a0 dbo.Codes ;\r\nROLLBACK ;\r\n<\/pre>\n<p class=\"caption\">Listing 31: Using an analytical function to detect and ignore ambiguities when using <span class=\"STCodeinTextChar\">UPDATE&#8230;FROM<\/span><\/p>\n<h2>Summary<\/h2>\n<p class=\"MsoNormal\">The goal of this article was to introduce, by way of some simple examples, some of the basic ideas that underpin defensive database programming. It is vital that you understand and document the assumptions that underpin your implementation, test them to ensure their validity, and eliminate them if they are not. It is also vital that you consider as many use cases as possible for your code, and ensure it behaves consistently in each case. Where inconsistencies or incorrect behavior are found, the defensive programmer will not only fix the offending module, but also test all other modules that might suffer from a similar problem and proactively safeguard against it.<\/p>\n<p class=\"MsoNormal\">Along the way, I hope you&#8217;ve learned the following specific lessons in defensive programming:<\/p>\n<ul>\n<li>How to use complex patterns to improve the robustness of <span class=\"STCodeinTextChar\">LIKE<\/span> searches<\/li>\n<li>How to avoid potential difficulties with <span class=\"STCodeinTextChar\">SET<\/span> <span class=\"STCodeinTextChar\">ROWCOUNT<\/span><\/li>\n<li>The importance of safe date formats and of explicitly specifying the required format when converting dates<\/li>\n<li>How to avoid dangerous ambiguity when performing updates by, for example:\n<ul>\n<li>Using <span class=\"STCodeinTextChar\">MERGE<\/span>, in SQL Server 2008<\/li>\n<li>Using subqueries, pre-SQL Server 2008<\/li>\n<\/ul>\n<\/li>\n<li>How to use subqueries or the <span class=\"STCodeinTextChar\">COUNT(*)<\/span> <span class=\"STCodeinTextChar\">OVER<\/span> analytic function to improve the robustness of modifications when using <span class=\"STCodeinTextChar\">UPDATE&#8230;FROM<\/span>, or updating inline views, so that ambiguous updates are ignored.<\/li>\n<\/ul>\n<div class=\"scheme--lightest-grey spaced-bottom\">\n  <img decoding=\"async\" class=\"media__image padded--tight\" style=\"float: right; vertical-align: middle;\" src=\"https:\/\/assets.red-gate.com\/external\/SimpleTalk\/database-devops-cog-160.png\"><\/p>\n<div class=\"padded\">\n<p class=\"padded-top--tight\"> <strong>DevOps, Continuous Delivery & Database Lifecycle Management<\/strong><br \/> <a href=\"\/collections\/database-lifecycle-management-patterns-practices-library\/?utm_source=simpletalk&amp;utm_medium=publink&amp;utm_campaign=dlm&amp;utm_content=pandparticle&amp;utm_term=bottombanner\">Go to the Simple Talk library<\/a> to find more articles, or visit <a href=\"https:\/\/www.red-gate.com\/solutions?utm_source=simpletalk&amp;utm_medium=publink&amp;utm_campaign=dlm&amp;utm_content=pandparticle&amp;utm_term=bottombanner&amp;__hstc=81186632.9b3ff88e8dc727f9f1f608ffe1681edd.1467735864337.1482415357732.1482419905332.123&amp;__hssc=81186632.36.1482419905332&amp;__hsfp=3698596899\">www.red-gate.com\/solutions<\/a> for more information on the benefits of extending DevOps practices to SQL Server databases.<\/p>\n<\/div>\n<\/div>\n\n","protected":false},"excerpt":{"rendered":"<p> We can all recognise good-quality database code: It doesn&#8217;t break with every change in the server&#8217;s configuration, or on upgrade. It isn&#8217;t affected by concurrent usage, or high workload. In an extract from his forthcoming book, Alex explains just how to go about producing resilient TSQL code that works, and carries on working.&hellip;<\/p>\n","protected":false},"author":6776,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143531],"tags":[5119,4168,5146,4150,4252,4190],"coauthors":[6821],"class_list":["post-847","post","type-post","status-publish","format-standard","hentry","category-t-sql-programming-sql-server","tag-alex-kuznetsov","tag-database","tag-defensive-database-programme","tag-sql","tag-t-sql-programming","tag-tsql"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/847","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/6776"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=847"}],"version-history":[{"count":6,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/847\/revisions"}],"predecessor-version":[{"id":68526,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/847\/revisions\/68526"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=847"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=847"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=847"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=847"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}