{"id":2379,"date":"2007-12-13T08:00:44","date_gmt":"2007-12-13T08:00:44","guid":{"rendered":"http:\/\/itknowledgeexchange.techtarget.com\/sql-server\/so-whats-the-difference-between-null-and-a-blank-field\/"},"modified":"2007-12-13T08:00:44","modified_gmt":"2007-12-13T08:00:44","slug":"so-whats-the-difference-between-null-and-a-blank-field","status":"publish","type":"post","link":"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/","title":{"rendered":"So what’s the difference between null and a blank field"},"content":{"rendered":"

There is always a discussion going on somewhere over having NULL values in a database.\u00a0 Some people (such as myself) are fine with NULL values.\u00a0 Some people don’t like having NULL values in there database for some reason.\u00a0 I’ve heard all sorts of reasons; “The screw up performance”, “They are hard to deal with”, “I like using a default value better”, “Default values are easier to work with”.<\/p>\n

The performance one is just bunk.\u00a0 NULL values are indexed just like any other value.<\/p>\n

The rest of the reasons to not use NULL values ignore the fact that a NULL value is a perfectly legit value for some fields.\u00a0 Not all fields should allow a NULL value to be placed in them.\u00a0 If you are building a shopping cart and have a line items table, the Quantity and Price fields probably shouldn’t allow nulls as you should know the price and quantity when creating the invoice.\u00a0 However in the same shopping card application the users phone number may need to allow NULL values.<\/p>\n

Something which is key to remember about NULL values is that NULL doesn’t mean nothing.\u00a0 It means unknown.\u00a0 There may be a value, there may not, we simply do not know what that value is.\u00a0 Putting in some default value will then put a value on the field when there shouldn’t be.\u00a0 A great example which I read recently involved a hypothetical database at a blood bank.\u00a0 A person comes in needing blood.\u00a0 They are entered into the system but the recipients blood type isn’t known.\u00a0 If you put in a default value of a blank value when you go to query the supply table, if there are pints of blood which have not been typed they will also have a blank value (as that is your standard default value).\u00a0 You’ll get a match when you run the query and possibly be giving the recipient blood which is of the wrong type.\u00a0 Now if you were using NULL values in the database the query would return no matching values as NULL <> NULL (under the default settings of Microsoft SQL Server).\u00a0 Granted we should be able to trust that the person give the recipient the blood would check the typing, but would you rather trust a person or the computer.\u00a0 People are overworked and usually underpaid, and nurses and doctors make simple mistakes just like the rest of us.\u00a0 Personally I’d rather know that the system wouldn’t find a match rather than have to worry that I’d be getting the wrong type of blood.<\/p>\n

If you really want to display that default value when the data is returned that’s what the ISNULL system function is for.\u00a0 This way you don’t have to write long case statements around each field which allows NULL.<\/p>\n

NULL values have there place, just like\u00a0data does.\u00a0 NULL values don’t mean that we don’t care about the data, it means that we simply don’t know the value of the data as of yet.\u00a0 It shows that we care about the system and the people using it.\u00a0 How annoying is it to look at a form which is full of default values and having to read through all of them instead of simply looking at the ones which are blank.<\/p>\n

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

There is always a discussion going on somewhere over having NULL values in a database.\u00a0 Some people (such as myself) are fine with NULL values.\u00a0 Some people don’t like having NULL values in there database for some reason.\u00a0 I’ve heard all sorts of reasons; “The screw up performance”, “They are hard to deal with”, “I […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[551,63],"tags":[],"class_list":["post-2379","post","type-post","status-publish","format-standard","hentry","category-blog","category-sql"],"yoast_head":"\nSo what's the difference between null and a blank field – Denny Cherry & Associates Consulting<\/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.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"So what's the difference between null and a blank field – Denny Cherry & Associates Consulting\" \/>\n<meta property=\"og:description\" content=\"There is always a discussion going on somewhere over having NULL values in a database.\u00a0 Some people (such as myself) are fine with NULL values.\u00a0 Some people don’t like having NULL values in there database for some reason.\u00a0 I’ve heard all sorts of reasons; “The screw up performance”, “They are hard to deal with”, “I […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/\" \/>\n<meta property=\"og:site_name\" content=\"Denny Cherry & Associates Consulting\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DennyCherryAssociatesConsulting\/\" \/>\n<meta property=\"article:published_time\" content=\"2007-12-13T08:00:44+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dcac.com\/ad\/primary_image\/itke_so-whats-the-difference-between-null-and-a-blank-field\" \/>\n<meta name=\"author\" content=\"Denny Cherry\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@mrdenny\" \/>\n<meta name=\"twitter:site\" content=\"@dcacco\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Denny Cherry\" \/>\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.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/\",\"url\":\"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/\",\"name\":\"So what's the difference between null and a blank field – Denny Cherry & Associates Consulting\",\"isPartOf\":{\"@id\":\"https:\/\/www.dcac.com\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dcac.com\/ad\/primary_image\/itke_so-whats-the-difference-between-null-and-a-blank-field\",\"datePublished\":\"2007-12-13T08:00:44+00:00\",\"dateModified\":\"2007-12-13T08:00:44+00:00\",\"author\":{\"@id\":\"https:\/\/www.dcac.com\/#\/schema\/person\/cbc8485c710a3eee0e50dcfa9518636e\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/#primaryimage\",\"url\":\"https:\/\/www.dcac.com\/ad\/primary_image\/itke_so-whats-the-difference-between-null-and-a-blank-field\",\"contentUrl\":\"https:\/\/www.dcac.com\/ad\/primary_image\/itke_so-whats-the-difference-between-null-and-a-blank-field\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/www.dcac.com\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"So what’s the difference between null and a blank field\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dcac.com\/#website\",\"url\":\"https:\/\/www.dcac.com\/\",\"name\":\"Denny Cherry & Associates Consulting\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dcac.com\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dcac.com\/#\/schema\/person\/cbc8485c710a3eee0e50dcfa9518636e\",\"name\":\"Denny Cherry\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dcac.com\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/ccade9c22d8686e4b5a46cb49a636eea?s=96&r=x\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/ccade9c22d8686e4b5a46cb49a636eea?s=96&r=x\",\"caption\":\"Denny Cherry\"},\"sameAs\":[\"https:\/\/x.com\/mrdenny\"],\"url\":\"https:\/\/www.dcac.com\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"So what's the difference between null and a blank field – Denny Cherry & Associates Consulting","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.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/","og_locale":"en_US","og_type":"article","og_title":"So what's the difference between null and a blank field – Denny Cherry & Associates Consulting","og_description":"There is always a discussion going on somewhere over having NULL values in a database.\u00a0 Some people (such as myself) are fine with NULL values.\u00a0 Some people don’t like having NULL values in there database for some reason.\u00a0 I’ve heard all sorts of reasons; “The screw up performance”, “They are hard to deal with”, “I […]","og_url":"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/","og_site_name":"Denny Cherry & Associates Consulting","article_publisher":"https:\/\/www.facebook.com\/DennyCherryAssociatesConsulting\/","article_published_time":"2007-12-13T08:00:44+00:00","og_image":[{"url":"https:\/\/www.dcac.com\/ad\/primary_image\/itke_so-whats-the-difference-between-null-and-a-blank-field","type":"","width":"","height":""}],"author":"Denny Cherry","twitter_card":"summary_large_image","twitter_creator":"@mrdenny","twitter_site":"@dcacco","twitter_misc":{"Written by":"Denny Cherry","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/","url":"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/","name":"So what's the difference between null and a blank field – Denny Cherry & Associates Consulting","isPartOf":{"@id":"https:\/\/www.dcac.com\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/#primaryimage"},"image":{"@id":"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dcac.com\/ad\/primary_image\/itke_so-whats-the-difference-between-null-and-a-blank-field","datePublished":"2007-12-13T08:00:44+00:00","dateModified":"2007-12-13T08:00:44+00:00","author":{"@id":"https:\/\/www.dcac.com\/#\/schema\/person\/cbc8485c710a3eee0e50dcfa9518636e"},"breadcrumb":{"@id":"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/#primaryimage","url":"https:\/\/www.dcac.com\/ad\/primary_image\/itke_so-whats-the-difference-between-null-and-a-blank-field","contentUrl":"https:\/\/www.dcac.com\/ad\/primary_image\/itke_so-whats-the-difference-between-null-and-a-blank-field"},{"@type":"BreadcrumbList","@id":"https:\/\/www.dcac.com\/2007\/12\/13\/so-whats-the-difference-between-null-and-a-blank-field\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.dcac.com\/"},{"@type":"ListItem","position":2,"name":"So what’s the difference between null and a blank field"}]},{"@type":"WebSite","@id":"https:\/\/www.dcac.com\/#website","url":"https:\/\/www.dcac.com\/","name":"Denny Cherry & Associates Consulting","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dcac.com\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dcac.com\/#\/schema\/person\/cbc8485c710a3eee0e50dcfa9518636e","name":"Denny Cherry","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dcac.com\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/ccade9c22d8686e4b5a46cb49a636eea?s=96&r=x","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/ccade9c22d8686e4b5a46cb49a636eea?s=96&r=x","caption":"Denny Cherry"},"sameAs":["https:\/\/x.com\/mrdenny"],"url":"https:\/\/www.dcac.com\/author\/admin\/"}]}},"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts\/2379","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/comments?post=2379"}],"version-history":[{"count":0,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/posts\/2379\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/media?parent=2379"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/categories?post=2379"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dcac.com\/wp-json\/wp\/v2\/tags?post=2379"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}