SQL Server – How to Configure Collation for the Server, Database, Column, and Query Level in SQL SERVER???

Hey Folks,

You would have seen in my previous posts, that how the Collation Settings is configured during the Installation of SQL Server. You can also refer to that article by the link;

So once the collation setting is decided is being set as the default for all the Server, Database, Column and Query level. It can also be configured by using the Query editor.

SQL Server’s collation order is vital to sorting data because it not determines the alphabet, but also the accents, case, and other alphabet properties. If the collation is case sensitive, then the uppercase letters are sorted before the lowercase letters.

The following function gives you the list of installed collation options:

SELECT * FROM fn_helpcollations();

1_How_to_Configure_Collation_for_the_Server_Database_Column_and_Query_Level_in_SQL_SERVER

The following query reports the current server collation:

SELECT SERVERPROPERTY('Collation') AS ServerCollation;

2_How_to_Configure_Collation_for_the_Server_Database_Column_and_Query_Level_in_SQL_SERVER

The Collation setting for the Server Level can only be configured during the Installation of SQL Server 2008; otherwise the only option to change is to reinstall the SQL Server setup.

The Collation property for a Database or a Column can be set using the COLLATE keyword. The following code changes the ‘Test’ database collation so that it becomes case sensitive:

ALTER DATABASE Test
COLLATE SQL_Latin1_General_CP1_CS_AS;

Now, to view the current database collation properrty, write the following command:

SELECT DATABASEPROPERTYEX('TEST','Collation')
AS DatabaseCollation;

3_How_to_Configure_Collation_for_the_Server_Database_Column_and_Query_Level_in_SQL_SERVER

When you create a table, you can specify collations for each character-string column by using the COLLATE clause of the CREATE TABLE statement. If no collation is specified, the column is assigned the default collation of the database.

The collation of a column can be changed by using an ALTER TABLE statement similar to the following:

ALTER TABLE dbo.Students1 ALTER COLUMN Name NVARCHAR(10) COLLATE Greek_CS_AI

The Collation Property for Query can also be set but only with the combination of ORDER BY clause which will return a set of sorted rows:

SELECT * FROM dbo.ProductResults ORDER BY Name COLLATE Danish_Norwegian_CI_AI;

4_How_to_Configure_Collation_for_the_Server_Database_Column_and_Query_Level_in_SQL_SERVER

Well this was all about the Configuration of Collation Settings.

Hope you like it.

 

Regards

Piyush Bajaj

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook

 
Data Platform Virtual Summit 2020

Subscribe to SQLServerGeeks YouTube channel. If you want more learning content in your inbox, subscribe to SQLServerGeeks Bulletin.

SQLServerGeeks YouTube | SQLServerGeeks Bulletin | SQLServerGeeks Twitter

Avatar

About Piyush Bajaj

I am very passionate about SQL Server. I also did certification on MCSA – SQL Server 2012, Querying and Administering; MCTS – SQL Server 2008, Database Development; and MCTS – SQL Server 2005, Implementation & Maintenance, which helped me to get more knowledge and interest on this field.Please feel free to drop me any question online or offline, I will try to give you the best possible answer from my side.Right now I am working as a SQL Server developer in TCS. I have an experience of just 2.6 years, well I can only say that “If you have an interest and passion, experience might become a very small thing”.

View all posts by Piyush Bajaj →

Leave a Reply

Your email address will not be published. Required fields are marked *