Why we use collate Database_default in SQL Server?
MSDN states COLLATE DATABASE_DEFAULT clause casts the collation of an expression, column definition, or database definition to inherit the collation of the “current database”. To complement MSDN, the “current database” is the context of the database where the query is executed.
What does COLLATE SQL_Latin1_General_CP1_CI_AS mean?
If “What does COLLATE SQL_Latin1_General_CP1_CI_AS do?” means “What does the COLLATE clause of CREATE DATABASE do?”, then: Due to controlling the DB-level Collation of master , it is then the Collation used for Server-level data, such as Database names (i.e. name column in sys. databases ), Login names, etc.
How do you use collate?
You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. You can also specify a collation when you create a table using SQL Server Management Studio. If you do not specify a collation, the column is assigned the default collation of the database.
When to use collate in a SQL statement?
With the COLLATE clause, you can override whatever the default collation is for a comparison. COLLATE may be used in various parts of SQL statements. Here are some examples: With ORDER BY :
When to use collate in a string concatenation?
You do need COLLATE in the string concatenation because you are mixing database-level meta-data (which uses the database’s default Collation) and system-level meta-data (i.e. state_desc and permission_name) which comes from the hidden mssqlsystemresource database and usually has a Collation of Latin1_General_CI_AS_KS_WS.
When do I need to use custom collations?
If you are going to use custom collations for specific databases then yes, you’ll need to make the collations match whenever you are joining or unioning data from the two databases. In fact you will need to do this with many metadata queries anyway.
Do you need to collate columns in XML?
Non-string types (including XML) do not use COLLATE, and columns that are guaranteed to have the same Collation do not need it. For JOIN, WHERE, and HAVING predicates, it only needs to be specified on one side of the operator since the other side will be coerced into the specified Collation.