Database Creation using Create Database command

In SQL Server, database can be created by 2 ways:-

  1.  Using Create Database command
  2.  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 FilesMicrosoft SQL ServerMSSQL.1MSSQLDATADatabase name.mdf’ ,
 SIZE = 2048KB , FILEGROWTH = 1024KB )
 LOG ON   ( NAME = N’Database name_log’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATADatabase 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.

ON  PRIMARY   ( NAME = N’Company_DB’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATACompany_DB.mdf’ ,
 SIZE = 2048KB , FILEGROWTH = 1024KB )

 LOG ON   ( NAME = N’VCompany_DB_log’, FILENAME = N’C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLDATACompany_DB_log.ldf’ , SIZE = 1024KB , FILEGROWTH = 10%)

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in SQL Basic, SQL Server and tagged , . Bookmark the permalink.

31 Responses to Database Creation using Create Database command

  1. Pingback: see this website

  2. Pingback: สล็อตออนไลน์

  3. Pingback: member qq

  4. Pingback:

  5. Pingback: Steroide kaufen

  6. Pingback: didi promo code

  7. Pingback: 카지노사이트

  8. Pingback: Car Crashes Compilation Russia/USA/Europe 2019 HD

  9. Pingback: โกงชักดาบ

  10. Pingback: w88top

  11. Pingback: Stock Market Economy Gold Silver & Bitcoin 2019 Update

  12. Pingback: Empire Market

  13. Pingback: p2797#

  14. Pingback: Dream Market Deutsch

  15. Pingback: กู้ เงิน ด่วน มาก

  16. Pingback: Samsara Market

  17. Pingback: SEO Services

  18. Pingback: Drogen

  19. Pingback: Stock Markets Keep Making New Highs While the System Implodes Gold Silver Will Rise Soon

  20. Pingback: dang ky tai khoan 188bet

  21. Pingback:

  22. Pingback: sex

  23. Pingback: Cheap flights and hotels

  24. Pingback: sahabat qq

  25. Pingback: qiuqiu99

  26. Pingback: saranapoker

  27. Pingback: Growing Elite Marijuana Pdf Download

  28. Pingback: แทงบอล

  29. Pingback: Attitude Seeds

  30. Pingback: nagaqq

  31. bet168 says:

    You have noted very interesting details! ps decent web site.

Leave a Reply