question

guumbo avatar image
guumbo asked

Collation Sequence Case Sensitivity

Is there a way to query SQL Server through one of the system tables/views or one of the Information Schema views to determine if a given collation sequence is case sensitive or not? Obviously a person can tell by looking at the collation sequence name and see if it is case sensitive or not, but how can you write a query which clearly and is guaranteed to display whether or not a collation sequence is case sensitive?
collationcase-sensitive-sql-query
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi there. I know it's been almost 9 years, but you now have your answer :-)

0 Likes 0 ·
Pavel Pawlowski avatar image
Pavel Pawlowski answered
If you do a select select * from sys.fn_helpcollations() In the description column you have written whether particular collation is Case Sensitive or Case Insensitive. Also general rule is the the CS in the collation name means Case Sensitive and CI means Case Insensitive. If you take a look in the sys.databases and sys.columns system views, you will see collations used for that db/column and by the names and eventually description returned by sys.fn_helpcollations() you can determine whether the collation is Case Sensitive or Insensitive.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I was looking for was something a little more definitive. But this is not definitive as it is parsing natural english to determine the results. From the query you provided I use the following to get what I need: select name , case when charindex('case-sensitive',description)>0 then 1 else 0 end [Case Sensitive] from sys.fn_helpcollations() name Case Sensitive Albanian_BIN 0 Albanian_BIN2 0 Albanian_CI_AI 0 Albanian_CI_AI_WS 0 Albanian_CI_AI_KS 0 Albanian_CI_AS 0 Albanian_CI_AS_WS 0 Albanian_CI_AS_KS 0 Albanian_CS_AI 1 Albanian_CS_AI_WS 1 Albanian_CS_AI_KS 1
0 Likes 0 ·
Sorry the table output didn't format properly. Seeing as my Karma is not > 60 the following is all I could do :( name,Case Sensitive Albanian_BIN,0 Albanian_BIN2,0 Albanian_CI_AI,0 Albanian_CI_AI_WS,0 Albanian_CI_AI_KS,0 Albanian_CI_AI_KS_WS,0 Albanian_CI_AS,0 Albanian_CI_AS_WS,0 Albanian_CI_AS_KS,0 Albanian_CI_AS_KS_WS,0 Albanian_CS_AI,1 Albanian_CS_AI_WS,1 Albanian_CS_AI_KS,1 Albanian_CS_AI_KS_WS,1
0 Likes 0 ·
Binary Sorts are case sensitive. MSDN has a collation article that explains the collation naming scheme - http://msdn.microsoft.com/en-us/library/ms143515.aspx looking at the _CI _CS _BIN portions of the collation name IS definitive, and is how the collation article determined the case sensitivity of a collation. If you prefer to NOT parse the natural english, you must look for the _CI _CS and _BIN portions of the collation name. Here is your natural english query with an update for case-sensitive binary `select name , case when charindex('case-sensitive',description)>0 OR charindex('binary',description)>0 then 1 else 0 end [Case Sensitive] from sys.fn_helpcollations()` Here is your query modified to not look at the natural english (same results) `select name , case when charindex('_CS',name)>0 OR charindex('_BIN',name)>0 then 1 else 0 end [Case Sensitive] from sys.fn_helpcollations() `
0 Likes 0 ·

@KenJ, @guumbo, and @Pavel Pawlowski : It's a very common misconception that binary collations are also case-sensitive. They most definitely are not, though on a practical level there are some situations where the behavior is similar enough such that the differences aren't noticeable. Please see " No, Binary Collations are not Case-Sensitive " for details. And, please see my answer to this question for how to determine case-sensitivity (well, more so case-insensitivity) without needing to parse the name (though to be fair, checking for "_CI" and "_CS" should be reliable).

0 Likes 0 ·
srutzky avatar image
srutzky answered

Using the COLLATIONPROPERTY built-in function you can cleanly determine if the collation is case-INsensitive. That function will return a bitmasked value for the "ComparisonStyle" property. Any case-insensitive collation will include the value of 1 in the bitmasked value.

Unfortunately, not having a 1 in the bitmasked value does not necessarily indicate a case-sensitive collation since none of the binary collations (both _BIN and _BIN2) return a value containing 1 (they all return 0). And just to be clear about this: binary collations are not case-sensitive. However, for situations where there is no noticeable difference between the behaviors of case-sensitive and binary collations, the lack of 1 in the bitmasked value would effectively indicate case-sensitive.

For example:

SELECT col.[name],
       COLLATIONPROPERTY(col.[name] , 'ComparisonStyle') AS [ComparisonStyle],
       CONVERT(INT, COLLATIONPROPERTY(col.[name] , 'ComparisonStyle')) & 1
            AS [IsCaseInsensitive]
FROM   sys.fn_helpcollations() col
ORDER BY col.[name];

returns (I removed the "_SC" entries to save space as that property doesn't affect the ComparisonStyle):

name                        ComparisonStyle    IsCaseInsensitive
Albanian_100_BIN            0                  0
Albanian_100_BIN2           0                  0
Albanian_100_CI_AI          196611             1
Albanian_100_CI_AI_KS       131075             1
Albanian_100_CI_AI_KS_WS    3                  1
Albanian_100_CI_AI_WS       65539              1
Albanian_100_CI_AS          196609             1
Albanian_100_CI_AS_KS       131073             1
Albanian_100_CI_AS_KS_WS    1                  1
Albanian_100_CI_AS_WS       65537              1
Albanian_100_CS_AI          196610             0
Albanian_100_CS_AI_KS       131074             0
Albanian_100_CS_AI_KS_WS    2                  0
Albanian_100_CS_AI_WS       65538              0
Albanian_100_CS_AS          196608             0
Albanian_100_CS_AS_KS       131072             0
Albanian_100_CS_AS_KS_WS    0                  0
Albanian_100_CS_AS_WS       65536              0
....
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.