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 toOR
statements) andCAST
(gets changed toCONVERT
).
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)?
Best Answer
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
versusCONVERT
is concerned, it may simply be thatCONVERT
was first (and/or because it offers a superset of functionality). You should find that built-in (intrinsic) functions are generally preferred.