Problem
Creating a database in a test environment can be a quick and easy task and can be done using the SSMS GUI or by running a simple T-SQL statement in a SQL Server Management Studio (or SSMS) query window.
The challenge is, however, when you have to think ahead and want to use the database for years in the future to store SQL data, and you want to maximize the creation of the database.
Solution
Creating a database can be straightforward or very involved, depending on the desired level of detail. There are several steps involved in creating a database that can be used in a real-life application. In this tutorial, I will start the process of creating and building up a database that can be used in a web application. This series will guide you through each step.
When writing a tutorial about creating a database, there are many aspects to discuss, and plenty of options. This is just the basics to create a database, either with the SSMS GUI or with SQL scripts.
The database name
You can easily create a database to hold SQL data for a Human Resources system and call it anything, like HRSystTestDB and then start working in the database and before long, the database gets used in a Production environment and a lot of people and systems communicate to it, and then you are stuck with the name because it is too difficult to change the name!
Planning is key here! Do some planning upfront, think about the name and what it should be called in a production environment, and also do some basic planning regarding the information that would be kept in the database. I have seen so many databases and wondered how somebody decided on the database name, the table naming convention, and the schemas. Databases grow very quickly and changing them is very difficult and comes with some pain on the user and systems side!
The easy and basic way of creating a database
When creating a database in Microsoft SQL Server, using SQL Server Management Studio (SSMS) makes it very easy. Open up SSMS and login to the SQL Server where you will be creating a database.
Create a database with SSMS GUI
When SSMS is open, you should see the Object Explorer and some objects below it as in the image below.
Click on the “+” next to Databases to expand the list of databases.
The easiest way to create a database is to right-click on Databases and select New Database.
In the window that pops up, you can simply just type the name of the new database and click OK to create the database. Your new database will be created with the system’s default options.
Create database statement with T-SQL commands
In an open SSMS session, click on New Query to open a new SQL query window. In the SQL query window, just type CREATE DATABASE TestDB, like below, and click Execute or press Alt + X to execute the following command.
-- SQL Create database syntax
CREATE DATABASE [TestDB]
Your new database will be created with the system’s default options.
More advanced way of creating a database
Create a database with SSMS GUI
Follow the same process as above where we created a database with the SSMS GUI, but now you need to pay attention to complete some important options as outlined in red below.
- Script: this will extract a SQL script into a new query window with all of the settings you made on this screen. This allows you to generate a script and run it at a later time instead of clicking on OK which will make the changes immediately.
- Database name: this is the name of your database and in this case: TestDB
- Owner: this is the Owner of the database and should exist already on the server and in this case: TestLogin. In a tutorial later in this series, I will be working through a process to create a SQL Server login and a database user.
- Database files: the names of the Data and the Log files
- Path: the path where the data file and the log file should reside. Important to have them on different drives in a production environment!
- File Name: the file names of the data and log files on the different drive locations as in the point above.
Create a database with T-SQL commands
When you are creating a database in a production environment, you would do some planning upfront.
- Make sure you have enough disk space to host the data and log files
- Also, ensure the data and log files are NOT on the same disk
See the script below with the most important options to create a database.
-- Create a SQL Server Database with the data and log files on different drives
USE master
GO
-- Create database syntax with more specific options
CREATE DATABASE [TestDB]
ON
(NAME = N'TestDB', -- Logical name of the Data file
FILENAME = N'D:\SQLServer\Datafiles\TestDB.mdf', -- The operating system file name
SIZE = 10, -- The size of the file
MAXSIZE = 20, -- The maximum size to which the file can grow, the default is MB
FILEGROWTH = 2) -- The automatic growth increment of the file, the default is MB
LOG ON
(NAME = N'TestDB_log', -- Logical name of the Log file
FILENAME = N'E:\SQLServer\Logfiles\TestDB_Log.ldf', -- The operating system file name
SIZE = 5, -- The size of the file
MAXSIZE = 20, -- The maximum size to which the file can grow, the default is MB
FILEGROWTH = 2); -- The automatic growth increment of the file, the default is MB
GO
It is important to place the data file and the log file on different drives when creating the database in the Production environment and do not use the C:\ drive!
Let us take a look at the options used in the SQL script in the above example to create the TestDB database.
- NAME: this is the logical name of the data or log file
- FILENAME: specifies the system file name of the server
- SIZE: specifies the size of the file
- MAXSIZE: specifies the maximum size to which the file can grow, the default is MB
- FILEGROWTH: specifies the automatic growth increment of the file, the default is MB
Remove or DROP a SQL Server database
To remove the database we created above is also very easy, that is if no users are using the database yet!
Just type the below command to remove the database. First, make sure you are not using the database anymore and change to the master database.
USE master;
GO
-- Drop Database syntax if it exist
DROP DATABASE IF EXISTS TestDB;
GO
Complete scripts to create a SQL database
Here is a complete piece of SQL code that can be used to DROP a database if it exists and then CREATE a new database.
/**********************************************************************/
-- Create a Database with the CREATE DATABASE SQL Script
USE master;
GO
--Drop Database if it exists
DROP DATABASE IF EXISTS TestDB;
GO
--Another way to drop a Database and close connections to it
--Use a Try...Catch block to check first
BEGIN TRY
ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE IF EXISTS TestDB
PRINT 'DATABASE TestDB dropped!'
END TRY
BEGIN CATCH
PRINT 'DATABASE TestDB not available to drop'
END CATCH;
GO
-- Create a Database
CREATE DATABASE TestDB;
GO
USE TestDB;
GO
Here is another example to DROP a database if it exists and then CREATE a new database where you specify some of the database settings.
/*******************************************************************/
--Create a Database with the data and log files on different drives
USE master
GO
--Create the database options with more specific options
CREATE DATABASE [TestDB]
ON
(NAME = N'TestDB', -- Logical name of the Data file
FILENAME = N'D:\SQLServer\Datafiles\TestDB.mdf' ,-- The operating system file name
SIZE = 10, -- The size of the file
MAXSIZE = 20, -- The maximum size to which the file can grow, the default is MB
FILEGROWTH = 2) -- The automatic growth increment of the file, the default is MB
LOG ON
(NAME = N'TestDB_log', -- Logical name of the Log file
FILENAME = N'E:\SQLServer\Logfiles\TestDB_Log.ldf', -- The operating system file name
SIZE = 5, -- The size of the file
MAXSIZE = 20, -- The maximum size to which the file can grow, the default is MB
FILEGROWTH = 2); -- The automatic growth increment of the file, the default is MB
GO
--Remove the Database after use
--Change to the master database
USE master;
GO
--Drop a Database and close connections to it
BEGIN TRY
ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE IF EXISTS TestDB
PRINT 'DATABASE TestDB dropped!'
END TRY
BEGIN CATCH
PRINT 'DATABASE TestDB not available to drop'
END CATCH;
GO
Conclusion
Creating a database is fairly simple and can be done in no time at all whether using the SSMS GUI or using a T-SQL script. Planning a database for long-term use will need some thinking and planning to ensure the longevity and accuracy of the database.
Next Steps
- Look out for the next step in this series: Create Table with SQL Server Data Types
- For more expert knowledge on creating databases and the options that can be used, see the following article SQL Server Create Database Examples
