Compare performance difference of T-SQL Between and '<' '>' operator?

Question

I've tried searching through search engines,MSDN,etc. but can't anything. Sorry if this has been asked before. Is there any performance difference between using the T-SQL "Between" keyword or using comparison operators?

Answer 1

You can check this easily enough by checking the query plans in both situations. There is no difference of which I am aware. There is a logical difference though between BETWEEN and "<" and ">"... BETWEEN is inclusive. It's equivalent to "<=" and "=>".

Answer 2

The query engine converts between into >= and <= (take a look at the query plan) so in practise they're identical and in theory >= <= is faster because the engine won't have to translate. Good luck noticing a difference though.

I use between anyway, I find it reads easier

Very complex queries/nested views with numerous between comparisons might benefit from changing into >= <= as this might potentially prevent optimisation timeouts by reducing the time spent on refactoring the query (just a theory, untested by me & I've never noticed it)

Answer 3

Love when folks give code to do your own testing, you need to do a larger subset / repeated test to account for indexes being loaded into memory, etc... before jumping to conclusions though. Here is the same code with a larger table and 10 iterations

DECLARE
    @Startdatetime datetime ,
    @Diff int = 0 ,
    @Addrowcount int = 1000 ,
    @ptr int = 1;


SET NOCOUNT ON;

--Create a tempory table to perform our tests on
DROP TABLE dbo.perftest

CREATE TABLE dbo.perftest( id int NOT NULL
                                       IDENTITY(1 , 1)
                                       PRIMARY KEY ,
                           mytext nvarchar( 50 )NOT NULL );

--Now add some sample rows

SET @Addrowcount = 20000;

WHILE(@Addrowcount > 0)

    BEGIN

        INSERT INTO dbo.perftest( mytext )
        VALUES( 'thetext' );

        SET @Addrowcount = @Addrowcount - 1;

    END;

WHILE @ptr < 10 -- do this a few times to account for indexes being loaded into memory

BEGIN

    SELECT @Startdatetime = GETDATE();

    -- do method 1 here

    SELECT mytext
      FROM dbo.perftest
      WHERE(id >= (100 + (@ptr * 1000)))
       AND (id <= (500 + (@ptr * 1000)));

    --end method1

    SELECT @Diff = DATEDIFF( millisecond , @Startdatetime , GETDATE());

    PRINT ':Method 1: ' + CAST(@Diff AS nvarchar( 20 )) + ' ms';

    --reset start time

    SELECT @Startdatetime = GETDATE();

    --do method2 here

    SELECT mytext
      FROM dbo.perftest
      WHERE id BETWEEN (300 + (@ptr * 1000))
        AND (800 + (@ptr * 1000));

    --end method2

    SELECT @Diff = DATEDIFF( millisecond , @Startdatetime , GETDATE());

    PRINT ':Method 2: ' + CAST(@Diff AS nvarchar( 20 )) + ' ms';

    SET @ptr = @ptr + 1

END

Gives you a very different set of results:

--Method 1    -- 10 ms
--Method 2    -- 33 ms
--Method 1    -- 40 ms
--Method 2    -- 26 ms
--Method 1    -- 23 ms
--Method 2    -- 23 ms
--Method 1    -- 13 ms
--Method 2    -- 16 ms
--Method 1    -- 13 ms
--Method 2    -- 20 ms
--Method 1    -- 6 ms
--Method 2    -- 16 ms
--Method 1    -- 26 ms
--Method 2    -- 16 ms
--Method 1    -- 13 ms
--Method 2    -- 13 ms
--Method 1    -- 16 ms
--Method 2    -- 13 ms

I would say from this (still pretty unscientific) test, not much difference either way.