SQL Index Search

Question

I have a strange business with a request. I have a table with two charts, StartNum and EndNum, which defines ranges of numbers. I have a pointer to these two columns. The request takes a given number and returns the range in which it falls. WHERE clause where @Num >= StartNum and @Num <= EndNum.

In the Profile, there is a big difference between finding a number near the top of the list and next to it. The lower the number, the longer the reading. The query plan shows that it uses a search on my index.

As an index, a balanced tree, of course, should not be that much of a difference. Can someone please explain this to me?

Small print: This refers to the release of SQL 2005 Workgoup. The table has about 200,000 rows. The index is nonclustered (the clustered index is in the identity column, but the data was inserted in StartNum order). The index has 716 pages, has a depth of 3, is fragmented by 3%.

Answer 1

Cracked him!

EndNum is always> = StartNum, so I just need to find the largest StartNum, which is <= @Num. I changed the index to StartNum desc and request to top 1 ... where StartNum <= @Num order by StartNum desc. Now, for any @Num value, it's just an index that searches with 5 reads and a duration of 0, which I did after.

Thanks for the help guys.

Answer 2

. , :

SELECT StartNum, EndNum
FROM Books
WHERE (@Num >= StartNum) AND (@Num <= EndNum)

SQL Server . , , , .

, :

SELECT BookID, Title, IDBN, Author, StartNum, EndNum
FROM Books
WHERE (@Num >= StartNum) AND (@Num <= EndNum)

SQL Server, , , , :

• ISBN

, :

• StartNum
• EndNum
• BookID

. StartNum implicily , , .

, " ", , .

---

: " ".

: 4, 89, 221, 442

4 , , . .

, 827 ?

: 1, 2, 6, [snip 825 ], 1087, 1128

, . . SQL Server , , (.. " " )

---

StartNum EndNum (, ), , . , SQL Server , , (, 5% ), .

, SQL Server StartNum EndNum , . @Num , SQL Server , . , .

Answer 3

StartNum ASC, EndNum DESC?

, , StartNum ASC, EndNum ASC. .

StartNum EndNum ( ) , , - .

, .

I'm having trouble duplicating behavior without real columns. I set up a test pattern with 200,000 start cycles StartNum-EndNum 100-199, 200-299, etc. I was able to get her to perform a clustered index scan, table scan, and search for a non-clustered index, but nothing is close to what you are talking about.