Do I need to run sp_refreshview and sp_refreshsqlmodule after changing column collations?

Question

I look after a database for a vendor’s application that our system team uses to store passwords. This database is part of a three-node AlwaysOn Availability Group. The instance and database currently use the Latin1_General_CI_AS collation, but the vendor’s new update requires SQL_Latin1_CP1_CI_AS.

My planned approach is:

• Migrate to a new SQL Server instance.

• Restore the database to that instance and set the database collation to SQL_Latin1_CP1_CI_AS.

• For each user table, drop indexes, foreign keys, check constraints, computed columns, and indexed views.

• Change the collation for every column that stores char, varchar, nchar, or nvarchar data.

• Recreate all the dropped indexes, keys, constraints, computed columns, and indexed views.

While researching, I learned that views cache column metadata (including collation). That suggests I’ll need to run sp_refreshview on any views referencing the affected columns.

My question is:
Do I also need to run sys.sp_refreshsqlmodule on stored procedures, functions, and triggers that reference these columns after the collation change? Or is sp_refreshview sufficient?

• Charlieface: Simple script to refresh all modules (incl procedures, functions and triggers) dbfiddle.uk/rM2i0Fze

Answer 1

It's probably not be necessary based on your other changes and restarting the instance which will flush the various caches anyway, but might not be a bad idea to do so, just to be sure. It should help verify that there are no collation mismatches after your changes.

P.S. Are you sure about going from Latin1_General_CI_AS to SQL_Latin1_CP1_CI_AS instead of the other way around? I only ask as that is a definite step backwards that I would recommend against (if at all possible).

• Cpt_Brandon: Thanks for the feedback, appreciated. I’ll add an extra verification step to catch any collation mismatches (refresh views, refresh modules where applicable, and mark key procs to recompile after the change). Re the direction: I share your concern that moving from Latin1_General_CI_AS to SQL_Latin1_CP1_CI_AS is a step backward. I’ve raised this with the vendor, but they’re adamant that the app update requires SQL_Latin1_CP1_CI_AS for both the instance and database at this time.