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
My Personal Notes arrow_drop_up

Check out this Author's contributed articles.

If you like GeeksforGeeks and would like to contribute, you can also write an article using contribute.geeksforgeeks.org or mail your article to contribute@geeksforgeeks.org. See your article appearing on the GeeksforGeeks main page and help other Geeks.

Please Improve this article if you find anything incorrect by clicking on the "Improve Article" button below.


Article Tags :
Practice Tags :


Be the First to upvote.


Please write to us at contribute@geeksforgeeks.org to report any issue with the above content.