Is SQL Server 2005 faster when relationships are defined in a table?
I am assuming you are talking about foreign keys. It is also called referential integrity and is one of the constraints . Foreign keys aren't the only constraints — you can have unique and control constraints. Anyway, referential integrity is slightly slower for
s, not faster for
The reason is that it must check
ed values to make sure they exist in another table.
If you want to improve query performance
, you want to put indexes on the columns that you will join and filter. However, indexes are really expensive as they slow down
s because the indexes have to be updated every time the table changes like that.
So, if your table is large
, don't add too many indexes. If your table is predominantly
, use indexes where you can. The Database Engine Tuning Advisor can help you determine these indexes for some of your most common queries.
Make sure you use the Query Execution Plan when running your queries ( Ctrl+ Lin SSMS) so you can see what SQL Server is doing. You want as much as possible
, as that means it is able to make the most of the index!
source to share