SQL SERVER – Storing Data in Hindi, Chinese, Gujarati and Tamil (or any other) Language in the Same Table

Long time ago, I have written a blog which talks about multi language support available with SQL Server.

SQL SERVER – 2005 – Multiple Language Support

I have been getting below questions on regular basics and it still baffles me:

  1. How do I pass Hindi parameters in SQL Server procedure as I want to insert a row with Hindi text?
  2. What is the collation I need to use if I have to store Tamil in SQL Server tables?
  3. I am inserting the data in Gujarati but while selecting I am getting “???”

Here are some basic rules which you may want to keep in mind. While storing Unicode data, the column must be of Unicode data type (nchar, nvarchar, ntext). Another rule is that the value must be prefixed with N while insertion.

If you are using a stored procedure then while passing the parameter to stored procedure make sure that N is used before the string. Also make sure the parameter declared within the stored procedure which are used for carrying those variable are also defined as Unicode.

Here is the sample script and the output which can make sure understand the things. I have used Google translate to do translation and I am not sure how accurate it is in Kannada, Tamil and Telugu.

use master
If db_id('SQLAuthority') is not null
drop database SQLAuthority
set nocount on
Create database SQLAuthority
use SQLAuthority
Create Table MultiLanguage_Will_Not_Work (i int, j varchar(10), k varchar(100))
-- Hindi
Insert into MultiLanguage_Will_Not_Work values (1, 'with N', N'मेरा नाम Pinal है')
-- Tamil
Insert into MultiLanguage_Will_Not_Work values (2, 'without N', 'என் பெயர் PINAL ஆகிறது')
Select * from MultiLanguage_Will_Not_Work
Create Table MultiLanguage (i int,j varchar(10), k nvarchar(100))
-- Gujarati
Insert into MultiLanguage values (1, 'with N', N'મારું નામ પિનલ છે')
-- Telugu
Insert into MultiLanguage values (2, 'without N', 'నా పేరు PINAL ఉంది')
-- Kannada
Insert into MultiLanguage values (3, 'with N', N'ನನ್ನ ಹೆಸರು ಪಿನಾಲ್ ಆಗಿದೆ')
Select * from MultiLanguage
use master
drop database SQLAuthority

Here is the output

unicode 01 SQL SERVER   Storing Data in Hindi, Chinese, Gujarati and Tamil (or any other) Language in the Same Table

Same would work for any language as well. It is important for us to know the basics and this blog post is one of many basics blogs that I plan to write. Do let me know which topic you would like me to revisit as part of this – back to basics series? More ideas you give me, more interesting the series can be.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Detecting Database Case Sensitive Property using fn_helpcollations()

In my recent Office Hours, I received a question on how to determine the case sensitivity of the database.

The quick answer to this is to identify the collation of the database and check the properties of the collation. I have previously written how one can identify database collation. Once you have figured out the collation of the database, you can put that in the WHERE condition of the following T-SQL and then check the case sensitivity from the description.

FROM fn_helpcollations()

helpcollations SQL SERVER   Detecting Database Case Sensitive Property using fn helpcollations()

The method shown above is the most recommended method and I suggest using the same.

When I was a young DBA, I did not have the patience to follow the above method. I used to do something very simple.

WHERE 'SQL' = 'sql'

If the above query returns me the result, it means that the database is case-insensitive. Please note that by no means do I suggest using this method; I really recommend using the method fn_helpcollations().

Another interesting suggestion was from Dave Dustin who is SQL Server MVP from New Zealand. He has provided the following script:

FROM sys.Databases
WHERE name='<databasename>'
AND (collation_name LIKE '%CS%' OR collation_name LIKE '%BIN%')

Insert your database name in the WHERE clause. If the query returns any result, it means the database is case-sensitive.

It’s interesting to see that one simple question can result to three interesting ways to know the answer. Do you know any other method to know the database case sensitivity? Please share it here and I will post it with due credit.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Change Collation of Database Column – T-SQL Script – Consolidating Collations – Extention Script

This document is created by Brian Cidern, he has written this excellent extension to SQL Expert who SQL SERVER – Change Collation of Database Column – T-SQL Script. His scripts are not only extremely helpful to achieve the task of consolidating collations in quick script. His script not only works perfectly but excellent piece of code and logic.

