December 20, 2012 at 8:43 am
dawryn (12/20/2012)
BTW found this request for unique constraint ANSI compliance regarding multiple null values
I greatly wish that a large number of people would pile in and downvote that awful connect item. As Michael Lato pointed 5 and a quarter years ago (and as was confirmed by MS nearly 5 years ago), there's no need to change because it's possible to use a filtered index to have the effect Hugo was asking for. As Erland Sommarskog pointed out 5 years ago, the current MS definition of a UNIQUE constraint is extremely useful in many situations. I added a very tongue in the cheek comment early last year suggesting a move in very much the opposite direction - get rid of the idiocy in the standards - because I thought the intransigence and apparently brainless ISO-worship of supporters of the connect item in the face of there being perfectly satisfactory means of achieving the sorts of constraints that they want without destroying the current extremely useful feature of SQL server needed an example of similar stupidity (the no nulls at all unique constraint is trivial to achieve, just as is the mutiple nulls allowed one, and therefor throwing away the existing useful feature to make it the only sort of unique constraint would be exactly as stupid as implementing this connect item) to wake them up to their own stupidity. Unfortunately that didn't work. In fact I suspect it encouraged David Portas to add another dangerous connect item a few days later, which I would also encourage everyone to vote against (if they have time - it's rather unlikely to happen anyway); I tried sarcasm there too - again, it didn't work.
Tom
December 20, 2012 at 8:56 am
L' Eomot Inversé (12/20/2012)
dawryn (12/20/2012)
BTW found this request for unique constraint ANSI compliance regarding multiple null valuesI greatly wish that a large number of people would pile in and downvote that awful connect item.
You're welcome to your opinion.
As Michael Lato pointed 5 and a quarter years ago (and as was confirmed by MS nearly 5 years ago), there's no need to change because it's possible to use a filtered index to have the effect Hugo was asking for.
And as Hugo pointed out, the issue is not just about creating the same effect some other way, but about keeping SQL skills transferable and minimizing the differences between implementations, especially when such differences aren't necessary.
As Erland Sommarskog pointed out 5 years ago, the current MS definition of a UNIQUE constraint is extremely useful in many situations.
Name them. I have exactly 0 situations where this definition is useful to me. I have many where the ANSI-standard definition is useful, and the UNIQUE INDEX workaround is cumbersome and non-intuitive.
I added a very tongue in the cheek comment earlier this year suggesting a move in very much the opposite direction than the idiocy in the standards because I thought the intransigence and apparently brainless ISO-worship of supporters ....
Opinion is valuable; insults are not. Rephrasing your last two sentences to remove the insults leaves us with this:
I added a very tongue in the cheek comment earlier this year suggesting a move in very much the opposite direction than the standards because I thought I could change the minds of people who didn't agree with me by trivializing their position. Unfortunately that didn't work.
EDIT: Fixed bad IFCode.
December 20, 2012 at 9:36 am
(Bob Brown) (12/20/2012)
I knew there might be a controversy over this question but I took a stab at YES because of the Itsik Ben-Gan article I read on this subject. Still I was nervous that the wording would bite me because I usually go the wrong way.;-)http://www.sqlmag.com/article/sql-server/unique-constraint-with-multiple-nulls
Thanks for bringing that reference to the discussion. This is useful information.
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
December 20, 2012 at 10:50 am
Totally disagree with the answer to the question.
"You can use UNIQUE constraints to make sure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.
Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table.
Also, unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column.
A UNIQUE constraint can be referenced by a FOREIGN KEY constraint"
Using multiple filtered indexes is essentially creating multiple unique constraints, each of which will still only allow one null value. That is allowed but a single unique constraint still only allows a single null value.
December 20, 2012 at 10:56 am
The answer (or the question) is misleading. It asks about a unique constraint, then answers about a unique index. These are NOT the same thing.
MSDN is clear on the subject :
Also, unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column.
If the author had applied the unique constraint mentioned in the question, then the answer would be reversed.
http://msdn.microsoft.com/en-us/library/ms191166%28v=sql.105%29.aspx
December 20, 2012 at 11:05 am
Good question, poor title.
=> If you ask about constraints I think in constraints.
=> If you ask about indexes I think in indexes.
But if you ask about vacations I think that it's time to take on 😛
December 20, 2012 at 5:02 pm
George M Parker (12/20/2012)
Totally disagree with the answer to the question."Also, unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column. "
The trouble with that quotation from BoL (you should attribute what you quote, but never mind) is that it's actually wrong (like a few other things in BoL). You can demonstrate it's wrongness using the code that SQLRNNR posted earlier, or with the code snippet I use to educate people on this topic, which is if exists (select * from sys.objects where type = 'U' and name = 'k') drop table k
create table k (a int primary key,b int, c int, unique(b,c))
insert k values (0,0,0),(1, 1, null), (2,null,1), (3,2,null), (4,null,2),(5,3,3),(6,null,null)
select * from sys.key_constraints where name like 'UQ__k__%'
select * from k order by a
drop table k
Tom
December 20, 2012 at 5:12 pm
Narud (12/20/2012)
Good question, poor title.
The question is explicitly about constraints:
Is it possible to create a table with unique constraint ....
and the title is too. So why is the question good if the title is poor?
Anyway, there's nothing wrong with the question or indeed with the answer (the answer is correct), it's just that the explanation (not the answer) is about indexes, not about constraints.
=> If you ask about constraints I think in constraints.
=> If you ask about indexes I think in indexes.
But if you ask about vacations I think that it's time to take on 😛
On those pointes I definitely agree!
Tom
December 21, 2012 at 3:04 am
L' Eomot Inversé (12/20/2012)
dawryn (12/20/2012)
BTW found this request for unique constraint ANSI compliance regarding multiple null valuesI greatly wish that a large number of people would pile in and downvote that awful connect item. As Michael Lato pointed 5 and a quarter years ago (and as was confirmed by MS nearly 5 years ago), there's no need to change because it's possible to use a filtered index to have the effect Hugo was asking for. As Erland Sommarskog pointed out 5 years ago, the current MS definition of a UNIQUE constraint is extremely useful in many situations. I added a very tongue in the cheek comment early last year suggesting a move in very much the opposite direction - get rid of the idiocy in the standards - because I thought the intransigence and apparently brainless ISO-worship of supporters of the connect item in the face of there being perfectly satisfactory means of achieving the sorts of constraints that they want without destroying the current extremely useful feature of SQL server needed an example of similar stupidity (the no nulls at all unique constraint is trivial to achieve, just as is the mutiple nulls allowed one, and therefor throwing away the existing useful feature to make it the only sort of unique constraint would be exactly as stupid as implementing this connect item) to wake them up to their own stupidity. Unfortunately that didn't work. In fact I suspect it encouraged David Portas to add another dangerous connect item a few days later, which I would also encourage everyone to vote against (if they have time - it's rather unlikely to happen anyway); I tried sarcasm there too - again, it didn't work.
Hi Tom,
I don't know what happened when you wrote that - I have never seen that kind of language from you before! "intransigence" (had to look that word up in a dictionary) ... "apparently brainless" ... "stupidity" ... not your style at all! Luckily, you targeted the up-voters of that connect item, not the submitter, so I don't need to feel offended. 😉
My point in submitting that Connect suggestion is not that you cannot implement the business rule of uniqueness for non-missing values in any other way. You can. Just as you can implement the business rule of uniqueness for non-missing value with the added restriction of only a single missing value (plus the additional really awkward wording for UNIQUE on multiple columns). We do not even need a UNIQUE constraint at all, there are workarounds for everything.
My point is also not that I want the current implementation of UNIQUE removed from the product.
My point is that IF a feature is implemented that has the same name as a feature in the ANSI standard, then it should behave exactly as described in that standard. If a vendor chooses to offer additional options as well - fine, but use optional keywords for them, and make sure that the defaults are for ANSI behaviour.
Good examples of this are TOP (non-standard - but no problem, leave it out and your queries behave as described in ANSI, put them in and you explicitly choose to get non-standard behaviour - and you'll get a very clear error message when porting that code to another platform); and DECLARE CURSOR (which can be used in two different ways, either ANSI-compliant with limited options, or non-standard with more options).
A terrible example is the UNIQUE constraint - declare it and at first sight, it behaves exactly as expected. In the worst case, you'll only realise that it behaves differently on your new platform when the application is already in production.
December 21, 2012 at 7:07 am
+1
December 21, 2012 at 7:12 am
Terry300577 (12/20/2012)
TriggerMan (12/20/2012)
These questions really are like individuals' SQL scripting styles... there are always 50 perspectives... I give you from BOL from SQL 2008 Express R2 (which many of you Gurus constantly lament us Noobs for not using):"unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column."
...and the original question:
"Is it possible to create a table with unique constraint that allows multiple NULL values from SQL Server 2008 onwards? "
I understand that there are nuances in the wording between the question and reference. I implore those posting questions: PLEASE state ALL of the variables when posting the question. The fact that it is also a T-SQL question worth ONE point leads one to believe that there are no tricks or esoteric musings.
Missing one pointers is very discouraging to us NOOBS.. I think the key to keeping your sanity with SQL is to pick your favorite guru and emulate his/her style. It doesn't matter whom you choose, there will be 100 Million DBAs/users who will tell you that, "...sure you got the desired results, but THIS(MY) way is more efficient." I've never seen ones and zeroes be interpreted so subjectively. I guess that's the beauty of taming the SQL beast. Value-added IT support. 🙂
+1
I found my Guru! 😛
December 24, 2012 at 5:28 pm
As explained in several answers, the 'solution' created a UNIQUE INDEX (on the non-NULL parts of the table) not a UNIQUE CONSTRAINT on the entire table.
Derek
December 27, 2012 at 9:00 am
Lots of posts and comments but none of them, including the answer code, shows a UNIQUE CONSTRAINT on a TABLE column that allows more than one NULL value.
A UNIQUE INDEX is not a UNIQUE CONSTAINT and they are used for two differant things.
December 27, 2012 at 12:16 pm
Hugo Kornelis (12/21/2012)
My point is also not that I want the current implementation of UNIQUE removed from the product.
My point is that IF a feature is implemented that has the same name as a feature in the ANSI standard, then it should behave exactly as described in that standard. If a vendor chooses to offer additional options as well - fine, but use optional keywords for them, and make sure that the defaults are for ANSI behaviour.[/quote-0]
Hugo you are better than this.
MS SQL Server is an ACID compliant Transactional RDMS system that Logs all DML.
It also supports ANSI SQL as a secondary language. :w00t:
So your arguments suggesting we should limit our Choices and options as DBA's and Developers when using MS SQL server so it will support some language standard that is secondary to T-SQL and ACID support seem very contrary and argumentative. 😎
December 27, 2012 at 12:41 pm
SanDroid (12/27/2012)
Hugo Kornelis (12/21/2012)
My point is also not that I want the current implementation of UNIQUE removed from the product.
My point is that IF a feature is implemented that has the same name as a feature in the ANSI standard, then it should behave exactly as described in that standard. If a vendor chooses to offer additional options as well - fine, but use optional keywords for them, and make sure that the defaults are for ANSI behaviour.[/quote-0]
Hugo you are better than this.
MS SQL Server is an ACID compliant Transactional RDMS system that Logs all DML.
It also supports ANSI SQL as a secondary language. :w00t:
So your arguments suggesting we should limit our Choices and options as DBA's and Developers when using MS SQL server so it will support some language standard that is secondary to T-SQL and ACID support seem very contrary and argumentative. 😎
ANSI is a standardisation institute. ANSI has published several standards, over the years, for relational databases. Those standards include descriptions of language features. Companies that create a relational database can choose to adhere to the standard, either fully or partially, or not to adhere to it at all. As far as I know, there are no companies with 100% ANSI compliance, but most major players in the RDBMS market comply with a large part of the ANSI standard.
SQL Server supports only one language: T-SQL. That language complies for a good part with the ANSI standard. It doesn't implement all ANSI features, and it implements lots of features that are not in the ANSI standard.
I don't suggest limiting choices and options. I do think that it is needlessly confusing that SQL Server implements one specific feature that has the same name as an ANSI standard feature, but a different implementation.
Viewing 15 posts - 46 through 60 (of 64 total)
You must be logged in to reply to this topic. Login to reply