NULL NOT NULL defaults to default in sql

Question

If I set an entry in Sql with a Default constraint, for example

 [Publicbit] BIT DEFAULT ((0)), 

Do I need to set a NULL/NOTNULL ?

Edit: I am using a boolean, but please extend your answer to other data types.

Answer 1

You do not need Null restrictions, but the default value will not protect your table from explicit NULL. Therefore, you must use the NOT NULL constraint if you want to apply this condition.

 use tempdb go CREATE TABLE example ( id BIT DEFAULT (0) ) INSERT example (id) VALUES (null) SELECT * FROM example 

Answer 2

No, they are not required.

  column_name 
     ...
     [NULL |  NOT NULL] 

You should not expect NULL when your parameters are 0/1 .

Answer 3

You must specify NOT NULL to protect against NULL from accessing this entry. It seems that the only valid values ​​are 0 and 1. If someone (or you in the future) writes a record (or code) passing in NULL for this column, then it will be allowed if you did not specify NOT NULL.

By specifying DEFAULT, you only protect NULL if the SQL INSERT statement does not include this column.

Answer 4

A column with a DEFAULT value may be required (NOT NULL), or it may be null (NULL). In my opinion, from the point of view of writing queries, it is better to have required columns, because a column that allows zero values ​​can lead to unnecessary complexity:

Example # 1 : in some cases, if you want to show all inactive lines, you can write

 WHERE Publicbit = 0 

but if this column allows null, you can use

 WHERE Publicbit = 0 OR Publicbit IS NULL 

Example # 2 : if you want to show those lines that have different values ​​for MyColum1 and MyColum2 , you can use

 WHERE MyColumn1 <> MyColumn2 

but if these columns are null then you should rewrite in this way

 WHERE MyColumn1 <> MyColumn2 OR MyColumn1 IS NULL AND MyColumn2 IS NOT NULL OR MyColumn1 IS NOT NULL AND MyColumn2 IS NULL 

or

 WHERE MyColumn1 <> MyColumn2 OR EXISTS ( SELECT MyColumn1 EXCEPT SELECT MyColumn2 ) 

Answer 5

It is simply optional. It is up to you if you want to specify it or not.

From MSDN :

Determines whether valid column values ​​are allowed. NULL is not strictly a restriction, but can be specified in the same way as NOT NULL.