Sql: Using Null Values VS. Default Values

NULL vs Default Value in SQL Server Database

The meaning of NULL in a database should be reserved for 'unknown' which is not the same as empty. So as long as the default values you use reflect the nature of the underlying data, (i.e. it is not 'unknown') I would actually recommend using default values.

However, your mileage may vary. It depends on your application. If it handles unknown values in a different way, then by all means go with that :)

SQL Server how to set a default value when the column is null

Use case

select case 
when user_Name is null then "default value"
else user_name
end
from table

SQL Column definition: default value and not null redundant?

DEFAULT is the value that will be inserted in the absence of an explicit value in an insert / update statement. Lets assume, your DDL did not have the NOT NULL constraint:

ALTER TABLE tbl ADD COLUMN col VARCHAR(20) DEFAULT 'MyDefault'

Then you could issue these statements

-- 1. This will insert 'MyDefault' into tbl.col
INSERT INTO tbl (A, B) VALUES (NULL, NULL);

-- 2. This will insert 'MyDefault' into tbl.col
INSERT INTO tbl (A, B, col) VALUES (NULL, NULL, DEFAULT);

-- 3. This will insert 'MyDefault' into tbl.col
INSERT INTO tbl (A, B, col) DEFAULT VALUES;

-- 4. This will insert NULL into tbl.col
INSERT INTO tbl (A, B, col) VALUES (NULL, NULL, NULL);

Alternatively, you can also use DEFAULT in UPDATE statements, according to the SQL-1992 standard:

-- 5. This will update 'MyDefault' into tbl.col
UPDATE tbl SET col = DEFAULT;

-- 6. This will update NULL into tbl.col
UPDATE tbl SET col = NULL;

Note, not all databases support all of these SQL standard syntaxes. Adding the NOT NULL constraint will cause an error with statements 4, 6, while 1-3, 5 are still valid statements. So to answer your question: No, they're not redundant.

Is it pointless to add a default value of NULL to a nullable field

In SQL server there is not advantage in doing that, because:

  • When you omit a nullable column from the field list of an INSERT it will use NULL when no default constraint value is defined:

    -- update_datetime will be null when no default constraint is defined on that column
    insert into configuration_table (flag, insert_datetime)
    values (1,'20171209')
  • If you use the keyword DEFAULT as value for inserting the default value it will use NULL when no constraint is defined:

    -- update_datetime will be also null when no default constraint is defined on the column
    insert into configuration_table (flag, insert_datetime, update_datetime)
    values (1,'20171209',default)

In fact, there is a small disadvantage in doing that because that column will have an unneeded dependency on the constraint (for example, if the column is int and you want to ALTER the column to bigint it will fail because of the constraint).

So yes, it's pointless to set the default value on a nullable field to NULL, at least in SQL Server (maybe other RDBMS have quirks that made it useful).

Set default value in query when value is null

Use the following:

SELECT RegName,
RegEmail,
RegPhone,
RegOrg,
RegCountry,
DateReg,
ISNULL(Website,'no website') AS WebSite
FROM RegTakePart
WHERE Reject IS NULL

or as, @Lieven noted:

SELECT RegName,
RegEmail,
RegPhone,
RegOrg,
RegCountry,
DateReg,
COALESCE(Website,'no website') AS WebSite
FROM RegTakePart
WHERE Reject IS NULL

The dynamic of COALESCE is that you may define more arguments, so if the first is null then get the second, if the second is null get the third etc etc...

No default value Vs NULL Vs 0 in MySQL as a default value for text and integer fields

I use this simple rule of thumb:

I use NOT NULL for any field for which it would indicate a logical error in the system not to have supplied a value.

If telling the difference between "value not supplied" and "value supplied, but empty" then I use a field which may defaults to NULL. Defaulting to "" (or 0 for an integer field) is bad because it prevents you from making that distinction.



Related Topics



Leave a reply



Submit