SQLServer index for between where the values are sums
If I have a table with three decimal columns x_coordinate
, y_coordinate
and proximity
and a where clause that looks like either this:
@x_test between (x_coordinate - proximity) and (x_coordinate + proximity)
and
@y_test between (y_coordinate - proximity) and (y_coordinate + proximity)
or this:
power (@x_test - x_coordinate, 2) < power (proximity, 2)
and
power (@y_test - y_coordinate, 2) < power (proximity, 2)
Short of storing values in the table for x_min
, x_max
, y_min
and y_max
(I don't have the authority to change to database layout), can an index be constructed that will speed this up? My understanding of how indices work makes me think that due to the fact that the value in the table I'm comparing against is computed that there are not.
This logic is currently done by getting all of the results from a particular date range from the database (I left that portion out of the example queries), and filtering out by proximity in the client program. This was apparently done in the past for开发者_如何学C speed reasons, but with database technology nowadays I was going to perform some speed tests to see if that was still true.
You can add a computed column and create an index on it; there are some restrictions, check here. If you are not allowed to change table design, you can always create a VIEW with computed column and index it. Depends on SQLServer edition, this index might be used (Enterprise Edition) while querying underlying table implicitly.
精彩评论