Create Database in SQL Server 2019
In SQL Server, a database is made up of a collection of objects like tables, functions, stored procedures, views etc. Each instance of SQL Server can have one or more databases. SQL Server databases are stored in the file system as files. A login is used to gain access to a SQL Server instance and a database user is used to access a database. SQL Server Management Studio is widely used to work with a SQL Server database.
Type of Database in SQL Server
There are two types of databases in SQL Server: System Database and User Database.
System databases are created automatically when SQL Server is installed. They are used by SSMS and other SQL Server APIs and tools, so it is not recommended to modify the system databases manually. The followings are the system databases:
- master: master database stores all system level information for an instance of SQL Server. It includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.
- model: model database is used as a template for all databases created on the instance of SQL Server
- msdb: msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.
- tempdb: tempdb database is used to hold temporary objects, intermediate result sets, and internal objects that the database engine creates.
User-defined Databases are created by the database user using T-SQL or SSMS for your application data. A maximum of 32767 databases can be created in an SQL Server instance.
There are two ways to create a new user database in SQL Server:
Create Database using T-SQL Script
You can execute the SQL script in the query editor using Master database.
USE master;
CREATE <database-name>
The following creates 'HR' database.
USE master;
CREATE DATABASE 'HR';
The Following create 'HR' database with data and log files.
USE master;
CREATE DATABASE [HR]
ON (NAME = N'HR', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\HR.mdf', SIZE = 1024MB, FILEGROWTH = 256MB)
LOG ON (NAME = N'HR_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\HR_log.ldf', SIZE = 512MB, FILEGROWTH = 125MB)
Make sure that the data and log file path exist before executing the above SQL script.
Now, open SSMS and refresh the databases folder and you will see 'HR' database is listed.
Learn more on Create Database SQL Script Syntax.
Create Database using SQL Server Management Studio
Open SSMS and in Object Explorer, connect to the SQL Server instance. Expand the database server instance where you want to create a database.
Right-click on Databases folder and click on New Database.. menu option.
In New Database window, enter a name for the new database, as shown below. Let us enter the database name ‘HR'.
The Owner of the database can be left at default or to change the owner, click on […] button.
Under the Database files grid, you can change the default values for the database and the log files. Every SQL Server database has at-least a minimum of two operating system files: Data file and Log file.
- Data Files contain data and objects like tables, views, stored procedures, indexes etc.
- Log files contain the information required to recover all transactions in a database. There must be at-lease one log file for each database. Learn more about Database Files and Filegroups
Make it as large as possible based on the maximum amount of data you expect.
To change database options, select the Options page. You can change the Collation, Recovery model under this tab, as shown below.
Collation specifies the bit patterns that represent each character in a dataset. SQL Server supports storing objects having different collations in a single database.
Recovery model is a database property that controls how transactions are logged. There are three options under Recovery models: Simple, Full & Bulk-logged. Typically a database uses a Full recovery model.
Compatibility Level lists SQL Server 2008, 2012, 2014, 2016, 2017 & 2019. The latest version installed i.e., SQL Server 2019 is selected by default.
Containment type has two options: None and Partial. None is selected by default.
Now, select Filegroups tab. Filegroups are the physical files on your disc, where SQL server data is stored. By default a Primary data file is created while creating a new database. Learn more about Files and Filegroups.
Click Ok to create a new 'HR' database. This will be listed in the database folder, as shown below.
In the above figure, the new 'HR' database is created with the following folders:
Database Diagrams: It graphically shows the structure of the database. You can create a new database diagrams by right-clicking on the folder and selecting Create New Diagram
Tables: All the system and user defined tables associated with the database are available under this folder. Tables contain all the data in a database.
Views: All the System and used defined views are available under this folder. System views are views that contain internal information about the database.
External Resources: Any Service, computer, fileshare, etc that are not a part of the SQL Server installation are stored here. Contains 2 folders 1) External Data Sources 2) External File Formats
Programmability: The Programmability folder lists all the Stored Procedures, Functions, Database Triggers, Assemblies, Rules, Types, Defaults, Sequences of the database
Service Broker: All database Services are stored in this folder
Storage: Stores information on Partition Schemes, Partition Functions, Full Text Catalogs,
Security: Database Users, Roles, Schemas, Asymmetric Keys, Certificates, Symmetric Keys, Security policies are created & available in the Security folder of every database.
Thus, you can create a new database in SQL Server using T-SQL script or SSMS.
