Unit 5: SQL Server For Backend Development

INT402 — Modern Web Programming Tools And Techniques 10 min read

I. Orientation

A database is an organized collection of related data managed through a database management system (DBMS). SQL Server is Microsoft’s relational DBMS, designed to store, query, secure, and process structured data for desktop, web, and enterprise applications. Backend development commonly uses SQL Server between the user interface and application logic: the application sends SQL commands, SQL Server processes them, and the database returns results.

Relational principle: Data is stored in tables made of rows and columns, and relationships are represented through keys.
Data integrity: Rules such as primary keys, foreign keys, NOT NULL, and CHECK constraints control valid data.
SQL communication: Structured Query Language (SQL) is used to define, retrieve, modify, and control database data.
Transaction reliability: Transactions follow atomicity, consistency, isolation, and durability (ACID).
Backend role: Server-side code validates requests, executes parameterized queries or procedures, and returns results to clients.
Security convention: Users should receive only the permissions required for their work; application input must not be concatenated directly into SQL strings.

II. Introduction to Databases

A. Introduction to Databases

A database provides persistent, structured storage so applications can retrieve and update information efficiently.

Database components: A database contains objects such as tables, views, indexes, stored procedures, functions, and constraints.
Table structure: A Students table might contain StudentID, Name, Email, and Course; each row represents one student.
DBMS function: A DBMS manages storage, concurrent access, transactions, authorization, backup, and recovery.
Schema meaning: A schema defines object names, column data types, relationships, and access rules.
Application connection: A backend may connect using a SQL Server driver and a connection string containing the server, database, authentication method, and encryption settings.

B. Types of Databases

Database types differ according to their data model, relationships, and typical workload.

Relational databases: SQL Server, MySQL, and PostgreSQL store structured data in related tables using SQL.
Document databases: MongoDB stores records as JSON-like documents, allowing flexible fields.
Key-value databases: Redis associates a key such as session:1042 with a value and is useful for caching.
Graph databases: Neo4j represents entities as nodes and relationships as edges, useful for network analysis.
Centralized and distributed systems: A centralized database runs mainly in one location, while a distributed database stores or replicates data across multiple servers.
Selection criterion: A relational database is appropriate when transactions, relationships, and consistent schemas are central requirements.

III. Introduction to SQL Server

A. Introduction to SQL Server

SQL Server is a relational database management system that provides database storage, SQL processing, security, and administration tools.

Core engine: The Database Engine stores data, optimizes queries, manages transactions, and controls concurrent operations.
Database organization: A SQL Server instance can contain multiple databases; each database contains schemas and database objects.
Tools: SQL Server Management Studio (SSMS) provides a graphical interface for writing queries and administering servers.
Authentication: Windows Authentication uses operating-system identities, while SQL Server Authentication uses SQL logins.
Data types: Common types include int, decimal(10,2), date, datetime2, bit, varchar(100), and nvarchar(100).
Backend relevance: An API can execute SELECT, INSERT, or stored procedures through a connection pool.

B. SQL Server Tables

SQL Server tables represent entities and their attributes in a relational design.

Rows and columns: A row is one record, such as one employee; a column defines an attribute such as EmployeeName.
Data definition: Tables are created with CREATE TABLE, which specifies names, types, and constraints.

SQL

CREATE TABLE Employees (
    EmployeeID int PRIMARY KEY,
    EmployeeName nvarchar(80) NOT NULL,
    HireDate date,
    Salary decimal(10,2)
);

Normalization: Splitting repeated data into related tables reduces duplication; departments should usually be stored separately from employees.
Indexes: An index on Email can accelerate searches, although indexes require additional storage and can slow modifications.
Identity values: IDENTITY(1,1) generates values beginning at 1 and increasing by 1, commonly for surrogate keys.

IV. SQL Language and Commands

A. SQL Commands

SQL commands are commonly classified according to whether they define structures, manipulate data, control access, or manage transactions.

DDL: Data Definition Language includes CREATE, ALTER, and DROP; CREATE TABLE creates a database object.
DML: Data Manipulation Language includes INSERT, UPDATE, and DELETE; these change table rows.
DQL: SELECT retrieves data, for example SELECT Name FROM Students.
DCL: Data Control Language includes GRANT, DENY, and REVOKE for permissions.
TCL: Transaction Control Language includes BEGIN TRANSACTION, COMMIT, and ROLLBACK.
Execution rule: A statement is generally terminated with a semicolon, although SQL Server often permits omission.

B. Data Manipulation Commands

Data Manipulation Commands modify or retrieve records and should be used with carefully scoped conditions.

Insert: INSERT INTO Employees (EmployeeID, EmployeeName) VALUES (1, 'Asha'); adds one row.
Update: UPDATE Employees SET Salary = 50000 WHERE EmployeeID = 1; changes only the selected employee.
Delete: DELETE FROM Employees WHERE EmployeeID = 1; removes matching rows; omitting WHERE may remove every row.
Select: SELECT EmployeeName, Salary FROM Employees; returns selected columns.
Merge or upsert: SQL Server’s MERGE can synchronize source and target rows, but explicit INSERT and UPDATE logic is often easier to audit.
Transaction protection: Related changes can be grouped so failure triggers ROLLBACK rather than leaving partial updates.

V. Integrity and Query Logic

A. Constraints

Constraints are database-enforced rules that prevent invalid or inconsistent data.

Primary key: Uniquely identifies each row and disallows NULL, as in StudentID int PRIMARY KEY.
Foreign key: Requires a value to match a key in another table, such as Orders.CustomerID referencing Customers.CustomerID.
Unique: Prevents duplicate values, for example one account per email address.
Not null: Requires a value; Name nvarchar(60) NOT NULL prevents missing names.
Check: Enforces a condition such as CHECK (Salary >= 0).
Default: Supplies a value when none is provided, such as Status varchar(20) DEFAULT 'Pending'.
Integrity benefit: Constraints protect data even when multiple applications write to the same database.

B. SQL Clauses

SQL clauses specify which columns to return, which rows to select, and how results should be grouped or ordered.

FROM: Identifies the source table, as in FROM Employees.
WHERE: Filters individual rows before grouping; WHERE Salary > 40000 excludes lower salaries.
GROUP BY: Forms groups, such as employees grouped by DepartmentID.
HAVING: Filters groups after aggregation, for example HAVING COUNT(*) > 5.
ORDER BY: Sorts results using ASC or DESC; ORDER BY Salary DESC shows highest salaries first.
TOP: Limits rows, as in SELECT TOP 10 * FROM Products ORDER BY Price DESC.
Evaluation idea: Conceptually, SQL processes FROM, WHERE, grouping, HAVING, SELECT, and ORDER BY, although the optimizer may execute operations differently.

C. SQL Operators

Operators compare values, combine conditions, calculate expressions, and test membership.

Comparison: =, <>, >, <, >=, and <= compare values such as Price >= 100.
Logical: AND, OR, and NOT combine predicates; parentheses clarify expressions like Status = 'Open' AND (Priority = 1 OR Priority = 2).
Range and pattern: BETWEEN 10 AND 20 includes a range, while LIKE 'A%' matches text beginning with A.
Set membership: IN ('Active', 'Pending') replaces repeated OR comparisons.
Null testing: IS NULL and IS NOT NULL must be used because NULL = NULL is not true.
Arithmetic: +, -, *, /, and % calculate values; Salary * 1.10 represents a 10% increase.
Precedence: Parentheses are evaluated first, followed by arithmetic, comparison, and logical operators.

D. SQL Joins

SQL joins combine rows from related tables using a matching condition.

Inner join: Returns only matching records.

SQL

SELECT o.OrderID, c.CustomerName
FROM Orders AS o
INNER JOIN Customers AS c
    ON o.CustomerID = c.CustomerID;

Left join: Returns every row from the left table and matching rows from the right; unmatched right-side columns become NULL.
Right and full joins: A right join preserves the right table; a full join preserves unmatched rows from both tables.
Cross join: Produces every combination, so 3 products and 4 regions produce 12 rows.
Self join: Joins a table to itself, such as employees linked to their managers.
Join risk: Missing or incomplete conditions can create duplicate or Cartesian results; join columns should normally use related keys.

VI. SQL Server Views

A. SQL Server Views

A view is a named, stored SELECT statement that presents data as a virtual table.

Abstraction: A view can hide complex joins, exposing CustomerName and OrderTotal without exposing implementation details.
Security: Permissions can be granted on a view while restricting direct access to sensitive base columns.
Reuse: A reporting query stored as SalesByMonth can be queried with SELECT * FROM SalesByMonth.
Limitations: Ordinary views do not normally store independent data, and complex views may be difficult to update or optimize.
Dependency: Changes to underlying columns can break a view, so schema changes require dependency management.

B. Types of Views

SQL Server views can be categorized by purpose and implementation.

Simple view: Uses one table and limited expressions, such as a view of active users.
Complex view: Uses joins, aggregates, or multiple tables, such as a monthly sales report.
Indexed view: Stores an indexed result under strict SQL Server requirements and can improve repeated aggregate queries.
System view: Catalog views such as sys.tables expose metadata about databases and objects.
Partitioned view: Combines similarly structured tables, often across databases or servers, using UNION ALL.

C. User Defined Views

A user-defined view is created by a developer to provide a controlled, reusable representation of application data.

Creation: CREATE VIEW defines the view.

SQL

CREATE VIEW dbo.ActiveEmployees
AS
SELECT EmployeeID, EmployeeName, HireDate
FROM dbo.Employees
WHERE IsActive = 1;

Use: SELECT EmployeeName FROM dbo.ActiveEmployees; queries the view like a table.
Modification: ALTER VIEW changes its definition; DROP VIEW removes it.
Security boundary: Granting SELECT on the view can expose only the listed columns.
Design limitation: Avoid relying on SELECT *; explicitly named columns make changes predictable.

VII. Stored Procedures

A. Introduction to Stored Procedure

A stored procedure is a named group of SQL statements stored and executed on the SQL Server.

Purpose: Procedures centralize reusable operations such as creating an order or retrieving a customer’s history.
Parameters: Input parameters accept values, while output parameters return values; parameters also reduce injection risk.
Performance: SQL Server can reuse execution plans, although performance depends on query design and parameter behavior.
Transactions and errors: TRY...CATCH, transactions, and THROW allow procedures to handle failures consistently.
Separation of concerns: The backend calls a procedure, while table structure and validation logic remain on the server.

B. User Defined Stored Procedure

A user-defined stored procedure is an application-specific procedure created with CREATE PROCEDURE.

Definition and execution:

SQL

CREATE PROCEDURE dbo.GetEmployeesByDepartment
    @DepartmentID int
AS
BEGIN
    SET NOCOUNT ON;

    SELECT EmployeeID, EmployeeName
    FROM dbo.Employees
    WHERE DepartmentID = @DepartmentID;
END;
GO

EXEC dbo.GetEmployeesByDepartment @DepartmentID = 3;

Parameter safety: @DepartmentID is treated as a value, unlike unsafe string-built SQL such as '... WHERE DepartmentID = ' + input.
Output behavior: A procedure may return result sets, status codes, or declared output parameters.
Permissions: GRANT EXECUTE ON dbo.GetEmployeesByDepartment permits execution without granting direct table access.
Maintenance: Procedures should have clear names, explicit columns, documented parameters, and limited responsibilities.
Transactional example: A procedure inserting an order and its items can use BEGIN TRANSACTION, COMMIT, and ROLLBACK to preserve all-or-nothing behavior.