Complete Microsoft SQL Server Notes

1. Introduction to SQL Server

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It is used for storing and retrieving data. SQL Server is known for its reliability, scalability, and powerful querying capabilities.

• SQL stands for Structured Query Language.
• SQL Server can be used for online transaction processing (OLTP), data warehousing, and more.

2. SQL Server Architecture

SQL Server follows a client-server architecture:

• Database Engine: The core service for storing, processing, and securing data.
• SQL Server Services: Handles security, backup, and access control.
• Query Processor: Responsible for interpreting SQL queries and generating execution plans.

3. Databases in SQL Server

A database in SQL Server is a container for tables, views, indexes, stored procedures, etc.

Creating a Database:

CREATE DATABASE School;

This creates a database named School.

4. Tables in SQL Server

A table is a collection of data organized into rows and columns. Each column represents a data type, and each row represents a record.

Creating a Table:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    FirstName NVARCHAR(50),
    LastName NVARCHAR(50),
    Age INT
);

5. Data Types in SQL Server

SQL Server supports various data types:

• INT: Integer values.
• VARCHAR: Variable-length string.
• DATETIME: Date and time.
• FLOAT: Floating-point numbers.
• BIT: Boolean values (0 or 1).

Example:

CREATE TABLE Employees (
    EmployeeID INT,
    Name VARCHAR(100),
    Salary FLOAT,
    HireDate DATETIME
);

6. Inserting Data into a Table

To insert data into a table, you use the INSERT INTO statement.

Example:

INSERT INTO Students (StudentID, FirstName, LastName, Age)
VALUES (1, 'John', 'Doe', 20);

7. Querying Data (SELECT)

The SELECT statement is used to retrieve data.

Basic Query:

SELECT * FROM Students;

Select Specific Columns:

SELECT FirstName, LastName FROM Students;

Filtering Data:

SELECT * FROM Students WHERE Age > 18;

8. Updating Data (UPDATE)

To update existing data in a table, you use the UPDATE statement.

Example:

UPDATE Students
SET Age = 21
WHERE StudentID = 1;

9. Deleting Data (DELETE)

To delete data from a table, you use the DELETE statement.

Example:

DELETE FROM Students
WHERE StudentID = 1;

10. Joins in SQL Server

Joins combine rows from two or more tables based on a related column. SQL Server supports various types of joins:

1. INNER JOIN:

Returns rows when there is a match in both tables.

Example:

SELECT Students.FirstName, Students.LastName, Courses.CourseName
FROM Students
INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
INNER JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

2. LEFT JOIN (LEFT OUTER JOIN):

Returns all records from the left table, and the matched records from the right table. If no match, NULL is returned for columns from the right table.

Example:

SELECT Students.FirstName, Students.LastName, Courses.CourseName
FROM Students
LEFT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
LEFT JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

3. RIGHT JOIN (RIGHT OUTER JOIN):

Returns all records from the right table, and the matched records from the left table. If no match, NULL is returned for columns from the left table.

Example:

SELECT Students.FirstName, Students.LastName, Courses.CourseName
FROM Students
RIGHT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
RIGHT JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

4. FULL OUTER JOIN:

Returns all records when there is a match in one of the tables.

Example:

SELECT Students.FirstName, Students.LastName, Courses.CourseName
FROM Students
FULL OUTER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
FULL OUTER JOIN Courses ON Enrollments.CourseID = Courses.CourseID;

5. CROSS JOIN:

Returns the Cartesian product of both tables (i.e., all possible combinations).

Example:

SELECT Students.FirstName, Courses.CourseName
FROM Students
CROSS JOIN Courses;

11. Aggregate Functions

SQL Server provides aggregate functions for summarizing data:

• COUNT(): Counts rows.
• SUM(): Sums numeric data.
• AVG(): Averages numeric data.
• MIN(): Returns the smallest value.
• MAX(): Returns the largest value.

Example:

SELECT COUNT(*) AS TotalStudents FROM Students;

12. Grouping Data (GROUP BY)

The GROUP BY clause groups rows sharing the same value.

Example:

SELECT Age, COUNT(*) AS TotalStudents
FROM Students
GROUP BY Age;

13. SQL Server Transactions

Transactions ensure that a set of operations are executed successfully as a single unit.

Begin Transaction:

BEGIN TRANSACTION;
UPDATE Students SET Age = 22 WHERE StudentID = 1;
COMMIT;

If something goes wrong, you can use ROLLBACK:

ROLLBACK;

14. Views in SQL Server

A view is a virtual table, defined by a query.

Example:

CREATE VIEW StudentDetails AS
SELECT FirstName, LastName, Age FROM Students WHERE Age > 18;

You can query the view like a table

SELECT * FROM StudentDetails;

15. Indexes in SQL Server

Indexes improve the speed of data retrieval.

Example:

CREATE INDEX idx_student_name ON Students (FirstName);

This query creates an index on the FirstName column.

16. Stored Procedures

A Stored Procedure is a collection of SQL statements stored for later use.

Example:

CREATE PROCEDURE GetStudentsByAge (@age INT)
AS
BEGIN
    SELECT * FROM Students WHERE Age = @age;
END;

You can call the stored procedure:

EXEC GetStudentsByAge 20;

17. Triggers in SQL Server

A trigger is a special type of stored procedure that runs automatically when an event (INSERT, UPDATE, DELETE) occurs.

Example:

CREATE TRIGGER AfterStudentInsert
ON Students
FOR INSERT
AS
BEGIN
    PRINT 'New student inserted';
END;

18. Normalization in SQL Server

Normalization reduces data redundancy and improves data integrity by organizing data into tables.

19. Backup and Restore in SQL Server

Backup and Restore are essential for data safety.

Backup Example:

BACKUP DATABASE School TO DISK = 'C:\backups\school.bak';

Restore Example:

RESTORE DATABASE School FROM DISK = 'C:\backups\school.bak';

20. Basic Operations in SQL Server

Truncate Table:

TRUNCATE removes all rows from a table but does not delete the table itself. It’s faster than DELETE.

TRUNCATE TABLE Students;

Drop Table:

DROP TABLE deletes the table structure along with its data.

DROP TABLE Students;

Drop Database:

DROP DATABASE removes a database completely.

DROP DATABASE School;

Resetting Identity Column:

If a table has an IDENTITY column, you can reset the identity value using DBCC CHECKIDENT.

DBCC CHECKIDENT ('Students', RESEED, 1);

This resets the identity column of the Students table to start from 1 again.