A collation specifies the bit patterns that represent each character in a data set. Collations also determine the rules that sort and compare data. SQL Server supports storing objects with different collations in a single database. For non-Unicode columns, the collation setting specifies the code page for the data and which characters can be represented. Data that is moved between non-Unicode columns must be converted from the source code page to the destination code page.

Transact-SQL statement results can vary when the statement is run in the context of different databases that have different collation settings. If it is possible, use a standardized collation for your organization. This way, you do not have to explicitly specify the collation in every character or Unicode expression. If you must work with objects that have different collation and code page settings, code your queries to consider the rules of collation precedence.

As databases expand to support a growing global market, users must be able to work with character data in meaningful ways. Collations let users sort and compare strings according to their own conventions. Collations are a critical part of creating a database and manipulating data. Many server-to-server activities can fail or yield inconsistent results if collation settings are not consistent across servers. To avoid this, select a Windows locale or SQL Server collation (SQL_*) to match the collation settings in other instances of SQL Server.

SQL Server collations include the following collation sets:

Windows collations

Binary collations

SQL Server

SQL Server 2008 supports setting collations at the following levels:

Server

Database

Column

Expression

if you want to change the collation of a database, you can get the current collation using the following snippet of T-SQL:

SELECT name, collation_name  FROM sys.databases WHERE name = 'test2'    
--put your database name here This will yield a value something like:

Latin1_General_CI_AS The _CI means "case insensitive" - if you want case-sensitive, use _CS in its place:
Latin1_General_CS_AS So your T-SQL command would be:

ALTER DATABASE test2 -- put your database name here    
COLLATE Latin1_General_CS_AS   -- replace with whatever collation you need 

You can get a list of all available collations on the server using:
SELECT * FROM ::fn_helpcollations() 

You can see the server's current collation using:
SELECT SERVERPROPERTY ('Collation') 

However, the changes will affect for new data that is inserted on the database. On the long run follow as suggested above.

For the specified issue, if they wanna check only the data only for verification, the DBA can find the collation as above and set the collation at query level as below:

CREATE TABLE Locations
(Place varchar(15) NOT NULL);
GO
INSERT Locations(Place) VALUES ('Chiapas');
INSERT Locations(Place) VALUES ('Colima');
INSERT Locations(Place) VALUES ('Cinco Rios');
INSERT Locations(Place) VALUES ('California');
GO
--Apply an typical collation
SELECT Place FROM Locations
ORDER BY Place
COLLATE Latin1_General_CS_AS_KS_WS ASC;
GO
-- Apply a Spanish collation
SELECT Place FROM Locations
ORDER BY Place
COLLATE Traditional_Spanish_ci_ai ASC;
GO

Collation also determines Case sentivity.Default Collation of the SQL Server installation SQLLatin1GeneralCP1CI_AS is not case sensitive.

To change the collation of the any column for any table permanently run following query.

ALTER TABLE Table1
ALTER COLUMN Column1 VARCHAR(20)
COLLATE Latin1_General_CS_AS

A collation specifies the bit patterns that represent each character in a data set. Collations let users sort and compare strings according to their own conventions.

1) Code Page

This is a single-byte character set that represents the alphabet, punctuation & symbols of a language. Data types using a code page are char, varchar & ntext.

Code page does not apply to double-byte (Unicode) characters.

2) Sort Order for Unicode data types.

Unicode file types require double byte storage. Data types are nchar , nvarchar and ntext.

3) Sort order for code page characters

Sort order itself is affected by :

Case Sensitivity In comparison and sorting operations, are uppercase and lowercase characters considered equal? In a Case Sensitive collation 'A' <> 'a', 'Cat' <> 'CAT' etc.

Accent Sensitivity

In comparison and sorting operations, are accented and unaccented characters considered equal? Does 'a' = '?', 'Bronte' = 'Brontë'?

Kana sensitivity

Japan has 2 types of characters Hiragana and Katakana.

In comparison and sorting operations, are Kana sensitivite collation treated them as equal?

Width sensitivity

In comparison and sorting operations, is a single byte character equal to it's double-byte representation?

Collation naming reveals all this information.

For example, the Latin1GeneralCI_AS collation is a Latin code page that is Case Insensitive (CI) and Accent Sensitive (AS).

A collation specifies the bit patterns that represent each character in a data set. Collations also determine the rules that sort and compare data. SQL Server supports storing objects with different collations in a single database. For non-Unicode columns, the collation setting specifies the code page for the data and which characters can be represented. Data that is moved between non-Unicode columns must be converted from the source code page to the destination code page.

Transact-SQL statement results can vary when the statement is run in the context of different databases that have different collation settings. If it is possible, use a standardized collation for your organization. This way, you do not have to explicitly specify the collation in every character or Unicode expression. If you must work with objects that have different collation and code page settings, code your queries to consider the rules of collation precedence.

As databases expand to support a growing global market, users must be able to work with character data in meaningful ways. Collations let users sort and compare strings according to their own conventions. Collations are a critical part of creating a database and manipulating data. Many server-to-server activities can fail or yield inconsistent results if collation settings are not consistent across servers. To avoid this, select a Windows locale or SQL Server collation (SQL_*) to match the collation settings in other instances of SQL Server.

SQL Server collations include the following collation sets:

Windows collations

Binary collations

SQL Server

SQL Server 2008 supports setting collations at the following levels:

Server

Database

Column

Expression

if you want to change the collation of a database, you can get the current collation using the following snippet of T-SQL:

SELECT name, collation_name  FROM sys.databases WHERE name = 'test2'

--put your database name here This will yield a value something like:


Latin1GeneralCI_AS The _CI means "case insensitive" - if you want case-sensitive, use _CS in its place:

Latin1GeneralCS_AS So your T-SQL command would be:

ALTER DATABASE test2 -- put your database name here    COLLATE Latin1GeneralCS_AS   -- replace with whatever collation you need You can get a list of all available collations on the server using:

SELECT * FROM ::fn_helpcollations() You can see the server's current collation using:

SELECT SERVERPROPERTY ('Collation') 


However, the changes will affect for new data that is inserted on the database. On the long run follow as suggested above.

For the specified issue, if they wanna check only the data only for verification, the DBA can find the collation as above and set the collation at query level as below:

CREATE TABLE Locations
(Place varchar(15) NOT NULL);
GO
INSERT Locations(Place) VALUES ('Chiapas');
INSERT Locations(Place) VALUES ('Colima');
INSERT Locations(Place) VALUES ('Cinco Rios');
INSERT Locations(Place) VALUES ('California');
GO
--Apply an typical collation
SELECT Place FROM Locations
ORDER BY Place
COLLATE Latin1GeneralCSASKSWS ASC;
GO
-- Apply a Spanish collation
SELECT Place FROM Locations
ORDER BY Place
COLLATE TraditionalSpanishciai ASC;
GO


                                        

Collation

The physical storage of character strings in Microsoft SQL Server is controlled by collations. Collations let users sort and compare strings according to their own conventions. Collations are a critical part of creating a database and manipulating data.

A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages).

In most cases, a computer runs the Windows system locale that matches the language requirements of the user. Therefore, SQL Server Setup automatically detects the Windows system locale and selects the appropriate SQL Server collation. For backward compatibility, the default English-language (US) collation is SQLLatin1General.

Each SQL Server collation specifies three properties:

• The sort order to use for Unicode data types (nchar, nvarchar, and ntext). A sort order defines the sequence in which characters are sorted, and the way characters are evaluated in comparison operations.
• The sort order to use for non-Unicode character data types (char, varchar, and text).
• The code page used to store non-Unicode character data.

SQL Server 2008 collations controls

• The code page that is used to store non-Unicode data in SQL Server.
• The rules that govern how SQL Server sorts and compares characters that are stored in non-Unicode data types. SQL Server Setup will detect the Windows collation on the computer where SQL Server is being installed. Then, it will automatically select the collation for your instance of SQL Server. Sorts and comparisons might be different for Unicode and non-Unicode characters.

we must choose a collation carefully that matches the type of characters encountered in a particular database. It is important to take into account as well how this database will interact with other database in the same instances, or through a linked SQL Server instances.

Collations have the following configurable attributes, which can generally be deduced from the Collation type name

Character Encoding: What type of character encoding to use (for example for most Western European languages, the Latin1_General is a good character encoding type)

