SQL COMMANDS -- DataBase Creation

Every body starts with the select  command in SQL Server. But, how can somebody execute a select command without a database and table. So, In this blog we will see how to create database.

New Database can be created in 2ways.

Database Creation in Object Explorer:
  1. In Object Explorer, connect to an instance of the SQL Server Database Engine and then expand that instancee.
  2. Right-click Databases, and then click New Database.
  3. In New Database, enter a database name.
  4. Enter other details and click OK to create a new database.
  5. To change the default setting, go to Options tab in the below screen.
  6. In Options tab, you can change the Collation, Recovery model and compatability level.
  7. Files groups (secondary etc.,) can be added in the Filegroups Tab.





















After making all required changed Click 'OK' to create a new Database.

Database Creation in Query Window:
Though we can create datbase using object explorer, thats not frequently used.
Many DBA's use query window to create database.

Simple command to create a database with default settings is :

                 CREATE DATABASE TEST

TEST is my database name.

Syntax:  CREATE DATABASE <Database Name>

Database Name can be a maximum of 128 characters.


Here is the typical Database creation query :

CREATE DATABASE [TEST] ON PRIMARY 
( NAME = N'TEST', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST.mdf' , SIZE = 1216KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )LOG ON
( NAME = N'TEST_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST_log.LDF' , SIZE = 504KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)


In the above query Primary data file is created in the path  C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\TEST.mdf

Data and log in SQL Server are stored in mdf files.

Size of the above TEST database is 1216KB and its maximum size is unlimited. Once the data in database exceeds 1216KB , then it will ve incremented by 1024KB and this process will go on.
Thats is database will auto grow to accomadate new data.
Some of the important points about Database:

  1. A database can have only one primary file.
  2. The CREATE DATABASE statement must run in autocommit mode
  3. By Default Auto growth property of Database is set to True.i.e., Database will expand automatically once it reaches its maximum limit. Once the database is created corresponding mdf file will be created in the primary location path:Here is the screen shot for the database creation of TEST Database mdf file ( Both log and prmiary file ).
To confirm whether your database is created or not, follow the below steps:

Use following command to select a database.

USE TEST

Where TEST is the database name. If you command is executed without errors, then your database creation is successful.

Typical Database selection Query :

USE <database Name >


Now, your TEST  database is ready. Start exploring various features and properties of database now. In next blog we will continue will the remaining SQL commands.

This entry was posted in . Bookmark the permalink.

Leave a reply