Recently I wrote article about SQL SERVER – 2008 – Install SQL Server 2008 – How to Upgrade to SQL Server 2008 – Installation Tutorial, I received couple of comment suggesting that I did not talk about SQL Server default collation setting or how to change default collation when installing SQL Server 2008.
While installing SQL Server 2008 on Server Configuration setting select “Collation” tab. It will bring up setting displayed in following image.
You can check the default collation of SQL Server as well can change it from the same. SQL Server offers the SQL_Latin1_General_CP1_CI_AS collation as the default collation for server installations.
Why the Default Collation Deserves a Second Look
Collation controls how SQL Server sorts and compares text. It decides whether 'abc' equals 'ABC', and how accented letters are treated. In the name SQL_Latin1_General_CP1_CI_AS, CI means case insensitive and AS means accent sensitive.
The value setup suggests depends on the Windows locale of the machine, so two servers installed in different countries can end up different without anyone choosing it. Check what you have with this query:
SELECT SERVERPROPERTY('Collation') AS ServerCollation;
Pick the collation carefully during setup. Changing the server collation later means rebuilding the system databases, which is a lot more work than one click on the installation screen.
A database can have its own collation, but temporary tables in tempdb use the server collation. When the two differ, joins between a temp table and a user table can fail with a collation conflict error. Adding COLLATE DATABASE_DEFAULT to the text columns in your temp table definitions avoids that.
You can check one database with SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation'); and a single column can have its own collation too, so when a comparison behaves strangely, look at the column definition as well. A case sensitive database collation also makes table and column names case sensitive in that database, which can break scripts that were written carelessly.
My advice: use the collation your application vendor asks for, and keep it the same on development, test and production servers. Many strange sorting bugs disappear when all three match.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
6 Comments. Leave new
“SQL Server offers the SQL_Latin1_General_CP1_CI_AS collation as the default collation for server installations”
This is not necessarily true
When I install on a UK machine using current disks or when I ask my hosting company to set up a new server the default is Latin1_General_CI_AS which although similar is not the same and will lead to conflicts so I have to do the REBUILDDATABASE thing to recreate the system databases.
Generally I don’t forget to change it in the setup but the hosting company use an image so they cannot create a default install with anything else (apparently – I think they’re just laze and won’t).
Dave
Good one Dave.
Hope someone covers the topic about the collation issues when installing SQL Server in different continents because these days due to high availability and load balancing everyone needs to understand what locale, Collation etc they should be in, for all the servers in respective countries or continents. Should they be the same then which one is the best etc etc… Cheers.
Hello sir,
While installing sql server 2008 on my windows 7 32-bit system, I get an error saying “The collation was not found error code 0x84b40000”.
When i select the collation tab, i see that the Database Engine field is blank and freezed. I cannot type anything there and as soon as i click on ‘Customize’ the installation ends there itself.
Please help.
Hello sir,
While installing sql server 2008 on my windows 7 32-bit system, I get an error saying “The collation was not found error code 0x84b40000″.
When i select the collation tab, i see that the Database Engine field is blank and freezed. I cannot type anything there and as soon as i click on ‘Customize’ the installation ends there itself.
Please help.
Thanks for explanation. It really helped. The visual explanation was really easy to understand.
Thanks for the explenation