Skip to content
Related Articles

Related Articles

Improve Article

Create Database in MS SQL Server

  • Last Updated : 28 Aug, 2020

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

Attention reader! Don’t stop learning now. Learn SQL for interviews using SQL Course  by GeeksforGeeks.

My Personal Notes arrow_drop_up
Recommended Articles
Page :