Empty string and Default values

Published 02 September 08 08:56 AM | Madhivanan 

Often newbies use empty string and consider it as NULL. But when an empty string is passed, it may not be stored as empty string or NULL. It actually depends on the datatype of the column

Consider this example

DECLARE @t TINYINT,@s SMALLINT,@i INT,@big BIGINT,@f FLOAT,@bit BIT, @sm SMALLMONEY,@m MONEY,@d DATETIME
 
SELECT
        @t='',

        @i='',
        @big='',

        @f='',
        @bit='',

        @sm='',
        @m='',
        @d=''

SELECT
        @t as tinyint_col,
        @i as int_col,

        @big as bigint_col,
        @f as float_col,

        @bit as bit_col,
        @sm as smallmoney_col,

        @m as money_col,
        @d as datetime_col

 

The result is Zero for all the variables except @d which has the date value of 1900-01-01 00:00:00.000 





So beware of this and always use NULL if you want NULL values stored in the column 
and dont use empty string

Comments

# Other SQL Server Blogs around the Web said on September 2, 2008 4:04 AM:

Often newbies use empty string and consider it as NULL. But when an empty string is passed, it may not

# SQL Server Transact-SQL (SSQA.net) : Empty string and Default values said on September 2, 2008 4:05 AM:

PingBack from http://sqlserver-qa.net/blogs/t-sql/archive/2008/09/02/4832.aspx

Anonymous comments are disabled