SQL Version: Collations can either be specific to a SQL Server version (for example if Collation type name has “100″, then this is a SQL Server 08 Collation, “90″ indicates a SQL Server 05 Collation

Case Sensitivity: CI indicates “Case Insensitivity” and CS indicates “Case Sensitivity”. If A and a, B and b, etc. are treated in the same way then it is case-insensitive else case-sensitive.

Accent Sensitivity: AI indicates “Accent Insensitivity” and AS indicates “Accent Sensitivity”, Lack of accent identifier in Collation type name indicates “Accent Insensitivity”. If a and á, o and ó are treated in the same way, then it is accent-insensitive.

Width Sensitivity: WI indicates “Width Insensitivity” and WS indicates “Width Sensitivity”, Lack of width identifier in Collation type name indicates “Width Insensitivity”. When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

Kanna Sensitivity: KI indicates “Kanna Insensitivity” and KS indicates “Kanna Sensitivity”, Lack of Kanna identifier in Collation type name indicates “Kanna Insensitivity”.When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

References:

http://msdn.microsoft.com/en-us/library/aa174903%28v=sql.80%29.aspx

http://msdn.microsoft.com/en-us/library/ms144260.aspx

Collation is concerned with how character data is interpreted by SQL Server. Until you run into a problem concerning them, you’re probably blissfully unaware of their existence. The following error is easy to generate by joining columns of different collations.

Msg 468, Level 16, State 9, Line 1 Cannot resolve the collation conflict between "SQLLatin1GeneralCP1CIAS" and "Latin1GeneralCIAS" in the equal to operation.

A collation ties together :

1) Code Page

This is a single-byte character set that represents the alphabet, punctuation & symbols of a language. Data types using a code page are char, varchar & ntext.

Code page does not apply to double-byte (Unicode) characters.

2) Sort Order for Unicode data types

Unicode file types require double byte storage. Data types are nchar , nvarchar and ntext.

3) Sort order for code page characters

Sort order itself is affected by :

Case Sensitivity

In comparison and sorting operations, are uppercase and lowercase characters considered equal? In a Case Sensitive collation 'A' <> 'a', 'Cat' <> 'CAT' etc.

Accent Sensitivity

In comparison and sorting operations, are accented and unaccented characters considered equal? Does 'a' = '?', 'Bronte' = 'Brontë'?

Kana sensitivity

Japan has 2 types of characters Hiragana and Katakana.

In comparison and sorting operations, are Kana sensitivite collation treated them as equal?

Width sensitivity

In comparison and sorting operations, is a single byte character equal to it's double-byte representation?

Collation naming reveals all this information.

For example, the Latin1GeneralCI_AS collation is a Latin code page that is Case Insensitive (CI) and Accent Sensitive (AS).

There are few steps in order to Explore Collations:

Step 1 : What Collation is SQL installed under?

SELECT SERVERPROPERTY('COLLATION')

Step 2 : Which collations are available to me?

SELECT Name, Description FROM fn_helpcollations()

Step 3 : Which databases have a different collation to the server default?

SELECT

NAME AS DATABASE_NAME

, DATABASEPROPERTYEX(NAME,'COLLATION') AS DBCOLLATION

, SERVERPROPERTY('COLLATION') AS SERVERCOLLATION

FROM SYS.DATABASES

WHERE CONVERT(SYSNAME,DATABASEPROPERTYEX(NAME,'COLLATION')) <> SERVERPROPERTY('COLLATION')

Step 4 : Show me the collation for each column in my database

SELECT

C.TABLECATALOG AS DATABASENAME

,C.TABLE_SCHEMA

,C.TABLE_NAME

,C.COLUMN_NAME

,DATA_TYPE

,SERVERPROPERTY('COLLATION') AS SERVER_COLLATION

,CONVERT(SYSNAME,DATABASEPROPERTYEX(D.NAME,'COLLATION')) AS DATABASE_COLLATION

,C.COLLATIONNAME AS COLUMNCOLLATION

FROM INFORMATION_SCHEMA.COLUMNS C

INNER JOIN SYS.DATABASES D

ON DBID(C.TABLECATALOG) = DB_ID(D.NAME)

WHERE DATA_TYPE IN ('VARCHAR' ,'CHAR','NVARCHAR','NCHAR','TEXT','NTEXT')

