Clever Geek Handbook

Is SQL Server 2005 faster when relationships are defined in a table?

I have heard that SQL is faster if table relationships are defined.

It's true?

Or maybe it is slower, I would like to know.

+2


source to share


2 answers


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 insert

s, not faster for select

s.

The reason is that it must check insert

ed values to make sure they exist in another table.

If you want to improve query performance select

, you want to put indexes on the columns that you will join and filter. However, indexes are really expensive as they slow down insert

s, update

s and delete

s because the indexes have to be updated every time the table changes like that.



So, if your table is large insert

/ update

, don't add too many indexes. If your table is predominantly select

, 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 seek

, as that means it is able to make the most of the index!

+4


source


SQL is not faster if table relationships are defined.



Indexes improve performance; referential integrity (tabular relationships) ensures data quality in line with business requirements.

+3


source






More articles:


All Articles