Home Forums Article Discussions Article Discussions by Author Discuss content posted by Arto Ahlstedt How long is a NULL? RE: How long is a NULL?

  • December 12, 2009 at 12:09 pm

    #1091572

    vk-kirov (12/12/2009)

    Hugo Kornelis

    Thank you for explanation. Could you also explain why "under SQL Server 2000, the result is '1234' regardless of the number of NULLs appended"?

    Thanks!

    First, please be aware that my explanation for the SQL Server 2005 behaviour is based on guesswork and then verified by experiments. I don't have a SQL Server 2000 instance running, so I can only guess there.

    Also be aware that, as far as I know, there is no documentation about how SQL Server guesses the datatype of a NULL. As with any undocumented feature, it can change between sessions, or even between service packs or hotfixes.

    My guess is that on SQL Server 2000, the engine assumes a zero length string for NULL. That, or some completely different conversion takes place - if I recall correctly, a lot of the implicit type conversion stuff was changed between 2000 and 2005.

    Bottom line - if you need NULL to be the datatype you expect it to be, always use CAST(NULL AS datatype)

    Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
    Visit my SQL Server blog: https://sqlserverfast.com/blog/
    SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/