Solved

About collation

Posted on 2013-05-23
4
489 Views
Last Modified: 2013-05-24
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?
0
Comment
Question by:sepknow
4 Comments
 
LVL 6

Accepted Solution

by:
ianmills2002 earned 125 total points
ID: 39193255
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
 
LVL 1

Assisted Solution

by:CalvinWin
CalvinWin earned 125 total points
ID: 39193408
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
 
LVL 10

Assisted Solution

by:HuaMinChen
HuaMinChen earned 125 total points
ID: 39193414
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
 
LVL 15

Assisted Solution

by:Franck Pachot
Franck Pachot earned 125 total points
ID: 39194914
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question