Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

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.
0
bmsande
Asked:
bmsande
  • 6
  • 4
  • 3
2 Solutions
 
Scott PletcherSenior DBACommented:
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
 
bmsandeAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bmsandeAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
bmsandeAuthor Commented:
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
 
Scott PletcherSenior DBACommented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
bmsandeAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
-- 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
 
bmsandeAuthor Commented:
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
 
Chris LuttrellSenior Database ArchitectCommented:
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
 
bmsandeAuthor Commented:
Thanks @Chris.  I know it's a difficult question to answer.  We fully intend to do a thorough regression test.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 6
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now