• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 267
  • Last Modified:

SQL Server Collation

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
rand1964
Asked:
rand1964
5 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
COLLATE can be defined as far down as the column level, so you should be good.
0
 
rand1964Author Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
rand1964Author Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
rand1964Author Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
rand1964Author Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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
 
rand1964Author Commented:
THanks!
0
 
Crazy_SQLCommented:
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
 
rand1964Author Commented:
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
 
Carl TawnSystems and Integration DeveloperCommented:
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now