Why does SQL Server not pull expected results when using strings with comparison operators?

The question is based on an observation I had while working on something else. It is not stopping any production or development research work, but is more about satisfying my thirst for knowing why such a behaviour is being seen.

We can use comparison operators with strings as well. Hence, I tried to use the following query on a SQL Server 2012 instance with the sample AdventureWorks2012 database (the collation of the database and of the column is the default: SQL_Latin1_General_CP1_CI_AS):

USE AdventureWorks2012 ;
GO

--Returns 5 records
SELECT  pp.Name
FROM    Production.Product AS pp
WHERE   pp.Name >= N'Short' AND pp.Name <= N'Sport' ;
GO

Select allOpen in new window

The query only returns 5 records. This despite the fact that the search is an inclusive search and the Production.Product table contains records that begin with "Sport".


Now, when I replace "Sport" with "Sporu" (just moving one character up in the alphabet to verify whether characters after the word have any impact on the search) gives me 8 records.

USE AdventureWorks2012 ;
GO

--Returns 8 records
SELECT  pp.Name
FROM    Production.Product AS pp
WHERE   pp.Name >= N'Short' AND pp.Name <= N'Sporu' ;
GO

Select allOpen in new window

What's going on inside of SQL Server that allows it to fetch "Short-Sleeve Classic Jersey" for the starting word "Short" but prevents it from fetching "Sport-100 Helmet" for the ending word "Sport" despite the search being an inclusive search?