Hats off to you Brian! You can reach Brian at his email address (brians.sql.blog (at) gmail (dot) com) or leave comment here.

Download all scripts and explanation here

About Collation Consolidation

At some time in your DBA career, you may find yourself in a position when you sit back and realize that your database collations have somehow run amuck, or are faced with the ever annoying CANNOT RESOLVE COLLATION message when trying to join data of varying collation settings.  I’m sure there are as many reasons as to how you’ve come to this juncture as there are readers.  Maybe you have DBA’s and instances around the world and the local staff are using their own collations.  Or, maybe you’ve learned the hard way that ALTER DATABASE x SET COLLATION y doesn’t traverse.  Whatever it is, you’re probably here for the one underlying reason.  Manually consolidating collations would be a nightmare.  So, without loading to much fluff, let’s jump right in.

Here is the basic approach:

  1. Identify all the columns which have incorrect collation values.
  2. Identify all objects bound to above columns, including indexes, keys, checks and UDF’s,
  3. Script out all of the above object DDL.
  4. Drop all of the objects.
  5. Update all the columns with the correct collation
  6. Recreate all of the dropped objects using the DDL generated in step 3

This is done with a collection of non-destructive scripts which generate the DDL to perform all of the above.  This generated DDL MUST be saved to scripts for later use on the target databases.  And obviously, you’ll need one set of scripts per database.  If that requires clarification, just stop reading now.

In this article, I give to you the four scripts to do all this and will try to provide as much understanding as possible.  The first script is purely to create a stored procedure which will be repeatedly called.  This proc will need to exist in a database named “DBADB”.  If you want to use a different database, you’ll need to do a search and replace in the first script and the last script since the database name is hard-coded.  I tried to make these somewhat generic, but the only approach that came to mind would be to use OLE to invoke the VBScript engine to prompt you for the name of the database where this stored procedure is located.  Overkill in my opinion.  Ctrl-H is your friend here.

The script to perform step 3 above uses DMO via OLE, so if you’ve disabled the extended stored procedures; sp_OACreate, sp_OASetProperty, sp_OAMethod, etc, you won’t be able to continue until they’ve been enabled.

One final note.  If you’re not a member of the System Administrators role, please don’t try to execute these.  You’ll probably find your account revoked quickly.

Ok, time to roll up your sleeves.

Script 1 – The DDL Generator

This first script is to create a stored procedure.  This procedure must reside in a location that will not interfere with other databases, whether they’re development, test or production.  The SP uses DMO via OLE to capture a buffer dump of the object code.  The result is the same as if you right-clicked an object and selected “Script to new window as…”

The DMO hierarchy of a SQL instance is weird.  Object reference isn’t particularly intuitive.  But, for the most part, objects are contained within the associated table collection.  In DBA language, that would look like

Table 1:M Checks

Table 1:M Keys

Table 1:M Indexes

Database 1:M UserDefinedFunctions

Therefore, to locate a particular object in the DMO hierarchy, you would need to pass in all the required parameters, including the object type.  So, for checks, keys and indexes, you must also pass in the database name and the parent table name.  For functions, only the database name.


@in_dbName SysName,

@in_objType VarChar(100),

@in_objName VarChar(100),

@in_objSubName VarChar(100) = NULL


Now, the good news.  The subsequent scripts will do all of the above automatically, and this script will be called repeatedly.

Implementing this script.

As I mentioned, this script will be called repeatedly.  The script from whence it’s called will need to be modified to make reference to the correct database name.  These scripts have been designed to work with a DBA-use only database called “DBADB”.  Therefore, if you choose to keep this procedure located in a database of a different name, you’ll need to perform a search and replace for all the “DBADB” reference.  Otherwise, you will need to create a database named “DBADB”.

Execute the first script.  This will create the necessary stored procedure.

Script 2 – Drop Dependencies

The name for this script is somewhat deceiving.  It is a non-destructive script and only performs a set of SELECT statements.  Rather, this script generates a collection of DROP [object] statements.

The goal here is to identify all the objects that are bound to columns which have incorrect collation settings.  There is no clean way to pull them all back in a single statement since determining this is handled differently for the various types of objects.  So, they’re all concatenated together with UNION’s.  The orders in which these are executed are as follows:

  1. Table Functions
  2. Unique Constraints
  3. Check Constraints
  4. Indexes

