T-SQL CREATE DATABASE Examples
Here are various code examples for creating a database with Transact-SQL (T-SQL).
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
The following example uses 3 filegroups to group the data files. In this case, there's the primary filegroup, the
MusicGroup1 filegroup, and the
The following example attaches creates a database snapshot from the
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.
The following example creates a database and specifies the collation (in this case,
Note that if no collation is specified, the the server collation is used.