Do indexes suck in SQL?

Question

Say I have a table with a large number of rows and one of the columns which I want to index can have one of 20 values. If I were to put an index on the column would it be large?

If so, why? If I were to partition the data into the data into 20 tables, one for each value of the column, the index size would be trivial but the indexing effect would be the same.

Answer 1

It's not the indexes that will suck. It's putting indexes on the wrong columns that will suck.

Seriously though, why would you need a table with a single column? What would the meaning of that data be? What purpose would it serve?

And 20 tables? I suggest you read up on database design first, or otherwise explain to us the context of your question.

Answer 2

Indexes (or indices) don't suck. A lot of very smart people have spent a truly remarkable amount of time of the last several decades ensuring that this is so.

Your schema, however, lacking the same amount of expertise and effort, may suck very badly indeed.

Partitioning, in the case described is equivalent to applying a clustered index. If the table is sorted otherwise (or is in arbitrary order) then the index necessarily has to occupy much more space. Depending on the platform, a non-clustered index may reduce in size as the sortedness of the rows with respect to the indexed value increases.

YMMV.