InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 11:41:07 GMT20http://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx+1Tue, 15 Jan 2013 13:36:32 GMTasifkareemhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]Christian Buettner-167247 (1/11/2013)[/b][hr]Oh my, why is this question still wrong in January?!? :([/quote]That's a good question. These days it seems usual that something this bad is corrected quite quickly. Don't know why this one hasn't been.Fri, 11 Jan 2013 05:11:05 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxi couldn't fight back to those who said the question is wrong Chris :blink:GopiFri, 11 Jan 2013 05:06:03 GMTGopi Shttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxOh my, why is this question still wrong in January?!? :(Fri, 11 Jan 2013 01:20:36 GMTChristian Buettner-167247http://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]Hugo Kornelis (12/21/2012)[/b][hr]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. ;-)[/quote]Maybe I was remembering the period from 1985 to 1992, when I was young (¿¡when did I start calling my 40s young!?) and naïve and thought that ANSI would want the technically best standard instread of the best profits for American companies. But then came 1986 and 1992. I remember people I worked with back then (some from universities, some from Groupe Bull, some from INRIA, and various others) agreeing that it wouldn't possible to get anything into SQL2 (what we called the standard being worked on at that time, which eventually necame sql1992) unless it had already been implemented by IBM or by Oracle. I didn't keep my illusions. Really I should have lost them years before, because I had been bitten by the [strike]up[/strike]downgrade from a sensible version of Fortran to ASA (or was it already USASI? anyway, what is now called ANSI) Fortran66, which was basically Fortran IV (and was often incorrectly called Fortran IV) with the bits that hadn't been implemented by all of the big boys left out (Fortran IV was of course bad enough, but ASA can't be blamed for that, it wasn't involved in that "standard") when Fortran66 hit me in 1967.But SQL standardisation wasn't all bad - it did have NULL in it, despite not all American RDBMS implementations of the time time having NULL. Just think what life would have been like if it had followed what RTI had implemented under the direction of Stonebraker and Wong; we would have had no NULLs, just the sort of insane default value system so loudly advocated by Date. RTI's INGRES/QUEL system at that time used default values; and the default value for all numeric types was zero, so one couldn't know whether a zero meant "zero" or "value not known". This prompted Codd to write "I consider the number zero to be far too valuable in its normal role in all kinds of business activities", which amused me greatly when first I read it.Fri, 28 Dec 2012 12:35:19 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]SanDroid (12/27/2012)[/b][hr][quote][b]Hugo Kornelis (12/21/2012)[/b][hr][quote/]My point is also [b]not[/b] that I want the current implementation of UNIQUE removed from the product.My point is that [b][i]IF[/i][/b] 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]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 [b][u]secondary[/u][/b] 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. :cool:[/quote]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.Thu, 27 Dec 2012 12:41:14 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]Hugo Kornelis (12/21/2012)[/b][hr][quote/]My point is also [b]not[/b] that I want the current implementation of UNIQUE removed from the product.My point is that [b][i]IF[/i][/b] 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]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 [b][u]secondary[/u][/b] 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. :cool:Thu, 27 Dec 2012 12:16:56 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxLots 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.Thu, 27 Dec 2012 09:00:55 GMTSanDroidhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxAs 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.Mon, 24 Dec 2012 17:28:50 GMTDerek Dongrayhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]Terry300577 (12/20/2012)[/b][hr][quote][b]TriggerMan (12/20/2012)[/b][hr][i][/i]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):[i]"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."[/i]...and the original question:[i]"Is it possible to create a table with unique constraint that allows multiple NULL values from SQL Server 2008 onwards? "[/i]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. :-)[/quote]+1[/quote]I found my Guru! :-PFri, 21 Dec 2012 07:12:01 GMTTriggerManhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx+1Fri, 21 Dec 2012 07:07:13 GMTTriggerManhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]L' Eomot Inversé (12/20/2012)[/b][hr][quote][b]dawryn (12/20/2012)[/b][hr]BTW found this [url=https://connect.microsoft.com/SQLServer/feedback/details/299229/change-unique-constraint-to-allow-multiple-null-values]request for unique constraint ANSI compliance regarding multiple null values[/url][/quote]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 [url=https://connect.microsoft.com/SQLServer/feedback/details/658638/dont-allow-nullable-columns-as-part-of-unique-constraints]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.[/quote]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 [b]not[/b] 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 [b]not[/b] that I want the current implementation of UNIQUE removed from the product.My point is that [b][i]IF[/i][/b] 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.Fri, 21 Dec 2012 03:04:18 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]Narud (12/20/2012)[/b][hr]Good question, poor title.[/quote]The question is explicitly about constraints: [quote]Is it possible to create a table with unique constraint ....[/quote]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.[quote]=> 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 :-P[/quote]On those pointes I definitely agree!Thu, 20 Dec 2012 17:12:00 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]George M Parker (12/20/2012)[/b][hr]Totally disagree with the answer to the question. "Also, unlike PRIMARY KEY constraints, UNIQUE constraints allow for the value NULL. [b]However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column. [/b]"[/quote]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 [code]if exists (select * from sys.objects where type = 'U' and name = 'k') drop table kcreate 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 adrop table k[/code]Thu, 20 Dec 2012 17:02:01 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxGood 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 :-PThu, 20 Dec 2012 11:05:09 GMTNarudhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxThe 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. [b]However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column.[/b]If the author had applied the unique constraint mentioned in the question, then the answer would be reversed.[url]http://msdn.microsoft.com/en-us/library/ms191166%28v=sql.105%29.aspx[/url]Thu, 20 Dec 2012 10:56:33 GMTdinolghttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxTotally 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. [b]However, as with any value participating in a UNIQUE constraint, only one null value is allowed per column. [/b]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.Thu, 20 Dec 2012 10:50:25 GMTGeorge M Parkerhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b](Bob Brown) (12/20/2012)[/b][hr]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.;-)[url]http://www.sqlmag.com/article/sql-server/unique-constraint-with-multiple-nulls[/url][/quote]Thanks for bringing that reference to the discussion. This is useful information.Thu, 20 Dec 2012 09:36:08 GMTSQLRNNRhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]L' Eomot Inversé (12/20/2012)[/b][hr][quote][b]dawryn (12/20/2012)[/b][hr]BTW found this [url=https://connect.microsoft.com/SQLServer/feedback/details/299229/change-unique-constraint-to-allow-multiple-null-values]request for unique constraint ANSI compliance regarding multiple null values[/url][/quote]I greatly wish that a large number of people would pile in and downvote that awful connect item.[/quote]You're welcome to your opinion.[quote]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.[/quote]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.[quote]As Erland Sommarskog pointed out 5 years ago, the current MS definition of a UNIQUE constraint is extremely useful in many situations.[/quote]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.[quote]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 ....[/quote]Opinion is valuable; insults are not. Rephrasing your last two sentences to remove the insults leaves us with this:[quote]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.[/quote]EDIT: Fixed bad IFCode.Thu, 20 Dec 2012 08:56:54 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]dawryn (12/20/2012)[/b][hr]BTW found this [url=https://connect.microsoft.com/SQLServer/feedback/details/299229/change-unique-constraint-to-allow-multiple-null-values]request for unique constraint ANSI compliance regarding multiple null values[/url][/quote]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 [url=https://connect.microsoft.com/SQLServer/feedback/details/658638/dont-allow-nullable-columns-as-part-of-unique-constraints]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.Thu, 20 Dec 2012 08:43:22 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]Yggaz (12/19/2012)[/b][hr]The problem lies in the words "unique constraint". It has at least TWO meanings: "something that does not allow duplicates" and "SQL Server object implemented via unique index".[/quote]This is the inherent problem: in English (and particularly in IT), the same terminology is often used when describing a concept and a specific implementation of the concept. We had a similar issue on a QotD a while back regarding foreign keys.I usually try to clarify by referring to the concept in all lower case, while referring to the named implementation in proper case. So in this case, while it's possible to create a unique constraint [concept] that supports multiple NULLs using a Unique Index [specific implementation] in SQL Server, it's not possible to do so with a Unique Constraint [specific implementation].But even so, those I'm communicating with have to know my syntax, because there's not a universal elegant way to make the distinction in this language.Thu, 20 Dec 2012 08:39:34 GMTsknoxhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxPretty poor QotD today. The explanation is complete rubbish - nothing at all to do with unique constraints.The answer is nevertheless correct, as Jason already pointed out so I don't need to roll out the code snippet I keep handy to prove that the statement about NULL and unique indexes in BoL is sloppily worded and misleading.I got it wrong because I made two assumptions. My first assumption was that the question meant what it said (unique constraint, not unique index). Clearly, given the explanation, that was an incorrect assumption - which demonstrates that the wording of the question was sloppy, to say the least. The second assumption was that the author of the question would believe the nonsense statement in BoL that any column in a unique constraint can have only one null, so picking that answer would work; dilly me. I don't know whether that assumption was right or wrong, because the author apparently believed that a unique filtered index was a table constraint, but the assumption cost me a point.Thu, 20 Dec 2012 08:16:54 GMTL' Eomot Inverséhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxGood question about a frequently unknown topic. I guessed wrong because I couldn't decided the slant the author was using. I figured if I said it was possible because of filtered indexes I would be wrong because the question clearly states constraint...but then maybe it was just an attempt at showing how a unique constraint only allows a single null. In the end I chose the wrong path. The horse has been beat to death, the wording was pretty suspect and misleading but the topic is a good one. I look forward to Gopi's next question. Keep them coming.Thu, 20 Dec 2012 08:12:47 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]TriggerMan (12/20/2012)[/b][hr][i][/i]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):[i]"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."[/i]...and the original question:[i]"Is it possible to create a table with unique constraint that allows multiple NULL values from SQL Server 2008 onwards? "[/i]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. :-)[/quote]+1Thu, 20 Dec 2012 07:37:50 GMTTerry300577http://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxI 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.;-)[url]http://www.sqlmag.com/article/sql-server/unique-constraint-with-multiple-nulls[/url]Thu, 20 Dec 2012 07:33:29 GMT(Bob Brown) http://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[i][/i]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):[i]"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."[/i]...and the original question:[i]"Is it possible to create a table with unique constraint that allows multiple NULL values from SQL Server 2008 onwards? "[/i]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. :-)Thu, 20 Dec 2012 07:31:15 GMTTriggerManhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxWhile I'm not thrilled with the wording in the question, I took it to mean that the constraint held multiple null values which isn't a confusion anyone else has pointed out yet, I do like what the question was getting at. This is a good way to to deal with the problem.Thu, 20 Dec 2012 06:55:39 GMTcfradenburghttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxI too looked at this as specifically dealing with the unique constraint syntax on a single column instead of unique indexes. Based on the sample code, the author had unique indexes in mind and should have labeled the question as so. Good discussion points here though!Thu, 20 Dec 2012 06:51:44 GMTKWymorehttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxThanks for the question!Lucky for me I answered before having my coffee. After less than 5 seconds of thought I chose the correct answer because I have used filtered indexes and know they have been available from 2008 and up.Coffee and more thought would have caused me to notice the unique constraint wording and totally messed up my ability to choose the right answer. :hehe:Enjoy!Thu, 20 Dec 2012 06:43:11 GMTDave62http://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxAs with the previous posts, I too got it wrong & disappointed with the wording of the question. I understood a UNIQUE CONSTRAINT to be a type of filter on a table to ensure data integrity, nothing to do with an index.In MSDN definition of a "UNIQUE CONSTRAINT" from: http://msdn.microsoft.com/en-us/library/ms191166%28v=sql.105%29.aspxMSDN states:"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 concept is a good and raises a good debate. Maybe SQL Server will meet ANSI standards SQL:92, SQL:1999, and SQL:2003 for a UNIQUE CONSTRAINT in the next version.Thu, 20 Dec 2012 06:35:50 GMTMike Hayshttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]Gopi S (12/20/2012)[/b][hr]Here is the proof that i followed placing reference link without pretending as if mine.http://www.sqlservercentral.com/questions/T-SQL/71798/[/quote]I guess posted the wrong link :-)Thu, 20 Dec 2012 05:58:21 GMTrhythm.varshneyhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]dineshbabus (12/19/2012)[/b][hr]Today's question is completely misleading. Keeping unique constraint in mind which allows only one NULL value i answered worngly.[/quote]+1Thu, 20 Dec 2012 05:25:14 GMTDugyChttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxHere is the proof that i followed placing reference link without pretending as if mine.http://www.sqlservercentral.com/questions/T-SQL/71798/Thu, 20 Dec 2012 02:25:44 GMTGopi Shttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxMe really confused when I saw the answer, the question should be "Unique Index" instead of "Unique Constraint".Thu, 20 Dec 2012 02:24:09 GMTudayroy15http://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxThu, 20 Dec 2012 02:19:34 GMTGopi Shttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]SQLRNNR (12/20/2012)[/b][hr]I don't disagree with the correct answer. I disagree with the explanation. I also do disagree with many of the complaints thus far about the question. You can most certainly have multiple null values in a unique constraint. [/quote]ANSI NULL is not equal to any other value, even to another NULL. This is kind of broken in SQL Server - NULL always equals NULL when the unique constraint is considered. So the whole idea of the null-friendly unique constraint is to make the server behaviour more standard. We cannot have duplicates, but multiple NULLs are not duplicates as they are NOT equal to each other. So the idea should be "any number of null values is allowed, but not-null duplicates are prohibited".And that is why the question is misleading. Yes we can prohibit any duplicates and allow any number of NULLs - but NOT with the constraint. And we could do that BEFORE 2008 as well - now we just got one more option.Your "solution" allows multiple nulls (not any number of nulls, just more than one-two-three-whatever), [b]but it allows duplicates on every column as well[/b] - as long as the whole set of values is not duplicated. Well if that is possible why bother to create unique constraint just on that column at all? Let's include the key, and the constraint will alllow [b]any number[/b] of NULLs to be inserted in the column!Thu, 20 Dec 2012 02:13:26 GMTYggazhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]Hugo Kornelis (12/20/2012)[/b][hr]The Microsoft implementation of the UNIQUE constraint is non-standard, and therefor dangerous - people with experience in other DMBS'es who switch to SQL Server can easily run into problems over it.[/quote]As someone brought up on Oracle, I wasted a day of my life learning that lesson :(Thu, 20 Dec 2012 02:10:31 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspx[quote][b]SQLRNNR (12/20/2012)[/b][hr]...This demonstrates that multiple null values is possible and that multiple null values is even possible within each of the columns so long as all of the columns together remain unique.[/quote]+1I assumed that was what the questioner meant, and hence got it right for the wrong reasons.Thu, 20 Dec 2012 02:09:23 GMTToreadorhttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxWhile I agree that the wording of the question is debatable (I chose my reply, and then was hoping I had second-guess the author's intention right), I also want to add that I am very happy to see a question on a useful feature such as filtered indexes. Not everyone knows about this feature and the ability it poses to enforce uniqueness on a subset of the data only.I am also happy to see that someone already posted a link to the connect item on making the UNIQUE constraint ANSI-compliant. For those who didn't follow that link: the ANSI standard says that constraints should not apply to NULL values. So a UNIQUE constraint should, by default, allow as many NULL values as you want (because the NULL rows should not be checked).The Microsoft implementation of the UNIQUE constraint is non-standard, and therefor dangerous - people with experience in other DMBS'es who switch to SQL Server can easily run into problems over it. And the reverse is true as well - a SQL Server expert who switches to another technology can also introduce errors if he thinks that the UNIQUE constraint will work the way he's used to.Thu, 20 Dec 2012 01:42:33 GMTHugo Kornelishttp://www.sqlservercentral.com/Forums/Topic1398745-2867-1.aspxThis is what Books Online says:[quote]Creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns. There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. Data validation occurs in the same manner and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, you should create a UNIQUE or PRIMARY KEY constraint on the column when data integrity is the objective. By doing this the objective of the index will be clear.[/quote]Thu, 20 Dec 2012 01:26:18 GMTSean Pearce