You must also edit this script to use your desired collation.  Perform a search and replace of SQL_Latin1_General_CI_AS to your chosen collation.

It’s performed in this order in attempt to avoid dependency issues.

The result is a collection of DROP statements.  This must then be copy and pasted into a script and saved for later use.  Best to use a naming convention that reflects the name of the database, and that this will be the first script to execute when you’re ready to begin the patching, for example: myDatabase_post-script_1.sql, myDatabase_post-script_2.sql, etc.

You should take note, it is possible that some databases may not have any objects that are bound to columns that need to be updated, so it’s perfectly fine if this script produces no results.

Script 3 – Change Collations

Much like script 2, this script is also non-destructive and will produce a collection of DDL statements to update the column collations.

You must also edit this script to use your desired collation.  Perform a search and replace of SQL_Latin1_General_CI_AS to your chosen collation.

VERY IMPORTANT.  These scripts were originally intended to allow us to consolidate all our collations in a SQL 2000 to SQL 2008 migration.  Therefore, this script WILL produce two lines of code that you may or may not want.



ALTER DATABASE ‘ + DB_NAME() + ‘ COLLATE ‘ + @collation

where @collation is a variable defined at the beginning of the script.

You can alter the original script I’ve provided, or you can simply remove them from the output before saving.

The remaining results will be a collection of statements to alter the column collations:

ALTER TABLE [tbl] ALTER COLUMN [clm] column_spec() COLLATE [new_collation]

Once this has been executed, copy and paste the output into another script file for later use (myDatabase_post-script_2.sql)

Script 4 – Create Dependent Objects

This is where most of the work takes place.

You must also edit this script to use your desired collation.  Perform a search and replace of SQL_Latin1_General_CI_AS to your chosen collation.

Open the script, connect to the desired database and hit the [fire] button.  Again, like those previous, this script is non-destructive, and produces a series of DDL statements.

The first you’ll notice in the output is the “USE DBADB” statement.  It is very important to note here that if the stored procedure (sp_scriptObject) from the very first script is stored in another database, that this is reflected in the output.

The remainder of the output looks like:

EXEC DBADB.dbo.sp_scriptObject…

With all of the required parameters, based on the type and location of the object.  The other thing you may notice here is that the order in which these statements are in, is the exact reverse of the order from the 2nd script; again, with the intent to avoid dependency crashes.

This output now needs to be copy and pasted to a new query window – not to a file.  The output of the new window should be set to text (Query > Results in Text) or (Ctrl-T).  Execute the statements.  Be aware, that this could take a significant amount of time based on the number of objects and how complex they are.

But, in essence, you will be trapping a buffer dump for each object that needs to be dropped.


There may be a case where an object’s DDL is simply too large.  There are limitations in which the VBScript engine stores string data.  I can’t recall exactly what the issue was, nor do I care to look it up again. But basically, the buffer is limited to 8k.  If your object DDL is greater than 8k, an error will be thrown and the object DDL will NOT be trapped.

After this script has finished, you must first scan the output for errors.  If any are found, identify the object which was not scripted and manually script those and paste them into the output.

This output must then be saved to a final script for execution against the target database.

Final Step

In the end, the above work will have produced 3 scripts that now need to be run against the target database.  Now is a good time to clarify that these generated scripts are very destructive.  So, please take your time, test, test and test.  If you choose to do this on a production box, your career could be at stake.  There is never one-size-fits-all solution, so for each scenario, these scripts may required modifications.  These were produced to fix my needs, but I did try to keep them as generic as possible for re-use.

On your target database, execute each of the generated scripts, obviously in the order specified.

The net result will leave your database with each column, along with the database set at your desired collation setting.

The Quick and Dirty Approach

