locked
Default Value or null field RRS feed

  • Question

  • Hi,

      This might sound like a rookie question but need to ask it. I have a table, where I have a boolean field with no default value so as default it is always null. Now when I do a select statement and check the field, everytime I am doing ISNULL(FieldName, 0). Is it good practice not to define a default value or really it does not effect the operation time.

    Thanks

    Ivan

    Thursday, January 10, 2019 7:41 PM

Answers

  • Hi Ivan Sammut,

    Actually, NULL doesn't mean "0", it represents missing value, SQL Server uses the three valued logic: TRUE,FALSE,UNKNOWN.

    If in your environment, NULL can be seen as FALSE, you can replace them to 0. But this operation may affect the result of CHECK, ON,WHERE,HAVING.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 11, 2019 7:08 AM
  • https://weblogs.sqlteam.com/mladenp/2007/09/06/how_does_sql_server_really_store_null-s/

    mohammad waheed

    Thursday, January 10, 2019 8:12 PM
  • In our database we define default values for all types except for datetime which are nullable. For all other types we don't allow NULLs and use default values. If you don't expect NULL to be useful and expect false to be the default value, then I suggest to add it as default value in your table.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, January 13, 2019 10:32 PM
    Answerer

All replies

  • Hi Ivan,

    Just curious, what do you mean by "check the field?" Are you using it in a criteria? If you can avoid it, try not to use a function if a native SQL keyword can do it, like Is Null.

    Thursday, January 10, 2019 8:03 PM
  • Hi,

    Everytime I query this table one of the conditions in the where clause is always isnull(fieldname, 0) <> 1

    10x

    Ivan

    Thursday, January 10, 2019 8:05 PM
  • https://weblogs.sqlteam.com/mladenp/2007/09/06/how_does_sql_server_really_store_null-s/

    mohammad waheed

    Thursday, January 10, 2019 8:12 PM
  • Hi,

    Everytime I query this table one of the conditions in the where clause is always isnull(fieldname, 0) <> 1

    10x

    Ivan

    Hi Ivan,

    Isn't isnull(fieldname,0)<>1 basically the same as (fieldname=0 OR fieldname is null) but without using a function call?

    Thursday, January 10, 2019 8:58 PM
  • Yes it is the same without a function call but with an extra condition the "or"

    Ivan

    Thursday, January 10, 2019 9:40 PM
  • Hi Ivan Sammut,

    Actually, NULL doesn't mean "0", it represents missing value, SQL Server uses the three valued logic: TRUE,FALSE,UNKNOWN.

    If in your environment, NULL can be seen as FALSE, you can replace them to 0. But this operation may affect the result of CHECK, ON,WHERE,HAVING.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, January 11, 2019 7:08 AM
  • In our database we define default values for all types except for datetime which are nullable. For all other types we don't allow NULLs and use default values. If you don't expect NULL to be useful and expect false to be the default value, then I suggest to add it as default value in your table.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Sunday, January 13, 2019 10:32 PM
    Answerer