Options
Data Compare uses ORDER BY with CAST AS NVARCHAR and COLLATE on VARCHAR columns
MartinFerrari
Posts: 1 New member
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:
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.
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.
Tagged:
Answers
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)
Kind regards,
Kurt McCormick
Product Support Engineer, Redgate
Need help? Take a look at our Help Center