Post reply

Case Insensitive Db on Case Sensitive Server

  • January 16, 2009 at 1:22 am

    #155138

    We have a sql 2005 DB server which uses a case sensitive collation ("binary"). I want to create a database on it using a case insensitive collation.

    When I looked at existing case insensitive databases they use the collation:

    SQL_Latin1_General_CP1_CI_AS

    When I went to create the case insensitive DB, the closest collation I could find it this:

    Latin1_General_CI_AS

    Are these the same collation?

    TIA,

    barkingdog

  • January 19, 2009 at 7:03 am

    #929199

    I think new database needs to be created with SQL_Latin1_General_CP1_CI_AS collation only..

    MJ

  • January 19, 2009 at 7:20 am

    #929212

    As Manu stated you need to use SQL_Latin1_General_CP1_CI_AS

    CREATE DATABASE [yourDbName] ON PRIMARY

    ( NAME = N'yourDbName_data'

    , FILENAME = N'X:\MSSQL.1\MSSQL\Data\yourDbName_data.mdf'

    , SIZE = ..., MAXSIZE = ..., FILEGROWTH = ...)

    LOG ON

    ( NAME = N'yourDbName_log'

    , FILENAME = N'V:\MSSQL.1\MSSQL\Data\yourDbName_log.ldf'

    , SIZE = .... , MAXSIZE = .., FILEGROWTH = ..)

    COLLATE SQL_Latin1_General_CP1_CI_AS

    Caution:

    When mixing collations, keep in mind you may get into troubles when launching cross database queries or even when using tempdb objects (@temptb, #temptb, ##temptb) because of collation conflicts.

    Johan

    Learn to play, play to learn !

    Dont drive faster than your guardian angel can fly ...
    but keeping both feet on the ground wont get you anywhere :w00t:

    - How to post Performance Problems
    - How to post data/code to get the best help[/url]

    - How to prevent a sore throat after hours of presenting ppt

    press F1 for solution, press shift+F1 for urgent solution 😀

    Need a bit of Powershell? How about this

    Who am I ? Sometimes this is me but most of the time this is me

  • January 20, 2009 at 7:44 am

    #929733

    If case insensitivity is your only criteria then "_CI_" is the key factor in the name of the collation you choose. If compatability is also a factor then you need to choose a collation that works with your existing databases (where required) and especially on the same instance (as previous post).

    However SQL_Latin1_General_CP1_CI_AS is provided for backward compatability (and is the default, I think, in SQL 2000). Wherever possible use Windows collations i.e. Latin1_General_CI_AS

  • January 20, 2009 at 6:52 pm

    #930026

    Great answers from everyone. Thanks,

    barkingdog

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply