Can we create index on varchar, when do we use it and what is the advantage of it in SQL server? Can someone help me in this?

Hi,

You can create index on varchar, of course, especially on fields that are supposed are going to be filtered frequently in your application. But the most important think is to choose correcty the fields for creating the index. You should take the most selective possible fields. If not, indexes might not be used, it will depend on "statistics", but the probability will be low. For example, you can make use of indexes if your queries are similar to:

select * from Table where ColumnIndexedName = 'Value'
select * from Table where ColumnIndexedName like 'Value%'

But, index will not be used if you use:

select * from Table where ColumnIndexedName like '%Value'

I would say that the only difference between using int or varchar columns to build indexes is the fact that the latter will take up less space to store values, and perhaps it might make the query faster. The same to create primary keys...


To conclude:

1) You can also create indexes on multiple fields combining int and varchar types, taking special care for filtering always on first field defined in the index. If you just filter using the second field index will not be used.

2) Try to avoid use of "*" operator. I mean, use only fields you need in your final result set. If all columns can be returned from the index the query will be "covered" and this will be great for the perfonmance. So, consider including new "aditional columns" to store in the index if possible according to your application requirements.


Regards!