T-SQL

  • Comments posted to this topic are about the item T-SQL

  • I have to disagree with the correct answer(s). The question asks for 4 correct answers yet 5 are presented. This causes a bit of a guessing game.

    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

  • SQLRNNR (1/3/2013)


    I have to disagree with the correct answer(s). The question asks for 4 correct answers yet 5 are presented. This causes a bit of a guessing game.

    I didn't read the last one ; when marked 4th one , I should read complete question and answers ;-)..

    yes the last one is correct, Unique key does allow one null.

    "The Unique key allows NULL values as a value"

    is an answer too..

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • Since last option said Unique key allows NULL values.. he didn't mention xactly one NULL value in the option . so I choosed other 4 and got it correct..

    --

    Dineshbabu

    --
    Dineshbabu
    Desire to learn new things..

  • dineshbabus (1/3/2013)


    Since last option said Unique key allows NULL values.. he didn't mention xactly one NULL value in the option . so I choosed other 4 and got it correct..

    --

    Dineshbabu

    That is true, but a Unique constraint can allow more than one null value.

    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

  • oh.. unique constraint can allow more than one null value.. I don't think so.. Please give me some xample...

    --
    Dineshbabu
    Desire to learn new things..

  • dineshbabus (1/3/2013)


    oh.. unique constraint can allow more than one null value.. I don't think so.. Please give me some xample...

    In SQL Server 2008, there is a concept of filtered indexes.

    You can combine this concept with Unique Constraint to create a unique constraint which allows more than one NULL value.


    Kingston Dhasian

    How to post data/code on a forum to get the best help - Jeff Moden
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • I think few days back same conversation happened... Unique constraint is different from unique index. While creating unique index we can create with filter and that will allow more than one NULL value but not with unique constraint.

    --

    Dineshbabu

    --
    Dineshbabu
    Desire to learn new things..

  • dineshbabus (1/3/2013)


    I think few days back same conversation happened... Unique constraint is different from unique index. While creating unique index we can create with filter and that will allow more than one NULL value but not with unique constraint.

    --

    Dineshbabu

    Go back to that QOTD thread and read my response showing that multiple null values is possible in a unique constraint.

    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

  • dineshbabus (1/3/2013)


    oh.. unique constraint can allow more than one null value.. I don't think so.. Please give me some xample...

    Here is the example

    http://www.sqlservercentral.com/Forums/FindPost1398825.aspx

    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

  • And here is the MS Reference (note the careful wording of One Null Value per Column is wrong if you run the test code I provided in the previous link)

    http://msdn.microsoft.com/en-us/library/ms191166(v=sql.105).aspx

    There are additional references in the comments on that page demonstrating multiple nulls in a single column.

    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

  • Thanks for providing few very good references for Unique constraint.

    But all the links gives lot of work arrounds for inserting NULL values. But my question is i have created an unique constraint on COL1. Now can I insert multiple NULL into COL1 ?

    Since I'm not much xpert in Sql Server, Please give me some detailed explaintaion.

    --

    Dineshbabu

    --
    Dineshbabu
    Desire to learn new things..

  • A single column Unique constraint can have only one Null value (unless you use the filtered index version of a unique index) which some debate is not a constraint).

    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

  • SQLRNNR (1/4/2013)


    A single column Unique constraint can have only one Null value (unless you use the filtered index version of a unique index) which some debate is not a constraint).

    That's what i'm trying to convey right from the begining..

    --

    Dineshbabu

    --
    Dineshbabu
    Desire to learn new things..

  • Dineshbabu (1/4/2013)


    SQLRNNR (1/4/2013)


    A single column Unique constraint can have only one Null value (unless you use the filtered index version of a unique index) which some debate is not a constraint).

    That's what i'm trying to convey right from the begining..

    --

    Dineshbabu

    But that is not an adequate definition of a unique constraint. A unique constraint can be on more than a single column and thus allow multiple nulls in each column. So even with the wording of the question and wording of the 5th answer - there is nothing that specifies a single column constraint and therefore the 5th answer must be correct.

    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

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

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