In this tutorial, we will show you how to create a Database in SQL Server. There are two ways by which you can create a Database in SQL Server. One is using the GUI Tools of the SQL Server Management Studio. The other option is to use the Create database SQL Query.
Table of Contents
Connect to the database using SSMS
Open and Connect to SQL Server using the management studio. If you do not have the SQL server installed, then you must install the SQL Server & also install SQL Server Management Studio
Using Create database statement
The simplified syntax of the Create Database statement is as follows.
1 2 3 | Create database <database_name> |
Where database_name is the name of the database. The name of the database must be adhere to rules of naming identifiers
For Example to create a database with the name Sales run the following command
1 2 3 | create database Sales |
To Create the database
- Open the Query Editor by clicking on the
Toolbar → New Queryor from the menuFile → New → Query with Current Connection. - Enter the Query
Create database Sales - Execute the query either by pressing
F5key or click onExecuteoption in the toolbar. Commands completed Successfullymessage appears in the message window.- Right, Click on the Database folder in Object Explorer.
- Click on Refresh.
- You will see the newly created database
Sales
Create Database Using SQL Server Management Studio
The another way to create database is to use the GUI Tools. Follow these steps.
- Select the Database node in object explorer
- Right Click and click on the new database option
- In the New Database dialog box, enter the name of the database as
Sales - Click ok
Database Name must be Unique
The database name must be unique in an Instance. If you try to create a database with the same name server will throw error.
1 2 3 4 5 6 | create database Sales //OK for the first time create database Sales //ERROR Database 'Sales' already exists. Choose a different database name. |
View Currently installed Database
You can view the list of Installed database either from the SSMS or from using the Query
Using SSMS
- Open SSMS and Open the Object Explorer (
Menu → View → Object Explorer) - Connect to an instance of the SQL Server Database Engine (
Connect → Database Engine) - Expand that instance.
- Go to the folder
databasesand expand it to view the list of databases. - Expand the
System databasesand you will see themaster,model,msdb&tempDbdatabases
Using Query
- Open the Query (
Toolbar → New Query) or (Menu → File → New → Query with Current Connection) - Enter the query, which is mentioned below
- Click on
Toolbar → Eexcuteor PressF5
1 2 3 | SELECT name, database_id, create_date FROM sys.databases ; |
The databases (master, model, msdb & tempDb) are automatically created by the SQL Server, when we install the SQL Server.
Location of the Database in the disk
The SQL Server Creates at least one data file & one transaction log file, when you Create a new database. You can find out the location of the files either from the SSMS or using the SQL Query
Using SQL Query
1 2 3 4 5 6 | SELECT DB_NAME(mf.database_id) AS DB_NAME , mf.name AS LOGICAL_NAME, mf.type_desc AS File_Type , mf.physical_name AS Physical_File_Location FROM sys.master_files AS mf ORDER BY mf.database_id, mf.type |
Using SSMS
- Select Database
- Right Click and click on Properties
- Click on Files in Database Properties Window
Syntax of Create Database
Here is the complete syntax of the Create Database statement. You can set many options like location of the data files, File Groups, File size, Growth Size, Max Size etc. Will cover all these in our future tutorials.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 | CREATE DATABASE database_name [ CONTAINMENT = { NONE | PARTIAL } ] [ ON [ PRIMARY ] <filespec> [ ,...n ] [ , <filegroup> [ ,...n ] ] [ LOG ON <filespec> [ ,...n ] ] ] [ COLLATE collation_name ] [ WITH <option> [,...n ] ] [;] <option> ::= { FILESTREAM ( <filestream_option> [,...n ] ) | DEFAULT_FULLTEXT_LANGUAGE = { lcid | language_name | language_alias } | DEFAULT_LANGUAGE = { lcid | language_name | language_alias } | NESTED_TRIGGERS = { OFF | ON } | TRANSFORM_NOISE_WORDS = { OFF | ON} | TWO_DIGIT_YEAR_CUTOFF = <two_digit_year_cutoff> | DB_CHAINING { OFF | ON } | TRUSTWORTHY { OFF | ON } | PERSISTENT_LOG_BUFFER=ON ( DIRECTORY_NAME='<Filepath to folder on DAX formatted volume>' ) } <filestream_option> ::= { NON_TRANSACTED_ACCESS = { OFF | READ_ONLY | FULL } | DIRECTORY_NAME = 'directory_name' } <filespec> ::= { ( NAME = logical_file_name , FILENAME = { 'os_file_name' | 'filestream_path' } [ , SIZE = size [ KB | MB | GB | TB ] ] [ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ] [ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ] ) } <filegroup> ::= { FILEGROUP filegroup name [ [ CONTAINS FILESTREAM ] [ DEFAULT ] | CONTAINS MEMORY_OPTIMIZED_DATA ] <filespec> [ ,...n ] } <service_broker_option> ::= { ENABLE_BROKER | NEW_BROKER | ERROR_BROKER_CONVERSATIONS } |
