/ SQL Server

3. Working with data in the Table taboos you should Break (SQL Fundamentals)

The series so far:

  1. SQL Server Fundamentals - Part 1: Life of Databases
  2. SQL Server Fundamentals - Part 2: How Introduction to Tables in Databases
    can keep you out of trouble
  3. SQL Server Fundamentals - Part 3: Working with data in the Table taboos you should Break
  4. SQL Server Fundamentals - Part 4: How Indexes can help you live a better life
  5. 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

3. Working with data in the Table taboos you should Break (SQL Fundamentals)
Share this

Subscribe to Coding Today

Popular queries