{"id":1118,"date":"2007-12-05T00:51:00","date_gmt":"2007-12-05T00:51:00","guid":{"rendered":"\/blogs\/paul\/post\/Search-Engine-QA-9-How-to-update-constraints.aspx"},"modified":"2017-04-13T09:52:05","modified_gmt":"2017-04-13T16:52:05","slug":"search-engine-qa-9-how-to-update-constraints","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-9-how-to-update-constraints\/","title":{"rendered":"Search Engine Q&A #9: How to update constraints?"},"content":{"rendered":"

<\/p>\n

\nA quickie today to get back into the swing of things.\n<\/p>\n

\nIn Kimberly's whitepaper on partitioning<\/a> she discusses the 'sliding window' scenario (where you switch in and out partitions of data into an existing table – see this<\/a> previous post for a few more details). She recommends that the constraints are extended rather than dropped and recreated – which I totally agree with. I had a question about why this is a best practice, and is it more efficient than dropping and recreating the constraints?\n<\/p>\n

\nLet's create a little example to illustrate all these points. A simple table called Sales with a couple of indexes and 100000 rows of data.\n<\/p>\n

\n\t<\/p>\n

\n\tCREATE<\/font> <\/font>TABLE<\/font> Sales <\/font>(<\/font>salesID <\/font>INT<\/font> <\/font>IDENTITY<\/font>,<\/font> SalesDate <\/font>DATETIME<\/font><\/font>);
\n\t<\/font>GO<\/font><\/font>\n\t<\/p>\n

\t<\/font><\/p>\n

\n\tCREATE<\/font> <\/font>CLUSTERED<\/font> <\/font>INDEX<\/font> Sales_CL <\/font>ON<\/font> Sales <\/font>(<\/font>SalesID<\/font><\/font>);
\n\t<\/font>CREATE<\/font> <\/font>NONCLUSTERED<\/font> <\/font>INDEX<\/font> Sales_NCL <\/font>ON<\/font> Sales <\/font>(<\/font>SalesDate<\/font><\/font>);
\n\t<\/font>GO<\/font><\/font>\n\t<\/p>\n

\t<\/font><\/p>\n

\n\tSET<\/font> <\/font>NOCOUNT<\/font> <\/font>ON<\/font><\/font>;
\n\t<\/font>GO<\/font><\/font>\n\t<\/p>\n

\t<\/font><\/p>\n

\n\tDECLARE<\/font> @a <\/font>INT<\/font><\/font>;
\n\t<\/font>SELECT<\/font> @a <\/font>=<\/font> 1<\/font><\/font>;
\n\t<\/font>WHILE<\/font> <\/font>(<\/font>@a <\/font><<\/font> 100000<\/font><\/font>)
\n\t<\/font>BEGIN<\/font><\/font>\n\t<\/p>\n

\t<\/font><\/p>\n

\n

\n\t\tINSERT<\/font> <\/font>INTO<\/font> Sales <\/font>VALUES<\/font> <\/font>(<\/font>GETDATE<\/font> <\/font><\/font>());
\n\t\t<\/font>SELECT<\/font> @a <\/font>=<\/font> @a <\/font>+<\/font> 1<\/font>;<\/font><\/font>\n\t\t<\/p>\n<\/blockquote>\n

\t<\/font><\/p>\n

\n\tEND<\/font>;
\n\t<\/font>GO<\/font><\/font>\n\t<\/p>\n

\t<\/font>\n<\/p><\/blockquote>\n

\nNow I want to create two constraints – for the lower and upper bounds of the sales date. I could do this using a single constraint with both conditions or two constraints with a single condition each. For simplicity I'll use one constraint, but first I want to see how expensive the operation is, so I'm going to turn on STATISTICS IO<\/font> – this is a very cool feature that gives the IO costs of a query after it's completed.<\/font>\n<\/p>\n

\n\t<\/p>\n

\n\tSET<\/font> <\/font>STATISTICS<\/font> <\/font>IO<\/font> <\/font>ON<\/font><\/font>;
\n\t<\/font>GO<\/font><\/font>\n\t<\/p>\n

\t<\/font><\/p>\n

\n\tALTER<\/font> <\/font>TABLE<\/font> Sales <\/font>WITH<\/font> <\/font>CHECK <\/font>ADD<\/font> <\/font>CONSTRAINT<\/font> [CK_Sales_SalesDate] <\/font>CHECK<\/font> <\/font>(<\/font>YEAR<\/font> <\/font>(<\/font>SalesDate<\/font>)<\/font> <\/font>>=<\/font> 2005 <\/font>AND<\/font> <\/font>YEAR<\/font> <\/font>(<\/font>SalesDate<\/font>)<\/font> <\/font><<\/font> 2008<\/font><\/font>);
\n\t<\/font>GO<\/font><\/font>\n\t<\/p>\n

\t<\/font><\/p>\n

\n\tTable 'Sales'. Scan count 1, logical reads 399, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/font><\/font>\n\t<\/p>\n

\n\tThe 399 logical reads are for the table scan that's done to ensure that the constraint is valid for the data currently in the table.<\/font>\n\t<\/p>\n

\t<\/font>\n<\/p><\/blockquote>\n

\nSo – the first question is why not drop\/create the constraint to update it? Well, what if invalid data is entered into the table between dropping and recreating the constraint?<\/font>\n<\/p>\n

\n\tALTER<\/font><\/font> <\/font>TABLE<\/font> Sales <\/font>DROP<\/font> <\/font>CONSTRAINT<\/font> [CK_Sales_SalesDate]<\/font><\/font>;
\n\t<\/font>GO<\/font><\/font><\/font> <\/p>\n

\n\tALTER<\/font> <\/font>TABLE<\/font> Sales <\/font>WITH<\/font> <\/font>CHECK <\/font>ADD<\/font> <\/font>CONSTRAINT<\/font> [CK_Sales_SalesDate] <\/font>CHECK<\/font> <\/font>(<\/font>YEAR<\/font> <\/font>(<\/font>SalesDate<\/font>)<\/font> <\/font>>=<\/font> 2005 <\/font>AND<\/font> <\/font>YEAR<\/font> <\/font>(<\/font>SalesDate<\/font>)<\/font> <\/font><<\/font> 2009<\/font><\/font>);
\n\t<\/font>GO<\/font><\/font>\n\t<\/p>\n

\t<\/font><\/p>\n

\n\tMsg 547, Level 16, State 0, Line 1<\/font>\n\t<\/p>\n

\n\tThe ALTER TABLE statement conflicted with the CHECK constraint "CK_Sales_SalesDate". The conflict occurred in database "ConstraintTest", table "dbo.Sales", column 'SalesDate'.<\/font>\n\t<\/p>\n

\n\tThe constraint can't be recreated and you have to find the invalid data and get rid of it – which may not be as easy as it sounds depending on your schema and business logic. So, the best practice is always to update a constraint. Well, you can't update an existing constraint but you can create a new constraint with different conditions and then drop the old constraint:<\/font>\n\t<\/p>\n

\n\t\t<\/p>\n

\n\t\tALTER<\/font> <\/font>TABLE<\/font> Sales <\/font>WITH<\/font> <\/font>CHECK <\/font>ADD<\/font> <\/font>CONSTRAINT<\/font> [CK_Sales_SalesDate2] <\/font>CHECK<\/font> <\/font>(<\/font>YEAR<\/font> <\/font>(<\/font>SalesDate<\/font>)<\/font> <\/font>>=<\/font> 2005 <\/font>AND<\/font> <\/font>YEAR<\/font> <\/font>(<\/font>SalesDate<\/font>)<\/font> <\/font><<\/font> 2009<\/font><\/font>);
\n\t\t<\/font>ALTER<\/font> <\/font>TABLE<\/font> Sales <\/font>DROP<\/font> <\/font>CONSTRAINT<\/font> [CK_Sales_SalesDate]<\/font><\/font>;
\n\t\t<\/font>GO<\/font><\/font>\n\t\t<\/p>\n

\t\t<\/font><\/p>\n

\n\t\tTable 'Sales'. Scan count 1, logical reads 399, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.<\/font><\/font>\n\t\t<\/p>\n<\/blockquote>\n

\n\tThe second question is which way is more efficient? The answer is neither. Dropping and recreating the constraint will obviously do a table scan again, but so does adding the new constraint – even though there's a trusted constraint in place already which guarantees that adding the new constraint can't possibly fail!!! Hopefully in the future the smarts will be built into the SQL Engine to recognize this and not do the unnecessary table scan (this process is known as interval subsumption<\/em> – according to my geeky wife :-))<\/font>\n\t<\/p>\n

\t<\/font><\/font><\/font>\n<\/p><\/blockquote>\n

<\/font><\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

A quickie today to get back into the swing of things. In Kimberly's whitepaper on partitioning she discusses the 'sliding window' scenario (where you switch in and out partitions of data into an existing table – see this previous post for a few more details). She recommends that the constraints are extended rather than dropped […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,65,78],"tags":[],"class_list":["post-1118","post","type-post","status-publish","format-standard","hentry","category-constraints","category-partitioning","category-search-engine-q-and-a"],"yoast_head":"\nSearch Engine Q&A #9: How to update constraints? - Paul S. Randal<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-9-how-to-update-constraints\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Search Engine Q&A #9: How to update constraints? - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"A quickie today to get back into the swing of things. In Kimberly's whitepaper on partitioning she discusses the 'sliding window' scenario (where you switch in and out partitions of data into an existing table – see this previous post for a few more details). She recommends that the constraints are extended rather than dropped […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-9-how-to-update-constraints\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2007-12-05T00:51:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2017-04-13T16:52:05+00:00\" \/>\n<meta name=\"author\" content=\"Paul Randal\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Paul Randal\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-9-how-to-update-constraints\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-9-how-to-update-constraints\/\",\"name\":\"Search Engine Q&A #9: How to update constraints? - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2007-12-05T00:51:00+00:00\",\"dateModified\":\"2017-04-13T16:52:05+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-9-how-to-update-constraints\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-9-how-to-update-constraints\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-9-how-to-update-constraints\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Search Engine Q&A #9: How to update constraints?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\",\"name\":\"Paul S. Randal\",\"description\":\"In Recovery...\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\",\"name\":\"Paul Randal\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g\",\"caption\":\"Paul Randal\"},\"sameAs\":[\"http:\/\/3.209.169.194\/blogs\/paul\"],\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Search Engine Q&A #9: How to update constraints? - Paul S. Randal","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-9-how-to-update-constraints\/","og_locale":"en_US","og_type":"article","og_title":"Search Engine Q&A #9: How to update constraints? - Paul S. Randal","og_description":"A quickie today to get back into the swing of things. In Kimberly's whitepaper on partitioning she discusses the 'sliding window' scenario (where you switch in and out partitions of data into an existing table – see this previous post for a few more details). She recommends that the constraints are extended rather than dropped […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-9-how-to-update-constraints\/","og_site_name":"Paul S. Randal","article_published_time":"2007-12-05T00:51:00+00:00","article_modified_time":"2017-04-13T16:52:05+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-9-how-to-update-constraints\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-9-how-to-update-constraints\/","name":"Search Engine Q&A #9: How to update constraints? - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2007-12-05T00:51:00+00:00","dateModified":"2017-04-13T16:52:05+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-9-how-to-update-constraints\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-9-how-to-update-constraints\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/search-engine-qa-9-how-to-update-constraints\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"Search Engine Q&A #9: How to update constraints?"}]},{"@type":"WebSite","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/","name":"Paul S. Randal","description":"In Recovery...","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.sqlskills.com\/blogs\/paul\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce","name":"Paul Randal","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/0b6a266bba2f088f2551ef529293001bd73bf026bc1908b9866728c062beeeb6?s=96&d=mm&r=g","caption":"Paul Randal"},"sameAs":["http:\/\/3.209.169.194\/blogs\/paul"],"url":"https:\/\/www.sqlskills.com\/blogs\/paul\/author\/paul\/"}]}},"_links":{"self":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1118","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/comments?post=1118"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/1118\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=1118"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=1118"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=1118"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}