David Portas (10/13/2010)
I doubt that a password would be a key. Most systems that I know of don't require passwords to be unique.
You're right, it was a bad example of the point regarding uniqueness.
In SQL Server a unique index on non-nullable columns is just a key by another name. Unique indexes and uniqueness constraints are simply two different ways to create keys. I would not recommend using a unique index instead of a uniqueness constraint. Constraints, not indexes, are the conventional, SQL standard and most obvious way to implement keys.
Same difference. From: http://msdn.microsoft.com/en-us/library/ms177420.aspx
The Database Engine automatically creates a UNIQUE index to enforce the uniqueness requirement of the UNIQUE constraint. Therefore, if an attempt to insert a duplicate row is made, the Database Engine returns an error message that states the UNIQUE constraint has been violated and does not add the row to the table. Unless a clustered index is explicitly specified, a unique, nonclustered index is created by default to enforce the UNIQUE constraint.
What do you mean by a "non-keyed" index?
I meant non primary keyed and typed too fast and didn't re-read what I wrote to translate internal mental lingo into what would be definitive instead of vague. Yes, Unique columns/indexes/constraints can be used as 'alternate keys'. They're just rarely used as them, because usually the need for the constraint means that means the key can be changed, thus possibly destroying the key associations.
Thus, while a unique can be an alternate key, an alternate key is always unique. The difference is not inconsequential, thus I separate the concept. The fact that some data is usually mobile in a 'natural key', say, like the company's name, is why I like surrogate keys, like identity fields. This means that if a user edits the company's name (which is unique, at least by state), we still haven't lost our unique locator that everything else hooks to it with.
Of course, if you have the business' FedTaxID, or a SSN, that never changes, this is a proper natural key. Stability is incredibly important to a key, if not a primary factor in deciding to use it.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA