New Features in SQL Server 2017 (Database Engine)

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

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

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

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

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

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