Professional Web Applications Themes

Case Sensitive - Microsoft SQL / MS SQL Server

I did the following: 1. create table amit (name varchar(10) not null Primary key) 2. insert into amit values ('a') 3. select * from amit 4. insert into amit values ('A') When I fired the insert statement as in 4th step, i got 'Violation of primary key constraint.' Can anyone tell me is SQL Server Case insensitive. If it really is case insensitive then I am sure there must be some global option for making it case sensitive. Actually I am from Oracle background, and haven't spent much time in SQL Server 2000. What I know off is Oracle being ...

  1. #1

    Default Case Sensitive

    I did the following:

    1. create table amit (name varchar(10) not null Primary
    key)
    2. insert into amit values ('a')
    3. select * from amit
    4. insert into amit values ('A')

    When I fired the insert statement as in 4th step, i
    got 'Violation of primary key constraint.'

    Can anyone tell me is SQL Server Case insensitive.
    If it really is case insensitive then I am sure there must
    be some global option for making it case sensitive.

    Actually I am from Oracle background, and haven't spent
    much time in SQL Server 2000. What I know off is Oracle
    being case sensitive.

    Thanking in advance for help extended.

    Regards,
    Amit

    amit Guest

  2. #2

    Default Re: Case Sensitive

    In earlier versions than 2000, you determined whether it is case sensitive at install time. In
    SQL2K, you define collation for the system db's at install time. You can specify some other
    collation as default for the db when you create the db. And you can divert from db default
    collation in CREATE TABLE. Run below to see collation for the columns:

    EXEC sp_help tblname

    --
    Tibor Karaszi, SQL Server MVP
    Archive at: [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]


    "amit" <amit_agarwal20yahoo.co.in> wrote in message
    news:083001c347a2$6e08a440$a401280aphx.gbl...
    > I did the following:
    >
    > 1. create table amit (name varchar(10) not null Primary
    > key)
    > 2. insert into amit values ('a')
    > 3. select * from amit
    > 4. insert into amit values ('A')
    >
    > When I fired the insert statement as in 4th step, i
    > got 'Violation of primary key constraint.'
    >
    > Can anyone tell me is SQL Server Case insensitive.
    > If it really is case insensitive then I am sure there must
    > be some global option for making it case sensitive.
    >
    > Actually I am from Oracle background, and haven't spent
    > much time in SQL Server 2000. What I know off is Oracle
    > being case sensitive.
    >
    > Thanking in advance for help extended.
    >
    > Regards,
    > Amit
    >

    Tibor Karaszi Guest

  3. #3

    Default Re: Case Sensitive

    You can set the collation for the database to be case sensitive, or you can
    set this at the column level (but you'll need to drop the primary key
    constraint temporarily in order to do this). Take a look at collation in
    books online...



    "amit" <amit_agarwal20yahoo.co.in> wrote in message
    news:083001c347a2$6e08a440$a401280aphx.gbl...
    > I did the following:
    >
    > 1. create table amit (name varchar(10) not null Primary
    > key)
    > 2. insert into amit values ('a')
    > 3. select * from amit
    > 4. insert into amit values ('A')
    >
    > When I fired the insert statement as in 4th step, i
    > got 'Violation of primary key constraint.'
    >
    > Can anyone tell me is SQL Server Case insensitive.
    > If it really is case insensitive then I am sure there must
    > be some global option for making it case sensitive.
    >
    > Actually I am from Oracle background, and haven't spent
    > much time in SQL Server 2000. What I know off is Oracle
    > being case sensitive.
    >
    > Thanking in advance for help extended.
    >
    > Regards,
    > Amit
    >

    Aaron Bertrand [MVP] Guest

  4. #4

    Default Re: Case Sensitive

    Here is a repro you can try to see the difference between CS (case
    sensitive) and CI (case insensitive) collations at the column level.
    Typically, I try to avoid making an entre database or server case sensitive,
    since this can break a lot of existing code.



    CREATE TABLE [dbo].[foooo]
    (
    bar CHAR(1)
    COLLATE SQL_Latin1_General_CP1_CI_AS
    NOT NULL PRIMARY KEY
    )
    GO

    INSERT foooo(bar) VALUES('a')
    INSERT foooo(bar) VALUES('A') -- fails

    GO

    DECLARE constraint_name SYSNAME
    SELECT constraint_name = so.name
    FROM sysconstraints sc
    INNER JOIN sysobjects so
    ON sc.constid = so.id
    AND so.xtype='PK'
    AND sc.id=OBJECT_ID('foooo')

    EXEC('ALTER TABLE [dbo].[foooo]
    DROP CONSTRAINT '+constraint_name)

    ALTER TABLE [dbo].[foooo]
    ALTER COLUMN bar CHAR(1)
    COLLATE SQL_Latin1_General_CP1_CS_AS
    NOT NULL
    GO

    ALTER TABLE [dbo].[foooo]
    ADD CONSTRAINT PKfoooo
    PRIMARY KEY(bar)
    GO

    INSERT foooo(bar) VALUES('A') -- succeeds
    GO

    SELECT * FROM foooo

    DROP TABLE foooo
    GO



    --
    Aaron Bertrand, SQL Server MVP
    [url]http://www.aspfaq.com/[/url]

    Please reply in the newsgroups, but if you absolutely
    must reply via e-mail, please take out the TRASH.


    "amit" <amit_agarwal20yahoo.co.in> wrote in message
    news:083001c347a2$6e08a440$a401280aphx.gbl...
    > I did the following:
    >
    > 1. create table amit (name varchar(10) not null Primary
    > key)
    > 2. insert into amit values ('a')
    > 3. select * from amit
    > 4. insert into amit values ('A')
    >
    > When I fired the insert statement as in 4th step, i
    > got 'Violation of primary key constraint.'
    >
    > Can anyone tell me is SQL Server Case insensitive.
    > If it really is case insensitive then I am sure there must
    > be some global option for making it case sensitive.
    >
    > Actually I am from Oracle background, and haven't spent
    > much time in SQL Server 2000. What I know off is Oracle
    > being case sensitive.
    >
    > Thanking in advance for help extended.
    >
    > Regards,
    > Amit
    >

    Aaron Bertrand [MVP] Guest

  5. #5

    Default Re: case sensitive

    Try changing collation for your where clause to one that is case sensitive. In example bellow I use Latin1_General_CS_AI.
    For list of available sql collations execute the following:

    SELECT *
    FROM ::fn_helpcollations()

    Example

    create table t1
    (name varchar(30))

    insert into t1
    select 'Santos'
    union all
    select 'Santana'
    union all
    select 'Santa'
    union all
    select 'samuel'
    union all
    select 'jorge'
    union all
    select 'hispano'

    select * from t1
    where convert(varchar(10),name) like convert (varchar(10),'Sa')+ '%' collate Latin1_General_CS_AI

    drop table t1

    "sam" <com> wrote in message news:0ce201c3575e$e41c0610$gbl... 


    Dean Guest

Similar Threads

  1. SQL 92 - case sensitive
    By rabaaoui abdelhak in forum MySQL
    Replies: 1
    Last Post: February 28th, 05:49 PM
  2. #23026 [Com]: Make Zend case-sensitive (classes, functions, remove case-insensitive)
    By nvivo at mandic dot com dot br in forum PHP Development
    Replies: 0
    Last Post: October 19th, 12:17 PM
  3. Glob() is Case Sensitive
    By Krhis in forum PHP Development
    Replies: 1
    Last Post: July 26th, 04:30 AM
  4. getPos is case sensitive
    By Lingo Dude in forum Macromedia Director Lingo
    Replies: 9
    Last Post: July 17th, 05:29 PM
  5. Is varaible name case sensitive?
    By Hang in forum ASP
    Replies: 3
    Last Post: July 10th, 08:21 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139