Sample Header Ad - 728x90

What is causing BETWEEN to be replaced with <= and >=?

0 votes
1 answer
81 views
We have a Visual Studio solution for our SQL Server 2019 database, and it is stored in a git repo. One table has a couple of check constraints using the BETWEEN operator, like this. CONSTRAINT chk_FileTbl_MonthlyDate CHECK (MonthlyDate BETWEEN 1 AND 31) When I used Redgate SQL Compare 14 to compare the repo to the database, it displayed the constraint reformatted with less than and greater than operators, like this. CONSTRAINT chk_FileTbl_MonthlyDate CHECK (MonthlyDate >= 1 AND MonthlyDate <= 31) I thought it was a bug/feature in Redgate's software and submitted a ticket for which I am awaiting a response. However, my colleague then used the comparison tool in Visual Studio 2019 and it did the same thing. If we look at the file defining the table, it uses BETWEEN, but both comparison tools show and deploy a change script with the less than/greater than format. What is causing this behavior? We somewhat recently moved to SQL Server 2019, is it some kind of built-in optimization?
Asked by SQLDoug (247 rep)
Jun 26, 2020, 02:16 PM
Last activity: Jun 26, 2020, 02:43 PM