Mike Dougherty-384281 (5/29/2013)
Given that some of us might consider this a problem to be solved...
Can you index a computed column that is: left( [varcharmaxfield], 900 )
I didn't test it, but I'm pretty sure you can (you probably have to cast it as varchar(900), otherwise the data type will still be varchar(max).). This will persist the computed column, so you take a storage hit - those maximum 900 bytes are added to the table's main pages, *and* they are (obviously) stored in the index.
Also, I'm interested to hear from the gurus how/why this solution is suboptimal. (of course, "it depends") In general, a large free-form text field isn't a sensible "index" candidate. If you are trying to find the record based on content inside this field we should use full-text search.
I would normally be hesitant to index large character columns. It's not like they will ever be searched anyway!
That being said, if there is a business requirement to store string values in excess of 8000 bytes, *and* there is a business requirement that disallows duplicates, the inability to just create a UNIQUE constraint becomes a nuisance. If you want to enforce this rule in a way that does not report false violation when the first 900 characters happen to be the same, you'd need a trigger. For performance reasons, I would not compare every inserted or updated value to all others in the table. I might first do a quick test for duplicates on a substring - for instance that 900-character substring, maybe even a shorter one. That quick test would definitely benefot from a (nonunique) index as described above.
However, I would definitely also explore the option of using a checksum instead of a substring as the first quick test. And if I were a gambler, I would put my money on the checksum giving a better overall performance.
QotD are great for testing what we keep in our heads, but honestly I'm not sure there's too much value in knowing some of this stuff.
True, but that goes for a lot of QotD's. Some are good tests that I could see used in a job interview, but lots are just bits of trivia. Nice to know, but hardly relevant to the job.