Index a varchar column

I asked Google but I am still confused.

1) Is there a problem in indexing a Varchar column. When I shouldn't, and When I should

2) Index a char column VS Varchar co开发者_开发技巧lumn.

Thanks

---

1 - Index it if you are querying it and it is selective enough. If it is a column where 90% of the values are the same, there won't be much point.

2 - This is not a question, but I will guess you want to know if you should. Yes, if you query it and it meets the criteria above.

---

• ad 1) Yes, 900 bytes limit, huge keys, lots of index pages, lots of I/O involved, inefficient index operations. Conclusion: DON'T unless your varchar is about 50 chars max.
• ad 2) Same as 1. The real difference between char vs. varchar are fixed size vs. variable size (ie. char(100)) always takes 100 bytes in data page, varchar(100) takes up to 100)

---

Generally performance

In theory/design, you have a logical model where, say, username is unique.

However, at implementation time you know that to use this is expensive (case, accents, length etc) compared to using a surrogate "userid" column which is more efficient as an index. Saying that, you'd have an index on name anyway because it should be unique.

The difference would be where you use this index: if it's in a child table as a foreign key column, it's not a good idea. Or as a clustered index.

As a single index for a table with no FKs, then it's neither here nor there.

Finally, I would just use the char/varchar for stuff like ISO language or currency codes (DE, EN, GBP, CHF etc). But my cut off varies to be honest...