CRUD operations are fundamental operations that every database developer and administrator needs to understand. In this article, we will explore the concepts of CRUD operations and how they work in SQL Server.

Create

The first letter of CRUD, ‘C’, refers to the CREATE operation. This operation is used to add new records to a table. In SQL Server, we use the INSERT INTO statement to create new records. Here is an example:

USE AdventureWorks2016;

DROP TABLE IF EXISTS Demo;

CREATE TABLE dbo.Demo (
  id INT,
  name VARCHAR(100)
);

INSERT INTO dbo.Demo VALUES (1, 'John');

You can also insert multiple rows at once using the following syntax:

INSERT INTO dbo.Demo (id, name)
VALUES (2, 'Jane'),
       (3, 'Mike');

Additionally, you can insert rows from a SQL UNION clause using the following syntax:

INSERT INTO dbo.Demo (id, name)
SELECT 4, 'Sarah'
UNION ALL
SELECT 5, 'Emily';

Read

The second letter of CRUD, ‘R’, refers to the READ operation. This operation is used to retrieve data from a table. In SQL Server, we use the SELECT statement to read data. Here is an example:

SELECT * FROM dbo.Demo;

You can specify specific columns to retrieve by replacing the asterisk (*) with the column names. For example:

SELECT id, name FROM dbo.Demo;

You can also join multiple tables using the SQL JOIN clause:

SELECT p.*, s.*
FROM Production.Product AS p
JOIN Sales.SalesOrderDetail AS s
ON s.ProductID = p.ProductID;

Update

The third letter of CRUD, ‘U’, refers to the UPDATE operation. This operation is used to modify existing records in a table. In SQL Server, we use the UPDATE statement to update records. Here is an example:

UPDATE dbo.Demo
SET name = 'Robert'
WHERE id = 1;

You can update multiple columns at once by separating them with commas. Additionally, you can specify a condition to update only specific rows.

Delete

The last letter of CRUD, ‘D’, refers to the DELETE operation. This operation is used to remove records from a table. In SQL Server, we use the DELETE statement to delete records. Here is an example:

DELETE FROM dbo.Demo
WHERE id = 1;

Be cautious when using the DELETE statement without a WHERE clause, as it will delete all rows in the table.

Summary

CRUD operations are the foundation of SQL operations in any database system. Understanding how to create, read, update, and delete records is essential for working with databases. In this article, we explored the concepts of CRUD operations in SQL Server and provided examples of how to perform these operations. If you want to dive deeper into each operation, I recommend reading the SQL Insert, SQL Delete, and SQL Update articles.

Thank you for reading this article. I hope you found it informative and valuable. If you have any questions or comments, please feel free to leave them below.