SQL Server – Retrieving a Range of Rows With Multiple Nonclustered Indexes on the Table
Take the following query:
SELECT * FROM accounts
WHERE
BalanceRemaining BETWEEN 100 AND 200
AND
CustomerNum BETWEEN 1000 AND 1200
If no relevant indexes exist on the table, a table scan would be performed. If a nonclustered index exists on the BalanceRemaining column, the query optimizer might use that index. This would depend on the number of records returned. If too large, the index would be ignored. If a nonclustered index exists on the CustomerNum column, it might be used by the query optimiser if numbers are no too large..
If the query optimiser chooses to use one of the indexes, then SQL Server would process the range specified in the where clause by issuing a data page request for each pointer (assuming there is no clustered index on the table, so we are dealing with Row ID’s).
As each row is returned, the remaining criteria would be applied to the row. You could say the data has been filtered. The problem with this type of technique is that it’s inefficient. If we have a non-clustered index on the BalanceRemaining column and the query optimiser decides to use that index to perform the above query. The index may have, for example, 200 leaf level index key values that meet the BalanceRemaining range, and 200 data page requests will be performed. When SQL Server applies the CustomerNum filter, most of the data rows from the result set could be eliminated
The nonclustered index has been used index to retrieve a row set, most of which were discarded. Retrieving data pages is a very expensive operation in terms of resources and data returned..
However, if a second nonclustered index is created on column CustomerNum, the query optimiser will use both of the indexes in a lot of cases in the optimiser plan. The result of the query that have a BalanceRemaining between 100 and 200 and the set of accounts that have a customer number between 1,000 and 1,200 is the set intersection of the set of accounts.
Why not visit our web site for all you need to know about Above Ground Pool Covers. For reviews on Above Ground Pool Heaters visit our other site
Sports Drinks Explained
Recent Comments