?
Solved

About collation

Posted on 2013-05-23
4
Medium Priority
?
503 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 6

Accepted Solution

by:
ianmills2002 earned 500 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 500 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 11

Assisted Solution

by:HuaMinChen
HuaMinChen earned 500 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 500 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example, show how to take different types of Oracle backups using RMAN.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

777 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