Home > SQL Server > Collation functions and properties – CollationProperty – SQL Server

Collation functions and properties – CollationProperty – SQL Server


A collation encodes the rules governing the proper use of characters for a language. In simple words, collation in SQL Server provide sorting rules, case, and accent sensitivity properties for the data.

In this blog post, I am going to introduce collation related functions that are useful to learn.

CollationProperty() : This function returns the collation property value of a given collation name and property name. This function needs two mandatory arguments, collation name and property name which are predefined in SQL. We can use these value for the property name argument such as CodePage, LCID, ComparisonStyle and Version. You can use other functions to get collation name.

sp_helpsort : This procedure returns the sort order and character set of the current SQL instance.

fn_helpcollations() : This function returns all collation available in the current SQL instance.

ServerProperty (‘Collation’) : This function returns the collation settings value in the SQL server.

Experiment

I have executed the following SQL Queries in my SQL 2012 instance

select Collationproperty(‘Albanian_CI_AI_KS_WS’, ‘Codepage’) as ‘Code Page’,
Collationproperty(‘Albanian_CI_AI_KS_WS’, ‘LCID’) as ‘Locale ID’
GO

select Top 5 * from fn_helpcollations()
GO

sp_helpsort
GO

SELECT SERVERPROPERTY (‘Collation’) as ‘Default Collation’

Result

pic1

Advertisements
  1. August 22, 2013 at 4:10 pm

    Hi, is there any way to get current database collation similar to Server Collation? A kind of DatabaseProperty(‘Collation’) command?

    Thanks

    • August 23, 2013 at 10:13 am

      My quick thought is using the following query

      SELECT collation_name FROM sys.databases WHERE name = ‘Database Name’

      • August 23, 2013 at 10:52 am

        Hi Ayyappan,
        First at all, thanks for your quick response. The issue I had was caused by temporary tables created within stored procedures. Basically the database that is used to host my stored procedures was created with a collate name different than tempdb. By default, temporary tables use default database (tempdb) collate name if it’s not specified during table definition, and them, if temporary table is used later into a JOIN operation against a physical table which column is specified with a different collate name, that will thrown an error.

        So, I was looking a way to define the collate name for my temporary tables equal to collate name used by current database, and not the default from tempdb, and I finally found a solution:

        CREATE TABLE #temp
        (
        columnname nchar(20) collate DATABASE_DEFAULT
        )

        This will ensure that temporary tables will always use current database collate name and not the default defined on tempdb.

        At least it works for my scenario…

        Thanks,
        Carlos.

  2. August 23, 2013 at 11:02 am

    Carlos Redondo

    Great! Thanks for posting the problem definition and solution. It will definitely help others.

    Thanks
    Ayyappan

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: