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 recordsSELECT pp.NameFROM Production.Product AS ppWHERE pp.Name >= N'Short' AND pp.Name <= N'Sport' ;GO
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?
Thank-you for the quick response!