Sort the data based on the case sensitive (COLLATION)

SQL Server is, by default, case insensitive; however, it is possible to create a case-sensitive SQL Server database and even to make specific table columns case sensitive. The way to determine if a database or database object is to check its "COLLATION" property and look for "CI" or "CS" in the result.

• CI = Case Insensitive
• CS = Case Sensitive

We can check our sql server

  

It is case insensitive.

My requirement is to sort the data based on the case sensitive.

Let see below example

I am using “AdventureWorks2012” database and writing the below sql script

Normal sorting

SELECT p.Name, h.EndDate, h.ListPrice FROM Production.Product p INNER JOIN Production.ProductListPriceHistory h ON p.ProductID = h.ProductID ORDER BY p.Name ASC

“All-Purpose Bike Stand” is first record.

Now I am doing the sorting bases on the case sensitive (Capital and small letter)

SELECT p.Name, h.EndDate, h.ListPrice FROM Production.Product p INNER JOIN Production.ProductListPriceHistory h ON p.ProductID = h.ProductID ORDER BY p.Name COLLATE Latin1_General_BIN ASC

   

This sorting do first capital letter after that small.