Which Transact-SQL statement or statements can you use?

You are responsible for managing an instance of SQL Server 2008.
You run the following Transact-SQLstatements in the Query Editor to create a new table named CorpData to test different collation settings fordifferent columns:

You want to generate output that displays the values of both the columns without producing any errors.
Which Transact-SQL statement or statements can you use? (Choose all that apply.)

A.
SELECT * FROM CorpData;

B.
SELECT * FROM CorpData
WHERE Collation1 = Collation2;

C.
SELECT * FROM CorpData
WHERE Collation1 = Collation2 COLLATE SQL_Latin1_General_CP1_CI_AS;

D.
SELECT * FROM CorpData
WHERE Collation1 = Collation2 COLLATE SQL_Latin1_General_CP1_CS_AS;

Explanation:

You can use the following two Transact-SQL statements:
SELECT * FROM CorpData;
or
SELECT * FROM CorpData
WHERE Collation1 = Collation2 COLLATE SQL_Latin1_General_CP1_CI_AS;
Running these two queries will generate the following output:

When you have configured more than one collation setting in a table, SQL Server uses collation precedence todetermine the collation of the final result of an expression that evaluates to a character string. The collationprecedence rules apply only to the character string data types. There are four categories that are used to identifythe collations of all objects: coercible-default, implicit, explicit, and no-collation. In coercible-default collation, theobject 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. You should not use the following Transact-SQL statement: SELECT * FROM CorpData WHERE Collation1 = Collation2;
Running this statement generates the following error message: Cannot resolve collation conflict between ‘SQL_Latin1_General_CP1_CI_AS’ and’SQL_Latin1_General_CP1_CS_AS’ in the equal to operation. In this scenario, the collations defined for the two columns will be taken as implicit collation. When you combinetwo implicit expressions that have different collations, it results in no-collation. Therefore, the error is raised.Collation conflict prevents the database engine from joining or comparing two values with each other. To preventthis error from occurring, you should specify an explicit collation for the expression before the equals sign in the WHERE clause condition. In this scenario, the collation for the Collation1 column isSQL_Latin1_General_CP1_CI_AS. Therefore, you should use this collation with the COLLATE clause. You should not use the following Transact-SQL statement:
SELECT * FROM CorpData WHERE Collation1 = Collation2 COLLATE SQL_Latin1_General_CP1_CS_AS; Running this statement will not generate any error, but it will also not generate any output because in thisstatement the COLLATE clause specifies the collation for the Collation2 column. To display the values of boththe columns, you should specify an explicit collation for the expression before the equals sign in the WHERE clause condition, which is Collation1 in this scenario.

Objective:
Performing Data Management Tasks

Sub-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