SELECT COUNT(*), \r\n SUM(CASE WHEN LatestRecordedPopulation IS NULL \r\n THEN 1 ELSE 0 END)\r\nFROM Application.Cities;<\/pre>\nThis returns 37940 total rows and 11048 rows with a NULL population value. Now, let’s join the table to itself on the PK and the population value<\/p>\n
SELECT *\r\nFROM Application.Cities\r\n JOIN Application.Cities AS C2\r\n ON C2.CityID = Cities.CityID\r\nWHERE.LatestRecordedPopulation \r\n = Cities.LatestRecordedPopulation;<\/pre>\nThis returns 26892 rows, which you can do the math, is 37940-11048. Looking at this, without thinking about NULL<\/code> values (who does initially?), this has to return every row in the table. But clearly not. Usually this becomes obvious when a few customers living in one of those cities isn\u2019t showing up on a report (or maybe even not getting their shipments.)<\/p>\nThe pre-SQL Server 2022 way of handling this properly this was to do something like this:<\/p>\n
SELECT *\r\nFROM Application.Cities\r\n JOIN Application.Cities AS C2\r\n ON C2.CityID = Cities.CityID\r\n AND C2.LatestRecordedPopulation = \r\n Cities.LatestRecordedPopulation \r\n OR (C2.LatestRecordedPopulation IS NULL\r\n AND Cities.LatestRecordedPopulation IS NULL);<\/pre>\nNow we have checked the either they are the same value, or they both have a value of NULL<\/code>. This query returns every row in the table, but it is kind of tricky code. And looking for differences is even more difficult, because you have to check to see if the values are different, if column 1 is null and column2 is not, and again vice versa. Another way this is often done is to change the population comparison to<\/p>\nAND COALESCE(C2.LatestRecordedPopulation.-100) = \r\n COALESCE(Cities.LatestRecordedPopulation,-100)<\/pre>\nWhich is safe from a correctness standpoint (assuming you can coalesce your values to something that is 100% not possible), but not from a performance one. This eliminates index seek utilization for these columns and makes it slower. That isn\u2019t always an issue, but for larger data sets, you may end up with more scans than you hoped.<\/p>\n
Using the new syntax, we can simply write this as:<\/p>\n
SELECT *\r\nFROM Application.Cities\r\n JOIN Application.Cities AS C2\r\n ON C2.CityID = Cities.CityID\r\n AND C2.LatestRecordedPopulation \r\n IS NOT DISTINCT FROM \r\n Cities.LatestRecordedPopulation;<\/pre>\nThe name of the operator might be a little bit confusing because of the words FROM<\/code> and DISTINCT<\/code>, it really makes sense. DISTINCT<\/code> has a seemingly different usage here, but really it is the same meaning. If the value is the same, it is not distinct<\/em> from one another, and if it is different, it is distinct. And the DISTINCT<\/code> operator in the SELECT<\/code> clause honors NULL<\/code> values as a single bucket too. Now, go back and change the previous query to IS DISTINCT FROM<\/code> and 0 rows will be returned.<\/p>\nThis feature would have saved me many many hours over the years! Is this alone a reason to upgrade to SQL Server 2022 alone? Since I have never been the one to write those checks, and I use the free Express edition for my hobby databases\u2026 I can say an unqualified \u201cYes\u201d to that!<\/p>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Despite my recent job change from full time T-SQL code jockey\/architect to website editor for Simple-Talk, I will always be at heart at T-SQL programmer. While I will mostly be writing professionally in the foreseeable future will in support of an article, I will also continue writing code for several databases that I use for……<\/p>\n","protected":false},"author":56085,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[19684],"class_list":["post-94805","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94805","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\/56085"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=94805"}],"version-history":[{"count":8,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94805\/revisions"}],"predecessor-version":[{"id":94821,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/94805\/revisions\/94821"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=94805"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=94805"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=94805"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=94805"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![](\"https:\/\/www.red-gate.com\/simple-talk\/wp-content\/uploads\/2022\/09\/word-image-1.png\")
Despite my recent job change from full time T-SQL code jockey\/architect to website editor for Simple-Talk, I will always be at heart at T-SQL programmer. While I will mostly be writing professionally in the foreseeable future will in support of an article, I will also continue writing code for several databases that I use for multiple personal reason housed on a SQL Server Express server on the desk beside me.<\/p>\n