Why Does One Constraint Prevent A Sort? - Databases

TopAnswers Databases

Meta

Databases

TeX

Code Golf

APL

C++

.net

db<>fiddle

Java

*nix

PHP

PowerShell

Python

Rust

टेक्-मराठी

Typst

Web Client Dev

Web Server Dev

Why Does One Constraint Prevent A Sort?

sql-server add tag

meme

Assume that I am being truthful about creating this index:

    CREATE INDEX constraints_are_silly
    ON dbo.Users
    (
        Reputation,
        UpVotes
    ) INCLUDE (DisplayName);
    
Also assume that this is the most important query ever written by human hands for the benefit of humanity:

    SELECT TOP (1000)
           u.Reputation,
           u.UpVotes,
           u.DisplayName
    FROM dbo.Users AS u
    WHERE u.Reputation <= 1 
    ORDER BY u.UpVotes;
    
However, I'm dissatisfied with the query plan. This requires no assumption.

![2020-05-27_18-18-48.png](/image?hash=e10bc35e574122aae630befdb881b80619adf6db880207950ffb51d908f10493)

Since a current implementation rule for the database is that no one can have a Reputation of 0 or less, I add this constraint hoping that SQL Server will stop sorting data:

    ALTER TABLE dbo.Users ADD CONSTRAINT
        checko CHECK (Reputation > 0);
        
But I still end up with the same execution plan. In neither case is the plan a) trivial, or b) simple parameterized.

Now assume that I get really mad and change my constraint to this:

    ALTER TABLE dbo.Users ADD CONSTRAINT
        checko CHECK (Reputation >= 1);
        

And now I get a query plan that does not have a sort in it. My approval does not require assumption.

![2020-05-27_18-22-28.png](/image?hash=ea910235a7947e16b4fd1deb3a3f0969cc781da9b958ceebe0f86df8ab548a18)

Why does one constraint remove the need to sort, and one not?

Top Answer

Paul White

Essentially it's the same reason

```sql
SELECT TOP (1000)
    U.Reputation,
    U.UpVotes,
    U.DisplayName
FROM dbo.Users AS U 
WHERE U.Reputation <= 1 
AND U.Reputation >= 1 -- from the constraint
;
```

simplifies to `U.Reputation = 1`

but

```sql
SELECT TOP (1000)
    U.Reputation,
    U.UpVotes,
    U.DisplayName
FROM dbo.Users AS U 
WHERE U.Reputation <= 1 
AND U.Reputation > 0 -- from the constraint
;
```

does not.

The optimizer uses the check constraint to narrow the range of possible values, but doesn't take into account the **data type**.

If it did, it could reason like a human would, that `> 0 and <= 1` means the same as `= 1`. If it could follow the reasoning all the way along, the seek would have `Reputation = 1` and the Sort would be removed because sorting on a single value is unnecessary.

As it is, the optimizer sees a half-open range of possible Reputation values of (0..1] for the second case, so a sort appears to be necessary (as values like 0.1, 0.4, 0.7 etc. might be encountered).

1 Answer