SQL

🧠 Day 10: Stored Procedures, Triggers, Normalization & More in SQL

Photo of Mathimaa Mathimaa3 weeks ago
0 5 2 minutes read

🧠 Day 10: Stored Procedures, Triggers, Normalization & More in SQL

⚙️ What is a Stored Procedure?

A Stored Procedure is a set of SQL statements that are stored in the database and can be reused whenever needed.

It helps improve performance, reusability, and security.

📘 Definition:
A stored procedure is a precompiled collection of one or more SQL statements that can accept input parameters, return output parameters, or both.

🧩 Key Features

  • Can accept input and return output parameters

  • Allows code reuse

  • Reduces network traffic

  • Improves security and performance

🧱 Syntax

CREATE PROCEDURE procedure_name
@parameter datatype
AS
BEGIN
SQL_Query
END;

EXEC procedure_name;

📘 Example 1: Simple Procedure

CREATE PROCEDURE SelectAllCustomers
AS
SELECT * FROM Customers;
GO;

EXEC SelectAllCustomers;

💼 Example 2: EmployeeDB – Stored Procedure Demo

CREATE DATABASE EmployeeDB;
GO
USE EmployeeDB;
GO

CREATE TABLE employees (
id INT PRIMARY KEY IDENTITY(1,1),
name VARCHAR(100),
department VARCHAR(50),
salary DECIMAL(10,2)
);

INSERT INTO employees (name, department, salary)
VALUES
('John Doe', 'HR', 17000),
('Jane Smith', 'IT', 22000),
('James Brown', 'Finance', 18000),
('Emily White', 'IT', 25000),
('Michael Green', 'HR', 16000),
('Laura Black', 'Finance', 20000),
('David Blue', 'Marketing', 21000);

✅ Stored Procedure 1 – Get All Employees

CREATE PROCEDURE GetAllEmployees
AS
BEGIN
SELECT * FROM employees;
END;

EXEC GetAllEmployees;

✅ Stored Procedure 2 – Get Employees by Department

CREATE PROCEDURE GetEmployeesByDept
@DeptName VARCHAR(50)
AS
BEGIN
SELECT * FROM employees WHERE department = @DeptName;
END;

EXEC GetEmployeesByDept @DeptName = 'IT';

🧠 Normalization in SQL

Normalization is the process of organizing data to reduce redundancy and improve data integrity.

🔹 Goals of Normalization

  • Remove duplicate data

  • Ensure data consistency

  • Improve database efficiency

🧾 Types of Normal Forms

  1. 1NF (First Normal Form):

    • Eliminate repeating groups.

    • Ensure each cell holds a single value.

  2. 2NF (Second Normal Form):

    • Meet 1NF.

    • Remove partial dependencies.

  3. 3NF (Third Normal Form):

    • Meet 2NF.

    • Remove transitive dependencies.

  4. BCNF (Boyce–Codd Normal Form)

    • Stronger version of 3NF.

  5. 4NF, 5NF, 6NF:

    • Handle more complex dependencies and relationships.

⚡ Triggers in SQL

A Trigger is a special kind of stored procedure that executes automatically when a specific event occurs in the database.

🔹 Trigger Types

Type Description
DML Triggers Fired automatically on INSERT, UPDATE, or DELETE.
DDL Triggers Fired automatically on CREATE, ALTER, or DROP.
Logon Triggers Fired automatically when a user logs into SQL Server.

🧩 Example:
Automatically log updates made to a table.

🧱 Temporary Tables

Temporary tables are stored in TempDB and are deleted automatically when the session ends.

🔹 Uses

  • Store intermediate data.

  • Simplify complex queries.

  • Improve performance during data processing.

🧩 1. Local Temporary Table

  • Prefixed with #

  • Visible only to the current session

  • Dropped automatically when session ends

Syntax:

CREATE TABLE #TempTable (id INT, name VARCHAR(50));

🧩 2. Global Temporary Table

  • Prefixed with ##

  • Accessible to all sessions

  • Dropped when all sessions close

Syntax:

CREATE TABLE ##GlobalTemp (id INT, name VARCHAR(50));

⚙️ Indexes in SQL

Indexes are used to speed up data retrieval from a database table.

🔹 Types of Indexes

  1. Clustered Index – Sorts and stores data rows in the table based on key values.

  2. Non-Clustered Index – Stores index separately from actual data.

🧩 Example:

CREATE CLUSTERED INDEX idx_emp_id ON employees(id);
CREATE NONCLUSTERED INDEX idx_emp_dept ON employees(department);

🔁 Cursor in SQL

A cursor is a database object that allows you to process each row individually.

🔹 Types of Cursors

  1. Implicit Cursor – Managed automatically by SQL (e.g., in simple SELECT queries).

  2. Explicit Cursor – Declared and controlled manually by the user.

⚠️ Use cursors only when set-based operations are not possible — they are slower than standard SQL.

Photo of Mathimaa Mathimaa3 weeks ago
0 5 2 minutes read

Leave a Reply

Your email address will not be published. Required fields are marked *

Back to top button