Scripts with CAST are treated like they should be CONVERT

heck4

As an example, I have this script:

<div>CREATE TABLE [dbo].[tblCastConvertTest]<br>(<br>&nbsp;&nbsp;&nbsp;&nbsp; [Id]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; INT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL IDENTITY(1,1)<br>&nbsp;&nbsp;&nbsp; ,[IsIncluded]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; BIT&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; NOT NULL DEFAULT 1<br>&nbsp;&nbsp;&nbsp; ,[IsExcluded]&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; AS&nbsp; CASE<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WHEN [IsIncluded]=0 THEN CAST(1 AS BIT)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ELSE CAST(0 AS BIT)<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; END<br>&nbsp;&nbsp;&nbsp; ,CONSTRAINT [PK_tblCastConvertTest] PRIMARY KEY CLUSTERED ([Id])<br>)<br>GO<br></div>

After I execute it on the database and run a comparison, its showing a false positive difference:

Any ideas on why this is happening?

Mark R

Hi - what are you comparing to get that diff? Scripts folder to database?

Tianjiao_Li

@heck4

Following @Mark R 's investigation, we've reproduced  the issue and logged as it as SC-10390 in our internal bug tracking system. Thanks for reporting it! Please keep an eye on the release note.

heck4

Yes you are correct Mark. Scripts folder to database. We treat our source control as the system of record.

heck4

A few other things that may help:

• SQL Compare 10 does not exhibit this bug, and treats the two different syntaxes as interchangeable.
• I've read that CAST is implemented internally within SQL as an alias to CONVERT.
• Using SSMS 17.7, when I use the above script to create the table, and then re-script it out using SSMS it uses CONVERT, getting rid of my original CAST syntax.

Thanks. Feel free to close this ticket.

Tianjiao_Li

Thanks! I've put a note in the bug report.

Tianjiao_Li

@heck4

The dev team has looked into this issue. To be fair, it's not an error in SQL Compare. The differences are real, and we're reporting them as such. As you said, it turns out that SQL Server is basically rewriting the CAST operation to a CONVERT operation under the covers.

You should be able to workaround it by using SQL Server's preferred CONVERT syntax instead of CAST.