T-SQL

Question

T-SQL

PChiragS

SSCarpal Tunnel

Points: 4965
More actions
January 3, 2013 at 9:01 pm

#286980

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

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

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 1

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

demonfox SSCertifiable Points: 6289 More actions · Answer 2

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:

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 3

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 4

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

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 5

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

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 6

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/

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 7

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

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

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 11

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

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

Dineshbabu Hall of Fame Points: 3220 More actions · Answer 13

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

SQLRNNR SSC Guru Points: 281334 More actions · Answer 14

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