What are the challenges you face when working across database platforms? Take the survey
Options

Difference in collation is not picked up when using user defined types.

Hi!

When I do diffs between databases where (n)varchar columns have different collations it works just fine unless the column uses a user defined data type.

I'm using SQL Compare 13.8 so there might be some fixes done in later versions. If so it would be good to know. 
If you consider this a bug that needs fixing it is also good to know as well as if you don't plan to fix it.

Below is a tiny script you can use to create a couple of database with different collations so you can reproduce what I see.

The end goal with this little exercise is to make SQL Compare generate a script that changes the collation of all n(varchar) columns in a database. If you see other solutions to that problem I guess I'm  also interested in that.

cheers

/Micke

/*
use master;
drop database if exists xx;
drop database if exists yy;
go
create database xx collate Latin1_General_CI_AS;
create database yy collate Latin1_General_100_CI_AS_SC;
go
*/
use xx;
go
drop table if exists dbo.T;
drop type if exists dbo.VT;
go
create type dbo.VT from varchar(10);
go
create table dbo.T(C1 varchar(10), C2 dbo.VT);
go
use yy;
go
drop table if exists dbo.T;
drop type if exists dbo.VT;
go
create type dbo.VT from varchar(10);
go
create table dbo.T(C1 varchar(10), C2 dbo.VT);
go




Answers

  • Options
    Hi @micke_314

    Thank you for the instructions. I have forwarded this onto the development team to have a look at. For your information, this same thing occurs on the latest version. 

    Once I have an update, I'll let you know.

    Kind regards,

    Kurt McCormick
    Product Support Engineer, Redgate

    Need help? Take a look at our Help Center

  • Options
    Hi @micke_314

    I've heard back from the dev team.

    The reason the UDT does not have its collation defined is due to the fact that the collation for all UDTs are inherited from the collation of the database itself.

    They have discussed potentially providing an option to use the database schema and then changing the collation of a column using that, however to implement properly would require a lot of development work touching many parts of the engine which would take a long time.

    At this stage, the developers won't be taking on this change.

    Apologies.

    Kind regards,

    Kurt McCormick
    Product Support Engineer, Redgate

    Need help? Take a look at our Help Center

Sign In or Register to comment.