collation settings

Question

collation settings

Johnny B

SSCrazy

Points: 2965
More actions
June 13, 2006 at 3:31 pm

#173054

im building a new data staging server as our firm moves towards a warehouse strategy. From what I understand, the collation settings for SQL Server is recomended to remain the default (or that of the operating system).
My question: Are there considerations for setting collation based on the use of the server as data staging? there will be alot of cleaning and ETL processes going on. Can someone point me in a good direction for understanding how this setting will impact our goal of staging data?
SQL 2012 Standard VPS Windows 2012 Server Standard

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Yes...

My recommendation (for U.S. installations) is to select a collation that is Dictionary Order, Case Insensitive (CI) and Accent Sensitive (AS). This will normally be done during installtion of the server instance. You may want to select a "Code Page" (CP), as well.

The real key is that you should set the staging server to whatever your other servers are set to. You can see what the current setting is by opening Enterprise Manager, right clicking on the server instance, select [Properties], and take a peek at the [General] tab.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Oh, yeah... almost forgot... you can get a list of available collations using the following code...

SELECT *

FROM ::fn_helpcollations()

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Johnny B SSCrazy Points: 2965 More actions · Answer 3

and if our other developement/production servers have a collation setting inconsistant with some of our firms supposed 'international' data feeds? Is it better to change all the collations settings at once or ease into it?

I confese I know little about this. BOL tells how to change it (sounds like alot of work) but not why.

Has anyone HAD to change collation? What are the circumstances for really needing to chang the master db. And what are the default OS collations settings for windows 2003 in the US?

SQL 2012 Standard VPS Windows 2012 Server Standard