Constraints and SELECT Statements

Azure, SQL Server 2005, SQL Server 2008, SQL Server 2012, SQL Server 2014, TSQL
I've posted previously about how a foreign key constraint can change how a SELECT query behaves. Logically that just makes sense. But other types of constraints don't affect execution plans do they? Yes. Let's take this constraint as an example: [crayon-5b40b96100d63407040489/] That will ensure that no values less than zero can slip in there. We can even validate it: [crayon-5b40b96100d6f773041124/] Will give me an error: Msg 547, Level 16, State 0, Line 470 The INSERT statement conflicted with the CHECK constraint "CK_SalesOrderDetail_UnitPrice". The conflict occurred in database "AdventureWorks2014", table "Sales.SalesOrderDetail", column 'UnitPrice'. Let's look at a SELECT query now. If we run this: [crayon-5b40b96100d75335587355/] The resulting execution plan looks like this: But, if I modify the query to look like this, adding an additional AND filter on the constrained UnitPrice…
Read More