Collation conflict occur at operation on User define funtion & table's column

Collation conflict occur at operation on User define funtion & table's column.

When you concat or case/when operation on user define function and column, the collation maybe different.

you should use 'sp_help xxx' to view theire collation is the same.

 

1. if you have user define function, you can specific the collation like this:

 

ALTER function [dbo].[SplitString]

(

    @Input nvarchar(max),

    @Separator nvarchar(max)=',', 

    @RemoveEmptyEntries bit=1 

)

returns @TABLE table 

(

    [Id] int identity(1,1),

    [Value] nvarchar(max) collate Chinese_PRC_CI_AS

) 

2. you can change database default collation by run  "sql server management studio" at administrator role

execute this script like this:

alter database [CRM] collate Latin1_General_CI_AS   -- Chinese_PRC_CI_AS