Last post Aug 29, 2018 05:37 PM by mgebhard
Aug 29, 2018 05:16 PM|gunderj|LINK
The quick test below gets inconsistent results - the count(*) is 2 but the distinct result set is 1.
We are migrating applications from oracle to SQL Server 2012 and oracle treats mixed case consistently
where the count(*) is 1 and the distinct result set is 2.
For SQL Server we have used the work-around...
where x1 COLLATE Latin1_General_CS_AS = 'ttt'
which does work but this will cause massive amounts of code changes for us, including restructuring primary keys, foreign keys, etc.
Does this make sense, meaning the answer is yes, we have to rebhuild and go through all the code and add the COLLATE in logic that needs it?
Hopefully we have missed something and this issue has a better work-around? Thanks.
CREATE TABLE [dbo].[Table_1](
[x1] [nchar](10) NOT NULL,
[x2] [nchar](10) NOT NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
insert into Table_1(x1,x2) values ('ttt','a');
insert into Table_1(x1,x2) values ('TTT','b');
select count(*) from Table_1 where x1 = 'ttt'
select distinct x1 from Table_1
Aug 29, 2018 05:37 PM|mgebhard|LINK
Collation is set at the server, database, table, or script level. See the SQL support docs.