Database Collation | SQL with Manoj

Convert unicode Characters to be displayed in ASCII format – MSDN TSQL forum

March 18, 2011 Leave a comment

–> Question:

Is there a function (or any other way) in T SQL, by which I can somehow convert a unicode character (UTF – 8) and then display it in normal ASCII format?

Eg: I want to convert “Ha Nội” to “Hanoi” either programatically or in SQL.
Another Example: Réunion as “Reunion”

One other problem is sometimes some characters in unicode come up as “?” when cast using the above method:

Eg: Ḩaḑramawt ?a?ramawt

–> Answer:

This is related to database COLLATION settings.

This would not be the best way, but just in case an idea to sail you through….

SELECT 
	'Réunion', 
	CAST('Réunion' AS VARCHAR(100)) COLLATE SQL_Latin1_General_CP1253_CI_AI

… you can also play with COLLATE DATABASE_DEFAULT option.

For more info you can check my blog post on COLLATION, Collation Conflicts and Change a Database Collation.

Regarding your other problem, you want to match the foreign language characters to English, right? Which is not possible in most of the cases.

Just like in French, Réunion, contains ‘é’ which is similar to English ‘e’ but with l’accent aigue accent.

But this is not feasible for every character, and other languages too, like Chinese, Hindi. How can you map, and even if you map what symbol will it show? And thus every character cannot be matched against English and you’ll see invalid characters in your result set.

Ref link.

Categories: SQL Server Questions Tags: COLLATE DATABASE_DEFAULT, Collation Conflict, Database Collation, MSDN TSQL forum

SQL DBA – Change a Database Collation in SQL Server

March 25, 2010 3 comments

In previous post I discussed about collation differences b/w different databases/tables and how to query them. The solution was not permanent and every time one has to apply the default collation with the column names while matching them at where clause.

I thought to modify the collation of my database and tables on my new database and make it compatible to my old database. However this is not advisable for existing DBs, because collation setting within databases is done intentionally and for some purpose.

I issued the following sql statement to alter the database collation level:

alter database someDatabase collate SQL_Latin1_General_CP437_BIN

But it ended up on the following error:

Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'someDatabase' 
cannot be set to SQL_Latin1_General_CP437_BIN.

Then I came to know that the database has to be in single user mode, so that I can apply the changes. So I issued the following sql statements:

ALTER DATABASE someDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE someDatabase COLLATE SQL_Latin1_General_CP437_BIN
ALTER DATABASE someDatabase SET MULTI_USER

… and yes they get successfully executed. I checked the collation and it got changed to the new one.
But for the existing tables I had to manually change the collation of each & every tables by applying following sql statements:

alter table someTable alter column [someColumnX] varchar(9) COLLATE SQL_Latin1_General_CP437_BIN

Categories: DBA Stuff Tags: COLLATE, Collation, Database Collation, SQL_Latin1_General_CP437_BIN

SQL DBA – Collation Conflict in SQL Server

February 23, 2010 6 comments

Cannot resolve the collation conflict between “SQL_Latin1_General_CP437_BIN” and “SQL_Latin1_General_CP1_CI_AS” in the equal to operation.

Just few days back I came across this error when I tried to join two tables from 2 different databases. Didn’t get any clue for a few minutes so I googled up this error (Thanks google baba).

Collation is MS SQL Server is used for specifying the ordering of characters when you create or alter a table or create a domain. Collation settings, which include character set, sort order, and other locale-specific settings, are fundamental to the structure and function of Microsoft SQL Server databases. SQL Server uses them implicitly in SQL statements. To use them explicitly you need to override the default collation or the collation you specified when creating or altering the table or creating the domain. Collation can be applied for char, varchar, text, nchar, nvarchar, and ntext data types.

For example:

SELECT T1.EmployeeName, T2.DeptName
FROM ServerA.dbo.EmpTab T1
JOIN ServerB.dbo.DeptTab T2
ON T1.DeptCode = T2.DeptCode

There could be a possibility that both the servers use different Collations. If yes then you would get an error similar to then one I mentioned at the top of this topic. What you should do in this case?
1. You can alter the default Collation of either of the table columns/fields, but this could have a ripple effect for other tables currently in use with the altered table.
2. Use COLLATE DATABASE_DEFAULT keyword while matching the columns/fields, like:

SELECT T1.EmployeeName, T2.DeptName
FROM ServerA.dbo.EmpTab T1
JOIN ServerB.dbo.DeptTab T2
ON T1.DeptCode COLLATE DATABASE_DEFAULT
= T2.DeptCode COLLATE DATABASE_DEFAULT

More Info:
Collation Types: http://msdn.microsoft.com/en-us/library/ms144250%28SQL.90%29.aspx
Alter Table: http://msdn.microsoft.com/en-us/library/aa275462%28SQL.80%29.aspx
Alter Database: http://msdn.microsoft.com/en-us/library/aa275464%28SQL.80%29.aspx
Other: http://developer.mimer.com/documentation/html_92/Mimer_SQL_Engine_DocSet/Collations3.html