I have a case that come up regularly at work. I have many tables that use a 4 character string as a foreign key :
G is a sort of category, the
19 is the year, and the
1 is an instance.
Getting all rows of category
G is something we do very often. Usually like:
SELECT * FROM [Table] WHERE Left([ID], 1) = 'G'
There is a way to get this effect without manipulating a string, by joining to a table where this category is defined:
SELECT * FROM [Table] JOIN [Categories] ON [Table].CategoryID = [Categories].CategoryID WHERE [Categories].Letter = 'G'
My co worker insists that the first way is more performant, and rolls his eyes at me for doing it the second way.
Which one is better? Does joining by another table really add more work then checking the first character of a string?