Step 5 : Show me differences in collation settings on my server.

This produces two results sets :

1) Databases where collation is different from the server setting

2) Columns where collation is different from the database setting

IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS WHERE ID = OBJECT_ID('tempdb.dbo.#CollationComparison')) DROP TABLE #CollationComparison

CREATE TABLE #CollationComparison

(Database_Name SYSNAME

,Table_Schema SYSNAME

,Table_Name SYSNAME

,Column_Name SYSNAME

,Server_Collation SYSNAME

,Database_Collation SYSNAME

,Column_Collation SYSNAME)

DECLARE @SQL NVARCHAR(MAX)

DECLARE @dbname NVARCHAR(200)

DECLARE dbcursor CURSOR FOR

select name from sys.databases

OPEN dbcursor

FETCH NEXT FROM dbcursor INTO @dbname

WHILE @@FETCH_STATUS = 0

BEGIN

print @dbname

SET @SQL = 'INSERT INTO #CollationComparison

(Database_Name

,Table_Schema

,Table_Name

,Column_Name

,Server_Collation

,Database_Collation

,Column_Collation)

SELECT

C.TABLECATALOG AS DATABASENAME

,C.TABLE_SCHEMA

,C.TABLE_NAME

,C.COLUMN_NAME

,CONVERT(VARCHAR,SERVERPROPERTY(''COLLATION'')) AS SERVER_COLLATION

,CONVERT(SYSNAME,DATABASEPROPERTYEX(D.NAME,''COLLATION'')) AS DATABASE_COLLATION

,C.COLLATIONNAME AS COLUMNCOLLATION

FROM [' + @dbname + '].INFORMATION_SCHEMA.COLUMNS C

INNER JOIN SYS.DATABASES D

ON DBID(C.TABLECATALOG) = DB_ID(D.NAME)

WHERE DATA_TYPE IN (''VARCHAR'' ,''CHAR'',''NVARCHAR'',''NCHAR'',''TEXT'',''NTEXT'')

'

exec sp_executesql @SQL

print @sql

FETCH NEXT FROM dbcursor INTO @dbname

END

CLOSE dbcursor

DEALLOCATE dbcursor

SELECT DISTINCT ServerCollation,DatabaseCollation,DatabaseName FROM #CollationComparison WHERE ServerCollation <> Database_Collation

SELECT DISTINCT * FROM #CollationComparison WHERE ColumnCollation <> DatabaseCollation

So, now we got collation differences. Well we can deal with them at the query level, e.g;

in a WHERE clause ...

SELECT columnlist

FROM table

WHERE searchedcolumn COLLATE SQLLatin1GeneralCP1CS_AS = 'Searched Text'

Or in a JOIN

SELECT columnlist

FROM table1

LEFT OUTER JOIN table2

ON table1.textid = table2.textid COLLATE SQLLatin1GeneralCP1CI_AI

NB : Using Collate in this way will prevent the query optimiser from using optimal indexes and creating an efficient execution plan.

If you can change the database schema, you can ....

Change Collation of a column -

ALTER TABLE tablename

ALTER COLUMN columnname datatype

COLLATE collationname

e.g,

ALTER TABLE Person.Contact

ALTER COLUMN Lastname

COLLATE Latin1GeneralCS_AS

Change Collation of a database -

There are 2 approaches to this problem

1) Create a second database by scripting the first and transferring the data. Knowledgebase Article 325335 describes this.

2) IF you are Using Sql 2005 SP2+, you can change your existing database directly.

Firstly change the collation setting –

ALTER DATABASE [adventureworks] COLLATE Latin1GeneralCS_AS

Then change each column individually using the ALTER TABLE ... ALTER COLUMN... syntax.

To Change the default collation of a server:

Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.

Export all your data using a tool such as the bcp Utility. For more information, see Importing and Exporting Bulk Data.

Drop all the user databases.

Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:

Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] /SQLCOLLATION=CollationName

Reinstalling Sql Server is less time consuming in my opinion. Hopefully this will help everyone to understand collation and Collation Sensitivity .

A collation specifies the bit patterns that represent each character in a data set. Collations also determine the rules that sort and compare data.

