How to Create Database in Microsoft SQL Server?

Introduction

Microsoft SQL Server database is one of the most common databases in use that is easy to use and maintain. This is a relational database management system that is used to store and retrieve data from other applications. The SQL server database can be used on the same computer or it can be used across a network. 

In this blog, we will discuss how to create a SQL Server Database for GUI (Graphical User-Interface) so that the requirement for Command-Line Prompt can be eliminated.

To go ahead with the database concepts, it is always good to have SQL server installed on your system so that you may know how it works actually. 

Read: What is Foreign Key in SQL? How to Set, Add, Create & Use of Foreign Key

“To learn the practical concepts of the SQL Server online, register for a Demo class now!”

SQL Server Components

The SQL server architecture is based on a client-server architecture that is further divided into two major parts – workstation components and server components.

• Workstation Components – These components are stored in every SQL server operator machines like SSMS, SSCM, BIDS etc.
• Server Components – These are the services that are usually installed on the centralized server like SSAS, SSRS, and SSIS etc.

How to create a Database in SQL

How to install, run and open the SQL Server Management Studio Software

This Software is available for free from Microsoft to create and manage the SQL databases. The objective of this software is to make database handling easier with Graphical User-Interface instead of Command-Line prompt. It allows you to connect to a remote instance of an SQL Server and it is generally used by the administrator, testers, and the Developers. 

Here are two quick methods that explain how to open SQL Server Management Studio Software on your computer.

Once you will open the Management Studio Software, it will ask you to connect. If you have the permissions then you can connect to it by giving server address and the authentication details.

Read: SQL Server Views - Everything You Should Know

• To create the local database, set the Database Name and select the authentication type to “Windows Authentication”. Now Click on ‘Connect’ to continue with the local database creation.

How to Locate the Database Folder?

Once the connection is established with the server, this does not matter either it is local or remote, the object explorer windows will get opened at the left side of Window. At the top, it will show the server address, you are connected to.

• As soon as you click on Connect button, it will connect you to the server and you can check the server address in the ‘Left’ window. By default, the server address is expanded if it is not expanded then click on “+” button to locate the database folder.

How to create a new database?

Once you have located the database folder, this is the time to explore it. As soon as you will Right Click on the folder, ‘New Database’ option will appear to create a fresh database. Before the final creation of a database, give it a proper name so that you can identify it quickly. You can leave the rest of settings in default mode at the beginning level.

Once a new database is created, two files will be created automatically. These are the data files and the log files. The data file takes care of all the data that you want to store in database and log files is used to track the changes. As soon as, you click on the ‘OK’ button, Database is created with the cylindrical icon.

• Explore the Database Folder, give a proper name and leave rest of the settings in default mode only. Now click on the ‘OK’ button to create a fresh database with the cylindrical icon.

Read: SQL Server Recovery Models-Simple, Full and Bulk Log

How to create a table in the database?

A database cannot store data without a proper structure. This is the reason you need to create a table to define the proper schema and structure for the database. A table takes the responsibility to store data in the organized format for quick access and retrieval.

• To create a table, expand the new database folder under the main ‘Database’ folder. Right-click on the new database folder and select ‘New Table’ to start your work. A new table will appear in the Window where you can manipulate data, store it, and retrieve it.

How to create a primary key for the tabl

This is highly recommended to create a primary key for the first column of the table. The primary key is the unique ID or it is termed as the record number that will allow you to recall all the entries quickly at your convenience.

Read: What is SQL Delete Query? How to Delete Duplicate (Records, Rows, Tables)

Further, you need to define a data type for the column like ‘int’, ‘char’ etc. you should not allow the null values for the first column otherwise data access may be tough for you. In case, you allow the Null Values then the first entry will be ‘ZERO’ otherwise it would be at least one.

• In the Column properties, look for column specification option and set the identity one. It will increase the ID value automatically by one each time you enter a new value in the column. 

Understanding the structure of the table

The table is always composed of fields or columns and each column needs to have specific data type to store the values. For examples, if you wanted to create a Student database then a possible number of columns may be “Student Name”, “Address”, and “Phone number” etc. For name and address, we can define the ‘char’ data type while for the phone number column data type can be declared as ‘int’ to store the integer values.

Read: Delete vs Truncate SQL Server – What are the Differences?

Once you have finished with the primary column, you can see other fields appear underneath it. This function allows you to switch to the next column, now fill out the fields as per your convenience discussed earlier and fetch the right details from the columns as required.

• Most frequently used datatypes in Microsoft SQL Server are char, int, decimal etc.Once you have created all important columns for your table, this is the time to save it. Give a proper name to the table so that you can identify it quickly. This is highly mandatory for large databases where multiple tables are created. 

Execute the table, save data and query data

The table execution is simple in SQL database that can be completed with the help of a single button or a single command. Let us see how it is done actually-

Read: Job Roles and Responsibilities of a SQL Server Developer you Need to Know

• You can use execute SQL button on the toolbar once all information is entered to the columns. This is a red exclamation button with SQL label on it. Instead, you can use CTRL + R to execute the table and save the data to it.

At this point, you have created a well-structured database as per your requirements. You are free to create multiple tables as much you want. There is a limit for table creation for each database until you don’t work on the enterprise level database management system. Also, you can query data or reports for administrative purposes. If you working in command line prompt then SQL programming language is used by the SQL server.

Final Words:

In a nutshell, you have hands-on experience now on how to create a new database and table in Microsoft SQL server. You know how to insert values into the column and retrieve it. This is easy to execute the table, save data in the table and query the table data. 

Read: What is SQL Server Replication and How it Works?

This would be great if you are working on GUI instead of command line prompt. The graphical interface is more interactive and easy to use by learners either beginners or experienced. Take a tour to advance database programming with JanBask training now. We not only help with the training and mastered the database skills for you to build the experts.

****Happy Learning and All the best for your future as a database programmer****