Posted by Erland Sommarskog on 11/29/07 08:17

Don Li (tatata9999@gmail.com) writes:
>> OK, there are some spetacular differences between SQL collations +
>> varchar and Windows collations or anything with nvarchar with that type
>> of query.
>
> Don't really follow you here, could you elaborate a bit further on the
> collations topic?

Consider these tables:

CREATE TABLE sqlvarchar(
a int NOT NULL IDENTITY,
lotsoftext varchar(4000) COLLATE SQL_Latin_General1_CP1_CI_AS)

CREATE TABLE sqlunicode(
a int NOT NULL IDENTITY,
lotsoftext nvarchar(4000) COLLATE SQL_Latin_General1_CP1_CI_AS)

CREATE TABLE windowsvarchar(
a int NOT NULL IDENTITY,
lotsoftext varchar(4000) COLLATE Latin_General1_CI_AS)

CREATE TABLE windowsunicode(
a int NOT NULL IDENTITY,
lotsoftext nvarchar(4000) COLLATE Latin_General1_CI_AS)

Fill the tables with many rows. Then run queries like:

SELECT * FROM tbl WHERE lotsoftext LIKE '%sometext%'

You will find that the queries against sqlvarchar runs about seven times
faster than the other queries. At least that is my experience.

But as you already appears to be using SQL collation and varchar, that
observation does not help you much.


--
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

[Back to original message]