by Michael S. Kaplan, published on 2006/12/07 07:53 -05:00, original URI: http://blogs.msdn.com/b/michkap/archive/2006/12/07/1232220.aspx
At the beginning of the week I posted Part 0 of this series, so I figured I should start the series at some point. :-)
The first and most obvious thing to avoid is any of the SQL Server 2005 *_BIN2 collations. These "new in SQL Server 2005" collations that I have mentioned previously have really only one benefit -- if one is interfacing their database application with some other (non-SQLS) database that stores its data as UTF-32 or UTF-8 and you want their binary collation's and ours to match. Which is clearly not the case here.
When interfacing with NTFS or really anything else on Windows, the *_BIN2 collations just lead to a slight performance hit and a difference in behavior.
Okay, so we know that the *_BIN collations are a better choice than *_BIN2. What else do we know?
Well, we know that Unicode columns are needed, since that is what the file system supports. And as a corollary, we know that SQL compatibility collations (which suck in general), suck in particular in this case as they take us even further from where we'd like to be....
Now if you use Unicode columns then (as I pointed out here), all of the *_BIN collations are identical, so you can pick whichever one you want.
Above all, do not be tempted to go astray from the *_BIN path due to the temptation of the various *_CI collations. As I have said before, collation != case. Specifically here, the file system' notion of "convert to uppercase and binary compare" has nothing to do with SQL Server's notion of "ignore all case differences". I almost wish they called it something different, to tell you the truth.
At this point, we are as close as the default available collation choices can get us. And we actually are fairly close at this point.
I'll start getting negative about all this in the next post in the series.:-)
This post brought to you by ბ (U+10d1, a.k.a. GEORGIAN LETTER BAN)