Solved

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

Posted on 2016-07-25
13
122 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:
ScottPletcher 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:ScottPletcher
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
 

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:ScottPletcher
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 69

Expert Comment

by:ScottPletcher
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now