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)?

up vote 3 down vote accepted

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.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.