The Collation Sensitivities are
language sensitivity
case sensitivity
A and a, B and b, etc.
accent sensitivity
a and á, o and ó, etc.
Kana-sensitive
When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.
width sensitivity.
A single-byte character (half-width) and the same character represented as a double-byte character (full-width) are treated differently than it is width sensitive.

Selecting a SQL Server Collation
http://msdn.microsoft.com/en-us/library/ms144250.aspx

SQL Server collations include the following collation sets:

Windows collations
Windows collations - Windows collations define rules for storing character data based on the rules defined for an associated Windows locale. The base Windows collation rules specify which alphabet or language is used when dictionary sorting is applied, as well as the code page used to store non-Unicode character data. For Windows collations, the nchar, nvarchar, and ntext data types have the same sorting behavior as char, varchar, and text data types

Binary collations
Binary is the fastest sorting order.we have two kinds of binary collations: BIN collation, which has collation name ending with _BIN and BIN2 collation, which has collation name ending with _BIN2. “Binary” collation, we use a sorting algorithm based on the binary sequence of the string to sort the string data.

SQL Server
SQL collations are provided for compatibility with sort orders in earlier versions of Microsoft SQL Server.

Setting collations are supported at the following levels of an instance of SQL Server:

(1)Server-level collations

/*You can get a list of all available collations on the server*/  
SELECT * FROM ::fn_helpcollations()

/*You can see the server's current collation*/
SELECT SERVERPROPERTY ('Collation')

Entire server's collation change
You cannot change the entire server's collation any other way. You need to run the installation again to rebuild the master database with the new collation.

   Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName 
   /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] 
   /SQLCOLLATION=CollationName42

http://msdn.microsoft.com/en-us/library/ms179254.aspx

(2)Database-level collations

/*you can get the current collation using this snippet of T-SQL*/
    SELECT name, collation_name 
    FROM sys.databases
    WHERE name = 'dbname'   -- put your database 

/*Change Database Collation*/
     ALTER DATABASE dbname -- put your database name here
     COLLATE Latin1_General_CS_AS   -- replace with whatever collation you need

(3)Column-level collations

   ALTER TABLE myTable ALTER COLUMN mycol NVARCHAR(10) COLLATE Greek_CS_AI

(4)Expression-level collations

   SELECT name FROM myTable ORDER BY name COLLATE Latin1_General_CS_AI

http://msdn.microsoft.com/en-us/library/ms143726.aspx

SQL Server uses the collation setting to determine how non-Unicode character data is stored and how to sort and compare Unicode and non-Unicode data.

Collation means set of rules that determines how data is compared, ordered, and presented. Character data is sorted using collation information, including locale, sort order, and case sensitivity.

The characteristics of collation are as follows:

1. language sensitivity
2. case sensitivity
3. accent sensitivity
4. Kana-sensitive

5. width sensitivity

   --To find all collations available SELECT * FROM fn_helpcollations()

   --To find the SQL Server Collation level select SERVERPROPERTY('collation')

   --To find the database collation level SELECT DATABASEPROPERTYEX('LegacyReferenceDB', 'Collation') as DB_SQLCollation;

What is Collation?

Collation refers to a set of rules that determine how data is sorted and compared.

Character data is sorted using rules that define the correct character sequence, with options for specifying

case-sensitivity, accent marks, kana character types and character width.

Each SQL Server collation specifies three properties:

• The sort order to use for Unicode data types (nchar, nvarchar, and ntext). A sort order defines the sequence in which characters are sorted, and the way characters are evaluated in comparison operations.

• The sort order to use for non-Unicode character data types (char, varchar, and text).

• The code page ued to store non-Unicode character data.

To know the collation of the column for any table just run EXEC sp_help [TableName] in output it will show collation column also

Collation Sensitivity inside SQL Server

Case sensitivity

If A and a, B and b, etc. are treated in the same way then it is case-insensitive.

A computer treats A and a differently because it uses ASCII code to differentiate the input.

The ASCII value of A is 65, while a is 97. The ASCII value of B is 66 and b is 98.

Accent sensitivity

If a and á, o and ó are treated in the same way, then it is accent-insensitive.

A computer treats a and á differently because it uses ASCII code for differentiating the input.

