Computed columns Data Types

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

  • 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

  • Nice question!

    Thanks

    Igor Micev,
    My blog: www.igormicev.com

  • Optimum!

  • 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??

  • Raul! How's it going?

    Nice question 🙂

    Gaz

  • 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.

  • 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

  • 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...

  • 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!

  • 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!

  • 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?

  • 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