Random number in results searching for >= and <= numbers?

1

IDNUMBER >= '1000205830' and IDNUMBER <= '1000206010' ORDER BY ID_NUM DESC

I'm also getting IDNUMBER 1000206 included in the range?

2

IDNUMBER >= '1000205830' and IDNUMBER <= '1000206010' ORDER BY ID_NUM DESC

Here you should use cast as int

IDNUMBER >= cast ( '1000205830' as int )
IDNUMBER <= cast('1000206010' as int )

Then it should work
Data type of idnumber should also be int

3

thanks much appreciated! I'll give this a try.

4

If IDNUMBER is a char/varchar column, then you can't realistically do a numeric comparison on it. You should do a comparison that would be valid for a char/varchar column:

WHERE IDNUMBER >= '1000205830' and IDNUMBER <= '1000206010' AND LEN(IDNUMBER) = 10

You can try casting the original column to int, but:

  1. that could be vastly slower
  2. you could conversion error since non-int values may be in the column
  3. you could get overflow error because the value won't fit into an int, such as 3000000000.

If you insist on CASTing the column -- and I don't recommend it -- CAST to a bigint instead.

1 Like
5

another thing is .. Performance ,,..

YOU CAN CREATE AN INDEX
...just on those rows you are comparing ...

Its called filtered index .. only on this part
IDNUMBER >= '1000205830' and IDNUMBER <= '1000206010'

Filtered Index is a new feature in SQL SERVER . Filtered Index is used to index a portion of rows in a table that means it applies a filter on INDEX which improves query performance, reduces index maintenance costs, and reduce index storage costs compared with full-table indexes .

6
7

hi ifor

thanks for unexpected side effect

please suggest any alternatives !!!!!

8

I was able to get what I needed from this post. Thanks everyone much appreciated!