SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

The Voice of the DBA

Who Likes NULL?

The title says it all: who Likes NULL values in their tables?

I have tended to allow NULLs in quite a few places in my design, often because I view the world as messy and incomplete. I also find that applications are faulty, and might not validate data, might not run long enough without a crash to let a user insert a lot of data. The application might mangle data, or just might not have been updated to support a new column of data. I've found that there are times where I accept the messy real world and use NULL to represent unknown values.

Dr. Low notes this as well in a recent post. His view is similar to mine in that he uses NULL values when we don't know the actual data. This is preferable to some magic value that has to be coded in every application using the database. There are too many chances of mistakes, and definitely the possibility of leakage for these magic values.

As we use more and reporting and aggregation tools, users may inadvertently see strange values exposed. Many of these tools wouldn't be coded to translate magic values to some agreed upon value, which results in confusion and distraction for clients. The data in our systems becomes used in new and different ways as we start to connect new applications to existing databases. We may also use ETL processes to move information among systems, often to data warehouse or OLAP data stores. Often there are proof of concept prototypes built with self-service tools, such as Power BI, and the logic that was originally coded to translate magic values is lost.

That doesn't mean that every field should allow NULLs, but that we should consider them in places where the data is useful, but not necessarily mandated or captured in every transactions. If we have valid defaults, use them, but if not, don't be afraid of NULL. Understand the meaning and implications of allowing NULLs and use them carefully.

I'm curious about if you agree with me. Do you default to NULL values or do you avoid them at all costs? Do you use them judiciously? Give me the reasons why or why not, and if you have examples of where you allow NULLs, let us know.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums


The Voice of the DBA Podcast

Listen to the MP3 Audio ( 3.1MB) podcast or subscribe to the feed at iTunes and Libsyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

ADVERTISEMENT
SQL Source Control

How to track every change to your SQL Server database

See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. Learn more

Featured Contents

 

Auto-suggesting foreign keys and data model archaeology

David Poole from SQLServerCentral.com

If you can understand the data model then you can understand the intent of the application developers. More »


 

How to Fix sp_BlitzIndex Aggressive Indexes Warnings

Additional Articles from Brent Ozar Unlimited Blog

Either you've got too many indexes, or not enough. Here's how to tell. More »


 

Traditional database security doesn’t protect data

Alex Yates proposes that the features we think of as traditional database security are not sufficient to protect your data. More »


 

From the SQLServerCentral Blogs - Cloud Backup Comparison

Steve Jones from SQLServerCentral Blogs

I used to use Crashplan. This was about $150 a year, but I could to 5 machines. I used to... More »


 

From the SQLServerCentral Blogs - Use SSMS with a Different Windows Account – Back to Basics

Jason Brimhall from SQLServerCentral Blogs

One of the tasks I find myself doing on a fairly regular basis is running SSMS as a different Windows... More »

Question of the Day

Today's Question (by Steve Jones):

When I create a new Azure SQL Database, must I include this in a resource group?

Think you know the answer? Click here, and find out if you are right.


We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Azure.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.

ADVERTISEMENT

Microsoft SQL Server 2017 on Linux

This comprehensive guide shows, step-by-step, how to set up, configure, and administer SQL Server 2017 on Linux for high performance and high availability. Written by a SQL Server expert and respected author, Microsoft SQL Server 2017 on Linux teaches valuable Linux skills to Windows-based SQL Server professionals. You will get clear coverage of both Linux and SQL Server and complete explanations of the latest features, tools, and techniques. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I have an Orders table that contains the CustomerID and the CustomerTypeID columns. There are 4603 values for CustomerID and 12 values for CustomerTypeID.

Which column is more selective?

Answer: CustomerID

Explanation:

The more selective column is the one with more values. A couple of references.

From SQL Server Statistics Basics:

"...The more unique values, the higher the selectivity and the more effective the index."

From Query Processing Architecture Guide

"...Distribution statistics are kept for columns and indexes. They indicate the selectivity of the values in a particular index or column. For example, in a table representing cars, many cars have the same manufacturer, but each car has a unique vehicle identification number (VIN). An index on the VIN is more selective than an index on the manufacturer."


» Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2017 : SQL Server 2017 - Administration

Restore Backup from 2012 to 2017 GTM - Hi Everyone I'm trying to restore a backup that was taken from a 2012 SQL Server and restore to a SQL...


SQL Server 2017 : SQL Server 2017 - Development

Alternative for Polling Record State - We currently have a payment tracking system which uses MS SQL Server Enterprise. When a client requests a service, he...

DDL statements getting rollbacked - Hi All, Can we rollback DDL statements as well (create, drop, alter,truncate etc...)???? As per my knowledge in sql 2000 we...

Lock on SQL data rows - Hi, Can some one please help how can I acquire lock on rows while it is being worked by some one. My...


SQL Server 2016 : SQL Server 2016 - Administration

Connecting to an Availability Group - Hello, I'm setting up a 2016 ENT Availability Group with 2 VMs. I want to better understand my connection options. Server Names:...

Ola Hallengren's Maintenance question - I have a SQL server 2016 server that uses Ola Hallengren's Maintenance solution, Yesterday I got an alert SQL Server Alert...

Always on failover clustering - I'm a total rookie at clustering, so of course we will be starting a project requiring this feature soon. Are there...

Always On Availability Groups Failover Question - Hi, I'm curious if anyone knows the answer to this off the top of their head since I'm not able to...


SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Quick and Easy write back interface for Business users - Hi, I'm part of a small DW/BI team using SQL 2008( upgrading to 2016 in a couple of months) + tableau...


SQL Server 2014 : Administration - SQL Server 2014

Missing Indexes via Extended events - Is there a way to identify missing indexes via extended events ?


SQL Server 2012 : SQL Server 2012 - T-SQL

Wildcard in IN statement - I'm using a function to convert a users comma separated list of values to a table to use in a...

update query help - Hi Experts, Need some TSQL help. We have guids in our env and want to get rid of it down...

convert date to numeric - Hello, I have a excel document that contains 4 digit numbers in a column.  Sometimes these numbers come in as dates. ...

Efficient way to count distinct - I am trying to get a query to run which counts distinct values for two columns.   I have a temp table named #Paid...

HELP NEEDED: SQL server always on availability group - Connection string parameter - Hi all, I have several SQL instances, few with Always on AG activated and few with out Always on AG group. I...


SQL Server 2008 : T-SQL (SS2K8)

stop delete trigger firing when i'm doing an "admin delete" - Sometimes I don't quite have the right words to google or ask for help with …. please bear with.  I am...

how can I get uniqueidentifier value as return after insert a record - Hi... how can I get uniqueidentifier value as return after insert a record.


Cloud Computing : SQL Azure - Development

how to troubleshoot this issue??? function failing with error - Hi All, Need some tips to troubleshoot below issue. we are seeing  below error Intermitently and the error automatically gets...


SQLServerCentral.com : SQLServerCentral.com Website Issues

Older posts - (new to SQLServerCentral, bear with me) I have a situation where a SQL Server reboots itself nightly.  Looking for answers as...


Career : Employers and Employees

Dilemma between Microsoft BI developer or Application Developer - I am stuck in a dilemma; at my workplace I have to choose my career track between as Microsoft BI...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2018 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com