Fixing the collation on Instance,Database and column levels

Hi dears, Many of us come across the collation issue on many levels. We will see how to resolve the issues on each level. This post will cover the fallowing collation issues.
language

• Collation issues on the Instance level.
• Collation issues on the database level.
• Collation issues on the table on column levels.

Collation issues on the Instance level :-
Collation on the instance level was the collation we set during the installation of the instance, due to lack of knowledge about what suppose the Instance collation we may choose the different one. But it is not so straight forward to fix the issue, of course there in the field of information technology we suppose to give solutions.  Here is how, before we proceed what are all effects of collation on the instance level.  All the system databases will exists with server collation, which will be default collation for the new databases if you not specify the collation explicitly. Okay you may think no big issues, but of course not. Think about you are creating a temp table on any the stored procedure, this table created on the temp table will take the collation from instance collation.  We will proceed now to resolve the issue. For that we need to rebuild the master databases, those details can be found on the link. Rebuild master database
Collation issues on the database level:-
Fixing the collation on the database level was not a big deal, you can do that happily by the below script. But changing the collation of the database won’t change the collation of tables created already on the database.

USE [master]

GO

ALTER DATABASE [Database_name] SET  SINGLE_USER WITH NO_WAIT

GO

ALTER DATABASE [[Database_name] COLLATE SQL_Latin1_General_CP1256_CI_AS

GO

ALTER DATABASE [[Database_name] SET  MULTI_USER WITH NO_WAIT

GO

Many of us working on huge databases which was developed before years and of course developed by many teams may have different collations. Will given trouble while you make join columns of different collations, forcefully you have to use collate commands on the joining columns and also in where clause sometimes. All this things will lead to degrade the database performance. Think about ODS (Operational Data Store) database replicated from various database without keeping the copy collation false. You can fix the replication option later but the tables exist already will have the source collation.

Before we proceed to fix the issue we need to make sure the fallowing things. Make sure the Stored procedures using the collate command need to taken care. The SSIS package using the tables need to modified accordingly.
Now we will move towards finding a solution for such issue. By the below script you can list all the tables and columns with its collations.

select object_name(object_id) as tablename, name as columnname,collation_name

from sys.columns

where collation_name is not null

order by object_name(object_id),column_id

Now you know, what are all the collations you need to be changed, just pass the collation name which needs to be set for are all tables. Once you execute the below it will give you the alter statement, by which all the collation columns can be unified.

view source

print?

DECLARE @collate SYSNAME

SELECT @collate = 'Arabic_CI_AS'

SELECT

'[' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + '] -> ' + c.name

, 'ALTER TABLE [' + SCHEMA_NAME(o.[schema_id]) + '].[' + o.name + ']

ALTER COLUMN [' + c.name + '] ' +

UPPER(t.name) +

CASE WHEN t.name NOT IN ('ntext', 'text')

THEN '(' +

CASE

WHEN t.name IN ('nchar', 'nvarchar') AND c.max_length != -1

THEN CAST(c.max_length / 2 AS VARCHAR(10))

WHEN t.name IN ('nchar', 'nvarchar') AND c.max_length = -1

THEN 'MAX'

ELSE CAST(c.max_length AS VARCHAR(10))

END + ')'

ELSE ''

END + ' COLLATE ' + @collate +

CASE WHEN c.is_nullable = 1

THEN ' NULL'

ELSE ' NOT NULL'

END

FROM sys.columns c WITH(NOLOCK)

JOIN sys.objects o WITH(NOLOCK) ON c.[object_id] = o.[object_id]

JOIN sys.types t WITH(NOLOCK) ON c.system_type_id = t.system_type_id ANDc.user_type_id = t.user_type_id

WHERE t.name IN ('char', 'varchar', 'text', 'nvarchar', 'ntext', 'nchar')

AND c.collation_name != @collate

AND o.[type] = 'U'