- Using Create Database command
- Using SQL Sever Management Studio (SSMS).
In this article, I am going to explain the database creation using the command CREATE DATABASE.
Syntax for creating a database through Create database command is given below:-
CREATE DATABASE 'Database name'
ON PRIMARY ( NAME = N'Database name', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Database name.mdf' ,
SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'Database name_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Database name_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
Database name:- This defines the name of the database which we are going to create.
On:-It specifies that the disk files which we are going to use for storing the data section of the database are explicitly defined.
Primary:-It specifies the primary data file that contains the startup information for the database and points to the other files in the database. User data and objects like tables, stored procedures, views can be stored in this primary file (or in secondary data files) . Every database has one primary data file. Primary file has an extension of .mdf
Log on:-It specifies that the log files which we are going to use for storing the logs of the database are explicitly defined. If LOG ON is not specified, one log file is automatically created, which has a size that is 25 percent of the sum of the sizes of all the data files for the database, or 512 KB, whichever is larger.This log file information is used for recovering database. Log file has an extension of .ldf.
Size :- It is the initial size of the file. When size is not supplied for the primary file, the Database Engine uses the size of the primary file in the model database. When a secondary data file or log file is specified, but size is not specified for the file, the Database Engine makes the file 1 MB. The size specified for the primary file must be at least as large as the primary file of the model database.
Filegrowth:- It specify the automatic growth increment of the file. Growth increment is the amount of space added to the file every time whenever there is a requirement of new space.
As per MSDN, Filegrowth can be specified in MB, KB, GB, TB, or percent (%) and if a number is specified without an MB, KB, or % suffix, the default is MB. When % is specified, the growth increment size is the specified percentage of the size of the file at the time the increment occurs. The size specified is rounded to the nearest 64 KB.
Also if we do not want to allowed additional space, we have to give 0 as the value to Filegrowth. Also If Filegrowthis not specified, the default value is 1 MB for data files and 10% for log files. The minimum value is 64 KB.
There are many other optional arguments like Maximum size, collation etc. But in this article, I have explained the main arguments which we needs to mention while creating database using Create Database command.
For example, below SQL query will create the database Company_DB using Create database command.
CREATE DATABASE Company_DB
ON PRIMARY ( NAME = N'Company_DB', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Company_DB.mdf' ,
SIZE = 2048KB , FILEGROWTH = 1024KB )
LOG ON ( NAME = N'VCompany_DB_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Company_DB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
