January 11, 2011 at 9:58 am
I have a somewhat simple example with some followup questions on best practices:
- Let's say we have a Thing table which has two columns ThingID and ThingTypeID. ThingID is an IDENTITY column and ThingTypeID contains INTs stored in LUThingType.
- LUThingType contains 20 rows with ThingTypeID = 1-20 and a different Thing Type Name assigned to each one.
Questions:
- As a general rule, if I want to get a listing of each Thing and its associated ThingTypeName, I believe it is more efficient to perform an INNER JOIN than an OUTER JOIN right?
- What if we don’t force a Thing to have a ThingTypeID assigned. That would mean that Thing’s ThingTypeID = 0. That would of course force us to perform an OUTER JOIN.
- What if we insert a row into LUThingType and set ThingTypeID = 0 and set ThingTypeName = empty string. This will allow us to perform an INNER JOIN. Per the first question, is this preferred in terms of performance and query processing?
- If we do insert that “0” row into LUThingType as mentioned above, we can now enforce an official Foreign Key. Is there any performance impact on SELECT statements if have a Foreign Key?
January 11, 2011 at 11:06 am
JEFFREY SCHELL (1/11/2011)
I have a somewhat simple example with some followup questions on best practices:- Let's say we have a Thing table which has two columns ThingID and ThingTypeID. ThingID is an IDENTITY column and ThingTypeID contains INTs stored in LUThingType.
- LUThingType contains 20 rows with ThingTypeID = 1-20 and a different Thing Type Name assigned to each one.
Questions:
- As a general rule, if I want to get a listing of each Thing and its associated ThingTypeName, I believe it is more efficient to perform an INNER JOIN than an OUTER JOIN right?
In general, yes.
- What if we don’t force a Thing to have a ThingTypeID assigned. That would mean that Thing’s ThingTypeID = 0. That would of course force us to perform an OUTER JOIN.
Assuming you have referential constraints, ThingTypeId must be NULL or you have to have a value of 0 in the lookup table.
- What if we insert a row into LUThingType and set ThingTypeID = 0 and set ThingTypeName = empty string. This will allow us to perform an INNER JOIN. Per the first question, is this preferred in terms of performance and query processing?
That's how a lot of people do it. There is a school of though that believes no NULL values should ever be stored in the database. For the small number of rows we're talking about, most of the time, it won't make that much of a difference, assuming an index is in place.
- If we do insert that “0” row into LUThingType as mentioned above, we can now enforce an official Foreign Key. Is there any performance impact on SELECT statements if have a Foreign Key?
Yes, they're improved. See this blog entry[/url].
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
January 13, 2011 at 1:40 pm
Thank you Grant. That helps clarify things for me!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply