Data Compare uses ORDER BY with CAST AS NVARCHAR and COLLATE on VARCHAR columns

MartinFerrari

Hello,
     We're using Data Compare 13.8.0.12703, comparing a DB between a MS SQL Server 2012 and a MS SQL Server 2017.
     We have some tables with VARCHAR columns as primary keys. For those tables, instead of using the PK index, Data Compare issues a query with CAST and COLLATE in the ORDER BY clause, as follows:

SELECT [Code], [PaymentMethodCode], [Value]

FROM [dbo].[TestTable] WITH (NOLOCK)  ORDER BY CAST([Code] AS NVARCHAR(100)) COLLATE SQL_Latin1_General_CP1_CI_AS, CAST([PaymentMethodCode] AS NVARCHAR(5)) COLLATE SQL_Latin1_General_CP1_CI_AS

go

As the tables has a lot of records, this is considerably slower than just having a
ORDER BY Code, PaymentMethodCode clause.

Is there a way to avoid such CAST? I've checked the collations and columns definitions, and they are exactly the same in both databases.

Thank you,
     Martín.

Kurt_M

Hi @MartinFerrari

Thanks for your question. As this is most likely something that happens in the background, there won't be much that you can change options wise that will allow you to adjust how the custom comparison key works. You could try the following option to see if that impacts the speed at all, but I'm not sure it will:

https://documentation.red-gate.com/sdc13/setting-up-the-comparison/setting-project-options#:~:text=Force%20binary%20collation%20(case%2Dsensitive)