Post reply

Type

  • April 26, 2011 at 11:47 am

    #1316075

    Additionally, the MSDN/BOL page on CREATE TABLE mentions this "feature":

    "[ type_schema_name. ] type_name

    Specifies the data type of the column, and the schema to which it belongs. The data type can be one of the following:

    ...

    An alias type based on a SQL Server system data type. Alias data types are created with the CREATE TYPE statement before they can be used in a table definition. The NULL or NOT NULL assignment for an alias data type can be overridden during the CREATE TABLE statement. However, the length specification cannot be changed; the length for an alias data type cannot be specified in a CREATE TABLE statement.

    "

    http://msdn.microsoft.com/en-us/library/ms174979.aspx

  • April 26, 2011 at 2:30 pm

    #1316220

    Interesting question..

  • April 26, 2011 at 3:45 pm

    #1316278

    Nice question, thanks!

  • April 26, 2011 at 7:28 pm

    #1316342

    Are NULL|NOT NULL specifications for alias types only valid for table columns? That is when it's not overridden?

    CREATE TYPE NotNullType FROM VARCHAR(10) NOT NULL;

    DECLARE @nnt NotNullType

    SET @nnt = 'abc'

    SELECT @nnt

    SELECT @nnt = NULL

    SELECT @nnt

    The above code returns 'abc' and then NULL. Am I to assume that the "NOT NULL" specification is ignored when an alias is used to define a variable?

    Also NotNullType defined as parameters in stored procedures accepts NULL arguments, whether it has a default or not.

    CREATE PROCEDURE spTypeTest

    @nnt NOTNULLTYPE = 'a'

    AS

    SELECT @nnt

    GO

    spTypeTest NULL

  • April 26, 2011 at 9:17 pm

    #1316360

    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.

    Removing the NULL from the column definition causes the insert to fail, and verifies this theory.

  • April 26, 2011 at 10:46 pm

    #1316368

    Thanks a lot.......

    ---
    Thinking is the hardest work there is, which is the probable reason so few engage in it.

    Sunitha
    😎

  • April 26, 2011 at 10:50 pm

    #1316369

    nice question thanks 🙂

  • April 28, 2011 at 7:07 am

    #1317227

    Nice question.

    The behaviour seems to be quite well documented on the CREATE TABLE page, so it's somewhat bizarre to see in the explanation the statement"I haven't seen mentioned in any documentation yet that the NOT NULL declaration can be overridden when using the type, like in the example above. " immediately before the reference to the CREATE TABLE page that contains very clear documentation of this.

    It's a pity it isn't documented on the CREATE TYPE page as well (equivalent behaviour is documented for types created by sp_addtype on its page).

    As far as I can tell, how the system treats the nullability property of user-defined types (or alias types) for columns using them added in an ALTER COLUMN statement is nowhere documented in BoL, which is a pity. I think that behaviour is that the nullability of the alias type is ignored altogether, which is also a pity - there ought at least to be some sort of warning when a value other than the one supplied by the alias type will be used for any reason other than an explicit override by the user.

    Tom

  • May 8, 2011 at 6:46 pm

    #1321511

    I agree that this overriden property behaviour should be better explained in the BOL.

    I got it right because I have met this situation before almost by accident.

    It offers some flexibility to the programmer but maybe the BOL should be more clear about what can be overriden and what can't.

    Great question and great debate.

    Best regards,

    Best regards,

    Andre Guerreiro Neto

    Database Analyst
    http://www.softplan.com.br
    MCITPx1/MCTSx2/MCSE/MCSA

  • June 23, 2011 at 8:09 pm

    #1343456

    Doesn't make sense. even though i have gone through all the replies and explanations

Viewing 10 posts - 16 through 24 (of 24 total)

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