leslie

I'm VERY new to SQL. I have a SQL 2000 server and I need
to change a particular column in a table to be case
sensitive. Can anyone recommend a good way to do this? I
can change the entire database if I need to, but I'd
prefer to do only the column if I can.

Thanks in advance for any help!

jasper smith

In SQL 2000 you can specify collation at the column level thus you can use
the COLLATE clause on the column to define a different collation than the
database. However you will have to be carefull if joining to this column and
if trying to use an index on it as all string literals/variables will be
assigned the database collation and make the index unusable unless cast to
the correct collation. See COLLATE and CREATE TABLE in BOL

--
HTH

Jasper Smith (SQL Server MVP)

I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org

I'm VERY new to SQL. I have a SQL 2000 server and I need
to change a particular column in a table to be case
sensitive. Can anyone recommend a good way to do this? I
can change the entire database if I need to, but I'd
prefer to do only the column if I can.

Thanks in advance for any help!

dinesh.t.k

Leslie,

See this sample..

set nocount on
create table casesent
(
ide int identity(1,1),
nme varchar(20)
)
Go
--since we didnt mention any collation, it will take the default.
--To find what collation the table was created, do
sp_help casesent --SQL_Latin1_General_CP1_CI_AS
Go
--Run the below query to find out the collations
--which are case-sensitive
select * from ::fn_helpcollations()
where [description] like '%case-sensitive%'
--I selected a random one.Now changing the collation for nme column
--to be case sensitive
alter table casesent
alter column nme varchar(20) collate SQL_Latin1_General_CP1_CS_AS
Go
--Checking if it works
insert casesent values('leslie')
Go
select * from casesent where nme='Leslie'
Go


No rows as output.


--
Dinesh.
SQL Server FAQ at
http://www.tkdinesh.com