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

micke_314

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 ;

drop database if exists yy;

go

create database collate Latin1_General_CI_AS;

create database yy collate Latin1_General_100_CI_AS_SC;

go

*/

use ;

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

Kurt_M

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.

Kurt_M

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.