The ASCII value of a is 97 and áis 225. The ASCII value of o is 111 and ó is 243.

Kana Sensitivity

When Japanese kana characters Hiragana and Katakana are treated differently, it is called Kana sensitive.

Width sensitivity

When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

Previous Example no 4 was the same example for this ( Like [A-J] Question ).

Database, Tables and columns with different collation

SQL Server 2000/2005/2008 allows the users to create databases, tables and columns in different collations.

 Examples Databases with different collation

use master
go
create database BIN collate Latin1_General_BIN
go
create database CI_AI_KS collate Latin1_General_CI_AI_KS
go
create database CS_AS_KS_WS collate Latin1_General_CS_AS_KS_WS
go

--Tables and columns with different collation
Create table Mytable 
(
[colu] char(10) COLLATE Albanian_CI_AI_KS_WS NULL,
[Maydate] [char] (8) COLLATE Korean_Wansung_Unicode_CS_AS_KS NOT NULL ,
[Risk_Rating] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
)

--Comparing characters on the databases with different collation
--When we run the below code in CI_AI_KS and CS_AS_KS_WS the results will be completely different. 

declare @Accentvar1 char(1)
declare @Accentvar2 char(1)
declare @Casevar1 char(1)
declare @Casevar2 char(1)
set @casevar1 ='A'
set @casevar2 ='a'
set @Accentvar1 ='a'
set @Accentvar2 ='á'

if @casevar1 = @casevar2
    begin
    	print 'A and a are treated same'
    end
    else
    begin
    	print 'A and a are not treated same'
    end

if @Accentvar1 = @Accentvar2 
    begin
    	print 'A and á are treated same'
    end
    else
    begin
    	print 'A and á are not  treated same'
    end
--When we execute these statements on a CI_AI_KS database, the results are similar to those shown below.

    --A and a are treated same
    --A and á are treated same
    --When we execute these statements on a CS_AS_KS_WS database, the results are similar to those shown below.


--A and a are not treated same
--A and á are not treated same
--Simulating case sensitivity in a case in-sensitive database
--It is often necessary to simulate case sensitivity in a case insensitive database. The example below shows how you can achieve that.

Use CI_AI_KS
go
declare @var1 varchar(10)
declare @var2 varchar(10)
set @var1 ='A'
set @var2 ='a'
if ASCII(@var1) = ASCII(@var2)
print 'A and a are treated same'
else
print 'A and a are not same'

--However, the function ASCII cannot be used for words. In order to achieve the same functionality of simulating case sensitiveness, we can use the varbinary data type.

Use CI_AI_KS
go
declare @var1 varchar(10)
declare @var2 varchar(10)
set @var1 ='Good'
set @var2 ='gooD'
if cast(@var1 as varbinary) = cast(@var2 as varbinary) 
print 'Good and gooD are treated same'
else
print 'Good and gooD are not treated same'

Collation refers to a set of rules that determine how data is sorted and compared.

Character data is sorted using rules that define the correct character sequence, with options for specifying case-sensitivity, accent marks, kana character types and character width.

Case sensitivity - If A and a, B and b, etc. are treated in the same way then it is case-insensitive.

Accent sensitivity - If a and á, o and ó are treated in the same way, then it is accent-insensitive.

Width sensitivity - When a single-byte character (half-width) and the same character when represented as a double-byte character (full-width) are treated differently then it is width sensitive.

Collation handles basically two things.

First is the order in which rows are sorted.

Second is how data elements are compared to each other.

The sensitivity of these two aspects are controlled by:

• Case Sensitivity:: Does "A" = "a"?
• Width Sensitivity:: Does a single-byte character "A" = a double-byte character "A"?
• Accent Sensitivity:: Does "a" = "á"?
• Kana Sensitivity:: Are Hiragana and Katakana characters treated differently?

How does this affect query results?

One example would be if the query uses something like "SELECT TOP 10 ..." The sort order can affect which rows are the TOP 10 rows.

Another example would be if a user was filtering for items that have "cat" in a product's description. The query would look something like... SELECT * FROM Products WHERE Description LIKE "%cat%"

Depending on case sensitivity the user might not get these items...

• "Plastic Cat Toy"
• "Cathode Ray Tube"
• "The Complete Idiot's Guide to CAT Scans"