x

SQL Server Foreign Key constraint benefits

We're designing a database in which I need to consider some FK(foreign key) constraints. But it is not limited to formal structuring and normalization. We go for it only if it provides any performance or scalability benefits.

I've been going thru some interesting articles and googling for practical benifits, here're some links:

http://www.mssqltips.com/tip.asp?tip=1296

I wanted to know more about the benefits of FK (apart from the formal structuring and the famous cascaded delete\update)

  • FK are not 'indexed' by default so what are the considerations while indexing an FK?

  • How to handle nullable fields which are mapped as foreign key - is this allowed?

  • Apart from indexing, does this help in optimizing query-execution plans in SQL-Server?

I know there's more but I'd prefer experts speaking on this. Please guide me.

more ▼

asked Oct 27, 2009 at 11:11 AM in Default

Hmnt gravatar image

Hmnt
43 5 5 5

(comments are locked)
10|1200 characters needed characters left

4 answers: sort oldest

Obviously, indexing a foreign key allows that there will always be values in the field, even though there are duplicates. Whether you'd want to index it or not would depend on the data-type and whether you were likely to see any performance gains. Considering you would usually join to the table via the foreign key would suggest you would see some gains, but how much depends on table size/usage.

The second point should be moot as you can not have null values in a foriegn key field

The third point. It does help a bit as the Primary key this FK is attached to is likely to have a clustered index. Again, the benefits are dependant on the size of the table and also the size of your hardware (i've had to use query hints to stop using hash joins as I didn't have enough memory on servers before [SQL will not check how much it has free and just tries to use a hash join even if there is none, meaning the query waits until there is enough available, someone at Microsoft needs shooting for thinking that was a good idea!]).

more ▼

answered Oct 27, 2009 at 01:39 PM

RickD gravatar image

RickD
1.7k 1 1 4

(comments are locked)
10|1200 characters needed characters left

I'm a normal "would be DBA" level developer. I've been handling some databases with a few million records. A lot goes around importing data between database and its clone and then using that clone in the web-app environment.

Well, I've known that keeping PK indexes automatically and so it helps speedup the data access. Now, from this discussion I derive that if I'm using JOINs in my SQL-Queries then I shud use FK and index it to make the JOIN operations efficient.

For example, I have a table OrgMaster (contains all the Org records) then I have a BookingMaster table (contains all the Booking records). Now, the OrgMaster.Id is being 'referenced' as BookingMaster.OrgId. So, I have an FK for the OrgId-to-Id relationship and I shud 'index' it for better performance of any JOIN operation between both of these tables .. did I get it correctly?

All the above - at the cost of extra overhead of space and time (while inserting record in the table with FK).

I'd request that you provide me a list of points to be considered, like -

  • Is FK-index going to eat up too much space\time as table grows few million records?
  • In that case, is it worth to go for an FK-index "each time"?
  • In what case shud I NOT apply FK or Index it or do neither of it (of course I can handle a LOT from the app)
  • Any other tricky to speedup JOIN or other such time-consuming lookups?

Thank you.

more ▼

answered Oct 29, 2009 at 11:19 AM

Hmnt gravatar image

Hmnt
43 5 5 5

(comments are locked)
10|1200 characters needed characters left

An FK in your example may possibly speed up the JOIN, but I don't think you'd see a massive effect as you already have an Index on your join and SQl would hash match the results anyway. It would also slow down any insert/updates to BookingMaster. It is worth having a clustered index on these tables as this helps with speed of inserts, but any other indexes cause overheads. It really does depend on how often you are writing to this table and how time critical the loading of each records is.

Is FK-index going to eat up too much space\time as table grows few million records? - Possibly, it depends on your record size and requirements for reporting vs requirements for inserting data, it is always a trade off.

In that case, is it worth to go for an FK-index "each time"? - No, it very much depends on data-types, requirements and simply testing on your side.

In what case shud I NOT apply FK or Index it or do neither of it (of course I can handle a LOT from the app) - Anywhere adding the FK affects performance or a lot of inserts are required (the numeric value for a lot depends on the size and capabilities of youre server).

Any other tricky to speedup JOIN or other such time-consuming lookups? - Loads, but one example is any join that uses a function (substring, cast/convert etc..) to split the data as this will not make use of any indexes.

more ▼

answered Oct 29, 2009 at 12:06 PM

RickD gravatar image

RickD
1.7k 1 1 4

(comments are locked)
10|1200 characters needed characters left

I derive that if I'm using JOINs in my SQL-Queries then I shud use FK and index it to make the JOIN operations efficient

No specific need for a FK when you use a JOIN, but an Index will usually help.

Whilst a FK will ensure that you have referential integrity - i.e. you cannot accidentlaly create an orphan record - it will delay delete operations whilst the consistency of the database is checked.

Having said that I always have FKs where a relationship exists. Whist the DELETE performance is sometime terrible because of it, I cannot absolutely guarantee that I don't have a bug in my program that might cause data problems.

So when I have a FK in place I can safely use an INNER JOIN knowing that my data will not have any orphans. When I do not have a FK I have to use an OUTER JOIN "just in case" there are orphans.

Any other tricky to speedup JOIN or other such time-consuming lookups

Always always ensure that the datatype of the fields in the JOIN are the same - or use an explicity CAST / CONVERT to force the datatype to be the same.

more ▼

answered Oct 30, 2009 at 06:54 AM

Kristen gravatar image

Kristen ♦
2.2k 6 7 10

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x252
x35

asked: Oct 27, 2009 at 11:11 AM

Seen: 3158 times

Last Updated: Nov 11, 2009 at 06:07 AM