{"id":915,"date":"2009-03-22T08:04:00","date_gmt":"2009-03-22T08:04:00","guid":{"rendered":"\/blogs\/paul\/post\/How-to-change-a-default-constraint.aspx"},"modified":"2009-03-22T08:04:00","modified_gmt":"2009-03-22T08:04:00","slug":"how-to-change-a-default-constraint","status":"publish","type":"post","link":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-to-change-a-default-constraint\/","title":{"rendered":"How to change a default constraint"},"content":{"rendered":"

\nWhile I was teaching the MCM-Database class last week, we were discussing fragmentation and the effect of a high-order GUID key on an index. Without going into too many details, having a random GUID – as generated from the NEWID()<\/font> – function is bad, but having one generated by NEWSEQUENTIALID()<\/font> isn't anyway near so bad (I'll discuss the details more in the fragmentation series I'm starting). As part of the demo, we wanted to change the column default for the leading key of a table from NEWID()<\/font> to NEWSEQUENTIALID()<\/font> – problem was that none of us could remember the exact syntax, so we worked it out together. I thought it would make an interesting post, so here it is.<\/font>\n<\/p>\n

\nFirst off, here's my table with a poor clustered index key:<\/font>\n<\/p>\n

\n

\n\tCREATE TABLE BadKeyTable (
\n\t    c1 UNIQUEIDENTIFIER DEFAULT NEWID ()<\/strong> ROWGUIDCOL,
\n\t    c2 SMALLDATETIME DEFAULT GETDATE (),
\n\t    c3 CHAR (400) DEFAULT 'a',
\n\t    c4 VARCHAR(MAX) DEFAULT 'b');
\n\tGO
\n\tCREATE CLUSTERED INDEX BadKeyTable_CL ON BadKeyTable (c1);
\n\tGO<\/font>\n\t<\/p>\n

\n\tINSERT INTO BadKeyTable DEFAULT VALUES;
\n\tGO<\/font>\n\t<\/p>\n<\/blockquote>\n

\n(And you'll notice that I've given up doing nice colors in the T-SQL I post – it's too time consuming). The default we're interested in is in bold above. To change the default, we first need to find the constraint name so we can drop it. There are two queries you can use:<\/font>\n<\/p>\n

\n

\n\tSELECT [name] FROM sys.objects
\n\tWHERE [parent_object_id] = OBJECT_ID ('BadKeyTable');
\n\tGO<\/font>\n\t<\/p>\n<\/blockquote>\n

\nor<\/font>\n<\/p>\n

\n

\n\tSELECT [name] FROM sys.default_constraints
\n\tWHERE [parent_object_id] = OBJECT_ID ('BadKeyTable');
\n\tGO<\/font>\n\t<\/p>\n<\/blockquote>\n

\nThe second is obviously the more supported way, as the first will return the names of all sub-objects of this table – all constraints, and all internal tables, such as XML indexes. The second query returns:<\/font>\n<\/p>\n

\n

\n\tname
\n\t——————————-
\n\tDF__BadKeyTable__c1__7C8480AE
\n\tDF__BadKeyTable__c2__7D78A4E7
\n\tDF__BadKeyTable__c3__7E6CC920
\n\tDF__BadKeyTable__c4__7F60ED59<\/font>\n\t<\/p>\n<\/blockquote>\n

\nThe constraint we're interested in is the one for the first column – DF__BadKeyTable__c1__7C8480AE<\/font>. Now we need to drop the constraint and then add the new one as there's no way to simply alter the constraint in-place. We do that using:<\/font>\n<\/p>\n

\n

\n\tALTER TABLE BadKeyTable DROP CONSTRAINT DF__BadKeyTable__c1__7C8480AE;
\n\tGO<\/font>\n\t<\/p>\n<\/blockquote>\n

\n

\n\tALTER TABLE BadKeyTable ADD CONSTRAINT DF__BadKeyTable__c1
\n\tDEFAULT NEWSEQUENTIALID() FOR c1;
\n\tGO<\/font>\n\t<\/p>\n<\/blockquote>\n

\nAnd we're done.<\/font><\/p>\n","protected":false},"excerpt":{"rendered":"

While I was teaching the MCM-Database class last week, we were discussing fragmentation and the effect of a high-order GUID key on an index. Without going into too many details, having a random GUID – as generated from the NEWID() – function is bad, but having one generated by NEWSEQUENTIALID() isn't anyway near so bad (I'll […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[28,31,38,42,86],"tags":[],"class_list":["post-915","post","type-post","status-publish","format-standard","hentry","category-constraints","category-database-maintenance","category-example-scripts","category-fragmentation","category-sql-server-2008"],"yoast_head":"\nHow to change a default constraint - 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\/how-to-change-a-default-constraint\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to change a default constraint - Paul S. Randal\" \/>\n<meta property=\"og:description\" content=\"While I was teaching the MCM-Database class last week, we were discussing fragmentation and the effect of a high-order GUID key on an index. Without going into too many details, having a random GUID – as generated from the NEWID() – function is bad, but having one generated by NEWSEQUENTIALID() isn't anyway near so bad (I'll […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-to-change-a-default-constraint\/\" \/>\n<meta property=\"og:site_name\" content=\"Paul S. Randal\" \/>\n<meta property=\"article:published_time\" content=\"2009-03-22T08:04:00+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=\"2 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\/how-to-change-a-default-constraint\/\",\"url\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-to-change-a-default-constraint\/\",\"name\":\"How to change a default constraint - Paul S. Randal\",\"isPartOf\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#website\"},\"datePublished\":\"2009-03-22T08:04:00+00:00\",\"author\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-to-change-a-default-constraint\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-to-change-a-default-constraint\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/how-to-change-a-default-constraint\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.sqlskills.com\/blogs\/paul\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to change a default constraint\"}]},{\"@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":"How to change a default constraint - 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\/how-to-change-a-default-constraint\/","og_locale":"en_US","og_type":"article","og_title":"How to change a default constraint - Paul S. Randal","og_description":"While I was teaching the MCM-Database class last week, we were discussing fragmentation and the effect of a high-order GUID key on an index. Without going into too many details, having a random GUID – as generated from the NEWID() – function is bad, but having one generated by NEWSEQUENTIALID() isn't anyway near so bad (I'll […]","og_url":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-to-change-a-default-constraint\/","og_site_name":"Paul S. Randal","article_published_time":"2009-03-22T08:04:00+00:00","author":"Paul Randal","twitter_misc":{"Written by":"Paul Randal","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-to-change-a-default-constraint\/","url":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-to-change-a-default-constraint\/","name":"How to change a default constraint - Paul S. Randal","isPartOf":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#website"},"datePublished":"2009-03-22T08:04:00+00:00","author":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/#\/schema\/person\/ffcec826c18782e1e0adf173826a7fce"},"breadcrumb":{"@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-to-change-a-default-constraint\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.sqlskills.com\/blogs\/paul\/how-to-change-a-default-constraint\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.sqlskills.com\/blogs\/paul\/how-to-change-a-default-constraint\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.sqlskills.com\/blogs\/paul\/"},{"@type":"ListItem","position":2,"name":"How to change a default constraint"}]},{"@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\/915","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=915"}],"version-history":[{"count":0,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/posts\/915\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/media?parent=915"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/categories?post=915"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlskills.com\/blogs\/paul\/wp-json\/wp\/v2\/tags?post=915"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}