Posted by Don Li on 11/30/07 03:17

On Nov 29, 3:17 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
> Don Li (tatata9...@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, esq...@sommarskog.se
>
> Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
> Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Erland, I'm with you, thank you. Now, let's refer to a previous but
related thread,
http://groups.google.com/group/comp.databases.ms-sqlserver/browse_thread/thread/07bf13e23d6bf643/2b7c382a0f8dbaa9#2b7c382a0f8dbaa9

There you mentioned about cache, I searched BOL for more on the
subject to almost no avail, googling gets something like "BPool
(buffer pool) and MemToLeave", too theorical not practical. Any
pointer for this?

Don

[Back to original message]