Yes, Foreign Keys Help Performance

SQL Server
I created this example several years ago that illustrates how foreign key constraints can help performance. It's a contrived example. Granted. I feel like it illustrates the point. However, over the years, people have questioned one aspect of it. The optimizer uses the foreign keys to figure out which tables can be eliminated from the query, making for a more efficient plan and making the query run faster. The pushback has always been, "Yeah, Grant, but nobody writes T-SQL where they include extra tables that they don't need." My initial response, after I stop laughing, is to point out any number of ORM tools. But, you know what, let's assume that's correct. No one would ever create a giant catch-all view that has all their JOINs in one place so they don't have…
Read More

Constraints and SELECT Statements

Azure, SQL Server, T-SQL
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: ALTER TABLE Sales.SalesOrderDetail WITH CHECK ADD  CONSTRAINT CK_SalesOrderDetail_UnitPrice CHECK  ((UnitPrice>=(0.00))) That will ensure that no values less than zero can slip in there. We can even validate it: INSERT Sales.SalesOrderDetail (SalesOrderID, CarrierTrackingNumber, OrderQty, ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid, ModifiedDate ) VALUES (60176, -- SalesOrderID - int N'XYZ123', -- CarrierTrackingNumber - nvarchar(25) 1, -- OrderQty - smallint 873, -- ProductID - int 1, -- SpecialOfferID - int -22, -- UnitPrice - money 0.0, -- UnitPriceDiscount - money NEWID(), -- rowguid - uniqueidentifier GETDATE() -- ModifiedDate - datetime ); Will give me…
Read More