|
|
Posted by Erland Sommarskog on 05/28/07 21:44
D. (d@d.com) writes:
> How do I set binary collation on a single column?
With the COLLATE clause:
CREATE TABLE mytable (
col char(10) COLLATE Latin1_General_BIN2 NOT NULL,
...
> Do you think that this will improve performance on lookups?
Yes, since comparison is a straight byte-comparison you gain some cycles,
particularly if your default collation is a Windows collation. It's
diffiuclt to say exactly how much you will gain, because there is a lot
of if depends. For a simple lookup, it's may be only 5-10%. For an
operation as "col LIKE '%str%' it may be drastic as a factor of seven.
If your default collation is an SQL collation (one there the name starts
with SQL), the gain is likely to be so small, that it's not worth the
pain. Note that this only applies if you use char/varchar. For
nchar/nvarchar there is no difference between SQL and Windows collations.
> Do you think that having a single column with a different collation will
> not decrease performance?
It will not, but there will be more hassle with programming. And it
would not be the only column with that collation, if there are other
tables with foreign keys to this table.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|