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

     We're using Data Compare, 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

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,



Sign In or Register to comment.