Solved

SQL Server Collation

Posted on 2014-04-18
13
237 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
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!

 

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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

713 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