What are the challenges you face when working across database platforms? Take the survey

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.