locked
Database Case Sensitivity RRS feed

  • Question

  • hi
    how can we change a db from being case sensitive to case insensitive after creating it? i had to drop a database to change its collation for it had problems with the kinds of data being stored in it. i changed the collation from default to Arabic_BIN2 and now it has become case sensitive! how can i change it back to case insensitive? it takes too much time to recreate it.

    thank you all very much
    Tuesday, June 23, 2009 10:18 PM

Answers

  • The following code demonstrates changing a database to care about case-sensitive identifiers.  We create a database, and set the collation to Case Sensitive.  We create a table with a varchar value, and then change the collation of the database back.  However, the collation of the varchar column is still case sensitive - something to keep in mind - every table created while the case sensitive collation was enabled will be using the case sensitive collation (unless you specified a different collation).

    CREATE DATABASE CollationTestDB
    GO
    
    USE CollationTestDB
    GO
    
    ALTER DATABASE CollationTestDB COLLATE Latin1_General_CS_AS -- Case Sensitive
    
    CREATE TABLE Table1 (i int IDENTITY PRIMARY KEY, string varchar(50))
    INSERT INTO Table1 (string) VALUES ('STRING')
    
    SELECT * FROM table1  -- Should fail
    GO
    SELECT * FROM Table1  -- Should work
    
    ALTER DATABASE CollationTestDB COLLATE Latin1_General_CI_AS -- Case Insensitive
    SELECT * FROM table1  -- Should work now
    
    SELECT * FROM Table1 WHERE string = 'string' -- Won't work (return nothing) - the column's collation is case sensitive
    SELECT * FROM table1 WHERE string = 'STRING' -- Will work
    
    -- Change the collation of the column
    ALTER TABLE Table1 ALTER COLUMN string varchar(50) COLLATE Latin1_General_CI_AS
    SELECT * FROM Table1 WHERE string = 'string' -- Will work now
    
    USE Master
    GO
    DROP DATABASE CollationTestDB
    GO



    Wednesday, June 24, 2009 12:06 AM
    Answerer
  • Check this link also, please

    http://www.umachandar.com/technical/SQL2000Scripts/Main7.htm

    Regards

    Mona

    Sunday, June 26, 2011 9:20 PM

All replies

  • The following code demonstrates changing a database to care about case-sensitive identifiers.  We create a database, and set the collation to Case Sensitive.  We create a table with a varchar value, and then change the collation of the database back.  However, the collation of the varchar column is still case sensitive - something to keep in mind - every table created while the case sensitive collation was enabled will be using the case sensitive collation (unless you specified a different collation).

    CREATE DATABASE CollationTestDB
    GO
    
    USE CollationTestDB
    GO
    
    ALTER DATABASE CollationTestDB COLLATE Latin1_General_CS_AS -- Case Sensitive
    
    CREATE TABLE Table1 (i int IDENTITY PRIMARY KEY, string varchar(50))
    INSERT INTO Table1 (string) VALUES ('STRING')
    
    SELECT * FROM table1  -- Should fail
    GO
    SELECT * FROM Table1  -- Should work
    
    ALTER DATABASE CollationTestDB COLLATE Latin1_General_CI_AS -- Case Insensitive
    SELECT * FROM table1  -- Should work now
    
    SELECT * FROM Table1 WHERE string = 'string' -- Won't work (return nothing) - the column's collation is case sensitive
    SELECT * FROM table1 WHERE string = 'STRING' -- Will work
    
    -- Change the collation of the column
    ALTER TABLE Table1 ALTER COLUMN string varchar(50) COLLATE Latin1_General_CI_AS
    SELECT * FROM Table1 WHERE string = 'string' -- Will work now
    
    USE Master
    GO
    DROP DATABASE CollationTestDB
    GO



    Wednesday, June 24, 2009 12:06 AM
    Answerer
  • Dear Sir

    please check this link

    http://www.umachandar.com/technical/SQL2000Scripts/Main13.htm

     

    Regards

    Mona

    Sunday, June 26, 2011 9:17 PM
  • Check this link also, please

    http://www.umachandar.com/technical/SQL2000Scripts/Main7.htm

    Regards

    Mona

    Sunday, June 26, 2011 9:20 PM