June 27, 2013 at 4:08 pm
kapil_kk (6/27/2013)
Danny Ocean (6/26/2013)
I never used between for string operation. I also never seen this kind of condition in working scenario.:doze:
Same here...
I dont think there is any need of using BETWEEN with aplabets 😎
I have seen far more order comparisons on strings than on numbers. I agree there is no need for using BETWEEN on strings - but there's no need for it on numbers either, and I think that as BETWEEN is asymmetric in its treatment of the two bounds the language would be better without it.
Tom
June 27, 2013 at 10:06 pm
B,BOB,CARL,D,D
🙂
June 28, 2013 at 2:06 am
L' Eomot Inversé (6/27/2013)
I have seen far more order comparisons on strings than on numbers. I agree there is no need for using BETWEEN on strings - but there's no need for it on numbers either, and I think that as BETWEEN is asymmetric in its treatment of the two bounds the language would be better without it.
Agreed. Ranges on strings aren't uncommon - eg list everyone whose surnames begin with the letters 'A' to 'E'. You wouldn't use BETWEEN or you'd need
where surname between 'A' and 'EZZZZZZZZZZZZZ'
so
where surname >= 'A' and surname < 'F'
June 28, 2013 at 6:55 am
L' Eomot Inversé (6/27/2013)
kapil_kk (6/27/2013)
Danny Ocean (6/26/2013)
I never used between for string operation. I also never seen this kind of condition in working scenario.:doze:
Same here...
I dont think there is any need of using BETWEEN with aplabets 😎
I have seen far more order comparisons on strings than on numbers. I agree there is no need for using BETWEEN on strings - but there's no need for it on numbers either, and I think that as BETWEEN is asymmetric in its treatment of the two bounds the language would be better without it.
Coding stored in varchar fields would be an exception there, whether it is parts numbers, medical codes or whatever else where you need to use non-numeric coding for. Medical reporting in particular has a vast number of cases where BETWEEN is perfectly appropriate
July 2, 2013 at 2:03 am
A good addition to the recent questions using BETWEEN.
The Answer is completely Logical to me and I don't see anything Asymmetrical about the behaviour of the Lower and Upper Bounds.
David
July 2, 2013 at 8:09 am
David Conn (7/2/2013)
A good addition to the recent questions using BETWEEN.The Answer is completely Logical to me and I don't see anything Asymmetrical about the behaviour of the Lower and Upper Bounds.
David
If there wasn't an asymmetry there we would have "between 1 and 10" meaning the same as "between 10 and 1", and the whole point of some of the recent questions is that those mean different things, ie that there is an asymmetry. In other words, "between" in SQL behaves like "-" (invert the operand order and you get a different result) rather than like "+" (invert the operand order and the result is not changed).
Tom
July 4, 2013 at 2:39 am
I found this question very straightforward. Agreed the case-insensitive specification was unnecessary, but it's obvious 'D.' is greater than 'D' and 'B.', 'Bob' and 'Carl' are all greater than 'B'.
BETWEEN does have its problems, though, as noted by others already.
July 4, 2013 at 3:50 am
It was a good question.
July 25, 2013 at 7:14 am
good and easy question
August 5, 2013 at 2:25 pm
If 'order by 1 ' is commented/removed, I still get same result set in different order.
In other words wrt to performance, does Between puts the column in sorted order n get the result set? or does it check row by row?
Thanks,
Naveen.
Every thought is a cause and every condition an effect
August 5, 2013 at 3:06 pm
Naveen PK (8/5/2013)
If 'order by 1 ' is commented/removed, I still get same result set in different order.In other words wrt to performance, does Between puts the column in sorted order n get the result set? or does it check row by row?
SQL Server will NOT create a plan that first sorts the entire table and then uses the sorted list to find the matching "subset" of data.
However, if there is an index available on the column for the BETWEEN, then SQL Server might decide to use that index to evaluate the BETWEEN quickly (by using a seek to directly locate the first matching row, and then scanning until after the last matching row). Or it might not. That depends on lots of other factors - the optimizer is a complex beast that considers a lot of options, tries to predict their cost, and then picks the one that's probably cheapest.
Adding an ORDER BY could result in the optimizer changing from a plan that does not use the index to one that does. Or the optimizer could still choose not to use that index, and instead check all rows and then sort the matching rows.
EDIT: Obviously, in the case of this question, there is no index available. Here, SQL Server will always scan the table, check each row, and then either sort the result if there's an ORDER BY clause, or return it unsorted otherwise.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply