Changing the Collation on a table

A while ago, we came across this error in one of our main databases:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

So as  a workaround we created a view on the table to prevent it erroring:

select t.transaction_num, t.department_id, d.description 
from transaction_header t inner join department d
on t.department_id = d.department_id COLLATE Latin1_General_CI_AS
where t.department_id = 'SALES'

BUT none of this meant that the original problem was fixed. At the time it wasn’t a big issue and still doesn’t cause any problems but for completeness I wanted to sort this out.

Investigation

First, it was important to find out if any other columns in the database had the "SQL_Latin1_General_CP1_CI_AS" collation. For Info, the default collation on this database is "Latin1_General_CI_AS".

How to Find DB collation via T-SQL:

SELECT DATABASEPROPERTYEX('CMS3000Train', 'Collation') SQLCollation;

To find out which objects in the database have columns which have different collation to database default:

SELECT * FROM sysobjects 
  WHERE xtype = 'U' 
    and id IN 
    ( SELECT id FROM syscolumns WHERE collation <> 
    (SELECT DATABASEPROPERTYEX('MyDB', 'Collation')))

To get more detail on which columns are affected:

select name, id, colid, collation  from syscolumns
where id in (select id from sysobjects where name = 'department')

Solution

To resolve the collation and set it back to the same as the database default (after consideration of why it was set to be different in the first place!), the following can be run (only on non primary key columns)

ALTER TABLE department
Alter column description varchar (50) COLLATE Latin1_General_CI_AS

Changing Primary Key columns

The table needs to be rebuilt by pushing the data out into a temporary table and recreating the table.

BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_Department
    (
    department_id varchar(5) NOT NULL,
    description varchar(50) NULL,
    date_created datetime NOT NULL,
    date_deleted datetime NULL,
    username_created varchar(20) NOT NULL,
    username_deleted varchar(20) NULL
    )  ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Department)
     EXEC(
    'INSERT INTO dbo.Tmp_Department 
    (department_id, description, date_created, date_deleted, username_created, username_deleted)
    SELECT department_id, description, date_created, date_deleted, username_created, username_deleted 
    FROM dbo.Department WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Department
GO
EXECUTE sp_rename N'dbo.Tmp_Department', N'Department', 'OBJECT' 
GO
ALTER TABLE dbo.Department ADD CONSTRAINT
    PK_Department PRIMARY KEY CLUSTERED 
    (
    department_id
    ) WITH
    ( 
    PAD_INDEX = OFF, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON
    ) ON [PRIMARY]

GO
COMMIT

The original SQL can now be qeuried without the use of the COLLATE.

select t.transaction_num, t.department_id, d.description 
from transaction_header t inner join department d
on t.department_id = d.department_id 
where t.department_id = 'SALES'
Advertisements

One Response to Changing the Collation on a table

  1. Need to change collation in table to UTF-8_general_ci or when creating it.
    Can not find any solution of it for oracle. How can it possible.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: