Case Sensitive

[amit]

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]

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_agarwal20@yahoo.co.in> wrote in message
news:083001c347a2$6e08a440$a401280a@phx.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]]

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_agarwal20@yahoo.co.in> wrote in message
news:083001c347a2$6e08a440$a401280a@phx.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]]

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_agarwal20@yahoo.co.in> wrote in message
news:083001c347a2$6e08a440$a401280a@phx.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
>