I have a table in which I have created a clustered index in one of the column of type INT. I wanted to create a nonclustered index to increase the performance of the query. However, I do not have any column of type int.
I therefore thought of creating a nonclustered index on a varchar type column.
Will it be good to create an index on a column of type Varchar? Will it help improving the performance of a query? I know it is not a good thing to create an index on a varchar type column, but just wanted to know if it will improve query performance.
There is nothing whatever wrong with creating an index on a VARCHAR column, or set of columns.
Regarding the performance of VARCHAR/INT, as with everything in a RDBMS, it depends on what you are doing. What you may be thinking of is the fact that clustering a table on a VARCHAR key is (in SQL Server) marginally less efficient than clustering on a monotonically increasing numerical key, and can introduce fragmentation.
Or you may be thinking of what you have heard about writing JOINs on VARCHAR columns - it is true, it is a little less efficient than a JOIN on numeric type, but it is only a little less efficient, nothing that would lead you to never join on varchar cols.
None of this does not mean that you should not create indexes on VARCHAR columns. A needed index on a VARCHAR column will boost query performance, often by orders of magnitude. If you need an index on a VARCHAR, create it. It makes no sense to try to find an integer column to create the index on - the engine will never use it.