CRUD operations are foundation operations every database developer and administrator needs to understand. Let’s take a look at how they work with this guide.
Introduction
According to Wikipedia…
- “In computer programming, create, read, update, and delete (CRUD) are the four basic functions of persistent storage. Alternate words are sometimes used when defining the four basic functions of CRUD, such as retrieve instead of read, modify instead of update, or destroy instead of delete. CRUD is also sometimes used to describe user interface conventions that facilitate viewing, searching, and changing information; often using computer-based forms and reports. The term was likely first popularized by James Martin in his 1983 book managing the Data-base Environment. The acronym may be extended to CRUDL to cover listing of large data sets which bring additional complexity such as pagination when the data sets are too large to hold easily in memory.”
CRUD is an acronym that stands for Create, Read, Update, and Delete.
These are the four most basic operations that can be performed with most traditional database systems and they are the backbone for interacting with any database.
Getting started
Let’s get started to understand the concepts of CRUD operations in SQL Server
Create
The first letter of CRUD, ‘C’, refers to CREATE aka add, insert. In this operation, it is expected to insert a new record using the SQL insert statement. SQL uses INSERT INTO statement to create new records within the table.
Let us create a simple table named Demo for this example.
|
1 2 3 4 5 6 7 |
USE AdventureWorks2016; GO DROP TABLE IF EXISTS Demo; CREATE TABLE dbo.Demo (id INT, name VARCHAR(100) ); |
SQL Insert starts with the keyword INSERT INTO then specify the table name and the columns that we want to insert. The columns go inside of the parentheses and then we specify a VALUES clause.
|
1 2 3 |
INSERT INTO <tablename> (column1,column2,….) VALUES (value1,value2,….) |
We put in the table name demo after the insert into command. Now, supply the values to the listed columns id and name in the VALUES clause.
|
1 2 3 4 5 |
INSERT INTO dbo.Demo VALUES (1, 'Prashanth' ); |
To insert multiple rows, follow the below syntax
|
1 2 3 |
INSERT INTO <tablename> (column1,column2,….) VALUES(value1,value2,…. ),( value1,value2,…. ), (value1,value2,…. )… |
In the following example, the multiple values are listed within in the parenthesis and each list is separated by a comma delimiter
|
1 2 3 4 5 6 7 8 9 10 11 |
INSERT INTO dbo.Demo (id, name ) VALUES (2, 'Jayaram' ), (3, 'Pravitha' ); |
To insert rows from SQL Union clause, follow the below syntax
|
1 2 3 4 |
INSERT INTO <tablename> (column1,column2,….) SELECT value1,value2,… UNION ALL SELECT value1, value2,… |
In the following example, the multiple values are listed using SELECT statement and then these values combined and fed to the table using SQL UNION ALL set operator.
|
1 2 3 4 5 6 |
INSERT INTO dbo.demo SELECT 4, 'Prarthana' UNION ALL SELECT 5, 'Ambika'; |
The output lists all the inserted rows from the above samples.
- Notes:
- It is mandatory to insert at least all of the required columns, but you don’t have to update a column if those values are not required, or if there is a default value for that column
- A detailed explanation of SQL insert can be found in the following article: Overview of the SQL Insert statement
- SQL Insert statement only works against a single table unlike select which can work against multiple tables
- A detailed explanation of SQL Union clause can be found in the following article: SQL Union overview, usage and examples
Read
The second letter of CRUD , ‘R’, refers to SELECT (data retrieval) operation. The word ‘read’ retrieves data or record-set from a listed table(s). SQL uses the SELECT command to retrieve the data. When it comes to executing queries, you can use SQL Server Management Studio or SQL Server Data Tools or sqlcmd, based on your preference.
For example, to read related data from the specified table, refer to the below syntax.
|
1 |
SELECT * FROM <TableName> |
The SQL select statement allows you to query the tables. It allows you to retrieve specific data, one or more rows from one or more tables.
The SQL SELECT statement in a vast majority of the time going to contain names of columns from the table(s) that you would like to get data from. Once you have column names, the table name is required in the FROM clause. Now, in a SELECT list, after every column of data, you’re going to need a comma. So you separate each column with a comma, except, no comma after the last column. We’re going to have the SELECT keyword, column name followed by a comma, column name, and the last column name, no comma, FROM clause followed by table name.
In this case, one that will return every row in the Address table. And it will return just the AddressID, AddressLine1,AddressLine2,City, StateProvinceID and PostalCode columns.
The SQL SELECT statement uses a wildcard character (*) or asterisk to populate all the columns of the table(s). It provides a way to not have to list every column table(s). That’s by using the asterisk or ‘*’.
The output lists all the columns of the Address table. The following SQL going to give me all of the columns
|
1 2 3 |
USE [AdventureWorks2016]; GO SELECT * FROM [Person].[Address]; |
Next, the FROM Clause is going to have at least a table or it is possible to have multiple tables using SQL Join.
In the following example, the Product and SalesOrderDetail tables are listed in the FROM Clause of the Select statement.
|
1 2 3 |
SELECT * FROM Production.Product AS p JOIN Sales.SalesOrderDetail AS s ON s.ProductID = p.ProductID; |
- Notes: A detailed explanation of a few more SQL SELECT statement scenarios is discussed in the article: Overview of the SQL Order by clause
Update
The third letter of CRUD, ‘U’, refers to Update operation. Using the Update keyword, SQL brings a change to an existing record(s) of the table.
- You can refer to the article Overview of SQL Update to learn more about SQL update.
When performing an update, you’ll need to define the target table and the columns that need to update along with the associated values, and you may also need to know which rows need to be updated. In general, you want to limit the number of rows in order to avoid lock escalation and concurrency issues.
The basic syntax for an update:
|
1 2 3 |
UPDATE <TableName> SET Column1=Value1, Column2=Value2,… WHERE <Expression> |
The UPDATE keyword is followed by the name of the table or view to be updated, and then the set keyword followed by the column name and the value to be set, be it an expression, default, keyword, or null value. If you’re looking to specify which rows are modified using a search condition, the syntax is as follows: Everything is the same as the previous example, only this time you’ll see the “where” clause followed by an expression.
- A detailed explanation of SQL Update can be found in the following article: Overview of the SQL update statement
Delete
The last letter of the CRUD operation is ‘D’ and it refers to removing a record from a table. SQL uses the SQL DELETE command to delete the record(s) from the table.
- You can refer to the article Overview of SQL Delete to learn more about SQL delete operation.
For example, to delete related data from the specified table, refer to the below syntax
|
1 2 |
DELETE FROM <TableName> WHERE <Expression> |
When writing a DELETE statement, you’ll define the target table and also which rows you need to delete from the table. The syntax in its simplest form is the DELETE keyword followed by the table name. In some case without a WHERE clause in the query deletes ALL existing rows from the table. To apply a condition clause to the SQL DELETE statement, use the WHERE clause followed by the expression(s).
- Note: The detailed explanation of SQL delete can be found in the following article: Overview of the SQL delete statement
Summary
So, thus far, we’ve discussed a lot about CRUD operations. It is a termed as the foundation of SQL operations in any database products. We also discussed how to implement CRUD Operations in SQL Server.
I would recommend reading SQL Insert, SQL Delete, and SQL Update articles which part of CRUD operations. Implementing the Create, Update, Delete, and insert operations are reasonably simple because they are very similar operations.
The most efficient way to implement CRUD operations in SQL is through stored procedures. You can refer to the article Creating and using CRUD stored procedures for further reading.
Thanks so much for taking the time to read this article. I hope you found it simple and valuable. Feel free leave the comment below.
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021