Collation Questions SQL Server 2005 SP2

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • aj
    #1

    Collation Questions SQL Server 2005 SP2

    A few collation questions on SQL Server 2005 SP2, which I'll call SQLS.

    The default collation for SQLS is apparently SQL_Latin1_Gene ral_CP1_CI_AS.

    I wish to use a variation of this, SQL_Latin1_Gene ral_CP1_CS_AI
    collation, but there is no such collation returned from
    fn_helpcollatio ns(). Also, if I try to use this collation in
    a CREATE DATABASE stmt, SQLS yells about it.

    I see that there is a Latin1_General_ CS_AI. What effects are there
    in using this collation? The SQL_* collations are SQL collations,
    while non-SQL_* collations are Windows collations, yes? SQLS runs
    only on Windows, so am I safe in using Latin1_General_ CS_AI? What
    does the CP1 in the SQL collation signify? Am I asking for trouble?
    ------------------------------------
    Assuming that I set Latin1_General_ CS_AI (or any other case-sensitive
    collation) at the database level, I believe my DDL/DML for that database
    also becomes case-sensitive. How can I specify that I want ONLY my data
    access to be case-sensitive, and not my DDL/DML? I don't want to have
    to remember to type "select * from MyCamelCase" when "mycamelcas e"
    should work.

    Any help appreciated.

    A new SQLS DBA..

    aj
    Tags: None
    • Erland Sommarskog
      #2
      Re: Collation Questions SQL Server 2005 SP2

      aj (ronald@mcdonal ds.com) writes:
      A few collation questions on SQL Server 2005 SP2, which I'll call SQLS.
      >
      The default collation for SQLS is apparently SQL_Latin1_Gene ral_CP1_CI_AS.
      The default collation when you install SQL Server depends on your regional
      settings. SQL_Latin1_Gene ral_CP1_CI_AS is what you get when your regional
      settings is English (US).
      I see that there is a Latin1_General_ CS_AI. What effects are there
      in using this collation? The SQL_* collations are SQL collations,
      while non-SQL_* collations are Windows collations, yes? SQLS runs
      only on Windows, so am I safe in using Latin1_General_ CS_AI? What
      does the CP1 in the SQL collation signify? Am I asking for trouble?
      You should be fine. About everywhere else in the world when you install SQL
      Server, the default collation is a Windows collation. For instance, in my
      case it's Finnish_Swedish _CI_AS (but I always change it to
      Finnish_Swedish _CS_AS.)

      Windows collations are drawn from Windows and Unicode, and the sorting for
      varchar and nvarchar data is the same (save that nvarchar includes far more
      characters). SQL collations on the other hand are completely different
      for varchar and nvarchar. For varchar they are just an 8-bit character
      set, while for nvarchar they are Unicode. The flip side of this is since
      they for varchar only have 255 charcters, operations with varchar are
      quite a bit faster with SQL collations than with Windows collations (save
      binary collations). However, there are also potential for performance
      disasters with SQL collations if you join varchar and nvarchar that
      are less likly to occur with Windows collations.
      Assuming that I set Latin1_General_ CS_AI (or any other case-sensitive
      collation) at the database level, I believe my DDL/DML for that database
      also becomes case-sensitive. How can I specify that I want ONLY my data
      access to be case-sensitive, and not my DDL/DML? I don't want to have
      to remember to type "select * from MyCamelCase" when "mycamelcas e"
      should work.
      You would have to set the database collation to be one that fits your
      preference for identifiers and then explcitly set the collation for each
      column to be case-sensitive.

      My strong recommendation is that you should always develop on a
      case-sensitive collation. If you develop on a case-insensitive collation,
      and the customer then insists on case-sensitive, you may have a complete
      mess to sort out.

      Personally, I don't see the point of using MyCamelCase, if you don't
      care to remember how you originally defined it.


      --
      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

      Books Online for SQL Server 2005 at
      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

      Books Online for SQL Server 2000 at
      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

        Comment

        • aj
          #3
          Re: Collation Questions SQL Server 2005 SP2

          Thanks for your response. This confirms what we suspected.

          Do you have any idea why there is no SQL_Latin1_Gene ral_CP1_CS_AI
          collation? Can I get it somewhere?

          Erland Sommarskog wrote:
          aj (ronald@mcdonal ds.com) writes:
          >A few collation questions on SQL Server 2005 SP2, which I'll call SQLS.
          >>
          >The default collation for SQLS is apparently SQL_Latin1_Gene ral_CP1_CI_AS.
          >
          The default collation when you install SQL Server depends on your regional
          settings. SQL_Latin1_Gene ral_CP1_CI_AS is what you get when your regional
          settings is English (US).
          >
          >I see that there is a Latin1_General_ CS_AI. What effects are there
          >in using this collation? The SQL_* collations are SQL collations,
          >while non-SQL_* collations are Windows collations, yes? SQLS runs
          >only on Windows, so am I safe in using Latin1_General_ CS_AI? What
          >does the CP1 in the SQL collation signify? Am I asking for trouble?
          >
          You should be fine. About everywhere else in the world when you install SQL
          Server, the default collation is a Windows collation. For instance, in my
          case it's Finnish_Swedish _CI_AS (but I always change it to
          Finnish_Swedish _CS_AS.)
          >
          Windows collations are drawn from Windows and Unicode, and the sorting for
          varchar and nvarchar data is the same (save that nvarchar includes far more
          characters). SQL collations on the other hand are completely different
          for varchar and nvarchar. For varchar they are just an 8-bit character
          set, while for nvarchar they are Unicode. The flip side of this is since
          they for varchar only have 255 charcters, operations with varchar are
          quite a bit faster with SQL collations than with Windows collations (save
          binary collations). However, there are also potential for performance
          disasters with SQL collations if you join varchar and nvarchar that
          are less likly to occur with Windows collations.
          >
          >Assuming that I set Latin1_General_ CS_AI (or any other case-sensitive
          >collation) at the database level, I believe my DDL/DML for that database
          >also becomes case-sensitive. How can I specify that I want ONLY my data
          >access to be case-sensitive, and not my DDL/DML? I don't want to have
          >to remember to type "select * from MyCamelCase" when "mycamelcas e"
          >should work.
          >
          You would have to set the database collation to be one that fits your
          preference for identifiers and then explcitly set the collation for each
          column to be case-sensitive.
          >
          My strong recommendation is that you should always develop on a
          case-sensitive collation. If you develop on a case-insensitive collation,
          and the customer then insists on case-sensitive, you may have a complete
          mess to sort out.
          >
          Personally, I don't see the point of using MyCamelCase, if you don't
          care to remember how you originally defined it.
          >
          >

            Comment

            • Erland Sommarskog
              #4
              Re: Collation Questions SQL Server 2005 SP2

              aj (ronald@mcdonal ds.com) writes:
              Do you have any idea why there is no SQL_Latin1_Gene ral_CP1_CS_AI
              collation?
              No. Maybe they never developed this combiniation for SQL collations, but got
              it for free with Windows collation. fn_helpcollatio ns tells me that there
              are no CS_AI SQL collation at all.
              Can I get it somewhere?
              No. There is support for adding collations.

              If you feel strongly about it, you can submit a suggestion on
              http://connect.microsoft.com/SqlServer/Feedback.


              --
              Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

              Books Online for SQL Server 2005 at
              http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

              Books Online for SQL Server 2000 at
              http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                Comment

                Previous template Next
                Working...