Solved

SQL Server Collation

Posted on 2014-04-18
13
231 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
13 Comments
 
LVL 65

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
 

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
[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

 
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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

863 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now