Tag: functions

LEN() vs DATALENGTH() in SQL Server

Posted on by Ian

When using T-SQL in SQL Server (or Azure) the LEN() and DATALENGTH() functions will often return the same result, but not always. There are some cases where these functions will return completely different results for what appears to be the same data. This is because there’s an important difference between how the LEN() and DATALENGTH() functions work, as we’ll see here.

First up, here’s a quick definition of each:

LEN()
Returns the number of characters of the specified string expression, excluding trailing blanks.
DATALENGTH()
Returns the number of bytes used to represent any expression.

Note “characters” vs “bytes”. Also note that “excluding trailing blanks” only applies to one.

Here are some examples to demonstrate the differences between LEN() and DATALENGTH().
Continue reading →

How to Show the Server Collation in SQL Server (T-SQL)

Posted on by Ian

The server collation acts as the default collation for all system databases that are installed with the instance of SQL Server, as well as any newly created user databases. The server collation is specified during SQL Server installation.

Below are two ways to check the server collation in SQL Server using Transact-SQL.

The SERVERPROPERTY() Function

One option is to use the SERVERPROPERTY() function:

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation';

This returns the collation like this:

Server Collation
----------------------------
SQL_Latin1_General_CP1_CI_AS

Continue reading →

How to Show the Collation of a Database in SQL Server (T-SQL)

Posted on by Ian

Here are two ways to return the collation of a database in SQL Server using Transact-SQL.

Query sys.databases

The first option is to run a query against sys.databases to return the collation of a specific database. The WHERE clause allows you to narrow the results down to the database/s you’re interested in:

SELECT 
    name, 
    collation_name 
FROM sys.databases
WHERE name = 'Music';

This results in something like this:

name   collation_name              
-----  ----------------------------
Music  SQL_Latin1_General_CP1_CI_AS

In this case, we specified the database called Music.
Continue reading →

How to Find the Collation in SQL Server (T-SQL)

Posted on by Ian

Collation can be specified at the server level, database level, column level, expression level, and the identifier level. A different method is required for each of these.

Server Level Collation

Here’s how to find the server level collation:

SELECT CONVERT (varchar, SERVERPROPERTY('collation')) AS 'Server Collation';

This returns the collation like this:

Server Collation
----------------------------
SQL_Latin1_General_CP1_CI_AS

Continue reading →

How to Check your SQL Server Version

Posted on by Ian

SQL Server, or more specifically, Transact-SQL, includes a number of built in functions, including the @@version scalar function. The @@version function is a configuration function, which returns system and build information for the current installation of SQL Server. You can run this function at any time to find out which version of SQL Server you’re using.

Here’s how:

SELECT @@version;

When running that in a command line interface, you might see results that look like this:

Microsoft SQL Server 2017 (RTM-CU6) (KB4101464) - 14.0.3025.34 (X64) 
	Apr  9 2018 18:00:41 
	Copyright (C) 2017 Microsoft Corporation
	Developer Edition (64-bit) on Linux (Ubuntu 16.04.4 LTS)

1 row(s) returned

Executed in 1 ms

Continue reading →

How to Change the Collation of a SQL Server Database using T-SQL

Posted on by Ian

This is a quick article to demonstrate how to use Transact-SQL to change the collation of a database in SQL Server.

Here’s the basic code:

USE master;  
GO
 
ALTER DATABASE Solutions  
COLLATE French_CI_AS ;  
GO

This first changes to the master database, then we use the ALTER DATABASE statement to change the collation of the Solutions database to French_CI_AS collation.
Continue reading →