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 useNULL
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 useNULL
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
Retrieve Column Names and Types of a Stored Procedure
Order by Items Must Appear in the Select List If Select Distinct Is Specified
How to Get a List of Element Names from an Xml Value in SQL Server
Combine Multiple Select Statements
How to Create a Decimal Field in Access with Alter Table
Differencebetween a Stored Procedure and a View
What Is the Most Appropriate Data Type for Storing an Ip Address in SQL Server
Which SQL Statement Is Faster? (Having VS. Where...)
Why Are Aggregate Functions Not Allowed in Where Clause
Base 36 to Base 10 Conversion Using SQL Only
Check Whether String Is a Date Postgresql
Concurrency Handling of SQL Transactrion
Postgresql: Give All Permissions to a User on a Postgresql Database