You manage an instance of SQL Server 2008. The server contains several databases that have differentcollations.
An assistant administrator named Paul wants to create a table in a database named Products , but heis unfamiliar with collation precedence rules.
You want to inform Paul about collation precedence rules that he should consider when specifying differentcollations for columns in the table.
Which statements are true? (Choose all that apply.)
A.
Explicit collation takes precedence over implicit collation.
B.
Implicit collation takes precedence over coercible-default collation.
C.
You can combine two expressions that have been assigned different explicit collations.
D.
When you combine two expressions that have been assigned different implicit collations, the output isdisplayed using implicit collation.
Explanation:
Explicit collation takes precedence over implicit collation, and implicit collation takes precedence over coercible-default collation. SQL Server uses collation precedence to determine the collation of the final result of anexpression that evaluates to a character string. There are four categories that are used to identify the collations ofall objects. These categories are coercible-default, implicit, explicit, and no-collation. In coercible-default collation,the object is assigned the default collation of the database in which the object is created. In implicit collation, thecollation defined for the column in the table or view is assigned to the specified expression. Even if you explicitlyassign a collation to the column by using the
COLLATE clause in the CREATE TABLE or CREATE VIEW statement, the column reference is labeled as implicit. The explicit collation is assigned to expressions thatexplicitly specify a collation by using a COLLATE clause in the expression. No-collation indicates that the value ofan expression is obtained due to conflicting collations between two strings that have implicit collation. Forexample, when you run the following Transact-SQL statement: SELECT * FROM CorpData WHERE Column1 = Column2; In this statement, Column1
and Column2 have SQL_Latin1_General_CP1_CI_AS andSQL_Latin1_General_CP1_CS_AS collation, respectively, the following error message will be displayed:
Cannot resolve collation conflict between ‘SQL_Latin1_General_CP1_CI_AS’ and’SQL_Latin1_General_CP1_CS_AS’ in the equal to operation.
When you combine two implicit expressions that have different collations, it results in no-collation. Collation conflict prevents the Database Engine from joining or comparing two values with each other. To prevent this errorfrom occurring, you should specify an explicit collation for the expression before the equals sign in the WHERE clause condition as shown in the following example: SELECT * FROM CorpData WHERE Column1 = Column2 COLLATE SQL_Latin1_General_CP1_CI_AS;
The options stating that you can combine two expressions that have been assigned different explicit collationsand when you combine two expressions that have been assigned different implicit collations, the output isdisplayed using implicit collation are incorrect. When you combine two expressions that have been assigneddifferent explicit collations, the statement generates an error. When you combine two implicit expressions thathave different collations, the statement results in no-collation.Objective:
Performing Data Management TasksSub-Objective:
Manage collations.References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Technical Reference > Transact-SQLReference > Data Types (Transact-SQL) > Collation Precedence (Transact-SQL) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Development > Querying and ChangingData (Database Engine) > International Considerations for Databases and Database Engine Applications >Working with Collations > Setting and Changing Collations > Setting and Changing the Column Collation