Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.
Loading

1st Prize - Nokia Lumia 710


Win 31 copies of

SQLServer Quiz 2012 - DBA had to restore its database on new server runs a query to validate the data.

  • DBA had to restore its database on new server runs a query to validate the data. The same query on the same database started to give different results. After a quick research he figured out that collation was different of both the database. What is Collation and Collation Sensitivity inside SQL Server?

    Posted on 01-06-2012 00:00 |
    InterviewQuestions
    73 · 2% · 775

16  Answers  

Subscribe to Notifications
Previous 1 | 2 Next
  • Score
    7

    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
    
    Replied on Jan 6 2012 12:24AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    5

    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).

    Replied on Jan 6 2012 12:37AM  . 
    SQLShan
    691 · 0% · 48
  • Score
    5

    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
    

    Replied on Jan 6 2012 12:47AM  . 
    Latheesh NK
    51 · 4% · 1178
  • Score
    4

    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

    Replied on Jan 6 2012 1:24AM  . 
    Sineetha
    106 · 2% · 492
  • Score
    5

    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 .

    Replied on Jan 6 2012 3:20AM  . 
    dips
    928 · 0% · 30
  • Score
    7

    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

    Replied on Jan 6 2012 3:36AM  . 
    Mitesh Modi
    18 · 10% · 3080
  • Score
    5

    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;

    Replied on Jan 6 2012 3:39AM  . 
    Hardik Doshi
    20 · 9% · 2853
  • Score
    7

    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'
    
    Replied on Jan 6 2012 3:41AM  . 
    Yogesh Kamble
    142 · 1% · 349
  • Score
    4

    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.

    Replied on Jan 7 2012 11:04AM  . 
    Anup Warrier
    209 · 1% · 224
  • Score
    5

    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"
    Replied on Jan 9 2012 9:13AM  . 
    Cris
    194 · 1% · 242
Previous 1 | 2 Next

Your Answer


Sign Up or Login to post an answer.
Please note that your answer will not be considered as part of the contest because the competition is over. You can still post an answer to share your knowledge with the community.

Copyright © Rivera Informatic Private Ltd.