Specifies a range to test.
test_expression
test_expression
must be the same data type as both begin_expression
and end_expression.
NOT
Specifies that the result of the predicate be negated.
begin_expression
Is any valid expression.
begin_expression
must be the same data type as both test_expression
and end_expression.
end_expression
Is any valid expression.
end_expression
must be the same data type as both test_expressionand
begin_expression.
AND
Acts as a placeholder that indicates test_expression
should be within the range indicated by begin_expression
and end_expression.
Boolean
BETWEEN returns TRUE if the value of test_expression is
greater than or equal to the value of begin_expression
and less than or equal to the value of end_expression.
NOT BETWEEN returns TRUE if the value of test_expression is
less than the value of begin_expression or greater than the value of end_expression.
To specify an exclusive range, use the greater than (>)
and less than operators (<). If any input to the BETWEEN or NOT BETWEEN
predicate is NULL, the result is UNKNOWN.
A. Using BETWEEN with datetime values
The following example retrieves rows in which datetime
values are between '20011212' and '20020105',
inclusive.
USE AdventureWorks2012;
GO
SELECT BusinessEntityID,
RateChangeDate
FROM
HumanResources.EmployeePayHistory
WHERE RateChangeDate BETWEEN
'20011212' AND '20020105';
Here is the result set.
BusinessEntityID RateChangeDate
----------- -----------------------
3 2001-12-12
00:00:00.000
4
2002-01-05 00:00:00.000
The query retrieves the
expected rows because the date values in the query and the datetime
values stored in the RateChangeDate column have been specified without the
time part of the date. When the time part is unspecified, it defaults to 12:00
A.M. Note that a row that contains a time part that is after 12:00 A.M. on
2002-01-05 would not be returned by this query because it falls outside the
range.
Problems using BETWEEN
The BETWEEN operator is a
handy SQL construct, but it can cause unexpected results when it isn’t
understood. Consider the following code snippet:
where x between .9 and 1.10
One of the questions you
should ask is this: What is x?
What if x has a float, real,
or double data type? These data types do not store exact representations of
numbers, only approximations. When 0.9 is stored in a real column or variable,
it may be between 0.9 and 1.1. Or it may not. When you set a real to 0.9, internally
it becomes something very close to but maybe not quite equal to 0.9. Even
though you entered 0.9, it could be stored as something slightly less than 0.9.
Now consider this:
Now consider this:
where x between 9 and 11
If x is an integer and is set
to 9, it really is 9 and it will pass the test. Because of data types,
never let it be said that all numbers are created equal.
Let's refactor the statement
to be free of hardcoded values:
where x between y and z
where x between y and z
What's wrong with that? I'll
simplify it for you. Everything is an integer, x, y, and z are all declared
with the integer data type. Once again, the question to ask is what are x, y,
and z? Or to be a little clearer, ask what are the values of x, y, and z. The
uncertainty arises over the potential values of y and z. Do you know that y will never be more than z? After
performing substitutions, what if we have the following?
where 10 between 9 and 11
The statement would be true.
But what if after performing substitutions we would have obtained this?
where 10 between 11 and 9
The statement would be false
even though 10 is between 9 and 11. We see that order matters - no surprise if
you've ever read the documentation. Order is easy to see and deal with when the
values are hardcoded. But when you have variables, what if you really don't
know a priori if y <= z? You need to think of BETWEEN as a number line.
Look at these next two
snippets to see what happens when the order of the BETWEEN values is
changed:
where -10 between -9 and -11 -- this
does NOT work
where -10 between -11 and -9 -- this works
where -10 between -11 and -9 -- this works
Here is a workaround to
this dilemma of not knowing the relative positions on y and z on the number
line:
where x between y and z or x between z and y
BETWEEN vs. IN/OR
Let’s create a sample table
Create table #tbl3 (a int primary key, b int)
Declare @var int
Set @var = 1
While @var <= 1000
Begin
Insert into tbl3
values (@var,@var+1)
Set @var = @var + 1
End
Create index ncidx_b on #tbl3(b)
We have Clustered unique index on the column a and non clustered
index on the column b
Consider the following query, which uses the search condition IN
select * from #tbl3
where a in(31,32,33,34,35,36,37)
You can replace the non sargable search condition in this query
with a BETWEEN clause as follows:
select * from #tbl3
where a between 31 and 37
However the execution plans are looking similar take a closer look
at the data retrieval mechanism.
IN Clause:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed
time = 0 ms.
(7 row(s) affected)
Table '#tbl3_________0000000798DE'.
Scan count 7,
logical reads 14,
physical reads 0,
read-ahead reads 0,
lob logical reads 0,
lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed
time = 0 ms.
Between Clause:
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed
time = 0 ms.
(7 row(s) affected)
Table '#tbl3_____0000000798DE'.
Scan count 1,
logical reads 2,
physical reads 0,
read-ahead reads 0,
lob logical reads 0,
lob physical reads 0,
lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed
time = 0 ms.
Sql server resolved the IN condition containing 7 values into 7 OR
conditions So clustered index accessed seven times to get the data for these
seven OR conditions.
No comments:
Post a Comment