April 25, 2011 at 10:30 pm
Hello evryone... hw come a not null defined TYPE accept null value am i going wrong anywhere or else is there any internal feature i am missing..?
Can anyone be more descriptive so that it is more clear.
April 25, 2011 at 10:45 pm
Can a type declared as NOT NULL accept NULL values?
---
Thinking is the hardest work there is, which is the probable reason so few engage in it.
Sunitha
😎
April 25, 2011 at 11:23 pm
Great question!
It highlights something which could be considered a bug. It's certainly not something you would expect from reading the documentation on CREATE TYPE.
Being someone who actually used sp_addtype "back in the day" I wasn't surprised. The nullability is an attribute of the actual column. How the type is defined only determines how columns created from that type will behave unless specified otherwise.
April 25, 2011 at 11:32 pm
Interesting question, thanks!
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
April 26, 2011 at 12:13 am
Simple and good question
Srikant
April 26, 2011 at 5:04 am
I didnt get exactly what happening after using NULL again use of type declaration. if anyone know about this answer. Please Explain me.
April 26, 2011 at 5:11 am
Good question, Thanks.
M&M
April 26, 2011 at 5:40 am
Very good question.
I think the reason this works is that the NULL in the CREATE TABLE is an attribute of the column (since NULL is actually just a flag assigned to a column and not actually dependant upon data type). As such, the NULL attribute of the TYPE is a default, rather than a strictly enforced requirement, so adding NULL to the column definition overrides that default.
http://www.techtalkz.com/microsoft-sql-server/162468-weird-behaviour-user-defined-type-not-null.html shows the same thing happening, except in this case the NULL attribute is enforced by adding a column to a table after the fact without specifying a default value.
That behavior is documented fairly well, but in this behavior could definitely be documented better.
April 26, 2011 at 7:07 am
Excellent Answer.
April 26, 2011 at 7:19 am
Good question. Learned something new about creating types. :hehe:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 26, 2011 at 7:48 am
sknox (4/26/2011)
Very good question.I think the reason this works is that the NULL in the CREATE TABLE is an attribute of the column (since NULL is actually just a flag assigned to a column and not actually dependant upon data type). As such, the NULL attribute of the TYPE is a default, rather than a strictly enforced requirement, so adding NULL to the column definition overrides that default.
http://www.techtalkz.com/microsoft-sql-server/162468-weird-behaviour-user-defined-type-not-null.html shows the same thing happening, except in this case the NULL attribute is enforced by adding a column to a table after the fact without specifying a default value.
That behavior is documented fairly well, but in this behavior could definitely be documented better.
Thank you very much for this explanation! It looks very reasonable, still I wish that NOT NULL was a real constraint.
Cheers
April 26, 2011 at 8:01 am
The last place I worked had a number of user defined types that were defined as not null. When working on cleanup or troubleshooting scripts where I pulled stuff into a table variable but may not have that data yet I had to specify that NULL could go in the column. It came in very handy to be able to override that instead of having to figure out what data type matched up with the custom one and use the system type. If you don't specify NULL the column does inherit the NOT NULL property so while there is an override to allow NULL it does require extra action so a conscious decision is needed to override it.
April 26, 2011 at 10:30 am
Intriguing question - thanks
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
April 26, 2011 at 10:51 am
Rune Bivrin (4/25/2011)
Being someone who actually used sp_addtype "back in the day" I wasn't surprised. The nullability is an attribute of the actual column. How the type is defined only determines how columns created from that type will behave unless specified otherwise.
Exactly. This sets the defaults for your custom or user Data Type.
This parameter setting is well explained in this article:
http://msdn.microsoft.com/en-us/library/aa259606(SQL.80).aspx
Note The null_type parameter only defines the default nullability for this data type. If nullability is explicitly defined when the user-defined data type is used during table creation, it takes precedence over the defined nullability. For more information, see ALTER TABLE and CREATE TABLE.
With CLR user-defined types custom type mutators that also change how the type reacts make this type of behavior even more interesting.
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply