Solved

SQL Server Collation

Posted on 2014-04-18
13
243 Views
Last Modified: 2014-05-01
I don't know SQL well, and I don't know what collation is.

What I need to know is if collation is a setting that only affects individual databases or if it is a setting that affects the entire SQL Server (Microsoft SQL 2012)

Reason I ask is because we want to use one big SQL server but we have one particular application that requires specific collation settings...I need to know if they will affect the other databases.
0
Comment
Question by:rand1964
[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
13 Comments
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 100 total points
ID: 40009352
COLLATE can be defined as far down as the column level, so you should be good.
0
 

Author Comment

by:rand1964
ID: 40009574
Let me let you clarify this...

If I am running one Microsoft SQL Server 2012, and I have a SharePoint database, A Log Server Database, a Vulnerability scanner database and a DELTEK database....if I have to do some kind of weird collation on the DELTEK database it WILL NOT cause problems or effect the other databases at all?
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 400 total points
ID: 40010193
You can set it at the server level, which serves as a default for all new databases. But once the database exists you can change its collation independently of any other database.

So the quick answer is Yes, you can change it on one database without affecting the others.
0
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 

Author Comment

by:rand1964
ID: 40010280
One last thing, can you tell me in plain English what collation is and what it does....perhaps I will understand why this one application requires a different collation setting.  Their "technician" said we might have to run another SQL server dedicated to their application on the server that their application resides....that's really, really expensive....and just doesn't sound right to me.
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 400 total points
ID: 40010290
Collation is the rules governing the proper usage of a character set including its storage and ordering.

Different alphabets use different code pages for storing unicode data. The collation setting dictates what code page is in use and how the characters in that code page are treated when it comes to sorting and comparing characters.
0
 

Author Comment

by:rand1964
ID: 40010296
So, in your opinion, this technician doesn't understand that those settings can be totally different for each database hence his recommendation that we buy a separate SQL Server for that one application alone?
0
 
LVL 52

Assisted Solution

by:Carl Tawn
Carl Tawn earned 400 total points
ID: 40010304
If that is what your technician is saying, then s/he is wrong.

You could have 100 different databases on a server each with its own collation if you wanted to. Each would behave entirely independently of the other databases.

The only time it may become an issue would be if you wanted to do a cross-database query  where the two database have different collations.
0
 

Author Comment

by:rand1964
ID: 40010319
The only time it may become an issue would be if you wanted to do a cross-database query  where the two database have different collations.

OK...maybe that is what he is getting at...but if an application requires 3 databases couldn't you set those 3 databases collations to be identical?  I don't know if you can clone or copy and paste databases in SQL or not?
0
 
LVL 52

Accepted Solution

by:
Carl Tawn earned 400 total points
ID: 40010322
Yes you can set them all to be the same. You can still cross-query different collations, you just need to alter the syntax slightly to specify the collation to use when joining.
0
 

Author Comment

by:rand1964
ID: 40010324
THanks!
0
 

Expert Comment

by:Crazy_SQL
ID: 40016041
Hi
The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, and also any newly created user databases. The server collation is specified during SQL Server installation..Hope this answers
0
 

Author Comment

by:rand1964
ID: 40033746
Hi
The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, and also any newly created user databases. The server collation is specified during SQL Server installation..Hope this answers

Now you've confused me....can I use one SQL Server to run SharePoint which runs with out of box setings, and also run another application that requires altered collation settings....different databases...same SQL Server???
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 40034021
Simple answer...yes.

All that last comment is saying is that the collation settings at the server level are used as the defaults when creating new databases. But the collation can be changed at the database level.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

626 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