I have set the database collation to Latin1_General_BIN, to make string comparisons case-sensitive. Will this have an impact on performance? Will it have any impact on DML or DDL operations in the database? The database already exists with tables in it.

up vote 22 down vote accepted

Collations in SQL Server determine the rules for matching and sorting character data. Normally, you would choose a collation first based on the comparison semantics and sorting order the consumers of the data require.

Humans generally do not find that binary collations produce the sorting and comparison behaviours they expect. So, although these offer the best performance (especially the pure code-point BIN2 versions) most implementations do not use them.

Next in raw performance terms (but only for non-Unicode strings) are the backward-compatibility SQL collations. When working with Unicode data, these collations use a Windows collation instead, with the same performance characteristics. There are subtle traps here, so you need to have good reasons to choose a SQL collation these days (unless working on a US system, where it is still the default).

Windows collations are the slowest, in general, because of the complex Unicode comparison and sorting rules. Nevertheless, these offer complete compatibility with Windows in SQL Server, and are regularly maintained to keep up with changes in the Unicode standard. For modern use that includes Unicode data, a Windows collation is generally recommended.

TL;DR

If all you want is case-sensitive comparison and sorting semantics, you should choose the _CS_ (for Case Sensitive) variation of whichever base collation provides the expected behaviour for your users' language and culture. For example, both these are case-sensitive collations:

-- Latin1-General, case-sensitive, accent-sensitive
Latin1_General_CS_AS 

-- Latin1-General, case-sensitive, accent-sensitive for Unicode Data, 
-- SQL Server Sort Order 51 on Code Page 1252 for non-Unicode Data
SQL_Latin1_General_CP1_CS_AS

You can see these definitions using sys.fn_helpcollations

Examples

Four tables that are exactly the same except for the collation; one binary, one case-sensitive, one case-insensitive, and one SQL case-sensitive:

CREATE TABLE #Example_BIN
(
    string nvarchar(50) 
        COLLATE Latin1_General_BIN
        NOT NULL
);

CREATE TABLE #Example_CS
(
    string nvarchar(50) 
        COLLATE Latin1_General_CS_AI
        NOT NULL
);

CREATE TABLE #Example_CI
(
    string nvarchar(50) 
        COLLATE Latin1_General_CI_AI
        NOT NULL
);

CREATE TABLE #Example_SQL
(
    string varchar(50) -- Note varchar
        COLLATE SQL_Latin1_General_CP1_CS_AS
        NOT NULL
);

Same sample data for each table:

INSERT #Example_BIN
    (string)
VALUES
    (N'A'),
    (N'a'),
    (N'B'),
    (N'b'),
    (N'C'),
    (N'c');

INSERT #Example_CS
SELECT EB.string 
FROM #Example_BIN AS EB;

INSERT #Example_CI
SELECT EB.string 
FROM #Example_BIN AS EB;

INSERT #Example_SQL
SELECT EB.string 
FROM #Example_BIN AS EB;

Now we want to find strings greater than 'a':

SELECT EB.string AS BIN
FROM #Example_BIN AS EB
WHERE EB.string > N'a'
ORDER BY EB.string;

SELECT EC.string AS CS
FROM #Example_CS AS EC
WHERE EC.string > N'a'
ORDER BY EC.string;

SELECT EC2.string AS CI
FROM #Example_CI AS EC2
WHERE EC2.string > N'a'
ORDER BY EC2.string;

SELECT ES.string AS SQL
FROM #Example_SQL AS ES
WHERE ES.string > 'a' -- not Unicode
ORDER BY ES.string;

Results:

╔═════╗
║ BIN ║
╠═════╣
║ b   ║
║ c   ║
╚═════╝

╔════╗
║ CS ║
╠════╣
║ A  ║
║ b  ║
║ B  ║
║ c  ║
║ C  ║
╚════╝

╔════╗
║ CI ║
╠════╣
║ B  ║
║ b  ║
║ C  ║
║ c  ║
╚════╝

╔═════╗
║ SQL ║
╠═════╣
║ B   ║
║ b   ║
║ C   ║
║ c   ║
╚═════╝

Finally...

Note though, if we use a Unicode literal with the SQL collation, the implicit conversion rules result in a Windows collation comparison:

SELECT ES.string AS SQL
FROM #Example_SQL AS ES
WHERE ES.string > N'a'
ORDER BY ES.string;

...and the SQL collation results change:

