CODEWITHSUNDEEP
sqlsql-servert-sql
Costa
Costa

Reputation: 4085

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 column.

Thanks

Upvotes: 7

Views: 14197

Answers (3)

Pavel Urbančík
Pavel Urbančík

Reputation: 1496

  • 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)

Upvotes: 4

gbn
gbn

Reputation: 432261

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...

Upvotes: 0

JNK
JNK

Reputation: 65157

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.

Upvotes: 5

Related Questions