Best way to store tags in a sql server table?

Question

What's the best way to store tags for a record? Just use a varchar field? What about when selecting rows that contains tag x? Use the like operator?

thanks!

Answer 1

Depends on two things:
1) The amount of tags/tagged records
2) Whether or not you have a religious opinion on normalization :-)

Unless dealing with very large volumes of data, I'd suggest having a 'Tags' table mapping varchar values to integer identifiers then second table mapping tagged records to their tag ids. I'd suggest implementing this first, then check if it doesn't meet your performance needs. In that case, keep a single table with a id for the tagged row and the actual text of the tag, but in this I'd suggest you use a char column as it will kill your query if the optimizer does a full table scan against a large table with a varchar column.

Answer 2

Use a tags table with the smallest allowable primary key. If there are less than 255 tags use a byte (tinyint) or else a word (smallint). The smaller the key the smaller and faster the index on the foreign key in the main table.