In SQL Server (and Azure), the T-SQL DATALENGTH() function returns the number of bytes used to represent any expression.
For example:
SELECT DATALENGTH('Lit');
Result:
3
In this case, there are 3 bytes in the string Lit.
However, this is a simple example. The results can look a lot different, depending on the data type.
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()DATALENGTH()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().
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.
SERVERPROPERTY() FunctionOne 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
Here are two ways to return the collation of a database in SQL Server using Transact-SQL.
sys.databasesThe 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.
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.
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
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
MySQL includes the @@version system variable and the VERSION() function that enable you to find out what version of MySQL you’re running.
Here’s an example of using the VERSION() function:
SELECT VERSION();
This simply returns the version number and no more. Like this:
8.0.11
You can run the following query to return all the collations that are supported in your instance of SQL Server:
SELECT name, description FROM sys.fn_helpcollations();
Note that this returns quite a long list. Running that statement on SQL Server 2017 returns 3955 collations.
Below is a table of all collations supported in SQL Server 2017 (warning: It’s a long list!).
This list was returned by running the following code:
SELECT name, description FROM sys.fn_helpcollations();
That returns the following list of 3955 collations.
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.