SQL Server select-where statement issue

Question

I am using SQL Server 2008 Enterprise on Windows Server 2008 Enterprise. I have a question about tsql in SQL Server 2008. For select-where statement, there are two differnet forms,

(1) select where foo between [some value] and [some other value],

(2) select where foo >= [some value] and foo <= [some other value]? I am not sure whether between-and is always the same as using <= and >= sign?

BTW: whether they are always the same - even for differnet types of data (e.g. compare numeric value, comparing string values), appreciate if anyone could provide some documents to prove whether they are always the same, so that I can learn more from it.

thanks in advance, George

Answer 1

Yes they are always the same. The entry in Books Online for BETWEEN says

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.

Indeed you can see this easily by looking at the execution plans. You will see that Between doesn't even appear in the text. It has been replaced with >= and <= there is no distinction made between the two.

SELECT * FROM master.dbo.spt_values 
WHERE number between 1 and 3 /*Numeric*/

SELECT * FROM master.dbo.spt_values 
WHERE name between 'a' and 'b' /*String*/

select * from sys.objects 
WHERE create_date between GETDATE() and GETDATE()+100 /*Date*/