SQL Server How to Create Database

In the previous post you have learnt about different system databases in SQL Server. In this post you will learn how to create user defined databases in SQL Server.

How to Create a Database

To create a new database, you can use GUI (Graphic User Interface) or can use SQL command in the SQL server. First you will see the GUI approach to create database.

GUI Approach

To create a new database in GUI mode, right click ‘Databases’ folder in Object Explorer window and click New Database menu item. If object Explorer window is not visible to you then click the View menu in Menu bar and click Object Explorer menu item. You can also open the Object Explorer window by pressing F8 key.

In the New Database dialogue box, write the database name e.g. 'AppliedkDB' in the text box and leave other options to default values and click OK button. The database will be created by the name you have written in the text box.

Right click Databases folder in Object Explorer and Refresh it. Expand the Databases folder node to view newly created database 'AppliedkDB'. They are depicted in following steps.

Step1. Right click Databases folder.

Step2. Open New Database dialog box and enter database name e.g. 'AppliedkDb'.

Step3. Switch to Database Options in the dialog box.

Step4. Expand the node of newly created database 'AppliedkDb' to explore its different database objects.

MDF and LDF Files

Note that in the New Database dialogue box, you get details about two files. One of them is log file and another is data file. The data file and log file have file extension MDF and LDF respectively. The MDF file holds the data while LDF file stores the transaction log whenever database is changed.

These MDF and LDF files are created whenever a database is created. They have initial size of 8 MB. These files are saved in disk; you can change their location. Their size increases when tables and other objects are created in the database. 

Database Options

If you press the options tab in New Database dialogue box then you find different recovery modal, compatibility level and containment type of the newly created database. You should use the default options when you create a new database unless you know the impact of changing any of these options.

Right click the newly created database folder after creating the database and click the Refresh menu item. After refreshing, the newly created database will be visible inside the Databases folder.

Database Objects

When a database is created then different folders will be visible inside database folder such as Tables, Views, Synonyms, Programmability, Service Broker, Storage, Security etc. The subfolders are created to manage database objects categorically. When you create a database then system objects will be created automatically inside these subfolders to manage the database.

Originally most of these folders will be empty; for example no table will be visible inside the Tables folder but once you create a table then that table will be visible inside the Tables folder.

SQL Script Mode

The second approach to create a database in SQL Server is by executing SQL script. You can create a database by running CREATE DATABASE command in the script. It is very simple to create a database with default options in command mode. After the CREATE DATABASE command, write the database name as per the identifier rules in SQL Server. For example, running the following command will create AppliedkDB database with default values for database files.

CREATE DATABASE AppliedkDB

In CREATE DATABASE statement, after the database name we can write a number of parameters in square bracket. These parameters are used to define the nature of database e.g.name, filename, size, maximum size, its growth rate etc. We use named parameter approach to assign value to each name parameter. These parameters are basically defining the database files.

When database is created, two types of file- data file and log file are created. These files have a number of properties e.g. name, location, initial size, maximum size, its growth rate etc.

When CREATE DATABASE command is used to create database, these file properties are a specified inside square bracket as a key-value pairs named parameters separated by comma. We look at the following examples to create database by using parameters of files.

Example 1 Create database with 30 MB reserved initial size

CREATE DATABASE Appliedk

ON PRIMARY

(

NAME = appliedkDataFile,

FILENAME = 'D:\TestDb\appliedk.MDF',

SIZE = 25MB,

MAXSIZE = 100MB,

FILEGROWTH = 10MB

)

LOG ON

(

NAME = appliedkLogFile,

FILENAME = 'D:\TestDb\appliedk.LDF',

SIZE = 25MB,

MAXSIZE = 100MB,

FILEGROWTH = 10MB

)

GO

In above example, we have created MDF and LDF files in D:\TestDb folder. The folder must exist before running the script. MDF and LDF files attributes are defined inside square brackets. ON PRIMARY implies that MDF file belongs to filegroup called Primary. NAME is a logical file name and FILENAME is physical file name. You can verify the properties of files in D:\TestDb folder.

Example 2 Create database with multiple Data files

CREATE DATABASE Appliedk

ON PRIMARY

(

NAME = appliedkDataFile1,

FILENAME = 'D:\TestDb\appliedk1.MDF',

SIZE = 20MB,

MAXSIZE = 100MB,

FILEGROWTH = 10MB

),

(

NAME = appliedkDataFile2,

FILENAME = 'D:\TestDb\appliedk2.MDF',

SIZE = 25MB,

MAXSIZE = 100MB,

FILEGROWTH = 10MB

)

LOG ON

(

NAME = appliedkLogFile,

FILENAME = 'D:\TestDb\appliedk.LDF',

SIZE = 25MB,

MAXSIZE = 100MB,

FILEGROWTH = 10MB

)

GO

In above example, we have created two data files and a log file in D:\TestDb folder. Both MDF files belong to Primary filegroup. You can also create multiple log files.

Now we summarize some of important parameters of database files.

1. NAME parameter is to specify logical name of file.
2. FILENAME parameter is to specify physical name of file.
3. Every database file belongs to a FILEGROUP. By default file belongs to PRIMARY filegroup. Use ON PRIMARY clause for this.
4. MAX parameter is used to reserve the initial size of file on disk.
5. FILEGROWTH parameter specifies the rate at which database size will grow over the course of time. The rate is usually specified in MB megabytes.
6. MAXSIZE parameter is used to specify the maximum size that can be allocated to a database; when database size increases it can grow only up to the specified maximum size.
7. LOG ON clause is used to specify the log file.

Some other facts about database

When you use multiple data files for a database then SQL Server automatically stripes information across all the data files but same is not true for log file. When you use multiple log files than SQL Server sequentially writes one log file after another.

Every database as a special property called auto grow feature which allows database to expand automatically. You can disable this feature.

FILEGROUP

File groups feature is used to distribute data in separate groups. For example you can store all the payroll related data in one file group.

We know that a database contains different types of objects such as table, index, and store procedure etc. We can use file group to keep specific types of database object into one file group.

By default SQL Server provides only one file group named as primary; so all the database objects originally belongs to primary file group. But we can create additional file group and make one of them as default file group.

SOME POINTS ABOUT FILE GROUP

A file cannot be member of more than one file group.

The primary data file must belong to primary file group.

You can allocate tables, indexes, and image and text data to a file group.

In database only one file group can be default file group.

Every database has some system files which must belong to primary file group.

Different file groups are created in SQL Server database to provide better performance of the database. DBA should create different file group keeping in mind the business requirement.

You can add or remove a FILEGROUP from database by using alter database command followed by ADD or REMOVE FILEGROUP clause.

Likewise you can remove a data file or log file from the database by using Ultra database command along with remove file clause.

To remove multiple FILEGROUP from the database should use, separated file group name after the remove file group command.

How many databases should be created in real life project?

If you are working in a real world project then you should create development database in which you do all the testing and then n thereafter when the database is ready then the same database is recreated in the production environment.

Should I create my database and table in the designer mode?

Yes you can create them in the designer mode but at the same time you should store save the script generated at the separate location because in the production environment scripts will help you to regenerate the database objects if you have efficiency in writing the script then you should write the script on your own because the system generated scripts are usually verbose.

How can I list all databases of SQL Server?

EXEC sp_helpdb

Problem

Write a script to create a database named as sales with file size 20 MB with a maximum of 40 MB growing by 2 MB and log file size of 5 MB with maximum size 10 MB growing by 1 MB.

In the next post, you will learn about how to drop database in a SQL Server.