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.
- 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.
- 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