I am relatively new to databases. I am using MS SQL Server 2005.
I have this huge table (several millions rows) and I need to frequently do lookups with the following WHERE clause on two real-typed colunns:
WHERE ABS(Col1 - value1) < Error AND ABS(Col2 - value2) < Error
with value1, value2 and Error being some real-typed numbers.
My question is does it make sense to create an index for Col1 and Col2? And if so which sort of indexation? Given the sheer size of the table, try and error is a quite painful process.
My intuition from the trainings I found is that it would probably make sense to have a clustered index. But I am not sure if this is relevant to non-integer, non-unique fields, and if the ABS() function will not mess up any saving we could get from the indexation.
If anyone would have any light to share on this topic it would be greatly appreciated!