About collation

Hi Experts,

1) I have installed SQL Server 2008 and noticed that the default collation installed is SQL_Latin1_General_CP1_CI_AS. This collation is "not case sensitive", unlike my existing Oracle database.

May I know which collation should I choose to match (as near or close as possible) the "default" collation in Oracle?

2) Is it possible to change the collation of my new SQL database without re-installing? If yes, how?
sepknowAsked:
Who is Participating?
 
ianmills2002Connect With a Mentor Commented:
1) Try
SQL_Latin1_General_CP1_CS_AS

The CS stands for Case Sensitive. CI stands for Case Insensitive

2) Anytime I have got the collation wrong, I have recreated the database. I do not know if it can be done without recreating the database.
0
 
CalvinWinConnect With a Mentor Commented:
Please refer to the below link to change the collation.

http://blogs.msdn.com/b/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

This command will rebuild all the system databases. You can attach back all the user databases later but all the SQL logins and agent jobs will be gone because of master, msdb and model database are rebuild too. Better generate the scripts for your login and agent jobs as well as other server objects if you have.
0
 
HuaMin ChenConnect With a Mentor System AnalystCommented:
A collation encodes the rules governing the proper use of characters for either a language, such as Greek or Polish, or an alphabet, such as Latin1_General (the Latin alphabet used by western European languages). Please read
http://msdn.microsoft.com/en-us/library/aa174903%28v=sql.80%29.aspx

No need to reinstall as you can change it like
SELECT		a.User_ID,
FROM		dbo.Table_A AS a
WHERE		NOT EXISTS	(
					SELECT	*
					FROM	dbo.Table_B AS b
						WHERE	b.User_ID = a.User_ID COLLATE DATABASE_DEFAULT
						AND b.Attr_B = 'VALIDATED'
				)
ORDER BY	a.User_ID

Open in new window

0
 
Franck PachotConnect With a Mentor Commented:
Hi,
There is no "default" collation in Oracle, but you can check it with:
select * from nls_database_parameters where parameter='NLS_CHARACTERSET';
... just to be sure.
Regards,
Franck.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.