Basic operations with Database in Microsoft SQL Server

Hello everyone, in the previous posts, I have installed and established a connection to SQL Server with you through SSMS (SQL Server Management Studio).

You can enhance SSMS with dbForge Tools — a bundle of 15 essential SQL Server and Azure SQL development tools

In this next article, I will learn more about the most basic operations with databases in SQL Server through the SQL Server Management Studio interface tool. Ok, let's get started!

#first. Create a new database

+ Step 1: First, open SQL Server Management Studio and then connect to SQL Server.

1. (1) – Right-click on the item Database.
2. (2) – Select New Database…from the drop-down list of options to create a new database.

Basic operations with Database in Microsoft SQL Server Picture 1

+ Step 2: Next, you name the database you want to create in the item Database nameas shown below => Then click OKto confirm creating a new database.

Basic operations with Database in Microsoft SQL Server Picture 3

+ Step 3: After creating this is the general structure of a database in SQL Server. Basically, there are components such as Tables (tables), Views (views) . like other database management systems.

In addition, SQL Server has a built-in part Database Diagrams(design of the database) to help visualize the relationship between the tables in the database.

Basic operations with Database in Microsoft SQL Server Picture 5

#2. How to create a new table (table)

+ Step 1: In the section, Tablesright-click and select New=> select Table…as shown below to create a new table.

Note: In fact, we can create a table with a script (command), but in this article, I am aiming for those who are new to SQL Server, so we will manipulate it all with the tool (SSMS).

Basic operations with Database in Microsoft SQL Server Picture 7

+ Step 2: After creating a new table, we must add columns for that table. Here I take an example that I will create a table studentwith 3 columns that are id, first_name, last_namecorresponding data types bigintand varchar(50)as shown below.

After creating the columns (data fields), I click Ctrl + Sto save. At this time, I will enter the table name as student=> and press OK.

=> So finished creating a table in SQL Server.

Basic operations with Database in Microsoft SQL Server Picture 9

#3. Basic operations with tables in the database

When working with database management systems in general, we will mainly have 4 operations.

1. Create new data (command INSERT)
2. Edit, update data (command UPDATE)
3. Search and filter data by condition (statement SELECT)
4. Delete data (Command DELETE)

In SQL Server it is the same, in the image below:

1. (1) – Group of operations related to editing and searching data
2. (2) – Rename or delete the table.

Basic operations with Database in Microsoft SQL Server Picture 11

Select Top 100 Rows: This is the function that allows you to display the first 1000 records in the table. Next to it is the corresponding SQL statement (you can replace the number 1000 with other numbers).

Basic operations with Database in Microsoft SQL Server Picture 13

Edit Top 200 Rows: is a function that allows you to edit the first 200 records in the table.

Basic operations with Database in Microsoft SQL Server Picture 15

Design: is a function that allows you to view the design structure of the table, you can edit the table structure (add data fields, edit data types .)

Basic operations with Database in Microsoft SQL Server Picture 17

Rename or Delete: are two functions that allow you to rename a table or delete it from the current database.

Basic operations with Database in Microsoft SQL Server Picture 19

There are also some operations such as adding new, searching by condition, you must use SQL statements by creating a new query.

#4. Conclude

Well, above are some basic operations for those who are new to SQL Server database management system.

The operations are mainly done through SSMS without having to use many commands. It can be said that SQL Server is one of the few database management systems that does a good job of visualizing operations for such users.

Marvin Fry

Update 27 May 2021