CRUD Operations in SQL Server

By: Jeffrey Yao | Comments (3) | Related: > Database Design

Problem

I am a new SQL Server DBA, I heard of the CRUD acronym, but I do not quite understand the meaning and importance of these CRUD operations, can you please give a detailed explanation?

Solution

CRUD is the basic operations in any RDBMS, and this tip will take a detailed look at the CRUD operations in SQL Server.

What is CRUD?

CRUD means Create, Read, Update, Delete, and it can mean different things in different systems, but for SQL Server, it is commonly considered to map to the following SQL operations on table records.

CRUD	SQL
C - Create	Insert
R - Read	Select
U - Update	Update
D - Delete	Delete

According to Wikipedia, this acronym may be first coined by James Martin in his 1983 book Managing the Data-base Environment.

Importance of CRUD

CRUD operations in RDBMS are the basic operation units, built upon which is a very rich ecosystem, such as security control, object permission/access, locking mechanism, performance optimization, transaction control, resource control, etc.

There are a few obvious benefits here:

Facilitate operation security control: in the real world, for the same data, some people can read it but not change it, like your salary, which you can read it but you cannot update/delete it (at least without authorization). Some can only read/write their own data, but no access to other’s data. With CRUD operation concepts, we can satisfy all these various granular access requirements by imposing the right mix of permissions on CRUD operations.
Facilitate granularity of business design: when we design a business application, we often consider business objects as the building blocks (like in OOP design), such as in a banking application, we have CUSTOMER, ACCOUNT, and other objects like RATE, PRODUCT, etc. For each of these objects, we can apply CRUD operations on them (in OOP, these objects will have CRUD methods), and these make the application design simpler and more scalable.
Facilitate the trouble-shooting process: to better understand this benefit, we can use an opposite example. SQL Server has introduced the MERGE statement with SQL Server 2008, this Merge statement is a powerful function, it kind of blends insert, update, delete together in one unit. However, whenever I debug a script with such MERGE statement, I have to open another SSMS window to check the target and source tables based on the merge search condition and calculate the expected result and then compare with the MERGE execution result. Yes, the MERGE statement is concise but in a troubleshooting process, the debug time with a MERGE statement is longer as well.

Examples of CRUD

We will list a few simple CRUD examples here. First, we will create a simple table as follows:

use MSSQLTips				
if object_id('dbo.Product', 'U') is not null
   drop table dbo.Product;
create table dbo.Product (name varchar(128), price decimal(10,2));

C for Create (Insert)

There can be many different syntax forms for an insert, but most common ones are the following four forms:

-- method 1, with VALUES
Insert into dbo.Product (name, price)
values ('A', 12.34), ('B', 23.45);

-- method 2, with a SELECT
Insert into dbo.Product (name, price)
select 'C', 123.45
union all
select 'D', 234.56;
go

-- method 3, with EXEC a stored proc
-- so we first create a SP dbo.p
create proc dbo.p as
begin
   select [name]='H', [Price]='57.89'
end
go

insert into dbo.Product (name, price)
exec dbo.p;
go

drop proc dbo.p;
go				 				

-- method 4, bulk insert
bulk insert dbo.product
from 'c:\temp\a.csv'
with (firstrow=2, rowterminator='\n', fieldterminator=',')
go

In method 4, we assume we have an existing file c:\tem\a.csv and the file is as follows:

When we do a select on table dbo.Product, we will see the table are populated correctly as shown below:

R for Read (SELECT)

In SQL Server, to read data, there are literally two forms, one is data set read and another is data row read. The data set read is via SELECT and data set read can read multiple rows at one time like the following.

-- method 1, data set read
select * from dbo.Product where name in ('A', 'B')

The data row read exists in cursor read via FETCH, and the following is an example.

-- method 2, data row read via FETCH inside a cursor
declare @name varchar(100), @price decimal(10,2);				

declare curS cursor for 				
select name, price from dbo.Product				
where name in ('A', 'B') 
for update of Price				 				

open curS;

fetch next from curS into @name, @price; -- data row read
while @@fetch_status = 0
begin				
   print 'Product ' + quotename(@name, '[]') + ' is priced at ' + cast(@price as varchar(12));			
   fetch next from curS into @name, @price; -- data row read				
end				
close curS;
deallocate curS;

The result is

U for Update

Update is very straight-forward in SQL Server, and its most common form is as follows.

-- update Product A and B to increase Price by 5%
update p
set p.Price = p.Price * 1.05
from dbo.Product p
where name in ('A', 'B');

D for Delete

Delete data from a table in SQL Server can be literally two forms, one is the regular DELETE statement and another is TRUNCATE TABLE. There are lots of articles discussing about the difference between DELETE and TRUNCATE.

Here are two examples

-- method 1, delete 

delete from dbo.Product
where name = 'B'

-- method 2, truncate table
-- its final effect is similar to DELETE table without where clause
truncate table dbo.Product -- similar to DELETE FROM dbo.Product

In SQL Server 2008, there is a MERGE statement which can achieve the functions of CUD (no R). I purposely omit it here as it is not available across all SQL Server versions and also it is impossible to classify it to C or U or D.

Extending CRUD Concept

CRUD in essence seems to be DML (data manipulation language), but this concept can be extended to DDL (Data Definition Language). For example, if we consider a database as a container, we can CRUD lots of database objects, such as table, view, stored procedure and user, etc. Let’s use a table as an object, we can see the following CRUD actions:

C: Create table
R: sp_help <table> to check the table structure
U: Alter table
D: Drop table

Summary

In this tip, we have discussed what CRUD is in SQL Server, and why CRUD is important and how we can extend this CRUD concept from DML to DDL.

For a new DBA to grasp the knowledge of SQL Server, it may be good to look at SQL Server operations from a CRUD perspective, and this may make learning much easier as we can categorize the learning under each C/R/U/D.

Next Steps

The CRUD concept is the foundation of operations in any RDBMS system, and you can read more CRUD-related topics on MSSQLTips.com as listed below:

About the author

Jeffrey Yao is a senior SQL Server consultant, striving to automate DBA work as much as possible to have more time for family, life and more automation.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips