SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS
in [Server Configuration]
|
Prev: Database-Level Roles documentation in BOL
Next: What does Profiler/Perf Monitor tick marks represent?
From: JimLad on 11 Aug 2010 12:34 Hi, We have a large number of SQL 2000 databases having collation SQL_Latin1_General_CP1_CI_AS. We are now upgrading to SQL2008R2. I know that the default collation for 2008R2 British English is Latin1_General_CI_AS. One of our clients has indicated a preference for installing their new server with a default collation of Latin1_General_CI_AS. This will break our databases as it stands as our temp tables do not use the COLLATE database_default clause. Options: 1) Stick with SQL_Latin1_General_CP1_CI_AS for our databases, but update all temp tables to use the COLLATE database_default clause. 2) Update all our databases to Latin1_General_CI_AS. Option 1 seems a lot easier and my investigations today lead be to believe that Option 2 (changing collation) is very costly and time consuming. Can anyone suggest reasons why we should change collations, rather than making our databases server collation independent? Is support for SQL_Latin1_General_CP1_CI_AS likely to be dropped at any stage? Is Latin1_General_CI_AS definitely better than SQL_Latin1_General_CP1_CI_AS ? Cheers, James
From: Sylvain Lafontaine on 11 Aug 2010 13:53 Even if you change/update your collation to Latin1_General_CI_AS, you should still add the COLLATE database_default clause to all of your temp tables. This is because not everyone use Latin1_General_CI_AS or SQL_Latin1_General_CP1_CI_AS as their default database collation. -- Can anyone suggest reasons why we should change collations, rather than making our databases server collation independent? This question contains its own answer. -- Is support for SQL_Latin1_General_CP1_CI_AS likely to be dropped at any stage? Sure to be dropped in some time in the future but not before many, many years. -- Is Latin1_General_CI_AS definitely better than SQL_Latin1_General_CP1_CI_AS? Not necessarily. Latin1_General_CI_AS as a better/wider support for unicode characters but if you stick with english only, SQL_Latin1_General_CP1_CI_AS is a little bit faster. -- Sylvain Lafontaine, ing. MVP - Access Blog/web site: http://coding-paparazzi.sylvainlafontaine.com Independent consultant and remote programming for Access and SQL-Server (French) "JimLad" <jamesdbirch(a)yahoo.co.uk> wrote in message news:6c6b6692-02af-49f3-8bcf-c1532e346a03(a)f6g2000yqa.googlegroups.com... > Hi, > > We have a large number of SQL 2000 databases having collation > SQL_Latin1_General_CP1_CI_AS. We are now upgrading to SQL2008R2. > > I know that the default collation for 2008R2 British English is > Latin1_General_CI_AS. One of our clients has indicated a preference > for installing their new server with a default collation of > Latin1_General_CI_AS. This will break our databases as it stands as > our temp tables do not use the COLLATE database_default clause. > > Options: > 1) Stick with SQL_Latin1_General_CP1_CI_AS for our databases, but > update all temp tables to use the COLLATE database_default clause. > 2) Update all our databases to Latin1_General_CI_AS. > > Option 1 seems a lot easier and my investigations today lead be to > believe that Option 2 (changing collation) is very costly and time > consuming. > > Can anyone suggest reasons why we should change collations, rather > than making our databases server collation independent? Is support for > SQL_Latin1_General_CP1_CI_AS likely to be dropped at any stage? Is > Latin1_General_CI_AS definitely better than > SQL_Latin1_General_CP1_CI_AS ? > > Cheers, > > James
From: Erland Sommarskog on 11 Aug 2010 17:06
JimLad (jamesdbirch(a)yahoo.co.uk) writes: > We have a large number of SQL 2000 databases having collation > SQL_Latin1_General_CP1_CI_AS. We are now upgrading to SQL2008R2. > > I know that the default collation for 2008R2 British English is > Latin1_General_CI_AS. One of our clients has indicated a preference > for installing their new server with a default collation of > Latin1_General_CI_AS. This will break our databases as it stands as > our temp tables do not use the COLLATE database_default clause. > > Options: > 1) Stick with SQL_Latin1_General_CP1_CI_AS for our databases, but > update all temp tables to use the COLLATE database_default clause. > 2) Update all our databases to Latin1_General_CI_AS. > > Option 1 seems a lot easier and my investigations today lead be to > believe that Option 2 (changing collation) is very costly and time > consuming. Yes, changing the collation across the board does take some effort. But when it's done, it's done. Changing all temp tables is also an effort, and then a new developer fails to add the COLLATE clause. So it will be a recurring problem. Now, I don't know the relation between you and the client. Do all clients have their own copy of the same schema, or where does your code come in? > Can anyone suggest reasons why we should change collations, rather > than making our databases server collation independent? Is support for > SQL_Latin1_General_CP1_CI_AS likely to be dropped at any stage? Is > Latin1_General_CI_AS definitely better than > SQL_Latin1_General_CP1_CI_AS ? There is a quite a performance gotcha with SQL collations. Say that you have: SELECT ... FROM tbl WHERE indexedvarchar = @nvarcharvalue What happens where is that the varchar column is implicitly converted to nvarchar. With a Windows collation, the index is still alive. It is used less effciently, but the overhead is only 100-200%. With an SQL collation, the index is dead. This is because for an SQL collation, the rules for varchar and nvarchar are different. This may seem like a hypothetical case to you, but everyonce in a while I help people who have exactly this problem and have no clue what is going on. For this reason, it is my strong recommendation to use Windows collations. -- Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx |