[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 390
  • Last Modified:

Collation Setting on New Server

I'm going to be moving the database on my existing SQL 2005 server, to a new server running the same version of SQL.

The old server was built before my time, and from what I'm reading, I need to match the collation settings on the new server to the old server for the databases to work properly after I move them over.

On the current server, if I do a properties on the server instance at the top of the management studio, and choose properties it says the collation settings are the following:

SQL_Latin1_General_CP1_CL_AS

During the installation of the new server, I'm stuck on the collation settings part. By Default SQL collations is selected (the bottom radio button that refers to being compatible with earlier versions of SQL). I'm guessing that I need to change it to the top radio button (Collation designator and sort order) and choose Latin1_General, but at unsure of what sub-options to check:

Binary, Case - sensitive, Accent - sensitive, Binary - code point, Kana - sensitive, Width - sensitive

I'm sure that's what the CP1, CL, and AS represent, but I don't know what to pick. Thanks!

[EDIT] After reading a little more, it also appears that leaving the setting at the default (bottom radio button) also gives me Latin General, so I'm really not sure what was selected on the initial install of the original server.
0
jschweg
Asked:
jschweg
  • 2
1 Solution
 
DFW_EdCommented:
you can look up what the current code means using:

SELECT * FROM ::fn_helpcollations()

Though I'm not sure about the collation posted as I didn't see a "CL"
0
 
jschwegAuthor Commented:
I think that might actually be a CI
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> SQL_Latin1_General_CP1_CL_AS

Actually it should read as SQL_Latin1_General_CP1_CI_AS which is the default collation set by SQL Server..
CI - Case In sensitiveness so that characters 'A' and 'a' are considered the same
AS - Accent Sensitiveness so that UK and US english accent are not same.

Leave the collation to default which would not create any issues and change it only if there is any specific need of it.
0
 
jschwegAuthor Commented:
Yup, that did the trick.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now