T-SQL
-
(Bob Brown) (1/4/2013)
Toreador (1/4/2013)
Victor Abkin (1/4/2013)
I disagree with the list of correct answers: this list should contain 5 answers, not 4.Asking to choose 4 answers makes it confusing
There are 4 correct ansers - 2,4,5 and 6 - just not the answers marked as being correct.
Is number 3 an incorrect statement?
No #3 is correct.
by default a Primary key creates a Clustered Index
The problem is that 2,3,4,5,6 are all correct (or maybe incorrect if you realize that there is no such thing a unique key).
_______________________________________________________________
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/ -
Thank you Sean. I was not questioning the validity of the number of correct answers, I just wanted to know what I was missing on #3 being incorrect.
-
(Bob Brown) (1/4/2013)
Is number 3 an incorrect statement?The correct answer to that is 'sometimes' but since it's only a 2-state checkbox then it is incorrect. As I pointed out on page 3, the default is only clustered if there is not already a clustered index.
For instance
create table test(a int primary key,
b int unique clustered
)
will create a nonclustered primary key.
-
I think the choice as phrased is ambiguous and should be accepted as correct:
"The Unique key allows NULL values as a value"
This is conventional English phrasing in which "allows NULL values" often includes the case where only one NULL value is allowed. In other words, it doesn't disallow NULL values altogether.
I also would like points back for this question. But I don't know if points are given back for ambiguity as opposed to outright wrong answer choices.
- webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html -
L' Eomot Inversé (1/4/2013)
Dineshbabu (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
create table #Test (P int identity primary key, A int, B int, unique(A,B))insert #Test(A,B) values(1,NULL),(2,NULL),(3,NULL),(NULL,1),(NULL,2),(NULL,3)
insert #Test(A,B) values (NULL,NULL)
select * from #Test order by P
drop table #TestTry running that code and you'll see that despite teh unique constraing column A has 4 rows with NULL in it, and column B has 4 rows with null in it. In fact there are 7 rows and in every one of them either A or B or both are null.
Your statement about only one null being permitted is valid only when the unique constraint applies to a single column; it is not valid when it applies to multiple columns.
However, each pair is unique and attempting to enter a duplicate will fail.
create table #Test (P int identity primary key, A int, B int, unique(A,B));
insert #Test(A,B) values(1,NULL),(2,NULL),(3,NULL),(NULL,1),(NULL,2),(NULL,3);
insert #Test(A,B) values (NULL,NULL);
select * from #Test order by P;
go
insert #Test(A,B) values(1,NULL); -- Will fail
GO
insert #Test(A,B) values(2,NULL); -- Will fail
GO
insert #Test(A,B) values(3,NULL); -- Will fail
GO
insert #Test(A,B) values(NULL,1); -- Will fail
GO
insert #Test(A,B) values(NULL,2); -- Will fail
GO
insert #Test(A,B) values(NULL,3); -- Will fail
go
insert #Test(A,B) values(4,NULL); -- Will succeed
GO
select * from #Test order by P;
go
drop table #Test;
-
choose 4 of the 5 right answers but not the right answers because no where in the stated documentation does it discuss the creation of indexes but both talk about Null values. So were is the document that tell what type of index is created by each constraint?
-
SQLRNNR (1/3/2013)
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.
Wow... this again... Bad quesiton + Uniqueness settings =Endless Debate
-
SanDroid (1/4/2013)
SQLRNNR (1/3/2013)
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.
Wow... this again... Bad quesiton + Uniqueness settings =Endless Debate
😀
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 -
Toreador (1/4/2013)
I don't really like this question."They are not the same, but they both enforce uniqueness of the column on which they are defined"
assumes that they can both only be defined on a single column, whereas they can be multiple columns.
If this assumption is made then "The Unique key allows NULL values as a value" is indeed false, as you can have only one. But a unique constraint on 2 columns lets you have null in both values so the statement is true.
"by default a Primary key creates a Clustered Index"
is false. The default is only clustered if there is not already a clustered index.
create table test(a int primary key,
b int unique clustered
)
will create a nonclustered primary key even though this is not specified.
+1
-
Sean Lange (1/4/2013)
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.+1
I had to guess which 4 of the 5 correct answers were expected. I chose incorrectly.
+1
That's what I thought while attempting. Not lucky enough in my choice though 🙂
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Lynn Pettis (1/4/2013)
However, each pair is unique and attempting to enter a duplicate will fail.Yes, of course attempting to insert a duplicate will fail, that's the whole point of a UNIQUE constraint. But the statement I was debunking was nothing to do with duplicates, it was the satement that for a UNIOQUE constraint only 1 null is allowed: that's plainly false, because a UNIQUE constraint may allow, for example , a row with two NULLs amongst the columns covered by the constraint.
When at least one and maybe two of the "correct" answers to a question are wrong, and at least one of the "incorrect" answers is right, it's a poor question. And when someone tries to justify one of the author's errors by making a nonsense claim that needs to be debunked, and the question's author has taken encouragement in his misunderstanding from that nonsense, it doesn't help at all to jump in with a completely irrelevant comment that may be misinterpreted as contradicting that debunking.
Tom
-
Toreador (1/4/2013)
I don't really like this question."They are not the same, but they both enforce uniqueness of the column on which they are defined"
assumes that they can both only be defined on a single column, whereas they can be multiple columns.
If this assumption is made then "The Unique key allows NULL values as a value" is indeed false, as you can have only one.
Actually it might be false for a simpler reason: multiple values can't be a value - the plural is not the singular, and maybe that was the author's intention.
I hope it is safe to disregard that possibility.
Tom
-
I strongly agree with SQLRNNR, that`s true UNIQUE constraint does allow inserting multiple values (in a single column) as long as the whole compination of the UNIQUE constraint is not violated.
And we should got the point we lost back 🙂
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant -
Lynn Pettis (1/4/2013)
L' Eomot Inversé (1/4/2013)
Dineshbabu (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
create table #Test (P int identity primary key, A int, B int, unique(A,B))insert #Test(A,B) values(1,NULL),(2,NULL),(3,NULL),(NULL,1),(NULL,2),(NULL,3)
insert #Test(A,B) values (NULL,NULL)
select * from #Test order by P
drop table #TestTry running that code and you'll see that despite teh unique constraing column A has 4 rows with NULL in it, and column B has 4 rows with null in it. In fact there are 7 rows and in every one of them either A or B or both are null.
Your statement about only one null being permitted is valid only when the unique constraint applies to a single column; it is not valid when it applies to multiple columns.
However, each pair is unique and attempting to enter a duplicate will fail.
create table #Test (P int identity primary key, A int, B int, unique(A,B));
insert #Test(A,B) values(1,NULL),(2,NULL),(3,NULL),(NULL,1),(NULL,2),(NULL,3);
insert #Test(A,B) values (NULL,NULL);
select * from #Test order by P;
go
insert #Test(A,B) values(1,NULL); -- Will fail
GO
insert #Test(A,B) values(2,NULL); -- Will fail
GO
insert #Test(A,B) values(3,NULL); -- Will fail
GO
insert #Test(A,B) values(NULL,1); -- Will fail
GO
insert #Test(A,B) values(NULL,2); -- Will fail
GO
insert #Test(A,B) values(NULL,3); -- Will fail
go
insert #Test(A,B) values(4,NULL); -- Will succeed
GO
select * from #Test order by P;
go
drop table #Test;
Hi Lynn,
Thanks for conveying my thought with an example.
--
Dineshbabu
Desire to learn new things.. -
I just got confused with "NULL value(S)". The last S made it plural and made my guess wrong. 🙁
Good question. Just needed extra attention. 🙂
Viewing 15 posts - 46 through 60 (of 70 total)
You must be logged in to reply to this topic. Login to reply