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
Generally, we wrap CRUD operations into more user-friendly operations.
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.
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
- 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