Indexing a real-typed column (MS SQL Server)?

cm2187
cm2187 used Ask the Experts™
on
Hi

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!

Thanks
Charles
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010

Commented:
Hello cm2187,

I would doubt an index will help you here.  Because you are basing it on absolute values, the db engine is still
going to have do a full-table scan to evaluate the expressions in the WHERE clause.

Regards,

Patrick
Commented:
The use of a partition index might be more helpful - if you can gather a skew distribution of the two columns you can then index based on the  density of the ranges

http://msdn.microsoft.com/en-us/library/ms187802.aspx
Top Expert 2012

Commented:
Unless you are prepared to reconsider the use of a Real data type, you will have bigger problems than performance.  Since Real is an aproximate value, you may find that 2<> 2 which would wreck havoc on your WHERE clause.  Use a fixed length numeric (decimal) data tyoe instead.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
In my case I don't need a very high precision. But would decimals be more efficient than real? (I would tend to think they probably would if they are really an integer)
Dale BurrellDirector

Commented:
Use the database tuning adviser - its pretty good at recommending the right indexes for simple queries such as this.
Top Expert 2012

Commented:
>>In my case I don't need a very high precision.<<
Since you have them in a WHERE clause you could end up where @Value < @Value is true

>>But would decimals be more efficient than real? <<
Unless you are doing astronomical calculations always use fixed length numeric values.

>>I would tend to think they probably would if they are really an integer<<
No, they are not integers, they are just accurate.

Author

Commented:
Thanks very much

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial