T-SQL CREATE DATABASE Examples

Here are various code examples for creating a database with Transact-SQL (T-SQL).

Basic Code

The following code creates a database called Music.

That is the most basic statement you can use to create a database. If you don't have any specific requirements, this code should be sufficient.

However, you can modify the above code to the following:

This script ensures that you're using the master database before you attempt to create the database. It also drops (i.e. deletes) any existing database that has the same name of the one that you're trying to create.

Location of the Data and Transaction Log Files

You can also specify a location for the data and transaction log files (if you don't want the files to be created in the default location).

When you do this, NAME provides the logical name of the file, and FILENAME provides the physical path to the file on the operating system's file system.

Multiple Data and Transaction Log Files

The following example creates multiple data and transaction log files. We use a different name for each one. We also specify the size, maximum size, and filegrowth for each file.

We also use the PRIMARY keyword to specify that Music1 is the primary data file. The primary data file has an extension of .mdf, whereas the secondary data files have .ndf extensions.

Filegroups

The following example uses 3 filegroups to group the data files. In this case, there's the primary filegroup, the MusicGroup1 filegroup, and the MusicGroup2 filegroup.

USE master;  
GO  
CREATE DATABASE Music  
ON PRIMARY  
( NAME = MusicPrimary1_dat,  
    FILENAME = 'D:\mssql\data\MusicPrimary1.mdf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 15% ),  
( NAME = MusicPrimary2_dat,  
    FILENAME = 'D:\mssql\data\MusicPrimary2.ndf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 15% ),  
FILEGROUP MusicGroup1
( NAME = MusicGroup1File1_dat,  
    FILENAME = 'D:\mssql\data\MusicGroup1File1.ndf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 5 ),  
( NAME = MusicGroup1File2_dat,  
    FILENAME = 'D:\mssql\data\MusicGroup1File2.ndf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 5 ),  
FILEGROUP MusicGroup2  
( NAME = MusicGroup2File1_dat,  
    FILENAME = 'D:\mssql\data\MusicGroup2File1.ndf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 5 ),  
( NAME = MusicGroup2File2_dat,  
    FILENAME = 'D:\mssql\data\MusicGroup2File2.ndf',  
    SIZE = 10,  
    MAXSIZE = 50,  
    FILEGROWTH = 5 )  
LOG ON  
( NAME = Music_log,  
    FILENAME = 'D:\mssql\data\Log\Musiclog.ldf',  
    SIZE = 5,  
    MAXSIZE = 25,  
    FILEGROWTH = 5 );  
GO

Database Snapshot

The following example attaches creates a database snapshot from the Music database.

The following example attaches a database with multiple filegroups (it uses the same database created in the above Filegroups example).

Attach a Database

The following example attaches the Music database using the FOR ATTACH clause.

Database Collation

The following example creates a database and specifies the collation (in this case, Latin1_General_100_CS_AS_SC).

Note that if no collation is specified, the the server collation is used.