╔═════╗
║ SQL ║
╠═════╣
║ A   ║
║ B   ║
║ b   ║
║ C   ║
║ c   ║
╚═════╝

Given that this is an existing database that already has tables defined in it, there are some very serious implications to the action of changing the database collation, beyond the potential performance impact to DML opertions (which actually was already there). There is very real impact to performance and functionality, and this change not only did not achieve the intended goal (at least not consistently), but just as likely altered the behavior (or will alter the behavior when new tables are created) in terms of how data is ordered and equated.

Paul already provided good explanation and examples of the differences in performance and behavior between the different types of collations in his answer, so I won't repeat that here. However, a few points need some additional detail, and there are several other points to add in relation to the current scenario of changing the collation of an existing DB, as opposed to setting the collation of a new DB.

  1. Binary collations are more than just case-sensitive: they are everything sensitive! So, by using a binary collation (ending in _BIN or _BIN2), your comparisons are now also accent sensitive, kana sensitive, width sensitive, and potentially gluten sensitive (at least that seems to be the trend these days ;-) ). Was this the desired affect of making this change? Are end-users expecting this change of behavior?

  2. Collations affect not just comparisons, but also sorting. A binary collation will sort based on the ASCII or UNICODE byte value (depending on VARCHAR or NVARCHAR, respectively) of each byte. Hence, by choosing a binary collation you are giving up language- / culture- specific weighting rules that order each character (even characters in some language, such as Hungarian, that are comprised of 2 letters) according to that culture's alphabet. So, if "ch" should naturally come after "k", well, that ain't gonna happen using a binary collation. Again, was this the desired affect of making this change? Are end-users expecting this change of behavior?

  3. Unless you have specific backwards-compatibility requirements for your application, you should be using the BIN2 instead of BIN collations, assuming, of course, that you are wanting a binary collation in the first place. The BIN2 collations were introduced in SQL Server 2005, and according to the MSDN page for Guidelines for Using BIN and BIN2 Collations:

    Previous binary collations in SQL Server, those ending with "_BIN", performed an incomplete code-point-to-code-point comparison for Unicode data. Older SQL Server binary collations compared the first character as WCHAR, followed by a byte-by-byte comparison.

    ...

    You can migrate to the [_BIN2] binary collations to take advantage of true code-point comparisons, and you should use the new binary collations for development of new applications.

    It should also be noted that the _BIN2 collations conveniently match the behavior of the Ordinal option of the StringComparison Enumeration, such that comparisons and sorting done in .NET code using that option will yield the same results as those same operations being performed within SQL Server (when using the _BIN2 collations, of course).

  4. For similar reasons to what has just been stated regarding the _BIN2 collations, unless you have specific requirements to maintain backwards-compatibility behavior, you should lean towards using the Windows collations and not the SQL Server-specific collations (i.e. the ones starting with SQL_ are now considered kinda "sucky" ;-) ).

  5. When using Unicode data (i.e. string prefixed with N or coming into SQL Server from app code where the datatype has been specified as NChar or NVarChar), I don't see how using one collation vs another would make a difference for inserting or updating an NCHAR or NVARCHAR string field.

    When using non-Unicode data, or inserting into or updating a non-Unicode field, then the particular collation (database or field) might play a small role if any characters being inserted/updated need to be translated, or are not mapable (is that even a word?), as specified by the Code Page that is defined by the collation. Of course, this potential issue exists whenever one is using non-Unicode data or data types, and is not specific to this scenario of changing the DB collation. That change will impact string literals (which might have already been an issue if the DB collation was different than the field's collation). But even if no change is made to the DB collation, data coming in from other DBs or from outside of SQL Server (any client code) can contain any characters and be of any particular encoding.

  6. VERY IMPORTANT!!! When changing the database default collation, the collation specified for any existing string fields in any existing tables will not change, but any new fields will have a collation of the database default (unless overridden via the COLLATE clause). This will impact your queries in three ways:

    1) If any queries JOIN on any of those existing fields to any of the new fields, you will get a collation mismatch error:

    USE [master];
    GO
    
    IF (DB_ID(N'ChangeCollationTest') IS NOT NULL)
    BEGIN
        PRINT 'Dropping [ChangeCollationTest] DB...';
        ALTER DATABASE [ChangeCollationTest]
            SET SINGLE_USER
            WITH ROLLBACK IMMEDIATE;
    
        DROP DATABASE [ChangeCollationTest];
    END;
    GO
    
    PRINT 'Creating [ChangeCollationTest] DB...';
    CREATE DATABASE [ChangeCollationTest]
        COLLATE SQL_Latin1_General_CP1_CI_AS;
    GO
    
    USE [ChangeCollationTest];
    GO
    
    CREATE TABLE [CollateTest-SQL_Latin1_General_CP1_CI_AS]
                 (Col1 NVARCHAR(50) COLLATE DATABASE_DEFAULT, Col2 NVARCHAR(50));
    SELECT *
    FROM   sys.columns sc
    WHERE  sc.[object_id] = OBJECT_ID(N'[CollateTest-SQL_Latin1_General_CP1_CI_AS]');
    -- "collation_name" for both fields shows: SQL_Latin1_General_CP1_CI_AS
    GO
    
    USE [master];
    GO
    ALTER DATABASE [ChangeCollationTest]
        COLLATE Latin1_General_BIN2;
    GO
    USE [ChangeCollationTest];
    GO
    
    CREATE TABLE [CollateTest-Latin1_General_BIN2]
                 (Col1 NVARCHAR(50) COLLATE DATABASE_DEFAULT, Col2 NVARCHAR(50));
    SELECT *
    FROM   sys.columns sc
    WHERE  sc.[object_id] = OBJECT_ID(N'[CollateTest-Latin1_General_BIN2]');
    -- "collation_name" for both fields shows: Latin1_General_BIN2
    GO
    
    
    SELECT *
    FROM   dbo.[CollateTest-SQL_Latin1_General_CP1_CI_AS] ctSQL
    INNER JOIN  dbo.[CollateTest-Latin1_General_BIN2] ctWIN
            ON  ctWIN.Col1 = ctSQL.Col1;
    

    Returns:

    Msg 468, Level 16, State 9, Line 4
    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and
    "Latin1_General_BIN2" in the equal to operation.
    

    2) Predicates / filters on existing fields of existing tables (set to the prior default collation) that compare to string literals or variables won't error, but they might certainly be impacted performance-wise due to SQL Server needing to equate the collation of both sides and automatically converting the string literal or variable to the collation of the field. Enable "Include Actual Execution Plan" (Control-M) and then execute the following (assuming that you have already run the queries shown above):

    SELECT *
    FROM   dbo.[CollateTest-SQL_Latin1_General_CP1_CI_AS] ctSQL
    WHERE  ctSQL.Col1 = N'a';
    -- Unspecified collations on string literals and variables assume the database default
    -- collation. This mismatch doesn't cause an error because SQL Server adds a
    -- "[Col1]=CONVERT_IMPLICIT(nvarchar(4000),[@1],0)" but it can hurt performance.
    
    SELECT *
    FROM   dbo.[CollateTest-Latin1_General_BIN2] ctWIN
    WHERE  ctWIN.Col1 = N'a';
    -- No CONVERT_IMPLICIT; plan shows "[Col1]=[@1]".
    

    3) AND, speaking of implicit conversions, notice how it is the string literal (with an implied collation of the database default collation: Latin1_General_BIN2) that is converted, not the field in the table. Any guesses as to whether or not this filter will be case-insensitive (the old collation) or case-sensitive (the new collation)? Run the following to see:

    INSERT INTO dbo.[CollateTest-SQL_Latin1_General_CP1_CI_AS] (Col1)
    VALUES (N'a'), (N'A');
    
    SELECT ctSQL.Col1
    FROM   dbo.[CollateTest-SQL_Latin1_General_CP1_CI_AS] ctSQL
    WHERE  ctSQL.Col1 = N'a';
    

    Returns:

    Col1
    ----
    a
    A
    

    D'oh! Not only is there a slight (or maybe more significant?) performance hit for this query due to the CONVERT_IMPLICIT(), but it doesn't even behave in the desired case-sensitive manner.

    Ergo, if the collation is changed on a DB that already has tables, then yes, both performance AND functionality are impacted.

    If the collation is being set on a new DB, then Paul already covered that by explaining how a binary collation, while fast, probably won't sort in the way that one would expect or desire.


