Re: changing the SORT Collations

From: Andy Svendsen (andymcdba1_at_NOMORESPAM.yahoo.com)
Date: 02/27/04 

Date: Fri, 27 Feb 2004 14:59:43 -0500

Collation in SQL 2000 is now a per database setting. Before you had to
reinstall SQL to change the collation, and you could not load databases that
had a different sort setting.

In SQL 2000, you can set the collation on a server, database, table and
obviously transact-SQL level setting. To change the sort order for the
server, you have to detach all databases, uninstall and reinstall SQL in the
desired sort order, then convert the existing databases into new ones to
change the sort.

To change the sort of a database do ALTER DATABASE <db_name> COLLATE <new
collation order>

To alter a table, you can change the characters columns with ALTER TABLE

ALTER TABLE MyTable ALTER COLUMN ColumnName
           [varchar|char](size) COLLATE <new collation order> [NOT
NULL|NULL]

Read more about it by downloading SQL books online from
http://www.microsoft.com/sql and looking up 'change collation'. Good luck. 

-- 
*******************************************************************
Andy S.
MCSE NT/2000, MCDBA SQL 7/2000
andymcdba1@NOMORESPAM.yahoo.com
Please remove NOMORESPAM before replying.
Always keep your antivirus and Microsoft software
up to date with the latest definitions and product updates.
Be suspicious of every email attachment, I will never send
or post anything other than the text of a http:// link nor
post the link directly to a file for downloading.
This posting is provided "as is" with no warranties
and confers no rights.
*******************************************************************
"DaSaint" <anonymous@discussions.microsoft.com> wrote in message
news:592DF876-0E85-4B52-8733-7A4D52D62DB7@microsoft.com...
> Hi
>
> I am very new in SQL2000.  My question is , is it possible to change the
sort collation in SQL2000 after installation and how.  Currently my server
is set to case sensitive.  how do I change it to case insensitive?
>
> Also is it possible to do this on my select command instead?  For
instance, my server is case sensitive, but what i want to do is when I do a
select command, I would temporarily remove the case sensitivity so it can
get the desired results.  This setting is only temporary and it goes back to
the original setting once  the session is diconnected.  Is there a way to do
this?
>
> Thank you