Which is better? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Which is better?

Author

Topic

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-25 : 11:58:34

DECLARE @COLORS TABLE(colorID INT, colorName VARCHAR(10))
DECLARE @ITEMS TABLE(itemID INT , itemName VARCHAR(10), colorID INT)

INSERT INTO @COLORS VALUES (1,'RED'),(2,'BLUE')
INSERT INTO @ITEMS VALUES (1,'ITEMS',1),(2,'ITEMS2',2)

How should a query be written if we want all red colored items:

--HARD-CODING THE COLORID
SELECT * from @ITEMS WHERE colorID =1

OR

SELECT I.*
FROM @ITEMS I INNER JOIN @COLORS C ON I.colorID = C.colorID
WHERE C.colorName ='RED'

Is it advisable to hard-code the ID as in query1 or should we do a join to the master table and refer the text column as in query2?
On the same lines should we use ColorID IN (1,2) or join and then use ColorName IN ('RED','BLUE')

Which one is better and why?

Thanks

--------------------
Rock n Roll with SQL

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-11-25 : 12:42:25

I'd use #2, or some variation of it. The reason for the lookup table for colors is to allow the ColorID to change if needed.



SELECT I.*
FROM @ITEMS I 
WHERE
    I.colorID IN (SELECT c.colorID FROM @COLORS c WHERE c.colorName IN ('RED'))

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-25 : 13:03:37

quote:
Originally posted by ScottPletcher

The reason for the lookup table for colors is to allow the ColorID to change if needed.

Not to nitpick, but I'd say the reason for normalization is for easy changing of the value not the ID.

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-25 : 13:17:00

Ok thanks for your replies but most of the times I hear this argument, this being a master lookup table, it's "never" going to change except new additions, so it is safe to use hard-coded id's as it saves an extra join and the match is on an integer column which is faster than text comparison. So is it ok to hard-code provided we are very sure of no modifications in the lookup tables, given the benefits it may provide as against an additional join and text comparison?

But then again, is not maintenance an issue when we code something like: IN (2,4,7,8). For a new developer these are just numbers, so apart from easy modification of the text value, is there any other benefit in using a join + a text match?

--------------------
Rock n Roll with SQL

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2013-11-25 : 13:35:30

Why are you concerned about an extra join and text comparison? There will be no performance hit here if things are designed properly (code and indexes).

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-25 : 13:40:30

Honestly, it's 6 of one and half a dozen of the other.

If readability is your concern, then you can do as Scott suggest. Another alternative, if you want to hard-code IDs, is to use "constants" in your code. For example:

DECLARE @Color_Red INT = 1;
DECLARE @Color_Green INT = 2;
-- etc..

SELECT 
	* 
FROM 
	@ITEMS 
WHERE 
	colorID = @Color_Red

Obviously, that has maintenance implications as well.

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-11-25 : 15:13:45

quote:
Originally posted by Lamprey

quote:
Originally posted by ScottPletcher

The reason for the lookup table for colors is to allow the ColorID to change if needed.
Not to nitpick, but I'd say the reason for normalization is for easy changing of the value not the ID.

To be more explicit, "to allow the ColorID for a given color name to change".

That is, the color "RED" should remain "RED" no matter what the id is. I would never allow people to hard-code "1" as meaning "RED", because then you are violating the cardinal rule of putting meaning into the arbitrarily assigned key-code value.

rocknpop
Posting Yak Master

201 Posts

Posted - 2013-11-25 : 19:50:49

Lamprey, yes that is one way to go for improving readability. Thanks for pointing this out.

Tara, what if the Items table has anywhere between 1 million to 35 million records. Even If we create index on Items.ColorID and assuming Colors.ColorID is a PK and Colors.ColorName has another index on it, would the index on colorName would ever be used given that Colors table may hardly have at the most 10 records. What would be the best index strategy in this case?

Scott, I have always resisted from hard-coding ID's but could not back up my own statement with any proof as to why should we not use ID's. I have always been trumped by answers like this being a master table no row will be deleted; maybe we can put an instead of delete trigger in place; faster to join on ID etc.
I guess you have very rightly pointed out about the cardinal rule, this is what I have been missing all along, thank you.

Thanks to all of you for your replies.

--------------------
Rock n Roll with SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources