SQL change column collation to case sensitive

How to change the Collation of table column to Case insensitive or Case sensitive?

Lets prepare a sample table called DumpData, and insert few records as shown below.

CREATE Table dbo.DumpData
Name VARCHAR(50)

INSERT INTO dbo.DumpData
(ID, Name)
VALUES(1, 'abc'),
(2, 'AbC'),
(3, 'aBc'),
(4, 'ABC'),
(5, 'abC'),
(6, 'aBC'),
(7, 'AbC')

Now we have a table, and you can see the table output as shown below that contains some string values with upper case, some in lower case or some values are combine of upper and lower characters.

SELECT * FROM dbo.DumpData

Suppose you want to see only those names that have exact match with value ‘abc’, as all characters in ‘abc’ are in lower case, so you want to see all name which contains values ‘abc’  in lower case. Basically you want case sensitive search that returns exact matching records from table.

Lets see, what happens when you execute the following query.


SELECT * FROM dbo.DumpData 
 WHERE [NAME]= 'abc'

You can see for search value ‘abc’, it returns seven matching values which are either in upper case, lower case or mix. While for ‘abc’ there are only single record in table.

It means case senstivitiy is not checked on column so query returns all records contains ‘abc’ only, does not matter whether it is in upper case, lower case, or mix .

It happens because of default Collation of the SQL Server installation that is SQL_Latin1_General_CP1_CI_AI, that is not case sensitive.


Lets check the collation for column Name, using following query that check the collation for all columns in a SQL Server database table. Here we check for table DumpData.


As you can see, the colloation for column Name is Latin1_General_CI_AI, which is case insensitive.

CI in Latin1_General_CI_AI represents case insensitive.

To make query case sensitive and return only ‘abc’ from table, you can modify above query as following

SELECT * FROM dbo.DumpData 
WHERE [NAME] COLLATE Latin1_General_CS_AS = 'abc'

And you can see the output of query, it returns all single records which is exact match with search value ‘abc’. Means this time case sensitive is applied on column Name.

You can also make column case sensitive by changing column’s collation from case insensitive SQL_Latin1_General_CP1_CI_AI to case sensitive Latin1_General_CS_AS. 

Lets use Alter Command to change the column Name collation from case insensitive to case sensitive.

ALTER TABLE dbo.DumpData
COLLATE Latin1_General_CS_AS

Lets check, whether the column collation is changed or not, by executing same query that we used above to check collation for all columns in a SQL Server table.


You can see, now column collation has been changed to case Sensitive Latin1_General_CS_AS.

CS in Latin1_General_CS_AS represents Case Sensitive.


Now you can use simple query to search for case sensitive value for column Name.

Lets execute the below queries and see the output.

SELECT * FROM dbo.DumpData
WHERE [NAME]  = 'abc'

SELECT * FROM dbo.DumpData

SELECT * FROM dbo.DumpData


You can see, now case sensitive search is performed on column name and returns exact match for search values.


SQL Basics TutorialSQL Advance TutorialSSRSInterview Q & A
SQL Create tableSQL Server Stored ProcedureCreate a New SSRS Project List Of SQL Server basics to Advance Level Interview Q & A
SQL ALTER TABLESQL Server MergeCreate a Shared Data Source in SSRSSQL Server Question & Answer Quiz
SQL DropSQL Server PivotCreate a SSRS Tabular Report / Detail Report
..... More.... More....More
Power BI TutorialAzure TutorialPython TutorialSQL Server Tips & Tricks
Download and Install Power BI DesktopCreate an Azure storage accountLearn Python & ML Step by stepEnable Dark theme in SQL Server Management studio
Connect Power BI to SQL ServerUpload files to Azure storage containerSQL Server Template Explorer
Create Report ToolTip Pages in Power BICreate Azure SQL Database ServerDisplaying line numbers in Query Editor Window

 137 total views,  3 views today

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.