{"id":340,"date":"2012-10-29T08:00:02","date_gmt":"2012-10-29T15:00:02","guid":{"rendered":"http:\/\/www.sqldbpros.com\/?p=340"},"modified":"2012-11-14T12:47:00","modified_gmt":"2012-11-14T19:47:00","slug":"alter-table-add-not-null-column-sql-server","status":"publish","type":"post","link":"http:\/\/sqldbpros.com\/wordpress\/2012\/10\/alter-table-add-not-null-column-sql-server\/","title":{"rendered":"Alter Table: Add Not Null Column – SQL Server"},"content":{"rendered":"

Listen up my people.\u00a0 NOT NULL columns are your friend.\u00a0 It may take a few extra brain cells to populate it initially but the long term rewards in data quality are well worth it. (You'll also force all your software developer cohorts to think specifically about the values they'll be inserting into a column which is always a good thing).<\/p>\n

But wait!\u00a0 I have an existing table I want to add a column to.\u00a0 How can I add a NOT NULL column to an existing table without dropping and reloading the table?\u00a0 Have no fear true believer.\u00a0 SQL Server can accommodate this scenario with ease.\u00a0 Simply, craft a little T-SQL which will add the column and the default constraint at the same time.\u00a0 It's as easy as pie.\u00a0 (Somehow I got in the habit of some sort of pop culture tie in in most of these posts.\u00a0 I don't really have something concrete this time but since I did say \"pie\": Have you seen Waitress<\/a>?\u00a0 It's great movie with a tragic real life story involving the actress\/writer\/director Adrienne Shelly).<\/p>\n

Need to add a NOT NULL column to an existing table which already has rows?\u00a0 Here you go:<\/p>\n

ALTER TABLE dbo.TableA\r\nADD NewColumnA VARCHAR(100) NOT NULL \r\nCONSTRAINT DF_TableA_NewColumnA DEFAULT 'n\/a';<\/pre>\n
This code will add the new NOT NULL column NewColumnA to the table TableA.\u00a0 It will also add a default constraint to the column that will place the value \"n\/a\" in the column for all the existing rows.<\/p>\n
\"\"<\/a>
Not Null Pie - Try it and love it.<\/figcaption><\/figure>\n","protected":false},"excerpt":{"rendered":"
Listen up my people.\u00a0 NOT NULL columns are your friend.\u00a0 It may take a few extra brain cells to populate it initially but the long term rewards in data quality are well worth it. (You'll also force all your software developer cohorts to think specifically about the values they'll be inserting into a column which […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17,43,3,57,72],"tags":[35,84,32,10,85,6,9,12,11,173,5],"class_list":["post-340","post","type-post","status-publish","format-standard","hentry","category-data-architect","category-microsoft-sql-server","category-scripts","category-t-sql","category-table-design","tag-best-practice","tag-default","tag-design-review","tag-ms-sql","tag-not-null","tag-script","tag-sql-server","tag-sql-server-2005","tag-sql-server-2008","tag-t-sql","tag-tips"],"_links":{"self":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/340","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/comments?post=340"}],"version-history":[{"count":7,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/340\/revisions"}],"predecessor-version":[{"id":406,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/posts\/340\/revisions\/406"}],"wp:attachment":[{"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/media?parent=340"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/categories?post=340"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/sqldbpros.com\/wordpress\/wp-json\/wp\/v2\/tags?post=340"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}