December 2, 2010 at 6:53 am
I had the need for this and used a non-clustered unique filtered index. Simple, efficient, and solves the problem neatly while letting SQL Server enforce it.
As to why you'd ever want to, the reason I did it was because a field was optional (filled with "N/A") but if it was filled in it had to be with a unique value. So by indexing everything *except* N/A I could ensure the unique value of all "actual" entries.
Thank God 2008 finally implemented something Access has had since 2003. 🙂
December 2, 2010 at 6:56 am
roger.plowman (12/2/2010)
I had the need for this and used a non-clustered unique filtered index. Simple, efficient, and solves the problem neatly while letting SQL Server enforce it.As to why you'd ever want to, the reason I did it was because a field was optional (filled with "N/A") but if it was filled in it had to be with a unique value. So by indexing everything *except* N/A I could ensure the unique value of all "actual" entries.
Thank God 2008 finally implemented something Access has had since 2003. 🙂
<points a few posts up>
"This feature is present in MS-ACCESS starting from version 1.0 (year 1990)."
Dont know if thats true or not. But i wouldnt be supprised.
/T
December 2, 2010 at 7:03 am
tommyh (12/2/2010)
roger.plowman (12/2/2010)
I had the need for this and used a non-clustered unique filtered index. Simple, efficient, and solves the problem neatly while letting SQL Server enforce it.As to why you'd ever want to, the reason I did it was because a field was optional (filled with "N/A") but if it was filled in it had to be with a unique value. So by indexing everything *except* N/A I could ensure the unique value of all "actual" entries.
Thank God 2008 finally implemented something Access has had since 2003. 🙂
<points a few posts up>
"This feature is present in MS-ACCESS starting from version 1.0 (year 1990)."
Dont know if thats true or not. But i wouldnt be supprised.
/T
Dunno either. But since the feature was critical to the design of something I know used it in Access 2003 I was being conservative. 🙂 I believe Access 97 also had it.
Whatever the case it's about time! (laughing)
December 2, 2010 at 7:26 am
Good question - thought about it until it hurt:hehe:
Why would you want to use a where clause to enforce uniqueness? That one stumps me. Otherwise, I agree with the rest of the answers.
Steve Jimmo
Sr DBA
“If we ever forget that we are One Nation Under God, then we will be a Nation gone under." - Ronald Reagan
December 2, 2010 at 7:37 am
sjimmo (12/2/2010)
Why would you want to use a where clause to enforce uniqueness? That one stumps me.
It stumps you because you wouldn't want to do that. Pure and simple and that's why no one is getting this right hardly. You'd have to have something to guarantee that all of the inserts followed this pattern, and the closest thing to that is a trigger, thus it is one of the options. But once you give out INSERT permission, you lose control over INSERT statements.
December 2, 2010 at 7:39 am
jeff.mason (12/2/2010)
sjimmo (12/2/2010)
Why would you want to use a where clause to enforce uniqueness? That one stumps me.It stumps you because you wouldn't want to do that. Pure and simple and that's why no one is getting this right hardly. You'd have to have something to guarantee that all of the inserts followed this pattern, and the closest thing to that is a trigger, thus it is one of the options. But once you give out INSERT permission, you lose control over INSERT statements.
And then what about bcp, SSIS with constraints off, updates?
December 2, 2010 at 7:48 am
In SQL Server when you create a unique constraint on a nullable column, NULL would count as a value and you would only be able to use NULL once.
It came as a surprise to me. I thought: how could it be true? This is possibly a mistake in the QOTD, as this is not compatible with ANSI standards! I opened BOL and re-read articles about indexes. After that I decided to make a small investigation about how other DBMSs deal with NULLs in unique indexes, and here is the result.
ANSI compliant behavior: Oracle, MySQL, PostgreSQL, Firebird, Sybase IQ.
Behavior that is not ANSI compliant: MSSQL, Sybase ASE, IBM DB2.
Ok, MSSQL and Sybase ASE both have a common ancestor (Sybase SQL Server). But the behavior of DB2 looks odd to me.
(And yes, I have voted for Hugo's suggestion at the Connect page 🙂 )
December 2, 2010 at 8:00 am
jeff.mason (12/2/2010)
sjimmo (12/2/2010)
But once you give out INSERT permission, you lose control over INSERT statements.
It is not only that. Constraints should prevent invalid data updates no matter where they come from - ad hoc queries, procedures, SSIS Packages or anything you can imagine.
They also protect you from your own coding errors - not just from errors others may make. (Note: of course this requires that you test your code)
Best Regards,
Chris Büttner
December 2, 2010 at 8:03 am
Christian Buettner-167247 (12/2/2010)
jeff.mason (12/2/2010)
sjimmo (12/2/2010)
But once you give out INSERT permission, you lose control over INSERT statements.It is not only that. Constraints should prevent invalid data updates no matter where they come from - ad hoc queries, procedures, SSIS Packages or anything you can imagine.
They also protect you from your own coding errors - not just from errors others may make. (Note: of course this requires that you test your code)
You mean hit exec once in the dev cycle? :w00t:
:hehe::hehe:
December 2, 2010 at 8:52 am
Ninja's_RGR'us (12/2/2010)
97% wrong answers??? Maybe there's something not quite correct with the question.
A 'correct' answer is one that matches the exact set of correct choices. No partial credit, even if you get 4 out of 5!
December 2, 2010 at 8:55 am
johnf_amic (12/2/2010)
Ninja's_RGR'us (12/2/2010)
97% wrong answers??? Maybe there's something not quite correct with the question.A 'correct' answer is one that matches the exact set of correct choices. No partial credit, even if you get 4 out of 5!
I know, I got all of them right... except the ones I felt were wrong ;-).
December 2, 2010 at 9:45 am
I voted for Hugo's suggestion too. 🙂 It would be nice if they actually go with the ANSI standard on this one. I didn't think that the "Where Clause on Inserts" choice even sounded right so I missed this one too.
December 2, 2010 at 10:41 am
Thank you, gents, for wonderful discussion here about my question. I voted on Hugo's suggested as well. Coming from Oracle shop, this behavior was a surprise to me. So I thought I would sound it here.
I am sorry for confusing you with insert option, I only included it here because I found it on another blog as a solution to the problem, which I agree is not really a solution, but still a valid workaround, especially if it is put in a good InsertUpdate stored procedure.
I got my 1 point for this only, cause I submitted all the valid options 🙂
Marianna
December 2, 2010 at 1:51 pm
Simple facts:
This is an Administration question.
There is no way that any Database Administrator can limit the code being executed against a table to enforce a Unique constraint data requirement.
Since there is no reference material for this answer I do not beilieve it counts.
Since I get a point for every post to this discussion.
:cool:I am giong to get my point I lost back!;-)
December 2, 2010 at 1:52 pm
:cool:I am giong to get my point I lost back!;-)
Viewing 15 posts - 16 through 30 (of 43 total)
You must be logged in to reply to this topic. Login to reply