How to Create Database in SQL Server

by Manzoor Siddiqui · Published July 20, 2015 · Updated September 24, 2015

You can create database by SQL Server Management Studio (SSMS) or by T-SQL both methods are quite simple and takes very less time to create. But before creating any database you must analyse your business requirements, database parameters, available resources and no. of users and your working environments. To create a database you need at least one primary data file and one log file.

Now we will see how to create database.

Create Database by T-SQL

Syntax:

CREATE DATABASE <DatabaseName>

Database name is defined as nvarchar(128) with data type as ‘sysname’, you can simply create database with default values in a single line as:

CREATE DATABASE BooksInfo

It will create the database with default path and values but if you want to create as per your requirements then you can create the same as given below. Here each file has it’s logical and physical file name.

CREATE DATABASE BooksInfo
ON PRIMARY
(
 NAME = BooksInfo_Primary, 
 FILENAME = 'C:\Database\BooksInfo.mdf',
 SIZE = 50MB,
 MAXSIZE = 500MB,
 FILEGROWTH = 10%
)

LOG ON
(
 NAME = BooksInfo_Log,
 FILENAME = 'C:\DatabaseLog\BooksInfo_log.ldf',
 SIZE = 10MB,
 MAXSIZE = UNLIMITED, 
 FILEGROWTH = 10MB
)

It will create primary data file as BooksInfo.mdf and log file as BooksInfo_log.ldf but if you want to include one file group also then you can create the database as given below.

CREATE DATABASE BooksInfo
ON PRIMARY
(
 NAME = BooksInfo_Primary, 
 FILENAME = 'C:\Database\BooksInfo.mdf',
 SIZE = 50MB,
 MAXSIZE = 500MB,
 FILEGROWTH = 10%
),

FILEGROUP BooksInfo_G1
( 
 NAME = BooksFileGroup1,
 FILENAME = 'C:\Database\BooksInfo_G1.ndf',
 SIZE = 50MB,
 MAXSIZE = 500MB,
 FILEGROWTH = 10% )

LOG ON
(
 NAME = BooksInfo_Log,
 FILENAME = 'C:\DatabaseLog\BooksInfo_log.ldf',
 SIZE = 10MB,
 MAXSIZE = UNLIMITED, 
 FILEGROWTH = 10MB
)

Now your database will be created with one file group also as BooksInfo_G1.ndf. You can also mention collation with create database command but if you have not specified it then it will consider collation of your SQL Server instance as default.

Create Database by SSMS

To create database with SQL Server Management Studio go to Object Explorer –> right click on Databases –> select New Database –> enter a database name and click OK.

It will create a database with default values and path. If you want you can change the path and default values in new database window and add filegroup as well, by default primary filegroup is set.