How to Create a Database in SQL Server using the T-SQL Query

T-SQL Query to Create a Database in SQL Server
We create at Database in SQL Server, using the below T-SQL Query as follows..

CREATE DATABASE MyAnalyticsDB
ON
(NAME='MyAnalyticsData',
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MyAnalytics_Data.mdf',
SIZE=100 MB,
MAXSIZE=500 MB,
FILEGROWTH =10 MB )
LOG ON
(NAME='MyAnalyticsLog',
FILENAME='C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\MyAnalytics_LOG.ldf',
SIZE=25 MB,
MAXSIZE=250 MB,
FILEGROWTH =10 MB ); 

GO

Here :

MyAnalyticsDB - The Database name

ON -  The ON we used in two places, one defines the location of the file where the data is stored, and other defines the location where the log is stored.

If you use the ON [PRIMARY] , that indicates that the primary (or main) file group in which to physically stores the data.

NAME='MyAnalyticsData' - The logical name of the Database file that SQL Server internally referring.

FILENAME - The Filename indicating the physical name on the disc of the actual operating system file in which the data or log files(based on the sections defined above) will be stored.

By default the file will be located in below(which you can change while installation)

C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\'

The Database file will be saved with .mdf(Master Data File) extension and the log file will be saved with   .ldf(Log Data File) extension.

SIZE - The size of the database. By default the size will be in Megabytes. You can specify them in KB's or GB's or even in TB's as well.

MAXSIZE - The maximum size to which Database can grow. If you don't specify the MaxSize then the it is considered to be no limit. So that it will grow till the disc drive is full.

FILEGROWTH - The Filegrowth indicates the how many bytes(in KB, MB, GB, or TB) the Database to be grow. It determines how fast it grows to the MaxSize.

When the initial size of the Database (100 MB in above example) is full then it starts expanding when new objects/data inserted in to db.

In the above example the Database size start out at 100, and then it expands first time to 110 ( at 10 MB FileGrowth rate), and second time it will increase to 120 MB.
The Database size keep increasing till it reaches to the MAXSIZE. Once it reaches the MaxSize, it will not allow to store any data further, so that it will throw errors.

The same applicable to LOG file as well. Once the Log file is full, it will not store any further logs.
sp_helpdb :
One useful system stored procedure is sp_helpdb. This stored procedure returns information about all of your databases on your server such as the size, owner, when it was created and the database settings.

Exec sp_helpdb MyAnalyticsDB

Result :

Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=782, Collation=Latin1_General_CI_AI, SQLSortOrder=0, IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled

Droping a Database :
DROP DATABASE MyAnalyticsDB

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------