This section is for those who don’t really care to know the details and simply provides an exact step-by-step.

  1. Backup the target database.
  2. Copy the 4 scripts to an empty folder.
  3. Open Pre-Script_1_DROP_DEPENDENCIES.sql in a text editor and perform a search and replace of “Latin1_General_CI_AS” and change this to the desired collation.
  4. Open Pre-Script_2_CHANGE_COLLATIONS.sql in a text editor and perform a search and replace of “Latin1_General_CI_AS” and change this to the desired collation.
  5. Open Pre-Script_3_CREATE_DEPENDENCIES.sql in a text editor and perform a search and replace of “Latin1_General_CI_AS” and change this to the desired collation.
  6. Create a database named “DBADB”
  7. Execute Pre-Script_0_DBADB.sp_scriptObject.sql
  8. Change the working database to the one which requires collation changes.
  9. Execute Pre-Script_1_DROP_DEPENDENCIES.sql
  10. Save the output to a text file named “dbName_post-script_1.sql”
  11. Execute Pre-Script_2_CHANGE_COLLATIONS.sql
  12. Save the output to a text file named “dbName_post-script_2.sql”
  13. Execute Pre-Script_3_CREATE_DEPENDENCIES.sql
  14. Copy the output to a new query window and execute.
  15. Scan the secondary output for errors.  Identify any failed objects and manually script out the DDL.  Paste it into the location where the error occurred.
  16. Now, save this output to a text file named “dbName_post-script_3.sql”
  17. Copy dbName_post-script_1.sql, dbName_post-script_2.sql, and dbName_post-script_3.sql to the target machine.
  18. Connect to the desired target database and execute each of the above post-scripts in numeric order.

Good luck.  Please heed any warnings I may have mentioned.  If you have any questions, certainly send me an email and I will try to answer promptly.  But, also please be patient, I do have a full time job that takes priority.

Brian Cidern
brians.sql.blog (at) gmail (dot) com

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Change Collation of Database Column – T-SQL Script

Just a day before I wrote about SQL SERVER – Find Collation of Database and Table Column Using T-SQL and I have received some good comments and one particular question was about how to change collation of database. It is quite simple do so.

Let us see following example.

USE AdventureWorks
/* Create Test Table */
CREATE TABLE TestTable (FirstCol VARCHAR(10))
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
/* Change the database collation */
/* Check Database Column Collation */
SELECT name, collation_name
FROM sys.columns
FROM sys.objects
WHERE type = 'U'
AND name = 'TestTable')
/* Database Cleanup */

When ran above script will give two resultset. First resultset is before column’s collation is changed and it represents default collation of database. Second result set is after column’s collation is changed and it represents newly defined collation.

colcollation SQL SERVER   Change Collation of Database Column   T SQL Script

Let me know what are your ideas about collation and any problem if you have faced for the same. I am interested to share those with the SQL community.

Additionally, if you are looking for solution to SQL SERVER – Cannot resolve collation conflict for equal to operation visit here.

Reference : Pinal Dave (http://www.SQLAuthority.com)

SQL SERVER – Find Collation of Database and Table Column Using T-SQL

Today we will go over very quick tip about finding out collation of database and table column. Collations specify the rules for how strings of character data are sorted and compared, based on the norms of particular languages and locales

Today’s script are self explanatory so I will not explain it much.

/* Find Collation of SQL Server Database */
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation')
/* Find Collation of SQL Server Database Table Column */
USE AdventureWorks
SELECT name, collation_name
FROM sys.columns
FROM sys.objects
WHERE type = 'U'
AND name = 'Address')
name = 'City'

findcollation SQL SERVER   Find Collation of Database and Table Column Using T SQL

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2005 – Find Database Collation Using T-SQL and SSMS

This article is written based on feedback I have received on SQL SERVER – Cannot resolve collation conflict for equal to operation. Many reader asked me how to find collation of current database. There are two different ways to find out SQL Server database collation.

1) Using T-SQL (My Recommendation)

Run following Script in Query Editor
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;

collation2 SQL SERVER   2005   Find Database Collation Using T SQL and SSMS

2) Using SQL Server Management Studio

Refer the following two diagram to find out the SQL Collation.
Write Click on Database
collation SQL SERVER   2005   Find Database Collation Using T SQL and SSMS

Click on Properties
collation1 SQL SERVER   2005   Find Database Collation Using T SQL and SSMS

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2005 Collation Explanation and Translation – Part 2

Following function return all the available collation of SQL Server 2005. My previous article about the SQL SERVER – 2005 Collation Explanation and Translation.

FROM sys.fn_HelpCollations()

