For more information, including the list of OS locale to default collation mappings, see the 'Server-level collations' section of Collation and Unicode Support.

The default server-level collation is based upon the locale of the operating system.įor example, the default collation for systems using US English (en-US) is SQL_Latin1_General_CP1_CI_AS. The server collation is specified during SQL Server installation. Variables and are considered as different variables if the server-level collation is case-sensitive, and the same variables if the server-level collation is case-insensitive. Names of the variables, cursors, and GOTO labels.Collation of the CHAR, VARCHAR, NCHAR, and NVARCHAR columns in system views, system functions, and the objects in TempDB (for example, temporary tables).Sorting and comparison rules in =, JOIN, ORDER BY and other operators that compare textual data.You should carefully consider the server-level collation, because it may affect:

comparing values to a column result in the collation that is on the column or table if it is not set on the column, taking precedence.įinally comparing two columns with different collations is not possible as you get an error in sql to say that they do not match.The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, and also any newly created user databases.variable values read from a table also uses the database default.variable values use the database default, although this is only really evident if you do a comparison with something.scripts are compiled using the server collation so variable and table names etc follow what the server is set to.We get the same result in the case sensitive database which makes sense. Select top 1 case when data_cs = then ‘equal’ else ‘not equal’ end as equality If we now look at comparing values to columns and values, in the insensitive database we get a “not equal” when comparing a value to a case sensitive column: “equal” so the collation of data read in from a table into a variable takes on the collation of the database! “not equal”, hmm so is it that that the database collation affects it? Select top 1 = data_ci, = data_cs from ins This resulted in “equal”, then I wanted to check if two values read from a table and compared to each other used their own collation of the database collation:ĭata_ci varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,ĭata_cs varchar(25) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL Then in the case insensitive database I check whether a value read from a case sensitive table is equal:ĭata varchar(25) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL Select case when = then ‘equal’ else ‘not equal’ end as equality First of all in the sensitive database I check a value in a table with a collation that is not case sensitive:ĭata varchar(25) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL Next I wanted to know if values populated from a table followed the database collation or used the collation that the table used, if it was different from the database. Great so values are set using the database. Select case when ‘i’ = ‘I’ then ‘equal’ else ‘not equal’ end as equality_non_case_sensitive_db įor this, the case sensitive one shows “not equal” and the case insensitive one shows “equal”

Select case when ‘i’ = ‘I’ then ‘equal’ else ‘not equal’ end as equality_case_sensitive_db What about the collation of variables? Are the values of variables defined in script, the same as the database or server? So the actual script is compiled in the collation of the server. Must declare the scalar variable a server that is case insensitive, I get: On a server that is case sensitive, both fail: Given this script (guess how the sensitive and non-sensitive databases have been setup!): The first question I had was what collaction is the actual T-Sql script compiled in, is it the database or server? Given how important this is I thought I would investigate it a little: I really wasn't too sure of the answer, I generally try to ensure that databases are in the company default collation although I realise that this is not always possible. I was asked an interesting question about collations in sql server recently about where variables in a script got their collation's from. What collation variables take on in T-SQL

Sql collate