Create Database in MS SQL Server

Prerequisite – Introduction of MS SQL Server

Databases are a collection of objects like tables, views, stored procedures, functions, etc. In MS SQL Server, two sorts of databases are available.

• System databases
• User Databases

System Databases :
System databases are created automatically once we install the MS SQL Server. Below is a list of system databases –

• Master
• Model
• MSDB
• Tempdb

User Databases :
User databases are created by users (DBAs, and testers who have access to create database). To create a database, the below methods could be used –

• SQL Server Management Studio.
• Transact-SQL.

1. Using SQL Server Management Studio :
   • Connect to an SQL instance of the SQL Server Database Engine then expand that instance.
   • Right-click Databases, and then click New Database.
   • Enter a database name.
   • To create the database by with default values, click OK.

   
   

   
   
   
   
   

   Create New Database

   
   

   • Otherwise, continue with the following optional steps.
   • To change the owner name, click (…) to pick another owner.
   • To change the default values of the first data and transaction log files, within the Database files grid, click the editable cell and enter the new value.
   • To change the collation of the database, select the Options page, then select a collation from the list.

   
   

   

   Database Options

   
   

   • To change the recovery model, select the Options page, and choose a recovery model from the list.
   • To add more filegroup, click the Filegroups option. Click Add, then enter the values for the filegroup.

   
   

   

   Database Filegroup

   
   

   • To create the database, click OK.

   
   

2. Using Transact-SQL :
   • Connect to the Database Engine.
   • Open New Query.

   Syntax –

   CREATE DATABASE databasename
   [ ON
        [ PRIMARY ] <filespec> [...n ]
        [, <filegroup> [...n ] ]
        [ LOG ON <filespec> [...n ] ]
   ]
   [ COLLATE collation_name ]
   [ WITH <option> [...n ] ]
   [;]

   Example –

   Create database with default settings –

   CREATE DATABASE test;

   Create database with options –

   CREATE DATABASE test  
   ON (NAME = test_dat, --logical datafile name
       FILENAME = 'D:\DATA\testdat.mdf',  --physical datafile name
       SIZE = 10,  MAXSIZE = 50,  FILEGROWTH = 5)  
   LOG ON  (NAME = test_log, --logical logfile name
            FILENAME = 'L:\DATA\testlog.ldf',  --physical logfile name
            SIZE = 5MB,  MAXSIZE = 25MB,  FILEGROWTH = 5MB ) ;  
   GO