asked on

Change MSSQL column collation - is there anything I can do to make this easy?

Hi Experts,
I'm in a bind with our application upgrade. It's failing due to collation conflicts in the database. I analyzed our MSSQL collation properties by instance>database>columns and uncovered a mess.

Collation properties:
SQL 2014 instance: "SQL_Latin1_General_CP1_CI_AS"
Application database: "Latin1_General_CI_AS"
tempdb: "SQL_Latin1_General_CP1_CI_AS"

(SQL instance is dedicated to the application database. No other db's exist besides system db's)

I confirmed column level collation by running the following:

select sc.name column_name, sc.collation_name column_collation, so.name table_name from sys.columns sc, sys.objects so where sc.object_id = so.object_id and sc.object_id > 2000 and sc.collation_name IS NOT NULL order by table_name, column_name

In the database;
800 columns with "SQL_Latin1_General_CP1_CI_AS"
240 columns with "Latin1_General_CI_AS"

From a high level I believe the most efficient approach would be correcting collation on the 240 tables to match the SQL instance -AND- change the database collation. End result is everything will be "SQL_Latin1_General_CP1_CI_AS".

I believe the process goes..export data > drop the columns associated objects (indexes, triggers, etc) > drop the column > recreate column with proper collation, recreate objects, reimport data.

1. Are there any queries I can run to understand what objects are associated/dependent on these columns? That would help me understand how many changes are required and where.

2. Should I change the database collation before or after correcting the mismatched columns?

Thanks for any tips/tricks you can provide.

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bmsande

ASKER

Thanks @Scott

I'm going to ease into this with a variety of questions to cover my bases. Good to know I don't need to drop the column... which means I don't need to export/import data - correct?

Should I change the database collation first, before changing column collation? I'm not clear when this should be done. We like to change db collation to match the SQL instance.

Are there any queries I can run that provide analysis of the objects which reference a column that must be changed?

Thanks for your time.

Scott Pletcher

Yes, you can should the db collation first. That will make new tables get the correct collation, but it won't affect existing tables.

You can use view sys.sql_expression_dependencies to check for non-dynamic SQL code dependencies on a particular column or object.

bmsande

ASKER

Is the query below sufficient to find ALL dependencies of a column that I'm changing?

Results are returned in my testing but it does not include; indexes, triggers, & constraints contained on the table. Won't these need to be recreated I alter column collation in this table?

I'm putting together a plan of what changes are required for every column collation that's changed. I don't want to miss anything.

Thanks for your expertise!

USE AdventureWorks
GO
SELECT
referencing_schema_name = SCHEMA_NAME(o.SCHEMA_ID),
referencing_object_name = o.name,
referencing_object_type_desc = o.type_desc,
referenced_schema_name,
referenced_object_name = referenced_entity_name,
referenced_object_type_desc = o1.type_desc,
referenced_server_name, referenced_database_name
--,sed.* -- Uncomment for all the columns
FROM
sys.sql_expression_dependencies sed
INNER JOIN
sys.objects o ON sed.referencing_id = o.[object_id]
LEFT OUTER JOIN
sys.objects o1 ON sed.referenced_id = o1.[object_id]
WHERE
referenced_entity_name = 'Customer'

Open in new window

Scott Pletcher

True, the dependencies tables won't have internal SQL references, such as indexes, only code references.

Nothing can find ABSOLUTELY ALL references because of dynamic SQL. Naturally dynamic code can't be known ahead of time.

bmsande

ASKER

I have a table [WLock] that has an index associated [WLock_Primary].
(seen by expanding database>table>indexes with Mgmt Studio)

Screenshot:

I altered a column contained in the [WLock] table. Does the [WLock_Primary] index need to be dropped/recreated?

Scott Pletcher

If SQL allowed you to keep the index in place while the ALTER occurred, you shouldn't need to rebuild it, although I'm very surprised that SQL didn't force you to drop the index first.

Chris Luttrell

It works as long as the column you are altering is not involved in the index. Try this test code and uncomment the secondary index to see it fail:

CREATE TABLE WLOCK (
	Id INT IDENTITY,
	VarColumn NVARCHAR(200) COLLATE SQL_Latin1_General_CP1_CS_AS,  -- not the DB Default
	CONSTRAINT PK_WLOCK PRIMARY KEY CLUSTERED (Id)
);
--CREATE INDEX idx_Secondary_VarColumn ON dbo.WLOCK(VarColumn);

INSERT INTO dbo.WLOCK ( VarColumn )
VALUES  ( N'' ),( N'this' ),( N'ThiS' ),( N'THIS' ),( N'Is' ),( N'is' ),( N'a' ),( N'Test' );
SELECT *
FROM dbo.WLOCK W;

SELECT *
FROM dbo.WLOCK W
WHERE W.VarColumn LIKE '%IS%';

SELECT *
FROM dbo.WLOCK W
WHERE W.VarColumn LIKE '%is%';

ALTER TABLE dbo.WLOCK ALTER COLUMN VarColumn NVARCHAR(200) COLLATE DATABASE_DEFAULT; -- SQL_Latin1_General_CP1_CI_AS


SELECT *
FROM dbo.WLOCK W
WHERE W.VarColumn LIKE '%IS%';

SELECT *
FROM dbo.WLOCK W
WHERE W.VarColumn LIKE '%is%';


DROP TABLE dbo.WLOCK;

Open in new window

bmsande

ASKER

Thanks, @Scott. The alter command ran without error so I assume the table index [WLock_Primary] does not require rebuild.

I tried altering a column in a different table and received an error because an index exists, just as you described. I dropped the index, altered the column, then recreated the index. Is that the correct order of operations?

Before making changes to columns, I query for database references using the statement provided earlier today. I finally got a result for one of the tables that contains a column I must modify. The referencing object type is a view. I was able to alter collation on a column in this table with no error. Must the view be recreated? Or should I have dropped it before altering collation of the column?

SOLUTION

Chris Luttrell

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bmsande

ASKER

Thanks everyone for your help. I think I'm getting the hang of things. I need to clarify one thing.....

If there are no errors when altering collation of a column, is it safe to assume no references/dependencies need to be recreated?

I have run into a few errors when altering a column and it was as easy as dropping the object that's causing the error, altering column collation, then recreating whatever was dropped.

I've modified several columns collation with no error -- but I need to confirm I'm not missing anything just because I didn't receive an error.

Chris Luttrell

That is like saying, "Tell me what you DON'T know." Short of having a full regression test run on your application/database system, no one can say it is perfect. What you have done when you ran into the dependencies is the best thing you can and possibly doing the sp_refreshview on any views that may use the tables you changes. I know in the Red Gate tools I use to migrate schema changes, it always does the sp_refreshview anytime we alter a table.

bmsande

ASKER

Thanks @Chris. I know it's a difficult question to answer. We fully intend to do a thorough regression test.