Result Set: (only few of 1011 records)
Name Description
Latin1_General_BIN Latin1-General, binary sort
Latin1_General_BIN2 Latin1-General, binary code point comparison sort
Latin1_General_CI_AI Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Latin1_General_CI_AI_WS Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Latin1_General_CI_AI_KS Latin1-General, case-insensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Latin1_General_CI_AI_KS_WS Latin1-General, case-insensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Latin1_General_CI_AS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Latin1_General_CI_AS_WS Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Latin1_General_CI_AS_KS Latin1-General, case-insensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Latin1_General_CI_AS_KS_WS Latin1-General, case-insensitive, accent-sensitive, kanatype-sensitive, width-sensitive
Latin1_General_CS_AI Latin1-General, case-sensitive, accent-insensitive, kanatype-insensitive, width-insensitive
Latin1_General_CS_AI_WS Latin1-General, case-sensitive, accent-insensitive, kanatype-insensitive, width-sensitive
Latin1_General_CS_AI_KS Latin1-General, case-sensitive, accent-insensitive, kanatype-sensitive, width-insensitive
Latin1_General_CS_AI_KS_WS Latin1-General, case-sensitive, accent-insensitive, kanatype-sensitive, width-sensitive
Latin1_General_CS_AS Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-insensitive
Latin1_General_CS_AS_WS Latin1-General, case-sensitive, accent-sensitive, kanatype-insensitive, width-sensitive
Latin1_General_CS_AS_KS Latin1-General, case-sensitive, accent-sensitive, kanatype-sensitive, width-insensitive
Latin1_General_CS_AS_KS_WS Latin1-General, case-sensitive, accent-sensitive, kanatype-sensitive, width-sensitive

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Cannot resolve collation conflict for equal to operation

Cannot resolve collation conflict for equal to operation.

In MS SQL SERVER, the collation can be set at the column level. When compared 2 different collation column in the query, this error comes up.

FROM ItemsTable
INNER JOIN AccountsTable
WHERE ItemsTable.Collation1Col = AccountsTable.Collation2Col

If columns ItemsTable.Collation1Col and AccountsTable.Collation2Col have different collation, it will generate the error “Cannot resolve collation conflict for equal to operation“.

To resolve the collation conflict add following keywords around “=” operator.

FROM ItemsTable
INNER JOIN AccountsTable
= AccountsTable.Collation2Col COLLATE DATABASE_DEFAULT

Watch this quick video for resolution:

Collation can affect following areas:

1) Where clauses

2) Join predicates

3) Functions

4) Databases (e.g. TempDB may be in a different collation database_default than the other databases some times)

Checkout following video how collation can change the order of the column:

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – Collate – Case Sensitive SQL Query Search

Case Sensitive SQL Query Search

If Column1 of Table1 has following values ‘CaseSearch, casesearch, CASESEARCH, CaSeSeArCh’, following statement will return you all the four records.

SELECT Column1
FROM Table1
WHERE Column1 = 'casesearch'

To make the query case sensitive and retrieve only one record (“casesearch”) from above query, the collation of the query needs to be changed as follows.

FROM Table1
WHERE Column1 COLLATE Latin1_General_CS_AS = 'casesearch'

Adding COLLATE Latin1_General_CS_AS makes the search case sensitive.

Default Collation of the SQL Server installation SQL_Latin1_General_CP1_CI_AS is not case sensitive.

To change the collation of the any column for any table permanently run following query.

COLLATE Latin1_General_CS_AS

To know the collation of the column for any table run following Stored Procedure.

sp_help DatabaseName

Second results set above script will return you collation of database DatabaseName.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

SQL SERVER – 2005 Collation Explanation and Translation

Just a day before one of our SQL SERVER 2005 needed Case-Sensitive Binary Collation. When we install SQL SERVER 2005 it gives options to select one of the many collation. I says in words like ‘Dictionary order, case-insensitive, uppercase preference’. I was confused for little while as I am used to read collation like ‘SQL_Latin1_General_Pref_Cp1_CI_AS_KI_WI’. I did some research and find following link which explains many of the SQL SERVER 2005 collation.

Complete documentation MSDN – SQL SERVER Collation

Reference : Pinal Dave (http://blog.SQLAuthority.com)