Varchar column index

Question

I asked Google, but I'm still confused.

1) Is there a problem in indexing a Varchar column. When I should not, and when I should

2) Index a column VS varchar column char.

thanks

Answer 1

1 - Index it if you request it and it is sufficiently selective. If it is a column where 90% of the values ​​are the same, there will be no dots.

2 - This is not a question, but I think you want to know if you want. Yes, if you request it, and it meets the above criteria.

Answer 2

• ad 1) Yes, a limit of 900 bytes, huge keys, many index pages, a large number of input / output operations, inefficient index operations. Conclusion: NO if your varchar does not exceed 50 characters.
• ad 2) Same as 1. The real difference between char vs. varchar fixed size and variable size (i.e. char(100) ) always takes 100 bytes on the data page, varchar(100) takes up to 100)

Answer 3

Usually performance

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

However, during implementation, you know that using it is expensive (case, accents, length, etc.) compared to using the surrogate column "userid", which is more efficient as an index. Having said that, you will have an index by name, as it must be unique.

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

As a single index for a table without FKs, it is neither here nor there.

Finally, I would just use char / varchar for things like the ISO language or currency codes (DE, EN, GBP, CHF, etc.). But my cutting is honest ...