What is Collation in Databases?

Posted on by Ian

In database systems, Collation specifies how data is sorted and compared in a database. Collation provides the sorting rules, case, and accent sensitivity properties for the data in the 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.

Collation is also used to determine how accents are treated, as well as character width and Japanese kana characters. Collation can also be used to distinguish between various ideographic variation selectors in certain collations (such as the Japanese_Bushu_Kakusu_140 and Japanese_XJIS_140 collations that were introduced in SQL Server 2017).

Different database management systems will provide different collation options. Depending on the DBMS, collation can be specified at the server level, the database level, the table level, and the column level. Collations can also be specified at the expression level (so you can specify which collation to use when you run a query), and at the identifier level.
Continue reading →

New Features in SQL Server 2017 (Database Engine)

Posted on by Ian

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).
Continue reading →

SQL Server 2017: Available Features on Linux

Posted on by Ian

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).
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 →

How to Rename a SQL Server Database using T-SQL

Posted on by Ian

While you can certainly rename a SQL Server database in the SSMS GUI by right-clicking on the database name and selecting Rename, sometimes you might prefer (or need) to do it using Transact-SQL.

The most basic way to rename a database using T-SQL is like this:

-- Change to the master database
USE master;

-- Change the database name
ALTER DATABASE Films  
Modify Name = Movies; 
GO

The only problem with this basic script is that it doesn’t change the name of the data files and log files. In most cases you’ll probably want to change the names of these files to match the new name. In that case you can take the following script and replace the database name with your own (as well as its file names and paths):
Continue reading →

How to Specify the Location of Data Files and Log Files when Creating a Database in SQL Server

Posted on by Ian

The simplest way to create a database in SQL Server is to use CREATE DATABASE my_database without specifying anything else. When you do this, data files and log files are created in the default location (see how to find the default location).

However, sometimes you might want the data files and log files to reside in a different location. If that’s the case, use the following code example to explicitly state your own location for the database’s data files and log files.

USE master;
GO
CREATE DATABASE Solutions
ON
( NAME = Solutions_dat,  
    FILENAME = 'D:\mssql\data\Solutionsdat.mdf',
    SIZE = 10MB,
    MAXSIZE = 50MB,
    FILEGROWTH = 5MB )  
LOG ON
( NAME = Solutions_log,  
    FILENAME = 'D:\mssql\data\Solutionslog.ldf',
    SIZE = 5MB,
    MAXSIZE = 25MB,
    FILEGROWTH = 5MB );
GO

That example uses Windows path conventions (starts with a drive letter and uses a backslash).

Continue reading →

How to Find the Default File Location for Data Files and Log Files in SQL Server

Posted on by Ian

Any time you create a database in SQL Server, two files are created. One is the data file, and the other is the transaction log file.

The location of these files will depend on whether or not you explicitly specify a location for these files when you create the database. If not, they will be created in the default location.

You can find the default location with the following code:

SELECT
  SERVERPROPERTY('InstanceDefaultDataPath') AS 'Data Files',
  SERVERPROPERTY('InstanceDefaultLogPath') AS 'Log Files'

Continue reading →