SQL Server CRUD Operations – Part 2

SQL Server CRUD Operations – Part 2
3.6 (72%) 5 vote[s]

CRUD word is an acronym of the following essential database functions.

In the first part of CRUD article series, we mentioned the concept of the CRUD operations and then discussed the INSERT operation with examples. In this part, we will continue exploring the CRUD operations and will focus on the read, update and delete operation details of CRUD.

R- Read

The Read operation is used to retrieve data from a table and then it returns a result set which includes the records of the table. If we want to retrieve data of more than one table, we can use the JOIN operator. So we can create a logical relation between tables with the help of the JOIN operator. The SELECT statement plays a single primary role in the read operation. The SELECT statements are based on three parts:

Column part: In this part, we can define the columns from which we want to retrieve data.

Table part: In this part, we specify the table from which we want to get data.

Filter part: In this part, we can filter the data that we want to read. This part is not mandatory, so the SELECT statement can be executed without any filter part.

The simplest form of the select statement will be like the following form:

Now, we will go through the examples and reinforce our theoretical knowledge. At first, we need a sample table to read and for this reason, we will create a sample table with the help of the following query.

Reading all columns in the table

The asterisk (*) operator has wide usage in the SELECT statements because it returns all columns in the table. In the following example, we will return all columns and rows present in the table.

As you can see, in this select statement, we used the asterisk operator (*) and it allows us to return all columns of the table.

Tip: If you don’t need all columns of the table, you don’t need to use the asterisk (*) operator in the SELECT statement. The main idea behind this suggestion is that the asterisk (*) operator can influence the performance negatively because it causes more network traffic and uses more resources.

Reading particular columns of the table

In the SELECT statements, we can read the particular columns of the table. In the following example, we will return only the CountryName and CountryPopulation columns.

Using alias in the SELECT statements

In the SELECT statements, we can give temporary names to the table or to the columns and these temporary names are called aliases. Now, we will rewrite the previous two queries with table and column aliases. In the following query, the TblC alias will specify the table name.

In the following example, we will provide aliases for the column names so we will change the CountryName column name to CName and the CountryPopulation column name to CPop.

The usage purposes of the alias are:

  • Make the query more readable if the table or column names are complex.
  • If a table is using a query more than one time.
  • It can reduce the query writing effort if the table or column name is long.

Filtering SELECT statements

In SELECT statements, we can filter the result set through the WHERE clause. For example, if we want to filter the SELECT statement according to CountryName column and only return the data of Germany into resultset. The following query will perform read operation with a filter.

Sorting SELECT statements results

The ORDER BY clause helps to sort the result set of the SELECT statement according to specified column or columns. We can perform the ascending or descending sorting with the help of the ORDER BY clause. For example, we would like to sort the TblCountry table according to the population of the countries in the ascending order.

As you can see from the following image, the resultset of the query is sorted to ascending order.

Tip: You can use the column index in the ORDER BY clause and columns index numbers start by 1. We can also write the previous query as follows. Three (3) number indicates the CounrtyPopulation column.

U- Update

The UPDATE statement is used to modify the existing data in the table. The Update statement must include a SET clause so that we can define the target column in which we want to modify the data. The following query will change all rows of the CounrtyPopulation column value to 1.

In the UPDATE statements, we can use the WHERE clause so that we can modify particular row or rows in the table. Now, we will change the Japan row of CounrtyPopulation to 245000.

Actually, the UPDATE statement is a union of the delete and insert statements, so we can return the inserted and deleted values through the OUTPUT clause. Let’s make an example of it.

As you can see, we modified the CountryPopulation value from 1 to 22 and then we can find out the inserted and deleted values. Additionally, we can insert these values to a table variable. The table variable is a special variable type that can be used as a table. In the following example, we will insert the inserted and deleted values to the table variable.

@@ROWCOUNT is a system variable which returns the number of the row affected in the last statement. So we can use this variable to expose a number of the modified rows in the update statement. In the following example, the update query will change 3 rows and the @@ROWCOUNT system variable will return 3.

D- Delete

The Delete statement is used to remove existing row/rows in the table. At first, we will mention how to use the WHERE clause in the DELETE statements, just because most of the time we want to filter the deleted rows. In the following example, we will remove a particular row.

Though with the DELETE statement, we can remove all recordі in the table but the DELETE statement is very basic and we don’t use WHERE condition.

However, in some circumstances, according to database designs, the DELETE statement does not delete the row/rows if the DELETE statement violates the foreign key or other constraints. For example, in the AdventureWorks database, we cannot delete the ProductCategory table rows because ProductCategoryID is specified as a foreign key in the ProductSubcategory table.

Now, we will try to delete a row in the ProductCategory table and no doubt we will face following error.

Conclusion

In this article series, we explored the CRUD operations in SQL. CRUD operation includes INSERT, SELECT, UPDATE and DELETE statements and these are the basic functions of the SQL database. If anyone wants to learn SQL database programming, CRUD theory could be a good starting point.

References

Esat Erkeç

Esat Erkec is an SQL Server professional that began his career as a Software Developer over 8 years ago. He is an SQL Server Microsoft Certified Solutions Expert. Most of his career has focused on SQL Server Database Administration and Development. His current interests are in database administration and Business Intelligence.
Esat Erkeç

Latest posts by Esat Erkeç (see all)