Will Indexing Binary Columns Improve Performance for SQL WHERE BETWEEN statements?

Question

I have a table that has a binary column that represents IP Address data. One of the queries perform a BETWEEN comparison on that binary column.

If I index that column, will performance improve for the BETWEEN comparison in the SQL Statement?

Answer 1

In this example...

SELECT * FROM MyTable WHERE BinaryCol BETWEEN x and y

• the SELECT * could mean that the index is ignored, especially if the x/y cover a large proportion of the table
• If x/y are very selective, then it would cause a bookmark/RID lookup (especially SQL Server) to the clustered index.
• this applies to an index on BinaryCol alone or if it's covered

And is this example...

SELECT BinaryCol, AnotherCol, YetAnotherCol FROM MyTable WHERE BinaryCol BETWEEN x and y

• An index on BinaryCol that includes AnotherCol and YetAnotherCol will be covered and it's highly likely the index will be used
• if the x/y cover a very large proportion of the table, then maybe not

Unfortunately, "it depends"