It should also be noted that you can always specify collations per-condition. The COLLATE clause can be added to WHERE conditions, ORDER BY, and most any place that accepts a string.

Example 1 (WHERE condition):

SELECT tmp.col AS [SQL-CaseSensitive]
FROM (VALUES ('a'), ('A'), ('b'), ('B')) tmp(col)
WHERE tmp.col > 'a' COLLATE SQL_Latin1_General_CP1_CS_AS;

SELECT tmp.col AS [Windows-CaseSensitive]
FROM (VALUES ('a'), ('A'), ('b'), ('B')) tmp(col)
WHERE tmp.col > 'a' COLLATE Latin1_General_CS_AI;

Returns:

SQL-CaseSensitive
-----------------
b
B

Windows-CaseSensitive
-----------------
A
b
B

Example 2 (ORDER BY):

SELECT tmp.col AS [Windows-CaseSensitive]
FROM (VALUES ('a'), ('A'), ('b'), ('B')) tmp(col)
ORDER BY tmp.col COLLATE Latin1_General_CS_AI;

SELECT tmp.col AS [Windows-Binary]
FROM (VALUES ('a'), ('A'), ('b'), ('B')) tmp(col)
ORDER BY tmp.col COLLATE Latin1_General_BIN2;

Returns:

Windows-CaseSensitive
-----------------
a
A
b
B

