SQL Server 2005 - How to create case sensitive database

Hi,
I read SQL Server 2000 allows to create databases with different collations which are case sensitive. How do you do that?

Thanks



crazywolf2010Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
Hi. You would create each database with the collation you desire.
http://msdn.microsoft.com/en-us/library/aa258257(v=SQL.80).aspx
crazywolf2010Author Commented:
That is precisely what I want to know .. how?

Thanks
TempDBACommented:
crazywolf2010,
         As the expert (mwvisa1) suggested you the link which provides you with the syntax to create database with a type of collation, you can check them. Apart from that following collation properties are allowed:-

http://msdn.microsoft.com/en-us/library/aa176552(v=sql.80).aspx
http://msdn.microsoft.com/en-us/library/aa176556(v=sql.80).aspx

         If you want to alter the collation setting of a database you can do it with
 ALTER DATABASE [db_name] COLLATE [Collation Name]

But it has certain disadvantages:-
1. You should take the backup of your database before modifying as doing it can result in loss of data.
2.  nText columns will be recreated so your column order will be slightly different.
3. It will effect the indexes and the keys.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

crazywolf2010Author Commented:
OK good I can see details at http://msdn.microsoft.com/en-us/library/aa176552%28v=sql.80%29.aspx
So what should I select there? I can see too much of info on these links and I am looking for specific details.
I do not know much on Collation and what they do within sql server. Sorry for a hassle...

Thanks
TempDBACommented:
SQL_Latin1_General_CP1_CS_AS

Sorry missed one more reference in hurry

http://msdn.microsoft.com/en-us/library/ms143515.aspx

Now, you can combine the subfeatures in above with the features in this link to make it more worakable for you..

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Kevin CrossChief Technology OfficerCommented:
Thanks, TempDBA! I too was in a rush when I posted. Thanks for adding in the details crazywolf2010 needed. crazywolf2010, I think TempDBA did a good job explaining everything, but as a quick follow-up to your question on what collation does: it determines how text is sorted or compared in SQL server. In your case, you are making the database case sensitive; therefore, 'A' and 'a' will be considered unique entries in a database collated as case sensitive. If using the collation suggested by TempDBA, then note that 'À',  'A', and 'a' would all be different values ... with other collation values, those values may be considered one in the same.
TempDBACommented:
Its my pleasure, mwvisa1! Long way to go for getting your level knowledge....
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.