Computed columns Data Types

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    Comments posted to this topic are about the item Computed columns Data Types

  • Koen Verbeeck

    SSC Guru

    Points: 258927

    Very interesting question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • This was removed by the editor as SPAM

  • Igor Micev

    SSC-Dedicated

    Points: 33109

    Nice question!

    Thanks

    Igor Micev,
    My blog: www.igormicev.com

  • Carlo Romagnano

    SSC-Insane

    Points: 21747

    Optimum!

  • Bob JH Cullen

    SSCrazy

    Points: 2082

    the function LEFT can return NULL values therefore the computed column is created as NULL

    Just how can LEFT return a NULL when operating on a CHAR(8) NOT NULL value??

  • Gazareth

    One Orange Chip

    Points: 27737

    Raul! How's it going?

    Nice question 🙂

    Gaz

  • Gazareth

    One Orange Chip

    Points: 27737

    Bob Cullen-434885 (11/15/2013)


    the function LEFT can return NULL values therefore the computed column is created as NULL

    Just how can LEFT return a NULL when operating on a CHAR(8) NOT NULL value??

    I think it's that the LEFT function definition itself is not guaranteed to return a not null value, rather than being evaluated based on the particular column/usage.

  • SQLDoubleG

    Hall of Fame

    Points: 3090

    Gazareth (11/15/2013)


    Bob Cullen-434885 (11/15/2013)


    the function LEFT can return NULL values therefore the computed column is created as NULL

    Just how can LEFT return a NULL when operating on a CHAR(8) NOT NULL value??

    I think it's that the LEFT function definition itself is not guaranteed to return a not null value, rather than being evaluated based on the particular column/usage.

    Hi Gaz, how you doing!

    You're right, the function LEFT() can return NULL values, so it doesn't really matter if in this case, being the column defined as NOT NULL, it will never return NULL, the expression will be NULLable anyway

    SELECT LEFT(NULL, 4)

    Cheers

  • Bob JH Cullen

    SSCrazy

    Points: 2082

    Raul, Gaz, thanks both. I should have known better than to assume SQL would apply some intelligence to the expression analysis! 😉

    Odd, though, in the table designer, how the 'Allow Nulls' and 'Data Type' attributes get blanked for computed columns - no hints at all there about what is going on in its tiny mind...

  • Ed Wagner

    SSC Guru

    Points: 286958

    Good question. It made me think, so I enjoyed it.

  • Gazareth

    One Orange Chip

    Points: 27737

    Bob Cullen-434885 (11/15/2013)


    Raul, Gaz, thanks both. I should have known better than to assume SQL would apply some intelligence to the expression analysis! 😉

    Odd, though, in the table designer, how the 'Allow Nulls' and 'Data Type' attributes get blanked for computed columns - no hints at all there about what is going on in its tiny mind...

    You just have to be very specific in the column definition, as everything comes from that - for not-nullable wrap the definition in an ISNULL function, for datatypes use CAST/CONVERT. Otherwise default return types are used, which aren't always what you'd expect!

  • Gazareth

    One Orange Chip

    Points: 27737

    raulggonzalez (11/15/2013)


    Gazareth (11/15/2013)


    Bob Cullen-434885 (11/15/2013)


    the function LEFT can return NULL values therefore the computed column is created as NULL

    Just how can LEFT return a NULL when operating on a CHAR(8) NOT NULL value??

    I think it's that the LEFT function definition itself is not guaranteed to return a not null value, rather than being evaluated based on the particular column/usage.

    Hi Gaz, how you doing!

    You're right, the function LEFT() can return NULL values, so it doesn't really matter if in this case, being the column defined as NOT NULL, it will never return NULL, the expression will be NULLable anyway

    SELECT LEFT(NULL, 4)

    Cheers

    Cool, thanks mate!

  • tabinsc

    SSCommitted

    Points: 1812

    Good question with a not-so-obvious answer. I use computed columns all the time, but never though about having nulls in them.

    Tony
    ------------------------------------
    Are you suggesting coconuts migrate?

  • PChiragS

    SSCarpal Tunnel

    Points: 4965

    Good question.. Raul..

    Thanks

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply