CODEWITHSUNDEEP
sqlsql-serverperformanceindexing
Prashant Kumar
Prashant Kumar

Reputation: 249

Having non clustered index on my column used for joins.Is it good or bad?

I have a question that suppose there is a column that I used to put joins with other tables, should I have a non-clustered index on that column to improve performance?

The column is a nvarchar column which is not unique.

Thanks,

Upvotes: 1

Views: 68

Answers (2)

bjnr
bjnr

Reputation: 3437

Complementary to the post of @alzaimar, it is also important the selectivity of that column (= number of distinct values). If a value from that columns represents < 1-2% from total number of rows:

  • SS will perform an index seek
  • otherwise a clustered index scan will be performed.

To see statistics for that index:

DBCC SHOW_STATISTICS('<tablename>', '<indexname>')

Upvotes: 0

alzaimar
alzaimar

Reputation: 4622

An index is better than no index and a clustered index is usually better than a nonclustered index.

However, a nvarchar column used as a foreign key (I suppose) is not a good idea.

There is a very simple and important rule for columns used for indexing: keep it short, i.e. choose the datatype and size appropriate for the job.

Don't(!) use things like an product- or customer number as primary key. Try always(!) to use an anonymous ID such as an autoinc(Identity) or a unique identifier. The latter is important if you want to share data among several databases and uniquely identify each item.

Hope that answers your question a bit.

Upvotes: 1

Related Questions