The series so far:
- SQL Server Fundamentals - Part 1: Life of Databases
- SQL Server Fundamentals - Part 2: How Introduction to Tables in Databases
can keep you out of trouble- SQL Server Fundamentals - Part 3: Working with data in the Table taboos you should Break
- SQL Server Fundamentals - Part 4: How Indexes can help you live a better life
- SQL Server Fundamentals - Part 5: Non Relational Data Types in 2 Easy Steps
CRUD - On high level, there are four operations on table in SQL Server
Operation | SQL Command |
---|---|
Create | Insert |
Read | Select |
Update | Update |
Delete | Delete |
Wrapping CRUD
Generally, we wrap CRUD operations into more user-friendly operations.
Wrapping Read
We don't expect our users to write T SQL like SELECT Information from Table whenever they wanted some report.
We create a view, which stores select statement by giving it a name and we pass Views around.
CREATE VIEW vw_ProductPrice
AS
SELECT Name, Price
FROM Product
WHERE Supplier = 2;
Convenient and share columns that we wanted to share.
Wrapping Update
Let's take an example of our bank account transactions where we wanted to deposit amount from checking account to savings account.
We would like both updates to happen in a single transaction, i.e. if any one of them fails no changes should occur.
In this case, we will use Stored Procedures.
CREATE PROCEDURE transferFunds
AS
BEGIN TRANSACTION
-- Transaction stating all succeeds or all fails
UPDATE SAVINGS
SET Balance += 500
WHERE AccountID = 3;
UPDATE CHECKING
SET Balance -= 500
WHERE AccountID = 3;
COMMIT TRANSACTION
-- or if some error occurs
ROLLBACK TRANSACTION
Stored Procedure is executed using EXEC
Statement.
EXEC transferFunds;
Summary
- Data in tables is managed through CRUD operations (Create, Retrieve, Update, Delete)
- To create data, use INSERT
- To retrieve data, use SELECT
- To update data, use UPDATE
- To delete data, use DELETE
- Wrap your SELECT statements in views or procedures
- Wrap your INSERTs, UPDATEs, DELETEs and even SELECTs in procedures