SQL Server : Creation of user defined database

User Defined Database:
A Database which is created and managed by the users for storing their objects like Tables, Views, Stored procedures, Triggers, Indexes etc. is called a user-defined database.

A user-defined database can be created in two ways:

1. SQL Server Management Studio UI
2. SQL Query

1) SQL Server Management Studio UI
Upon finishing the installation of MS SQL Server, SQL Server Management Studio will be available to use in your machine.
To open this you can search for the program with its name or you can type ssms in the run command window as shown below.

It will open the SQL Server Management Studio, provide the credentials and login to this studio.
In the left-hand side, we find a Window called object explorer open it and right-click on the node databases and select New database  as shown below

It opens in a new window asking for the database as shown in below image, enter our database name(eg: Employee) and click on OK.

2) SQL Query
we can also create the database by using the SQL  query.

For creating a database using SQL query click on the new query and select the master database as shown in the below figure, it will open a New Query window in the right side panel.

Use the below syntax to create the Database.

Create Database <<Database Name>>

eg: Create Database Employee

With any of the above process, it creates a database and ads under the databases node as shown in below figure

Whenever a database is created on SQL server it will create two system files:
1. Data file
2. Log file

1) Data file
This is the file which contains the data and objects such as tables, indexes and stored procedures.
This data file can either be a primary or secondary data file.
The primary data file contains the startup information for the database and points to other files associated with the database.
Every database can have only one primary data file and saved with an extension .mdf which means matter data file.
The secondary data file is optional and user defined, we use secondary data file when the data is spread across multiple disks by putting each file on a different disk drive.
We can have any number of secondary data files and every secondary data file extension is .ndf that means the next data file

2) Log file
The log file contains all the information that is required to recover all transactions in the database because it holds transactional log information.
There must be at least one log file for each database which is saved with an extension .ldf which means log data file.
To view the .mdf and .ldf files of the database, open your 'My computer' and navigate to the below path:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA

Hope you understand this concept, follow this website for more posts like this.