Home / Database / SQL Server / Database Creation In SQL (Structured Query Language)
database creation in SQL

Database Creation In SQL (Structured Query Language)

Database Creation  In SQL : In SQL (Structured Query Language) Management Studio we can create the database using commands like below.

Database Creation In SQL

Database can be created using simple command.

  • Create Database < Database Name >

E.g. Create DatabaseMyDatabase

Above command will create the “Data File” (i.e. .mdf file) & “Log File” (i.e. Log File) for the database name with default values.
For specifying the Data file & Log file specification manually then we use the following syntax.

  • Create Database < Database Name >

On (<Data File Specification>)

Log On (<Log File Specification>)

Data & Log file specification will include the fields  Name, Filename, Size, Filegrowth&Maxsize.

  • Name: Data/Log file name.
  • Filename: Complete file path e.g. ‘d:\MyDatabase _dat.mdf’ or ‘d:\MyDatabase _log.ldf’.
  • Size: Size of file in MB.
  • Filegrowth: By how much MB you want to increase the file when more data needed to add.
  • Maxsize: What should be the Max Size of file in MB.

E.g.     Create Database MyDatabase

On (Name=MyDatabase_dat, Filename=’d:\MyDatabase _dat.mdf’, Size=5 ,Filegrowth=2 ,Maxsize= Unlimited)

Log On (Name=MyDatabase_log, Filename= ‘d:\MyDatabase _log.ldf’, Size=2, Filegrowth=1  , Maxsize= 500 )

Modifying Structure of db:

  • To get structure of database use the inbuilt store procedure “Sp_helpdb ‘<Database name>'”.

Add File: Using add file, additional data files can be added to the database.

  • Alter Database < Database Name >

Add file (<Data File Specification>)

E.g.    Alter Database MyDatabase

Add File (Name=MyDatabase1_dat, Filename=’d:\MyDatabase1 _dat.mdf’, Size=5 ,Filegrowth=2 ,Maxsize= Unlimited)

Add Log File: Using add log file, additional log files can be added to the database.

  • Alter Database < Database Name >

Add Log File  (<Data File Specification>)

E.g.    Alter Database MyDatabase

Add Log File (Name=MyDatabase1_log, Filename= ‘d:\MyDatabase1 _log.ldf’, Size=2, Filegrowth=1  , Maxsize= 500 )

Modify File: Modify file is used to update the specifications of an existing data/log files of the database.

  • Alter Database < Database Name >

Modify File  (<Data File Specification>)

E.g.  Alter Database MyDatabase

Modify File (Name=MyDatabase1_dat,  Size=5 ,Filegrowth=3 ,Maxsize= 500)

Remove File: Using Remove File, delete files operation can be performed. It is not possible to delete the first data & log file created to database.

  • Alter Database < Database Name >

Remove File  (<Logical Name>)

E.g.  Alter Database MyDatabase

Remove File MyDatabase1_dat

Modify Database Name: To modify db name use below command,

  • Alter Database < Database Name >

Modify Name  <New Database Name>

E.g.  Alter Database MyDatabase

Modify Name MyDatabase1

Deleting db: To delete database use below command,

  • Drop Database < Database Name >

E.g.  Drop Database MyDatabase1

 

Above way you can done with Database Creation  In SQL .

About Helpservices

About This site (allhelpservices.com) is dedicated to helping other bloggers learn the skills of blogging, wordpress, education, technology, social media sharing , SEO, Electronics, Earn Money From Blogging, Web hosting and web share their own experiences and promote the blogging medium. We started Allhelpservices in August 2015 mainly because we wanted to keep a record of what we wear learning from development and because we wanted to connect with other learners.

Check Also

Database

Database level Queries & Identity functions

Get list of tables in the Database: We can get all the tables in the ...

3 comments

  1. Very good blog article.Really looking forward to read more. Awesome.

Leave a Reply