Collation Codes
-
I am replacing one of our exisiting SQL Servers with a new Server running SQL Server 2005.
The collation code of the server is currently SQL_Latin1_General_CP1_CI_AS.
The rest of our servers all have the following collation code: Latin1_general_CI_AS.
What would be the effect if any of setting the collation code of the "new" server to match the others? (Latin1_General_CI_AS)
Any help would be appreciated
Carl
-
When you use "restore db" to put your db on SQL2005, then it will still have and use it's own collation. That could already be done in SQL2000.
If you don't use special collation-specific features for your data and applications, than scripting the db, removing all collation-info, creating it at the new server with that script, and the using SSIS/DTS to migrate the data maybe would be _the_ way to go.
If you use alter database to alter the db-collations, none of the existing objects will be affected ! Only the new objects, that have no collation specified in the ddl-statement, will have the new collation.
Altough technicaly spoken , mixing collations is allowed, at serverlevel, db-level, table-level, column-level, I'd only advise it for specific _needed_ features. Keep in mind that if you want to compare or concatenate columns of different collation, you must convert them to a common collation.
e.g.
select ....
from T_SQLServerCentralUsers C
inner join T_SQLServerCentralUsers sT
on C.uAlias COLLATE Latin1_General_CI_AI = sT.uAlias COLLATE Latin1_General_CI_AI
and C.uDtMember < sT.uDtMember
-- group by C.uAlias COLLATE Latin1_General_CI_AI
There also is a paht altering all collation-dependant columns ....
Alter table T_SQLServerCentralUsers
alter column uAlias varchar(128) COLLATE SQL_Latin1_General_CP1_CI_AS not null
print '--collation altered'
This alter-thing has implicit conversion !
Also check out http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=4&messageid=95954
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
From my further reading it mentions that the current Collation code:
SQL Collations
SQL collations are a compatibility option to match the attributes of common combinations of code page number and sort orders that have been specified in earlier versions of SQL Server. For example, for mapping a SQL Server 2000 SQL collation to what is specified in earlier versions of SQL Server, the SQL Server 2000 SQL collation SQL_Latin1_General_CP1_CI_AS matches the SQL Server version 7.0 default specification of:
- The ISO code page 1252.
- The dictionary order, case-insensitive character sort order.
- The General Unicode collation.
The SQL collations available in SQL Server 2000 do not match all combinations that can be specified in earlier versions of SQL Server. For example, no SQL Server 2000 SQL collation supports a case-sensitive sort order for non-Unicode data and case-insensitive sort order for Unicode data. The earlier SQL collations that cannot be exactly specified in SQL Server 2000 are called obsolescent SQL collations.
In SQL Server 2000, you should primarily use Windows collations. You should use SQL collations only to maintain compatibility with existing instances of earlier versions of SQL Server, or to maintain compatibility in applications developed using SQL collations in earlier versions of SQL Server.
Does this mean that the collation code allows compatability to SQL Server 7?
I believe the 2 collation codes are the same?
-
at collation level : yes.
We have a mix of 7.0 and sql2000 and are implementing sql2005, still using SQL_Latin1_General_CP1_CI_AS because of the # of servers and databases that have to communicate (DTS/SSIS) with eachother. So we want to avoid collation-issues for now.
With SQL2005 MS did not force us to migrate collations, so for the moment we do not migrate.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
So do you think it is best to set the collation code to SQL_Latin1_General_CP1_CI_AS on the new 2005 Server?
-
Since the old server is the onlyone having SQL_Latin1_General_CP1_CI_AS , and the others use Latin1_general_CI_AS , maybe this is _the_ occasion to migrate !
This way, when all servers will have Latin1_general_CI_AS and your collationproblem has disappeared !
You will have to check you application's needs, but I think it should go very smooth.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks for the advice.
So to migrate I script out the DB objetcs, tables, SPs' etc and create on the new server with Latin1_general_CI_AS collation and then DTS the data to the new server?
-
yep. Keep it simple.
If you have DRI (foreign keys), you may want to hold them until after the load !
Or you will have the burdon of a loadsequence exercise.
Test it, monitor and plan your downtime !
Then have your applications tested with the test-version!
Then if the results are ok plan production-implementation/migration
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks very much for all your advice.
I'll keep you informed with how things go.
Regards
Carl
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply