Solved

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

Posted on 2016-07-25
13
189 Views
Last Modified: 2016-08-05
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.
0
Comment
Question by:bmsande
  • 6
  • 4
  • 3
13 Comments
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 400 total points
ID: 41728561
You don't need to drop the column, you can ALTER the COLLATION.  But, yes, you must first drop references to the column, including FKs, indexes, computed columns and/or schema-bound views or functions.

For example, say you have a varchar(30) not null column with the wrong collation.  You can do this, assuming no dependent objects exist:

ALTER TABLE table_name ALTER COLUMN column_name varchar(30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL;

[Btw, be sure to explicitly specify "NULL" or "NOT NULL", otherwise it will default and could end up different!]
0
 

Author Comment

by:bmsande
ID: 41728574
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.
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41729642
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.
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

Author Comment

by:bmsande
ID: 41732047
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

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41732061
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.
0
 

Author Comment

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

Screenshot:
screenshot
I altered a column contained in the [WLock] table.  Does the [WLock_Primary] index need to be dropped/recreated?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 41732116
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.
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 41732128
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

0
 

Author Comment

by:bmsande
ID: 41732144
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?
0
 
LVL 26

Assisted Solution

by:Chris Luttrell
Chris Luttrell earned 100 total points
ID: 41732288
-- I dropped the index, altered the column, then recreated the index.
Yes, that is the correct order of operations.

If you did not have to drop the view to make your changes and want to be safe, you can use the following to "Refresh" the view, i.e. it will update any metadata it needs
EXEC sp_refreshview N'[Schema].[YourViewName]'
0
 

Author Comment

by:bmsande
ID: 41733692
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.
0
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 41733886
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.
0
 

Author Comment

by:bmsande
ID: 41733904
Thanks @Chris.  I know it's a difficult question to answer.  We fully intend to do a thorough regression test.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how the fundamental information of how to create a table.

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question