0

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?

Improve this question
1
  • 1
    Aaron's answer is correct, BETWEEN (and IN, and others) are syntactic sugar to make writing SQL easier and faster, but the engine changes it to it's internal syntax. (same with CAST/CONVERT). My practice with database projects is to avoid using the syntactic sugar versions as the comparison tools will otherwise continuously detect it as a change and redeploy the same objects over and over. I've found this to only be necessary on tables with constraints, stored procedures, functions and views don't exhibit this behavior.
    – Jonathan Fite
    Jun 26, 2020 at 14:53

1 Answer 1

Reset to default
1

If you write a query and generate an execution plan, you will see that it, too, translates BETWEEN to a pair of >= and <= predicates. These mean the same thing. Redgate is just showing you what SQL Server stored when you created the constraint, not what you typed. And it has nothing to do with SQL Server 2019.

That said, I prefer the two-predicate pattern, and talk about why BETWEEN is bad for date ranges here and here. My opinion is to avoid exceptions, especially when it is just convenient shorthand / syntactic sugar, and especially when it contradicts how SQL Server stores it internally. In other words, if you don't like that scripting yields a different result than your code, change your code and embrace >= AND <=.

Improve this answer
6
  • Thanks, @Aaron Bertrand. To avoid issues like this in the future, is there a list of operators, commands, etc., that are syntactic sugar and will cause comparison tools to see a change?
    – SQLDoug
    Jun 26, 2020 at 15:07
  • 1
    That would be a pretty big list, I think. CASE expressions, IIF, CHOOSE, filtered index predicates, all kinds of things can end up just different enough from your source control to trigger a delta. It's unlikely you can completely avoid this in general unless you memorize all of the syntax. :-)
    – Aaron Bertrand
    Jun 26, 2020 at 15:12
  • Odd, this must come up all the time for developers who use comparison tools on their database objects. I wonder what solutions or processes they use to make it work for them.
    – SQLDoug
    Jun 26, 2020 at 15:17
  • 2
    Not really, if you compare two databases - which I think is how most people use these tools - you will get the same translation to >= AND <= on both sides, because both source and destination made the same rewrite when the constraint was applied. The only time this would come up is when they're comparing the database to the code you wrote. I realize that's what you're doing, I just don't think that's what most people are doing. Maybe what some of these people do is, after they've applied the constraint to their database, they update the repo to reflect the database state?
    – Aaron Bertrand
    Jun 26, 2020 at 15:19
  • That makes a lot of sense, I'll take that back to the team. Thanks again for your help!
    – SQLDoug
    Jun 26, 2020 at 15:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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