SQL Server check case-sensitivity?
Solution 1
Collation can be set at various levels:
- Server
- Database
- Column
So you could have a Case Sensitive Column in a Case Insensitive database. I have not yet come across a situation where a business case could be made for case sensitivity of a single column of data, but I suppose there could be.
Check Server Collation
SELECT SERVERPROPERTY('COLLATION')
Check Database Collation
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;
Check Column Collation
select table_name, column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @table_name
Solution 2
If you installed SQL Server with the default collation options, you might find that the following queries return the same results:
CREATE TABLE mytable
(
mycolumn VARCHAR(10)
)
GO
SET NOCOUNT ON
INSERT mytable VALUES('Case')
GO
SELECT mycolumn FROM mytable WHERE mycolumn='Case'
SELECT mycolumn FROM mytable WHERE mycolumn='caSE'
SELECT mycolumn FROM mytable WHERE mycolumn='case'
You can alter your query by forcing collation at the column level:
SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = 'caSE'
SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = 'case'
SELECT myColumn FROM myTable
WHERE myColumn COLLATE Latin1_General_CS_AS = 'Case'
-- if myColumn has an index, you will likely benefit by adding
-- AND myColumn = 'case'
SELECT DATABASEPROPERTYEX('<database name>', 'Collation')
As changing this setting can impact applications and SQL queries, I would isolate this test first. From SQL Server 2000, you can easily run an ALTER TABLE statement to change the sort order of a specific column, forcing it to be case sensitive. First, execute the following query to determine what you need to change it back to:
EXEC sp_help 'mytable'
The second recordset should contain the following information, in a default scenario:
Column_Name Collation
mycolumn SQL_Latin1_General_CP1_CI_AS
Whatever the 'Collation' column returns, you now know what you need to change it back to after you make the following change, which will force case sensitivity:
ALTER TABLE mytable
ALTER COLUMN mycolumn VARCHAR(10)
COLLATE Latin1_General_CS_AS
GO
SELECT mycolumn FROM mytable WHERE mycolumn='Case'
SELECT mycolumn FROM mytable WHERE mycolumn='caSE'
SELECT mycolumn FROM mytable WHERE mycolumn='case'
If this screws things up, you can change it back, simply by issuing a new ALTER TABLE statement (be sure to replace my COLLATE identifier with the one you found previously):
ALTER TABLE mytable
ALTER COLUMN mycolumn VARCHAR(10)
COLLATE SQL_Latin1_General_CP1_CI_AS
If you are stuck with SQL Server 7.0, you can try this workaround, which might be a little more of a performance hit (you should only get a result for the FIRST match):
SELECT mycolumn FROM mytable WHERE
mycolumn = 'case' AND
CAST(mycolumn AS VARBINARY(10)) = CAST('Case' AS VARBINARY(10))
SELECT mycolumn FROM mytable WHERE
mycolumn = 'case' AND
CAST(mycolumn AS VARBINARY(10)) = CAST('caSE' AS VARBINARY(10))
SELECT mycolumn FROM mytable WHERE
mycolumn = 'case' AND
CAST(mycolumn AS VARBINARY(10)) = CAST('case' AS VARBINARY(10))
-- if myColumn has an index, you will likely benefit by adding
-- AND myColumn = 'case'
Solution 3
SQL server determines case sensitivity by COLLATION
.
COLLATION
can be set at various levels.
- Server-level
- Database-level
- Column-level
- Expression-level
One can check the COLLATION
at each level as mentioned in Raj More's answer.
Check Server Collation
SELECT SERVERPROPERTY('COLLATION')
Check Database Collation
SELECT DATABASEPROPERTYEX('AdventureWorks', 'Collation') SQLCollation;
Check Column Collation
select table_name, column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = @table_name
Check Expression Collation
For expression level COLLATION
you need to look at the expression. :)
It would be generally at the end of the expression as in below example.
SELECT name FROM customer ORDER BY name COLLATE Latin1_General_CS_AI;
Collation Description
For getting description of each COLLATION
value try this.
SELECT * FROM fn_helpcollations()
And you should see something like this.
You can always put a WHERE
clause to filter and see description only for your COLLATION
.
You can find a list of collations here.
Solution 4
You're interested in the collation. You could build something based on this snippet:
SELECT DATABASEPROPERTYEX('master', 'Collation');
Update
Based on your edit — If @test
and @TEST
can ever refer to two different variables, it's not SQL Server. If you see problems where the same variable is not equal to itself, check if that variable is NULL
, because NULL = NULL
returns `false.
Solution 5
The best way to work with already created tables is that, Go to Sql Server Query Editor
Type: sp_help <tablename>
This will show table's structure , see the details for the desired field under COLLATE column.
then type in the query like :
SELECT myColumn FROM myTable
WHERE myColumn COLLATE SQL_Latin1_General_CP1_CI_AS = 'Case'
It could be different character schema <SQL_Latin1_General_CP1_CI_AS
>, so better to find out the exact schema that has been used against that column.
![Kyle](https://i.stack.imgur.com/Ppot0.png?s=256&g=1)
Kyle
Self employed developer of SQLPro Studio for macOS, iOS and Windows.
Updated on January 24, 2021Comments
-
Kyle over 3 years
How can I check to see if a database in SQL Server is case-sensitive? I have previously been running the query:
SELECT CASE WHEN 'A' = 'a' THEN 'NOT CASE SENSITIVE' ELSE 'CASE SENSITIVE' END
But I am looking for other ways as this has actually given me issues in the past.
Edit - A little more info: An existing product has many pre-written stored procedures. In a stored procedure
@test != @TEST
depending on the sensitivity of the server itself. So what I'm looking for is the best way to check the server for its sensitivity.-
OMG Ponies almost 15 yearsWhen in doubt, drive both sides of the comparison to uppercase with UPPER...
-
-
Joel Coehoorn almost 15 yearsThat's not what he's talking about. He means how data is compared, not how code is processed.
-
Vinay Sajip almost 15 yearsI think the question is about comparing data in a case-insensitive way - not whether T-SQL statements are case-sensitive.
-
bkdimri almost 15 yearsThe query he provided makes it pretty clear what he's asking about.
-
Mark Callison almost 15 yearsI guess we will have to agree to disagree on that one
-
Chris J almost 15 yearsLooking at the OPs recent edit where he's talking about parameters and variables of mixed case, I think Mark's got a valid point here. It's not purely data in columns. Going to +1.
-
Tim Büthe over 12 yearsFor the sake of completeness, you can set the collation also in SQL using
SELECT * FROM foo where x = 'y' COLLATE sql_latin1_general_cp1_cs_as
-
Coops over 11 years
NULL = NULL
test would be dependant on ANSI_NULLS isn't it?where NULL IS NULL
would return true (Handling NULL Values in MSSQL Queries)[peter-urda.com/2010/11/handling-null-values-in-mssql-queries] -
Coops over 11 yearsDid +1 but maybe expand as 'SQL Server is not case sensitive' is not entirely true as a broad statement when referring to data comparison
-
Rob Levine over 11 years"SQL Server is not case sensitive" is not true. Whether SQL Server is case sensitive for data, or for schema (e.g. table names, field names) is dependent on collation settings. Remember that table names/field names are just metadata which is also affected by collation settings.
-
Zohar Peled about 9 yearsJust adding one more piece to the puzzle: if the collation name contains CI it's case-insensitive, if it contains CS it's case-sensitive. Read more in MSDN
-
gdoron is supporting Monica over 8 yearsThere are many things there are case sensitive, like URLs, passwords so there you go, examples of case sensitive columns.
-
Jeff Meden over 7 years@Gordon I hope you're not storing passwords in plaintext in your database!
-
Tom about 7 yearsAt MarkCallison: I doubt anyone would be asking whether Reserved Words are case-sensitive, but user-defined Identifiers would be a fair question. @RobLevine: What about Variables (which I doubt are stored in System Tables at least not permanent ones) - is there there some SQL Server setting that would make them case-sensitive (ala C#)?
-
Mitch Wheat about 5 years@gdoron: I've yet to see a case sensitive URL. stackoverflow.com/questions/7996919/… . Sure, the get part can be, but not the domain.
-
gdoron is supporting Monica about 5 years@JeffMeden 3 years later I just saw your comment. No, I'm not storing passwords as plain text, anyway, hashed passwords are case sensitive.
-
gdoron is supporting Monica about 5 years@MitchWheat we just had a big bug in my company because Google changed one of its websites to be case sensitive. They are real and they are spectacular.
-
Mitch Wheat about 5 years@gdoron: you are saying that google changed the domain part of a URL to be case sensitive? What's the URL?
-
gdoron is supporting Monica about 5 years@MitchWheat, no, where did you see my writing about the domain? I wrote URLs, domains are case insensitive. paths and querystrings on the other hand aren't.
-
Mitch Wheat about 5 years@gdoron which is what my comment states above! -> "Sure, the get part can be, but not the domain."