Computed columns Data Types

Question

Computed columns Data Types

SQLDoubleG

Hall of Fame

Points: 3100
More actions
November 14, 2013 at 8:20 pm

#300265

Comments posted to this topic are about the item Computed columns Data Types
November 15, 2013 at 1:51 am

This was removed by the editor as SPAM

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

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 2

Nice question!

Thanks

Igor Micev,My blog: www.igormicev.com

Carlo Romagnano SSC-Insane Points: 22680 More actions · Answer 3

Carlo Romagnano

SSC-Insane

Points: 22680

November 15, 2013 at 2:22 am

#1666652

Optimum!

Bob JH Cullen SSCrazy Points: 2082 More actions · Answer 4

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 More actions · Answer 5

Gazareth

One Orange Chip

Points: 27737

November 15, 2013 at 4:25 am

#1666673

Raul! How's it going?

Nice question 🙂

Gaz

Gazareth One Orange Chip Points: 27737 More actions · Answer 6

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: 3100 More actions · Answer 7

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 More actions · Answer 8

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: 287026 More actions · Answer 9

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

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Gazareth One Orange Chip Points: 27737 More actions · Answer 10

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 More actions · Answer 11

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 More actions · Answer 12

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 More actions · Answer 13

PChiragS

SSCarpal Tunnel

Points: 4965

November 15, 2013 at 7:45 am

#1666741

Good question.. Raul..

Thanks