Solved

SQL Server Collation

Posted on 2014-04-18
13
225 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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

744 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

13 Experts available now in Live!

Get 1:1 Help Now