One difference between nchar and nvarchar in the index mode in SQL Server 2008

In fact, these two types of the largest research point is the difference in the index. Now I write down one of my discoveries when I use SQL Server2008. (Note: This discussion does not establish any full-text indexing)

Usually we think like '% clothing% ' Such queries are unable to use the index, basically is the primary key scan.

But when we set up a "nonclustered" "unique" index for a "fixed-length character" field, we found that the index would be used if our query had only one result. Indexes are not used if the result is more than one or if the index is established by using the variable long character field.

Also, it is worth mentioning that if we filter using an and join condition, the index can still be used if the result is still one.

For example: SELECT * from movie where mname like '% of midnight% ' and '% '% '. Result returns a record the nonclustered unique index based on mname (char or nchar type) is valid. Hehe, some people laugh that fuzzy matching is to find a piece of ah, but sometimes we actually know the name of something but can not be very accurate and fully expressed, the index edge can be used. However, high performance database retrieval by these small means is far from enough, here is only to do a detailed discussion.

This may also be a nchar relative to the nvarchar of a practical significance of a performance improvement bar.

As to why there is such a difference, I very much hope that the master to enlighten when passing.