Windows-Binary
-----------------
A
B
a
b

Example 3 (IF statement):

IF ('A' = 'a') SELECT 1 AS [DatabaseDefault-CaseInsensitive?];
-- if the DB is not case-sensitive or binary, returns 1

IF ('A' = 'a' COLLATE Latin1_General_BIN2) SELECT 2 AS [Windows-Binary];

Returns:

DatabaseDefault-CaseInsensitive?
--------------------------------
1

{nothing}

Example 4 (associate with function input parameter):

SELECT  UNICODE(N'🂡') AS [UCS-2],
        UNICODE(N'🂡' COLLATE Latin1_General_100_CI_AS_SC) AS [UTF-16];
-- This character is a Unicode supplemental character and is not part of the
-- default UCS-2 encoding. In order for built-in functions to handle these
-- characters correctly, either the DB default collation needs to end in
-- "_SC" (available as of SQL Server 2012), or use as shown here.
-- See the character in more detail here: http://unicode-table.com/en/1F0A1/

Returns:

UCS-2    UTF-16
------   -------
55356    127137

The UCS-2 value of 55,356 is partially correct in that it is the first of the two values in the "surrogate pair". But unless explicitly given the _SC collation, the UNICODE() function can only see each character as a double-byte value and doesn't know how to properly handle a double double-byte surrogate pair.


UPDATE

Even with all of the examples above, one aspect of Case Sensitive comparisons that is usually overlooked, and is negated by binary comparisons / collations, is normalization (composition and decomposition) that is part of Unicode.

Example 5 (when a binary comparison is not case-sensitive):

True case-sensitive comparisons allow for combining characters that, in combination with another character, form yet another character that already exists as another Unicode code point. Case-sensitive comparisons care about the displayable character, not the code point(s) used to create it.

SELECT 'Equal' AS [Binary],
       NCHAR(0x00FC) AS [ü],
       N'u' + NCHAR(0x0308) AS [u + combining diaeresis]
WHERE  NCHAR(0x00FC) COLLATE Latin1_General_100_BIN2
    =  N'u' + NCHAR(0x0308) COLLATE Latin1_General_100_BIN2
-- No result as they are a different number of code points,
-- as well as being different code points.

SELECT 'Equal' AS [Case-Sensitive],
       NCHAR(0x00FC) AS [ü],
       N'u' + NCHAR(0x0308) AS [u + combining diaeresis]
WHERE  NCHAR(0x00FC) COLLATE Latin1_General_100_CS_AS -- ü
    =  N'u' + NCHAR(0x0308) COLLATE Latin1_General_100_CS_AS -- u + combining diaeresis
-- Result set returned, even being a different number of code points AND Accent Sensitive,
-- due to normalization

Returns:

Binary            ü     u + combining diaeresis
-------          ---   -------------------------
{nothing}

Case-Sensitive    ü     u + combining diaeresis
---------------  ---   -------------------------
Equal             ü     ü

True case-sensitive comparisons also allow for wide characters to equate to their non-wide equivalents.

IF (N'sofia' = N'sofia' COLLATE Latin1_General_100_BIN2)
  SELECT 'Values are the same' AS [Binary]
ELSE
  SELECT 'Values are different' AS [Binary];


IF (N'sofia' = N'sofia' COLLATE Latin1_General_100_CS_AS)
  SELECT 'Values are the same' AS [Case-Sensitive]
ELSE
  SELECT 'Values are different' AS [Case-Sensitive];

Returns:

Binary
---------------
Values are different


Case-Sensitive
---------------
Values are the same

Ergo:

BINARY (_BIN and _BIN2) collations are not Case-Sensitive!

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.