BETWEEN keyword in sql server

BETWEEN is a range tester which can be used either in WHERE clause or HAVING clause. 

Syntax:

<Test_Expression>  [NOT]  BETWEEN <Initial_Expression>  AND  <Ending_Expression>

Where:

<Test_Expression>: It any valid expression which to be test that it value is within the <Initial_Expression> and <Ending_Expression> or not.  It is commonly name of the column of a table.

<Initial_Expression>  and  <Ending_Expression>: It is the boundary values.

NOT: It is optional keyword which negates the meaning.

Return type: It return a Boolean value.

Note:  Data type of <Test_Expression>, <Initial_Expression> and <Ending_Expression> must be same or implicitly convertible.

For example:  Suppose RollNo is column of type integer

RollNo BETWEEN 1 AND 100

Above statement will return true if and only if   1 <= RollNo <= 100

RollNo NOT BETWEEN 1 AND 100

Above statement will return true if and only if    RollNo   < 1 or RollNo > 100

Consider we have Employee table which look like this:

EmpID	EmpName	DateOfJoining	Age
100	Sachin Tendulker	2008-12-05	35
101	Ricky Ponting	2001-06-08	40
102	Brian Lara	2000-03-28	42
103	Stephen Fleming	1987-11-01	54
104	Shaun Pollock	2001-11-22	32
105	Shane Warne	1988-04-21	51
106	Shoaib Akhtar	1990-09-17	42
107	Muttiah Muralitharan	2002-07-07	36
108	Andrew Flintoff	2004-05-30	33

Using BETWEEN with varchar:

SELECT *

FROM Employee

WHERE EmpName BETWEEN 'Sho' AND 'T'

BETWEEN statement will return true if and only if EmpName is start from Sho upto T same as dictionary. It is not case sensitive.

Output will be:

EmpID	EmpName	DateOfJoining	Age
103	Stephen Fleming	1987-11-01	54
106	Shoaib Akhtar	1990-09-17	42

Using BETWEEN with date:

SELECT *

FROM Employee

WHERE DateOfJoining BETWEEN '2001-11-22' AND '2008-12-05'

It will select all the employees whose DataOfJoining is between 2001-11-22 to  2008-12-05 including 2001-11-22 and 2008-12-05

Output will be:

EmpID	EmpName	DateOfJoining	Age
100	Sachin Tendulker	2008-12-05	35
104	Shaun Pollock	2001-11-22	32
107	Muttiah Muralitharan	2002-07-07	36
108	Andrew Flintoff	2004-05-30	33

Using BETWEEN in having clause:

SELECT Age,COUNT(*) AS Total

FROM Employee

GROUP BY Age

HAVING Age BETWEEN 40 AND 50

Output:

Age	Total
40	1
42	2

When sql server is able to use indexes and when not
How to use database engine tuning advisor to get recommendations in sql server
Effect of collation in indexing in sql server.
Write sql queries in set based approach sql server
Sql server best practices for stored procedure