SQL Server

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

by

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.

Read more

How to Specify the Collation in a Query in SQL Server (T-SQL)

by

In database systems, collation determines how data is sorted and compared in a database. For example, when you run a query using the ORDER BY clause, collation determines whether or not uppercase letters and lowercase letters are treated the same.

In SQL Server, collation is specified at the server level, the database level, and the column level.

Collation can also be applied to a character string expression to apply a collation cast. For example, you can use the COLLATE clause in a T-SQL SELECT statement to specify the collation to be used. Like this:

USE Music;
SELECT ArtistId, ArtistName
FROM Artists
ORDER BY ArtistName COLLATE Latin1_General_CI_AI;

That collation uses CI for case-insensitive, and AI for accent-insensitive.

Read more

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

by

In SQL Server, collation can be specified at the server level, database level, and the column level, as well as inside expressions.

To find out what collation a specific column uses, run a T-SQL query against sys.columns. Like this:

SELECT 
    name, 
    collation_name 
FROM sys.columns 
WHERE name = N'ArtistName';

This results in something like this:

name   collation_name              
----------  ----------------------------
ArtistName  SQL_Latin1_General_CP1_CI_AS

This example returns the collation of a column called ArtistName. Simply replace that with the name of the column you’d like to query.

Read more

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

by

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

Read more

How to Check your SQL Server Version

by

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

Read more

New Features in SQL Server 2017 (Database Engine)

by

Below is a list of new features added in the SQL Server 2017 database engine.

Support for Linux and Mac

For the first time since SQL Server was introduced back in 1989, SQL Server is available on Linux (Red Hat, SUSE, Ubuntu) and Docker.  This means you can also install SQL Server 2017 on a Mac (by using a Linux image in a Docker container).

The initial release of SQL Server 2017 for Linux doesn’t include the full set of features available in the Windows release, but it’s a good start.  Microsoft has stated that it is working on including more features in future releases.

For now, here’s a list of the main features available in SQL Server 2017 for Linux (as of its initial release).

Read more

SQL Server 2017: Available Features on Linux

by

SQL Server 2017 is available on Linux and Docker (which means that it’s also available on Mac). This is the first time SQL Server has been available on a non-Windows platform.

However, not all SQL Server features are available on Linux (at least, not in the initial release).

The following table outlines the main features available in the initial release of SQL Server 2017 on Linux. These are the same features available if you are running SQL Server on a Mac (given the Mac runs SQL Server 2017 via a Linux Docker container).

Read more

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

by

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.

Read more