07-31-2023, 02:56 AM
YES, a FK can speed up `SELECT` but slow down `INSERT/UPDATE/DELETE`
SQL Server uses *all* constraints (FK included) to build better execution plans for `SELECT`s.
For instance, if you run a query with `Column1 = X` and `X` does not fit the constraint, the server won't even touch the table.
P.S. unless the constraint is in ["untrusted" state][1], but that's a whole different story.
P.P.S. However having a foreign key (or other constraints) can slow down `INSERT/UPDATE/DELETE`s unless you have a non-clustered index on this column
[1]:
SQL Server uses *all* constraints (FK included) to build better execution plans for `SELECT`s.
For instance, if you run a query with `Column1 = X` and `X` does not fit the constraint, the server won't even touch the table.
P.S. unless the constraint is in ["untrusted" state][1], but that's a whole different story.
P.P.S. However having a foreign key (or other constraints) can slow down `INSERT/UPDATE/DELETE`s unless you have a non-clustered index on this column
[1]:
[To see links please register here]