What is this expression changing process?

Question

Consider this table definition:

CREATE TABLE [dbo].[Post]
(
    [Id] INT IDENTITY(1,1) NOT NULL,
    [PostType] VARCHAR(10) NOT NULL,

    CONSTRAINT [CK_Post_PostType] CHECK ([PostType] IN ('Question', 'Answer', 'Comment'))
)

If I run that and then look in sys.check_constraints:

select [definition] 
from sys.check_constraints
where [name] = 'CK_Post_PostType';

This is the output:

([PostType]='Comment' OR [PostType]='Answer' OR [PostType]='Question')

So it changed my "in" statement to a series of "or" statements instead.

The same thing is true of (at least)

• BETWEEN (gets changed to OR statements) and
• CAST (gets changed to CONVERT).

I realize this isn't a functional problem in SQL Server, since the expressions are logically equivalent. But it causes issues with schema compare tools like SSDT, because the source code is out of sync with the deployed code. I've blogged in more detail about the problem here: SSDT problems: deploying the same change over and over

Is there a name for this conversion process / behavior? Is there any documentation for the different conversions that can occur, so we can plan for them (or try to avoid them)?

Answer 1

Broadly, this is known as standardization or normalization.

Examples of this include (for expressions) Conjunctive Normal Form and Disjunctive Normal Form.

There is no Microsoft documentation I am aware of that lists all the steps of the multiple processes involved. Essentially, SQL Server converts things into a standard format to make things easier for it to work with internally. This occurs at various times, including during parsing, and the normalization stage of query compilation and optimization.

As far as CAST versus CONVERT is concerned, it may simply be that CONVERT was first (and/or because it offers a superset of functionality). You should find that built-in (intrinsic) functions are generally preferred.