1: Introduction to SQL Server

1: Introduction

Microsoft SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft. It is used to store, manage, retrieve, and manipulate large amounts of structured data using SQL (Structured Query Language).

In modern applications, the data size is very large, and manual handling is not possible. SQL Server helps organizations manage data efficiently by storing it in tables and maintaining relationships between them.

SQL Server is commonly used in:

• Banking systems

• Enterprise Resource Planning (ERP)

• Hospital management systems

• College and school ERP

• E-commerce applications

2. Basic Database Concepts

2.1 Data

Data refers to raw facts and figures that do not carry any meaning by themselves. Example: 101, Amit, 50000

2.2 Information

When data is processed and becomes meaningful, it is called information. Example: Employee Amit has a salary of ₹50,000.

2.3 Database

A database is a collection of related data stored in an organized manner so that it can be easily accessed, updated, and managed.

2.4 Table

A table stores data in rows and columns.

• Row → Record

• Column → Field

Each table represents a real-world entity such as Student, Employee, or Order.

3. SQL Server Architecture

SQL Server follows a client–server architecture.

1. Client sends SQL query

2. Query Processor validates syntax and creates an execution plan

3. Storage Engine retrieves or modifies data

4. The result is returned to the client

Database files:

• MDF (Primary Data File)

• LDF (Log File) – used for recovery and rollback

4. SQL Command Types

4.1 DDL – Data Definition Language

Used to define and modify database objects.

CREATE

Creates database objects.

CREATE TABLE Student (
RollNo INT,
Name VARCHAR(50),
Marks INT
);

ALTER

Modifies table structure.

ALTER TABLE Student ADD Age INT;

DROP

Deletes the table permanently.

DROP TABLE Student;

TRUNCATE

Deletes all records but keeps the structure.

TRUNCATE TABLE Student;

4.2 DML – Data Manipulation Language

Used to insert, update, and delete data.

INSERT

INSERT INTO Student VALUES (1, 'Amit', 85);

UPDATE

UPDATE Student SET Marks = 90 WHERE RollNo = 1;

DELETE

DELETE FROM Student WHERE RollNo = 1;

4.3 DQL – Data Query Language

Used to retrieve data.

SELECT * FROM Student;

With condition:

SELECT Name, Marks FROM Student WHERE Marks > 80;

4.4 TCL – Transaction Control Language

Manages transactions.

BEGIN TRANSACTION
UPDATE Account SET Balance = Balance - 500 WHERE AccNo = 1;
COMMIT;

Commands:

• COMMIT

• ROLLBACK

• SAVEPOINT

5. SQL Server Data Types (Detailed)

5.1 Numeric Data Types

Type	Size	Range	Description
INT	4 bytes	-2,147,483,648 to 2,147,483,647	Stores integer values
BIGINT	8 bytes	-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807	Stores very large integers
SMALLINT	2 bytes	-32,768 to 32,767	Stores small integer values
DECIMAL(p,s)	Variable	Depends on precision (p = 1–38)	Stores exact numeric values
FLOAT	8 bytes	Approx. ±1.79E+308	Stores approximate values

Note: DECIMAL stores exact values, whereas FLOAT stores approximate values and may cause rounding errors.

5.2 Character Data Types

Type	Size	Description
CHAR(n)	n bytes	Fixed-length character data
VARCHAR(n)	n bytes	Variable-length character data
NVARCHAR(n)	2n bytes	Stores Unicode characters
TEXT	Up to 2 GB	Stores large text data

Note: CHAR wastes space if the data length is small, while VARCHAR saves space.

5.3 Date & Time Data Types

Type	Size	Format	Description
DATE	3 bytes	YYYY-MM-DD	Stores date only
TIME	3–5 bytes	HH:MM:SS	Stores time only
DATETIME	8 bytes	YYYY-MM-DD HH:MM:SS	Stores date and time
DATETIME2	6–8 bytes	YYYY-MM-DD HH:MM:SS.n	Higher precision date and time

6. Constraints

Constraints are rules applied to table columns to restrict invalid data entry and to maintain data integrity in a database.

In simple words:

Constraints ensure that only valid, correct, and meaningful data is stored in the database.

Constraints are usually defined at the time of table creation, but they can also be added later using ALTER TABLE.

Why Constraints are Required

Without constraints:

• Duplicate data may be stored

• NULL values may appear where not allowed

• Invalid values (negative salary, wrong age, etc.) may be inserted

• The relationship between tables may break

Constraints help in:

• Maintaining data accuracy

• Enforcing business rules

• Improving database reliability

Types of Constraints in SQL Server

SQL Server mainly supports the following constraints:

1. NOT NULL

2. UNIQUE

3. PRIMARY KEY

4. FOREIGN KEY

5. CHECK

6. DEFAULT

6.1 NOT NULL Constraint

Meaning

The NOT NULL constraint ensures that a column cannot store NULL values.

Why Used

Used when a value is mandatory and must be provided.

Rules

• The column cannot be left empty

• The user must provide a value while inserting data

Syntax

column_name datatype NOT NULL

Example

CREATE TABLE Student (
    RollNo INT NOT NULL,
    Name VARCHAR(50) NOT NULL,
    Age INT
);

✔ RollNo and Name cannot be NULL ✔ Age can be NULL

6.2 UNIQUE Constraint

Meaning

The UNIQUE constraint ensures that all values in a column are different.

Why Used

Used when duplicate values are not allowed, but NULL may be allowed.

Rules

• Duplicate values not allowed

• One or more NULL values allowed (in SQL Server)

Syntax

column_name datatype UNIQUE;

Example

CREATE TABLE Employee (
    EmpID INT,
    Email VARCHAR(50) UNIQUE,
    Name VARCHAR(50)
);

✔ Same Email cannot be inserted twice ✔ Email can be NULL

Multiple UNIQUE Constraints

CREATE TABLE UserAccount (
    Username VARCHAR(30) UNIQUE,
    MobileNo BIGINT UNIQUE
);

6.3 PRIMARY KEY Constraint

Meaning

A PRIMARY KEY uniquely identifies each record in a table.

Why Used

Used to identify records uniquely and to create relationships with other tables.

Rules

• Must be UNIQUE

• Cannot be NULL

• Only one primary key per table

• Can be single-column or composite

Syntax

PRIMARY KEY (column_name)

Single Column Primary Key

CREATE TABLE Department (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

Composite Primary Key

CREATE TABLE Enrollment (
    StudentID INT,
    Subject VARCHAR(30),
    PRIMARY KEY (StudentID, Subject)
);

6.4 FOREIGN KEY Constraint

Meaning

A FOREIGN KEY creates a relationship between two tables by referencing the primary key of another table.

Why Used

• Maintains referential integrity

• Prevents orphan records

Rules

• Value must exist in the parent table

• Can accept NULL (unless NOT NULL applied)

• The child table depends on the parent table

Example (Parent–Child Relationship)

Parent Table :

CREATE TABLE Department (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

Child Table :

CREATE TABLE Employee (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(50),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
);

✔ Employee cannot have a DeptID that does not exist in the department table

ON DELETE / ON UPDATE

FOREIGN KEY (DeptID)
REFERENCES Department(DeptID)
ON DELETE CASCADE