Sql server® 2012 t-sql fundamentals

Dictionary sorting  Sorting and comparison of character data are based on dictionary order 

(A and a < B and b). 

You can tell that dictionary order is used because that’s the default when no other ordering is 

defined explicitly. More specifically, the element BIN doesn’t explicitly appear in the collation 

name. If the element BIN appeared, it would mean that sorting and comparison of character 

data was based on the binary representation of characters (A < B < a < b).

■

■

CI  The data is case insensitive (a = A).

■

■

AS  The data is accent sensitive (à <> ä).

www.it-ebooks.info

---

 

CHAPTER 2

 

Single-Table Queries

 

 63

In an on-premises SQL Server implementation, collation can be defined at four different levels: 

instance, database, column, and expression. The lowest effective level is the one that should be used. 

In Windows Azure SQL Database, collation can be indicated at the database, column, and expression 

levels.

The collation of the instance is chosen as part of the setup program. It determines the collations of 

all system databases and is used as the default for user databases.

When you create a user database, you can specify a collation for the database by using the COLLATE 

clause. If you don’t, the instance’s collation is assumed by default. 

The database collation determines the collation of the metadata of objects in the database and is 

used as the default for user table columns. I want to emphasize the importance of the fact that the 

database collation determines the collation of the metadata, including object and column names. 

For example, if the database collation is case insensitive, you can’t create two tables called T1 and t1 

within the same schema, but if the database collation is case sensitive, you can.

You can explicitly specify a collation for a column as part of its definition by using the COLLATE 

clause. If you don’t, the database collation is assumed by default.

You can convert the collation of an expression by using the COLLATE clause. For example, in a 

case-insensitive environment, the following query uses a case-insensitive comparison.

SELECT empid, firstname, lastname 

FROM HR.Employees 

WHERE lastname = N'davis';

The following query returns the row for Sara Davis, even though the casing doesn’t match, because 

the effective casing is insensitive.

empid       firstname  lastname 

----------- ---------- -------------------- 

1           Sara       Davis

If you want to make the filter case sensitive even though the column’s collation is case insensitive, 

you can convert the collation of the expression.

SELECT empid, firstname, lastname 

FROM HR.Employees 

WHERE lastname COLLATE Latin1_General_CS_AS = N'davis';

This time the query returns an empty set because no match is found when a case-sensitive com-

parison is used.

www.it-ebooks.info

---

64  

Microsoft SQL Server 2012 T-SQL Fundamentals