Sqlserver 2012 - Column '' in table '' is of a type that is invalid for use as a key column in an index

Question

After seeing this error when creating a NONCLUSTERED index I wondered if it would really be necessary to create this index in the following scenario:

I have a table with 11 columns, a column I save a URL, IE is a column nvarchar(300), this column accepts null values.

At a given time I need to do an SQL to get the table values where this column is not null. That’s why I wanted to create an index, to speed this up.

I am using EF6 and Sqlserver 2012. Is there a gain in creating an index in this scenario? If there is how I would create it?

Answer 1

This index would not be useful for this type of query you described.

We should not create indexes for columns whose information varies very little between records.

For example, it makes no sense to create an index for a column that always contains either zero or one. It is unlikely that an index like this will be used by the bank because a table scan (go through the entire table instead of using the index) most of the time it would be more efficient.

Since your query only checks whether the column is null or not, what is being checked is whether it is in one of only two possible states, that is, as in the example of zeros and ums, it is very unlikely that this index will be used because the table scan will usually perform better.

Indexes that are not used in the queries should not be created because although they do not bring any benefit still need to be maintained by the bank: they occupy space and consume processing.

See this article Microsoft for very interesting general guidelines on the design index: SQL Server Index Design Guide.

Finally, although there are useful general guidelines, it is always good to analyze the implementation plan of queries to identify whether in a specific scenario an index should or should not be created or removed.