CRUD Operations in SQL Server
By: Jeffrey Yao | Comments (3) | Related: More > Database Design
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?
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.
|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
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.
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:
- Standardizing SQL Server CRUD Templates with Rollback Plans
- Comparing performance for the MERGE statement to SELECT, INSERT, UPDATE or DELETE
- Using MERGE in SQL Server to insert, update and delete at the same time
About the author
View all my tips