Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
chengy
Starting Member
3 Posts |
 Posted - 2011-10-21 : 10:21:26
|
| All our current SQL Servers have the following attributes :* The Operating System is Windows 2003 SP2 (either Enterprise or Standard Edition).* The Windows Regional Setting is 'English (United Kingdom)' and Location is 'United Kingdom'.* The Windows 'Language for non-Unicode programs' is 'English (United Kingdom)'.* All boxes are ticked for the Windows 'Code page conversion tables'.* SQL Server 2000 (either Enterprise or Standard Edition) - 8.00.2187 (SP4).* SQL Server Collation is SQL_Latin1_General_CP1_CI_AS (Version=539, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52).* All SQL Server Logins have a 'Default Language' of 'English' (not 'British English').During a SQL Server 2008 R2 install on a new Windows 2003 SP2 server (this is our preferred upgrade path), [Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (Intel X86) , Apr 2 2010 15:53:02 , Copyright (c) Microsoft Corporation , Developer Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)].in the Server Configuration screen, a collation have to be specified.The default Collation settings appear to be :Database Engine : Latin1_General_CI_ASAnalysis Services : Latin1_General_CI_ASAs we had intended to perform a side-by-side upgrade of our SQL 2000 servers (rather than an in-place upgrade), I thought we had better specify our existing SQL 2000 collation (SQL_Latin1_General_CP1_CI_AS) for the SQL 2008 R2 install.So I selected the 'Customize' button for the Database Engine Collation.I noticed that SQL collations were available, but that the option was titled 'SQL collation, for backwards compatibility'.Does this mean that SQL collations are a deprecated feature and will be discontinued in a future release ? However, I cannot see SQL collations documented as a deprecated feature.As I will be restoring many SQL 2000 database backups on the new SQL 2008 R2 servers, should I be selecting SQL_Latin1_General_CP1_CI_AS for the Database Engine collation in the SQL 2008 R2 install as this is the collation of the source databases and will minimise risk ?Or should I select the default Latin1_General_CI_AS for the Database Engine collation in the SQL 2008 R2 install ?If I choose Latin1_General_CI_AS, what will be the effect be and what could be the risks of restoring databases with SQL_Latin1_General_CP1_CI_AS collation ?How can I check that this different collation is ok ?Or is there an exact Windows Collation equivalent in SQL 2008 R2 for 'Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52' ?(The Collation description for SQL_Latin1_General_CP1_CI_AS is Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data).If so, what is the 'Collation designator' and which boxes should be ticked for Binary , Case-sensitive , Accent-sensitive , Binary-code point , Kana-sensitive & Width-sensitive ?What disadvantages, if any, are there to choosing a SQL collation instead of a Windows collation ?I notice that for Analysis Services that there are no SQL collation choices.If SQL_Latin1_General_CP1_CI_AS was chosen for the Database Engine collation, would it matter that Latin1_General_CI_AS is chosen for the Analysis Services collation in the SQL 2008 R2 install ?What effect does having different collations for the Database Engine and Analysis Services ?I read that if you perform an In-Place upgrade of SQL Server 2000 to SQL Server 2008 R2, that you do not specify collation and just let the collation be set automatically.In this case, what would the SQL 2008 R2 Database Engine collation be if the SQL 2000 collation was SQL_Latin1_General_CP1_CI_AS ?What would the SQL 2008 R2 Analysis Services collation be ?Many thanks,Stuart |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-10-21 : 12:22:50
|
| I have the same issue and would suggest using SQL_Latin1_General_CP1_CI_AS unless you want to use COLLATE everywhere, especially if you use any linked servers. |
 |
|
|
|
|
|
|
|
Collation query for SQL Server 2008 R2 Install