Microsoft SQL Server SETEC Institute

Contents
Chapter 1: Introduction to SQL Server..................................................................................................4
1.1. Overview of SQL Server ..........................................................................................................4
1.1.1. History and Evolution ..........................................................................................................4
1.1.2. Editions and Versions ..........................................................................................................5
1.2. SQL Server Architecture ..........................................................................................................7
1.2.1. Database Engine .................................................................................................................7
1.2.2. SQL Server Services ............................................................................................................8
1.2.3. Tools and Utilities ...............................................................................................................8
1.3. SQL Server Installation and Configuration .................................................................................8
1.3.1. System Requirements ..........................................................................................................9
1.3.2. Installation Steps .................................................................................................................9
1.3.3. Post-Installation Configuration for SQL Server .......................................................................9
Chapter 2: Database Fundamentals .....................................................................................................10
2.1. Understanding Databases and Tables ......................................................................................10
2.1.1. Relational Database Concepts .............................................................................................10
2.1.2. Tables, Rows, and Columns ................................................................................................10
2.2. Data Types ...........................................................................................................................10
2.3. Creating and Managing Databases...........................................................................................11
2.3.1. CREATE DATABASE statement ........................................................................................11
2.3.2. ALTER DATABASE statement ...........................................................................................11
2.3.3. DROP DATABASE statement ............................................................................................12
Chapter 3: SQL Basics ........................................................................................................................13
3.1. SQL Syntax and Structure ......................................................................................................13
3.2. Querying Data ......................................................................................................................14
3.2.1. SELECT statement ............................................................................................................14
3.2.2. Filtering Data with WHERE Clause ....................................................................................14
3.2.3. Sorting Data with ORDER BY ...........................................................................................14
3.3. Joins ....................................................................................................................................14
3.3.1. Self Joins and Cross Joins ..................................................................................................18
Chapter 4: Advanced SQL Queries ......................................................................................................19
4.1. Subqueries ...........................................................................................................................19
4.1.1. Single-Row Subqueries ......................................................................................................19
4.1.2. multi-row subquery ...........................................................................................................19
4.1.3. correlated subquery ...........................................................................................................20
4.2. Set Operations ......................................................................................................................20
4.3. Aggregations and Grouping....................................................................................................20
4.3.1. GROUP BY clause ............................................................................................................20

Page 1 of 43
Microsoft SQL Server SETEC Institute

4.3.1.1. Aggregate functions.......................................................................................................20

Chapter 5: Data Manipulation and Transactions ..................................................................................21
5.1. Inserting Data ...................................................................................................................21
5.1.1. INSERT INTO statement ...................................................................................................21
5.1.2. Inserting multiple rows ......................................................................................................21
5.2. Updating Data ..................................................................................................................21
5.3. Deleting Data ...................................................................................................................22
5.4. Transactions .....................................................................................................................22
Chapter 6: Indexes and Performance Tuning .......................................................................................24
6.1. Understanding Indexes ..........................................................................................................24
6.1.1. Clustered and Non-Clustered Indexes in SQL Server ............................................................. 24
6.1.2. Index Architecture .............................................................................................................25
6.2. Creating and Managing Indexes ..............................................................................................25
6.2.1. Create index statement .......................................................................................................25
6.2.2. Drop Index statement - summary.........................................................................................26
6.3. Query Optimization ...............................................................................................................27
6.3.1. Execution Plans in SQL Server ...........................................................................................27
6.3.2. Query hints.......................................................................................................................27
6.3.3. Performance Monitoring Tools – Summary .......................................................................... 28
Chapter 7: Stored Procedures and Functions .......................................................................................29
7.1. Stored Procedures .................................................................................................................29
7.1.1. Creating and Executing Stored Procedures ........................................................................... 29
7.1.2. Parameters and Return Values in Stored Procedures............................................................... 29
7.1.3. Modifying and Dropping Stored Procedures ......................................................................... 29
7.2. User-Defined Functions .........................................................................................................30
7.2.1. Scalar Functions................................................................................................................30
7.2.2. Table-Valued Functions (TVFs) ..........................................................................................30
7.2.3. Inline Table-Valued Functions (iTVFs) ................................................................................ 30
Chapter 8: Triggers and Views ............................................................................................................32
8.1. Triggers ...............................................................................................................................32
8.1.1. DML Triggers ...................................................................................................................32
8.1.2. Instead of Triggers in SQL Server .......................................................................................32
8.1.3. Managing Triggers ............................................................................................................33
8.2. Views ..................................................................................................................................33
8.2.1. Creating and Managing Views in SQL Server ....................................................................... 33
Chapter 9: Security and Administration ..............................................................................................36
9.1. SQL Server Security Model....................................................................................................36
9.1.1. Authentication Modes (Windows, SQL Server)......................................................................... 36

Page 2 of 43
Microsoft SQL Server SETEC Institute

9.1.2. Logins and Users ..................................................................................................................36

9.2. Permissions and Roles ...........................................................................................................36
9.2.1. Granting and Revoking Permissions in SQL Server ............................................................... 36
9.2.2. Fixed Server Roles and Database Roles ............................................................................... 36
9.3. Backup and Recovery ............................................................................................................37
9.3.1. Backup Types (Full, Differential, Transaction Log) ............................................................... 37
9.3.2. Restoring Databases in SQL Server .....................................................................................37
9.3.3. Disaster Recovery Planning in SQL Server ........................................................................... 38
Chapter 10: Advanced Topics ..............................................................................................................39
10.1. SQL Server Integration Services (SSIS) ..................................................................................39
10.1.1. Overview of SSIS..........................................................................................................39
10.1.2. Creating and Managing SSIS Packages ............................................................................ 39
10.2. SQL Server Reporting Services (SSRS) ...................................................................................40
10.2.1. Deploying and Managing Reports ...................................................................................40
10.3. SQL Server Analysis Services (SSAS) .....................................................................................41
10.3.1. Overview of SSAS ........................................................................................................41
10.3.2. Creating and Managing OLAP Cubes .............................................................................. 41
10.4. High Availability and Replication...........................................................................................42
10.4.1. Always On Availability Groups .......................................................................................42
10.4.2. Database Mirroring in SQL Server ..................................................................................42
10.4.3. Replication Types in SQL Server .....................................................................................43

3
Page 3 of 43
Microsoft SQL Server SETEC Institute

Chapter 1: Introduction to SQL Server

1.1. Overview of SQL Server

SQL Server is a relational database management system (RDBMS) developed by Microsoft. It

is used to store, manage, and retrieve data as requested by other software applications, whether those
applications are running on the same computer or accessing it over a network. SQL Server provides
robust support for a wide range of data operations, from basic querying to complex analytics, and is
widely used in industries ranging from finance to retail.
Here’s a more detailed breakdown of SQL Server:
1.1.1. History and Evolution
The history and evolution of Microsoft SQL Server
spans several decades, with constant improvements
to support growing data needs, performance
requirements, and integration with various
technologies. Here’s a breakdown of its key
milestones:
Early Beginnings (1989 - 1990s)
1989: SQL Server 1.0 was released in collaboration
with Sybase and Ashton-Tate, based on Sybase’s SQL Server for OS/2.
1990: SQL Server 4.2 was released for OS/2.
1993: SQL Server 6.0 was introduced for Windows NT, optimizing the product for Microsoft’s
Windows environment.
Rise of Microsoft SQL Server (1990s - Early 2000s)
1995: SQL Server 6.5 introduced features like log shipping and indexed views.
1998: SQL Server 7.0 featured a redesigned engine, support for OLAP, and better business intelligence
tools.
2000: SQL Server 2000 introduced XML support, distributed partitioned views, and scalability
improvements.
Business Intelligence and Data Warehousing (2005 - 2010)
2005: SQL Server 2005 introduced SQL Server Management Studio (SSMS), SSRS, SSIS, and SSAS
for advanced reporting, integration, and analysis.
2008: SQL Server 2008 added features like compression, Transparent Data Encryption (TDE), and
Resource Governor.
2012: SQL Server 2012 introduced Always on Availability Groups, Column Store Indexes, and
Contained Databases.
Page 4 of 43
Microsoft SQL Server SETEC Institute

Cloud Integration and New Technologies (2014 - 2017)

2014: SQL Server 2014 enhanced scalability with in-memory OLTP and buffer pool extension.
2016: SQL Server 2016 introduced Stretch Database, Always Encrypted, Query Store, and R
integration for analytics.
2017: SQL Server was released on Linux, supporting broader deployment and adding graph databases.
Current Era and Continued Evolution (2019 - Present)
2019: SQL Server 2019 introduced Big Data Clusters, Data Virtualization, and improved hybrid
workload performance.
2021 and Beyond: Focus on cloud integration, AI, and machine learning with tools like Azure
Synapse Analytics and Power BI.
2022 and Beyond: SQL Server 2022 added features such as accelerated database recovery, ledger
tables, and better integration with Power BI and Azure Synapse.
1.1.2. Editions and Versions

1.1.2.1. Editions
 Enterprise Edition:
For large enterprises with advanced features like high availability, in-
memory OLTP, and analytics.
Maximum scalability and performance.

 Standard Edition:
For small to medium-sized businesses with core database features.
Limited scalability compared to Enterprise. \

 Developer Edition:
Full Enterprise features for development and testing (not for production).

Page 5 of 43
Microsoft SQL Server SETEC Institute

 Express Edition:
Free, lightweight edition for small applications, learning, or hobbyists.
Limited to 10 GB database size and 1 GB memory usage.

1.1.2.2. Version
SQL Server 2000: Introduced XML support and indexed views.

SQL Server 2005: Added SQL Server Management Studio (SSMS) and .NET integration.
SQL Server 2008: Introduced policy-based management and transparent data encryption.
SQL Server 2012: Added Always on Availability Groups and Columnstore Indexes.
SQL Server 2014: Introduced in-memory OLTP and Azure integration.
SQL Server 2016: Added JSON support, Query Store, and Stretch Database.

SQL Server 2017: First version to support Linux and added Python for machine learning.
SQL Server 2019: Introduced Big Data Clusters and enhanced data virtualization.
SQL Server 2022: Added Azure Synapse Link, improved security, and performance analytics.

Page 6 of 43
Microsoft SQL Server SETEC Institute

1.2. SQL Server Architecture

Microsoft SQL Server follows a client-server architecture that consists of several key
components working together to store, process, and manage data efficiently.

1.2.1. Database Engine

The Database Engine is the core of Microsoft SQL Server, responsible for query execution, transaction
management, data storage, and security.

Query Processor (Relational Engine): Parses, optimizes, and executes SQL queries.
Storage Engine: Manages data storage, retrieval, and physical files (MDF, LDF, NDF).
Transaction Management: Ensures ACID compliance using logs, locks, and isolation levels.
Buffer Pool: Caches frequently used data for performance optimization.
System Databases: Includes master, msdb, model, and tempdb for system operations.Indexing &
Performance Optimization: Uses Clustered, Non-Clustered, and Full-Text Indexes for faster queries.

Page 7 of 43
Microsoft SQL Server SETEC Institute

1.2.2. SQL Server Services

SQL Server includes multiple services that handle database management, automation, reporting, and
analytics.
Database Engine (MSSQLSERVER): Core service for query execution, transactions, and data storage.
SQL Server Agent (SQLSERVERAGENT): Automates jobs, backups, and alerts.
SQL Server Browser (SQLBROWSER): Helps clients connect to the correct SQL Server instance.
Full-Text Search (MSSQLFDLauncher): Enables fast text-based searches within tables.
Reporting Services (SSRS): Creates and manages interactive reports.
Analysis Services (SSAS): Supports OLAP and data mining for business intelligence.
Integration Services (SSIS): Handles ETL (Extract, Transform, Load) processes for data migration.
Polybasic Engine: Queries external data sources like Hadoop and Azure.
Machine Learning Services: Runs Python and R scripts for analytics and AI.
1.2.3. Tools and Utilities
 SQL Server Management Studio (SSMS):
Primary tool for managing SQL Server instances, databases, security, and query execution.
Features include query editor, database management, security settings, performance monitoring, and
integration with SSRS, SSIS, SSAS.
 SQL Server Configuration Manager:
Used to manage SQL Server services, configure network protocols, and modify client settings.
Key functions include service management, network protocol configuration, and managing SQL
Server aliases.
 Other Utilities:
SQLCMD: Command-line tool for executing T-SQL and scripts.
BCP: Bulk copy program for importing/exporting data.
SQL Server Profiler: Tool for tracing and analyzing SQL Server events for debugging and performance
tuning.
1.3. SQL Server Installation and Configuration

Page 8 of 43
Microsoft SQL Server SETEC Institute

1.3.1. System Requirements

 Hardware:
Processor: Minimum 1.4 GHz, multi-core recommended.
Memory: Minimum 4 GB RAM, 8 GB recommended.
Disk Space: At least 6 GB for installation; more for data and logs.
Disk Type: SSD recommended for better performance.
 Software:
Operating System: Windows Server or Windows 10/11 for development.
.NET Framework: Required version (typically .NET 4.6+).
Web Browser: Microsoft Edge, Internet Explorer 11, or latest Chrome.
Network: Ensure required ports (e.g., 1433) are open for connections.
1.3.2. Installation Steps
Run Setup: Download and run the SQL Server setup from the official Microsoft website.
Choose Installation Type: Select either "New SQL Server Standalone Installation" or "Add Features to
Existing Installation."
Accept License Terms: Review and accept the license agreement.
Select Features: Choose the features you need (e.g., Database Engine, SQL Server Agent, Reporting
Services).
Configure Instance: Select the SQL Server instance (default or named), and configure service accounts
and collation settings.
Set Authentication Mode: Choose between Windows Authentication or Mixed Mode and specify SQL
Server administrators.
Configure Data Directories: Set paths for database and log files.
Review and Install: Review your configuration and click "Install" to begin the process.
Complete Installation: Once installation is complete, restart the server if needed.
Post-Installation Configuration: Use SQL Server Configuration Manager for network protocols, install
SQL Server Management Studio (SSMS), and apply updates.
Verify Installation: Connect via SSMS to ensure all features are working.
1.3.3. Post-Installation Configuration for SQL Server
Network & Service Configuration – Enable/disable TCP/IP, Named Pipes, and manage
SQL Server services.
Authentication Mode – Choose between Windows Authentication (more secure) or Mixed Mode.
User Permissions & Roles – Assign minimum necessary privileges and database roles.
Database Default Locations – Configure data (.mdf) and log (.ldf) file paths for optimal storage.
SQL Server Agent – Enable for automating backups and maintenance jobs.
Performance Optimization – Adjust max server memory, tempdb settings, and indexing.
Backup Strategy – Set up full, differential, and log backups with offsite storage.
Remote Connections – Configure firewall rules and TCP/IP settings if needed.
Security Enhancements – Disable/rename say account, enable TDE, RLS, and data masking.
Monitoring & Alerts – Use logs, alerts, and Profiler/Extended Events for performance tracking.

yty

Page 9 of 43
Microsoft SQL Server SETEC Institute

Chapter 2: Database Fundamentals

2.1. Understanding Databases and Tables

2.1.1. Relational Database Concepts

Relational databases store data in structured tables, where each table consists of rows (records)
and columns (attributes). Key concepts include:
Tables: Store data in rows and columns.
Primary Key: Uniquely identifies each row in a table.
Foreign Key: Links a column in one table to the primary key of another table, establishing
relationships.
Relationships: Can be one-to-one, one-to-many, or many-to-many, defining how tables are connected.
Normalization: Organizes data to minimize redundancy and ensure integrity by splitting tables into
smaller, related ones.
Constraints: Enforce rules on data, like ensuring values are not null or unique.
Indexes: Improve the speed of data retrieval.
Views: Virtual tables created by queries to simplify complex data.
Transactions: A set of operations executed as a single unit to ensure data consistency using ACID
properties (Atomicity, Consistency, Isolation, Durability).
Data Integrity: Ensures the accuracy and consistency of data through keys and constraints.
2.1.2. Tables, Rows, and Columns
Tables store data in a structured format, representing entities like employees or orders.
Columns define the attributes of the data, with each column holding specific types of information (e.g.,
text, numbers, dates).
Rows represent individual records or data entries, with each row containing values for all column.
2.2. Data Types
 Numeric Data Types:
Store numbers, including integers (INT, BIGINT, SMALLINT, TINYINT) and decimals
(DECIMAL, NUMERIC, FLOAT, REAL).
 Character Data Types:
Store text-based data. Includes fixed-length (CHAR) and variable-length (VARCHAR)
strings, as well as Unicode strings (NCHAR, NVARCHAR) and large text data (TEXT,
NTEXT).
 Date/Time Data Types:
Store date, time, or both (DATE, TIME, DATETIME, SMALLDATETIME,
DATETIME2, DATETIMEOFFSET).
 Other Data Types:
Handle special data, such as binary data (BINARY, VARBINARY, IMAGE), Boolean
values (BIT), globally unique identifiers (UNIQUEIDENTIFIER), monetary values
(MONEY, SMALLMONEY), and structured data (XML, JSON).

Page 10 of 43
Microsoft SQL Server SETEC Institute

2.3. Creating and Managing Databases

2.3.1. CREATE DATABASE statement
The CREATE DATABASE statement in SQL is used to create a new database. It
defines a container where tables, views, stored procedures, and other database objects can be
stored.

Defines a new database with the specified name.

CREATE DATABASE database_name;

Requires appropriate permissions.

Some database systems allow additional options like collation and file locations.

ALTER DATABASE database_name

MODIFY [options];

Example:
This creates a database named SchoolDB.

CREATE DATABASE SchoolDB;

Best Practices:
Choose a meaningful database name.
Ensure proper permissions before execution.
Configure additional settings if needed.
2.3.2. ALTER DATABASE statement
The ALTER DATABASE statement in SQL Server is used to modify an existing database's properties,
settings, or files.
Basic Syntax:
Changing the recovery model:

ALTER DATABASE database_name

ADD FILEGROUP new_filegroup;

Renaming a database:

ALTER DATABASE old_database_name

MODIFY NAME = new_database_name;

Changing database options (e.g., setting AUTO_CLOSE to ON):

Page 11 of 43
Microsoft SQL Server SETEC Institute

2.3.3. DROP DATABASE statement

The DROP DATABASE statement in SQL Server is used to delete an existing database and all
its associated objects, such as tables, views, and stored procedures. Once a database is dropped, all the
data within it is permanently deleted.
Example:

DROP DATABASE daabase_name;

yty

Page 12 of 43
Microsoft SQL Server SETEC Institute

Chapter 3: SQL Basics

3.1.SQL Syntax and Structure

SQL (Structured Query Language) follows a specific syntax and structure for executing queries
and commands to interact with a database. Understanding SQL syntax is crucial for creating, querying,
and manipulating databases effectively.
The four basic SQL commands are essential for interacting with a database:
SELECT: Retrieves data from one or more tables. You specify the columns and the table, and
optionally use a WHERE clause to filter results.
Example:

SELECT first_name, last_name FROM Employees

WHERE department = 'HR';

INSERT: Adds new records (rows) into a table, specifying the columns and values to be inserted.
Example:

INSERT INTO Employees (first_name, last_name, department,

hire_date)

VALUES ('John', 'Doe', 'HR', '2023-01-15');

UPDATE: Modifies existing records in a table. You set new values for specific columns and can filter
which rows to update using a WHERE clause.
Example:

UPDATE Employees

SET department = 'Finance', hire_date = '2023-02-01'

WHERE first_name = 'John' AND last_name = 'Doe';

Page 13 of 43
Microsoft SQL Server SETEC Institute

DELETE: Removes records from a table based on a specified condition with a WHERE clause.
Example:

DELETE FROM Employees WHERE department = 'HR';

3.2. Querying Data

Querying data in SQL involves retrieving and manipulating information from one or more tables using
various commands and clauses. Key techniques include:
3.2.1. SELECT statement
The SELECT statement in SQL is used to retrieve data from one or more tables. Key components and
uses include:
Basic Syntax: SELECT column1, column2, ... FROM table_name; allows retrieving specific columns
or all columns (*).
Filtering: Use WHERE to filter results based on conditions.
Sorting: ORDER BY sorts the result set by one or more columns.
Limiting Results: Use LIMIT (MySQL/PostgreSQL) or TOP (SQL Server) to restrict the number of
rows returned.
Removing Duplicates: DISTINCT eliminates duplicate values in the results.
Combining Conditions: Use AND/OR to combine multiple conditions in WHERE.
Aggregate Functions: Functions like COUNT (), SUM (), and AVG () help perform calculations on the
data.
3.2.2. Filtering Data with WHERE Clause
The WHERE clause in SQL is used to filter records based on specific conditions, allowing you to
retrieve only the rows that meet your criteria. Key components include:
Comparison Operators: (=, !=, <, >, <=, >=) for basic comparisons.
Logical Operators: (AND, OR, NOT) to combine multiple conditions.
IN: Filters results to match any value in a list.
BETWEEN: Filters results within a specific range.
LIKE: Performs pattern matching for string values (e.g., using % for wildcards).
IS NULL/IS NOT NULL: Filters rows with or without NULL values.
3.2.3. Sorting Data with ORDER BY
The ORDER BY clause in SQL Server is used to sort query results by one or more columns in
ascending (default) or descending order. You can sort by multiple columns by specifying them in the
ORDER BY clause.
Key Points:
ASC: Sorts in ascending order (default).
DESC: Sorts in descending order.
It can be used to sort by one or more columns, Exam: ORDER BY column1 ASC, column2 DESC.
The ORDER BY clause is placed at the end of the query.
3.3.Joins

 INNER JOIN,
 LEFT JOIN,
 RIGHT JOIN,
 FULL OUTER JOIN

Page 14 of 43
Microsoft SQL Server SETEC Institute

• INNER JOIN:
Returns rows with matching values in both tables.
Non-matching rows are excluded.

 Example:

SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.OrderDate

FROM Customers

INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

CustomerID Name OrderID OrderDate

1 John 101 2024-02-01
2 Alice 102 2024-02-05

• LEFT JOIN (LEFT OUTER JOIN):

Returns all rows from the left table and matching rows from the right.
Non-matching rows from the right table are filled with NULL.
 Example:

Page 15 of 43
Microsoft SQL Server SETEC Institute

SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.OrderDate

FROM Customers

LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

CustomerID Name OrderID OrderDate

1 John 101 2024-02-01
2 Alice 102 2024-02-05
3 Bob null null

• RIGHT JOIN (RIGHT OUTER JOIN):

Returns all rows from the right table and matching rows from the left.
Non-matching rows from the left table are filled with NULL.
 Example:

SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.OrderDate

FROM Customers

RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

CustomerID Name OrderID OrderDate

1 John 101 2024-02-01
2 Alice 102 2024-02-05
null null 103 2024-02-07

Page 16 of 43
Microsoft SQL Server SETEC Institute

• FULL OUTER JOIN:

Returns all rows when there is a match in either the left or right table.
Non-matching rows from both tables are filled with NULL.
 Example:

SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.OrderDate

FROM Customers

FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

CustomerID Name OrderID OrderDate

1 John 101 2024-02-01
2 Alice 102 2024-02-05
3 Bob null null
null null 103 2024-02-05

Page 17 of 43
Microsoft SQL Server SETEC Institute

3.3.1. Self Joins and Cross Joins

• SEFT JOIN:
A table is joined with itself to represent relationships between rows within the same table,
typically using aliases.
 Example:

SELECT S1.StudentID, S1.Name, S1.Age

FROM Students S1 INNER JOIN Students S2

ON S1.Name = S2.Name AND S1.StudentID <> S2.StudentID;

studentID Name Ag
e
1 John 18
3 John 22

•CROSS JOIN:
Produces the Cartesian product of two tables, returning all possible combinations of
rows from both tables.
 Example:

SELECT Products.ProductName,
Colors.Color

FROM Products CROSS JOIN Colors;

ProductName Color
Shirt Red
Shirt Blue
Pants Red
Pants Blue

yty

Page 18 of 43
Microsoft SQL Server SETEC Institute

Chapter 4: Advanced SQL Queries

Advanced SQL queries involve more complex techniques and operations beyond basic queries,
providing powerful ways to retrieve, manipulate, and analyze data
4.1.Subqueries
A subquery in SQL is a query nested inside another query, allowing you to perform complex
operations by using the result of one query in another. There are several types of subqueries:
Single-Row Subquery: Returns a single value and is used with comparison operators.
Multiple-Row Subquery: Returns multiple rows and is used with operators like IN, ANY, or ALL.
Multiple-Column Subquery: Returns multiple columns, often used with comparison operators.
Correlated Subquery: References columns from the outer query and is evaluated for each row in the
outer query.
Scalar Subquery: Returns a single value and is commonly used in the SELECT clause.
4.1.1. Single-Row Subqueries
A single-row subquery in SQL returns exactly one row and one or more columns. It is used in the outer
query to compare values using operators like =, <, >, etc.
Key Points:
Returns one row with one or more columns.
Commonly used in the WHERE or HAVING clause.
Can compare results with operators like =, <, >, etc.

4.1.2. multi-row subquery

A multi-row subquery returns multiple rows and is used with operators like IN, ANY, and ALL to
compare a value against a set of values.
Key Points:
Returns multiple rows.
Commonly used with operators like IN, ANY, and ALL.
Allows comparisons between a value and a set of values from the subquery.

Page 19 of 43
Microsoft SQL Server SETEC Institute

4.1.3. correlated subquery

A correlated subquery is a subquery that references columns from the outer query, making it dependent
on the outer query for each row it processes. The subquery is executed for each row in the outer query.
Key Points:
References outer query columns.
Executed once for each row in the outer query.
Used with operators like WHERE, HAVING, or EXISTS.
4.2.Set Operations
UNION, INTERSECT, EXCEPT
These SQL set operations are used to combine or compare the results of two or more SELECT
queries.
• UNION:
Combines the results of two queries and removes duplicate rows.
Both queries must have the same number of columns with compatible data types.
• INTERSECT:
Returns only the rows that are common in both queries.
Eliminates duplicates and returns the intersection of the two result sets.
• EXCEPT:
Returns rows from the first query that are not present in the second query.
Eliminates duplicates and returns the difference between the two result sets.
4.3.Aggregations and Grouping
Aggregations and grouping are fundamental concepts in SQL that allow you to summarize data and
perform calculations over groups of rows
4.3.1. GROUP BY clause
The GROUP BY clause in SQL is used to group rows based on one or more columns and
perform aggregate calculations (Exam., SUM (), AVG (), COUNT ()) on each group.
Key Points:
Groups data by one or more columns.
Often used with aggregate functions to summarize data.
The HAVING clause filters the grouped results (since WHERE can't be used with aggregates).
4.3.1.1. Aggregate functions
Aggregate functions in SQL perform calculations on a set of values and return a single result. They are
commonly used with the GROUP BY clause to summarize data.
 Key Aggregate Functions:
• COUNT (): Counts the number of rows.
• SUM (): Adds up the values in a numeric column.
• AVG (): Calculates the average value of a numeric column.
• MIN (): Returns the smallest value.
• MAX (): Returns the largest value.

yty

Page 20 of 43
Microsoft SQL Server SETEC Institute

Chapter 5: Data Manipulation and Transactions

5.1. Inserting Data

5.1.1. INSERT INTO statement

The INSERT INTO statement in SQL is used to add new rows to a table. You can insert one or
multiple rows at once, and optionally specify which columns to insert data into.
Key Points:
Single Row: Insert one row by specifying column names and values.
Multiple Rows: Insert multiple rows in one statement by separating each set of values with commas.
Omitting Columns: If inserting into all columns in the correct order, column names can be omitted.
Default Values: Columns with default values will use those if not specified.
Example:

INSERT INTO Employees (EmployeeID, EmployeeName, Salary)

VALUES (1, 'John Doe', 50000);

5.1.2. Inserting multiple rows

The INSERT INTO statement allows inserting multiple rows in a single query.
Insert multiple rows with a single statement.
All rows must match the same column order and data types.
Reduces redundancy and improves performance compared to multiple single-row inserts.

INSERT INTO Employees (EmployeeID, EmployeeName, Salary, DepartmentID)

VALUES

(1, 'John Doe', 50000, 3),

(2, 'Jane Smith', 60000, 2);

5.2. Updating Data

5.2.1. UPDATE Statement
The UPDATE statement in SQL is used to modify existing data in a table. You can update one or more
columns for specific rows, with the option to apply conditions using the WHERE clause.
Key Points:
SET: Specifies the columns and their new values.
WHERE: Limits the rows to update; omitting it updates all rows.

Page 21 of 43
Microsoft SQL Server SETEC Institute

You can update multiple columns at once.

Example:

UPDATE Employees SET Salary = 55000

WHERE EmployeeID = 1;

5.2.2. Updating Multiple Columns

The UPDATE statement in SQL allows you to modify multiple columns in a table at once. You specify
the columns and their new values in the SET clause, separating them with commas.
Key Points:
Update multiple columns by listing them in the SET clause.
Use the WHERE clause to target specific rows. If omitted, all rows will be updated.
5.3. Deleting Data
5.3.1. The DELETE statement
The DELETE statement in SQL is used to remove rows from a table. You can delete specific rows
using a WHERE clause, or remove all rows if no condition is specified.
Key Points:
WHERE Clause: Specifies which rows to delete. Omit it to delete all rows.
Permanent Action: Deleting data is permanent and cannot be undone without a backup.
Performance: Deleting many rows can be slow, especially without proper indexing.
Exam:
DELETE FROM Employees WHERE EmployeeID = 1;

5.3.2. TRUNCATE TABLE statement

The TRUNCATE TABLE statement removes all rows from a table efficiently and quickly, without
logging individual row deletions, making it faster than DELETE. It resets identity columns and does
not activate any DELETE triggers. Unlike DELETE, TRUNCATE cannot be used with a WHERE
clause and is not as flexible for partial deletions.
Key Points:
Faster than DELETE for removing all rows.
Reset’s identity columns.
Cannot be used with WHERE.
Does not trigger DELETE triggers.
Cannot be undone unless within a transaction.
Exam:

TRUNCATE TABLE Employees;

5.4. Transactions
BEGIN TRANSACTION, COMMIT, ROLLBACK
The BEGIN TRANSACTION, COMMIT, and ROLLBACK commands in SQL manage the
flow of a transaction.
• BEGIN TRANSACTION: Starts a new transaction.
• COMMIT: Saves all changes made during the transaction and makes them permanent.
• ROLLBACK: Undoes any changes made during the transaction, reverting the database to its
previous state.

Page 22 of 43
Microsoft SQL Server SETEC Institute

5.4.1. Transaction isolation levels

Transaction isolation levels define the degree of visibility between concurrent transactions.
 The four levels are:
- Read Uncommitted: Allows dirty reads, non-repeatable reads, and phantom reads, offering the
highest concurrency but lowest consistency.
- Read Committed: Prevents dirty reads but allows non-repeatable reading and phantom reads,
balancing performance and consistency.
- Repeatable Read: Prevents non-repeatable reads but allows phantom reads, ensuring higher
consistency at the cost of some concurrency.
- Serializable: Prevents dirty reads, non-repeatable reads, and phantom reads, offering the
highest isolation but the lowest concurrency.

yty

Page 23 of 43
Microsoft SQL Server SETEC Institute

Chapter 6: Indexes and Performance Tuning

6.1. Understanding Indexes

Indexes improve query performance by enabling SQL Server to retrieve data efficiently instead of
scanning entire tables.
6.1.1. Clustered and Non-Clustered Indexes in SQL Server
Indexes improve query performance by making data retrieval faster. The two main types are: Clustered
Index
Stores data physically sorted based on the indexed column.
Only one per table (usually on the primary key).
Best for range queries (ORDER BY, BETWEEN).
Example:

CREATE CLUSTERED INDEX IX_Orders_OrderDate ON Orders (OrderDate);

Non-Clustered Index

Stores pointers to the actual data (does not change physical order).
Multiple allowed per table for different queries.
Best for searching specific values (WHERE, JOIN).
Example:
Key Differences

CREATE NONCLUSTERED INDEX IX_Customers_LastName ON Customers

(LastName);

Feature Clustered Index Non-Clustered Index

Data Storage Physically sorted Separate lookup table
Allowed Per Table 1 Multiple
Best For Sorting, range queries Searching, filtering
Space Usage No extra storage Requires additional storage
Page 24 of 43
Microsoft SQL Server SETEC Institute

6.1.2. Index Architecture

SQL Server indexes use a B-tree (Balanced Tree) structure for efficient data retrieval. Here's
how the architecture works.
Root Node: Directs to intermediate nodes.
Intermediate Nodes: Help navigate the tree.
Leaf Nodes: Store data or row pointers.
 Clustered Index
Leaf nodes store the actual data sorted by the indexed column.
Only one per table, and the data is physically ordered based on the index.
 Non-Clustered Index
Leaf nodes contain pointers (Row IDs) to the actual data, but the data is not physically ordered.
Multiple non-clustered indexes can exist on a table.
 Covering Index
Stores all required query columns in the leaf nodes, avoiding extra table lookups.
Storage & Pages
Indexes are stored in 8KB pages for efficient memory use, and regular maintenance
(rebuild/reorganize) is necessary to avoid fragmentation.
6.2. Creating and Managing Indexes

6.2.1. Create index statement

The CREATE INDEX statement in SQL Server is used to create clustered or non-clustered
indexes, enhancing query performance by allowing faster data retrieval.
Types of Indexes:
• Basic Index: Speeds up queries by indexing one or more columns.

CREATE INDEX IX_Customers_LastName ON Customers (LastName);

• Unique Index: Ensures column values are unique.

CREATE UNIQUE INDEX IX_Employees_Email ON Employees (Email);

• Composite Index: Indexes multiple columns for faster querying on combinations

CREATE INDEX IX_Orders_CustomerDate ON Orders (CustomerID, OrderDate);

• Clustered Index: Sorts and stores data rows based on the index key.
CREATE CLUSTERED INDEX IX_Orders_OrderDate ON Orders (OrderDate);

Page 25 of 43
Microsoft SQL Server SETEC Institute

• Non-Clustered Index: Stores pointers to data rows, allowing multiple indexes.

CREATE NONCLUSTERED INDEX IX_Customers_City ON Customers (City);

• Filtered Index: Indexes a subset of rows based on a WHERE condition.

CREATE NONCLUSTERED INDEX IX_Orders_Pending ON Orders (OrderDate) WHERE

Status = 'Pending';

• Full-Text Index: Supports full-text search queries.

CREATE FULLTEXT INDEX ON Articles (Title, Content);

6.2.2. Drop Index statement - summary

The DROP INDEX statement is used to delete an existing index from a table or view in
SQL Server, which can help improve performance during data modification operations.
Syntax:

DROP INDEX [IF EXISTS] index_name ON table_name;

index_name: Name of the index to delete.

table_name: Table or view from which to drop the index.
IF EXISTS: Prevents an error if the index doesn't exist.
 Examples:
Drop Non-Clustered Index: DROP INDEX idx_LastName ON Employees;
Drop Clustered Index: DROP INDEX idx_EmployeeID ON Employees;
Safe Drop with IF EXISTS: DROP INDEX IF EXISTS idx_ActiveEmployees ON Employe
Rebuilding Indexes:
Removes fragmentation by recreating the index.
Used for high fragmentation (>30%).
Example:

ALTER INDEX idx_EmployeeID ON Employees REBUILD;

 Reorganizing Indexes:
Defragments the index without rebuilding it.
Best for moderate fragmentation (10%-30%).
Example:

ALTER INDEX idx_EmployeeID ON Employees REORGANIZE;

 Updating Statistics:
Keeps query performance optimal by refreshing data distribution statistics.
Example:

UPDATE STATISTICS Employees;

Page 26 of 43
Microsoft SQL Server SETEC Institute

 Dropping Unused Indexes:

Removes indexes that are no longer used to save space and reduce maintenance overhead.
Example:

DROP INDEX idx_UnusedIndex ON Employees;

6.3. Query Optimization

6.3.1. Execution Plans in SQL Server
An execution plan shows how SQL Server will execute a query, detailing operations like
scans, joins, and sorts. It helps identify inefficiencies and optimize performance.
 Types of Execution Plans:
Estimated Execution Plan: Preview of the plan SQL Server will use without executing the
query.
Actual Execution Plan: Plan based on the actual execution, showing runtime statistics like CPU and
I/O costs.
 Key Components:
Operators: Actions like scan, seek, join methods (nested loop, merge join, hash match).
Cost: Percentage of total query cost associated with each operation.
I/O and CPU Costs: Resource usage for each operation.
Warnings: Indicates issues such as missing indexes.
Optimizing with Execution Plans:
Indexing: Use indexes to avoid table scans and improve efficiency.
Join Optimization: Choose the best join type for your data.
Rewriting Queries: Modify complex queries to reduce costly operations.
Updating Statistics: Ensure SQL Server has up-to-date information for optimal query plans.
6.3.2. Query hints
Query hints are used to influence the behavior of the SQL Server query optimizer by providing
specific instructions on how a query should be executed.
 Types of Query Hints:
Join Hints: Control the join type (e.g., LOOP, MERGE, HASH).
Index Hints: Force the use of a specific index (e.g., INDEX (IndexName)).
Table Hints: Affect table-level operations like locking (e.g., NOLOCK).
Max Parallelism: Specify the number of processors to use (MAXDOP).
Query Timeout: Enable specific trace flags (e.g., QUERYTRACEON).
 When to Use:
Performance Tuning: When the optimizer's chosen plan is inefficient.
Testing and Troubleshooting: To test specific execution plans or bypass optimizer decisions.
Optimizing Joins: When a particular join type performs better.
Avoiding Locks: To allow dirty reads (e.g., NOLOCK).

Page 27 of 43
Microsoft SQL Server SETEC Institute

6.3.3. Performance Monitoring Tools – Summary

SQL Server offers several performance monitoring tools to track query performance, server
health, and resource usage.
 SQL Server Management Studio (SSMS):
Activity Monitor: Displays real-time data on CPU, memory, and active sessions.
 SQL Server Profiler:
Captures and analyzes SQL events to troubleshoot performance issues.
 Dynamic Management Views (DMVs):
Real-time insights into queries, sessions, and resource usage.
Key DMVs include sys.dm_exec_requests (active queries) and sys.dm_db_index_physical_stats (index
fragmentation).
 Performance Monitor (Windows):
Tracks system-level performance like CPU, disk, and memory usage.
SQL Server Extended Events:
A lightweight, customizable tool for collecting and analyzing SQL Server events.
 Query Store:
Stores query execution plans and performance data over time for analysis.
 Database Tuning Advisor (DTA):
Suggests indexing and optimization strategies based on query workloads.
 SQL Server Data Collector:
Collects performance data for trend analysis and historical monitoring.

yty

Page 28 of 43
Microsoft SQL Server SETEC Institute

Chapter 7: Stored Procedures and Functions

7.1. Stored Procedures

7.1.1. Creating and Executing Stored Procedures
Stored procedures in SQL Server are precompiled collections of SQL statements used for
encapsulating logic and automating tasks.
Creating Stored Procedures: Use the CREATE PROCEDURE statement to define stored procedures
with input parameters and SQL logic.
Executing Stored Procedures: Execute with the EXEC or EXECUTE command, passing required
parameters.
Output Parameters: Stored procedures can return values via output parameters.
Error Handling: Incorporate TRY...CATCH blocks to handle errors within stored procedures.
Transactions: Stored procedures can manage transactions to ensure data integrity.
Modifying and Dropping: Modify with ALTER PROCEDURE or remove with DROP PROCEDURE.
7.1.2. Parameters and Return Values in Stored Procedures
 Parameters:
Input Parameters: Pass values into a stored procedure for processing.
Output Parameters: Return values from the procedure back to the caller.
Input/Output Parameters: Can be used for both passing values in and returning values out.
 Return Values:
A stored procedure can return an integer value using the RETURN statement, typically to indicate
success (e.g., 1) or failure (e.g., 0).
Combining Parameters and Return Values:
Stored procedures can use a mix of input, output, and return values for greater flexibility in handling
data and logic.
7.1.3. Modifying and Dropping Stored Procedures
Modifying Stored Procedures: Use the ALTER PROCEDURE statement to update an existing stored
procedure's logic, such as adding or changing SQL queries or parameters.
Dropping Stored Procedures: Use the DROP PROCEDURE statement to permanently delete a stored
procedure from the database.
 Best Practices:
Always back up stored procedures before making changes.
Page 29 of 43
Microsoft SQL Server SETEC Institute

Test changes in a development environment before applying them to production.

Use version control to track changes and maintain a history.
7.2.User-Defined Functions
7.2.1. Scalar Functions
Scalar Functions return a single value based on input parameters, such as integers, strings, or
dates.
Usage: They can be used in SQL queries wherever expressions are allowed, like in SELECT, WHERE,
HAVING, and ORDER BY clauses.
Creation: Use the CREATE FUNCTION statement to define scalar functions, specifying the function
name, parameters, return type, and logic to compute the result.
 Examples:
Concatenating first and last names.
Calculating age from a date of birth.
Modifying: You cannot modify scalar functions directly; you must drop and recreate them.
Dropping: Use the DROP FUNCTION statement to remove a scalar function.
 Best Practices:
Keep logic simple to avoid performance issues.
Use scalar functions for reusable logic but be mindful of performance, especially on large datasets.
7.2.2. Table-Valued Functions (TVFs)
Table-Valued Functions (TVFs) return a table as a result, unlike scalar functions that return a single
value.
There are two types of TVFs:
Inline TVFs (iTVF): Return a table from a single SELECT query, making them simpler and more
efficient.
Mult statement TVFs (mTVF): Return a table populated from multiple SQL statements and are more
flexible but can be less efficient.
Creating TVFs: Use the CREATE FUNCTION statement, defining parameters, return type (TABLE),
and the logic to return the table.
Usage: TVFs can be used in queries like regular tables or views, including in SELECT, JOIN, and
other SQL operations.
Modifying and Dropping: You must drop and recreate TVFs to modify them, and the DROP
FUNCTION statement is used to delete them.
 Best Practices:
Use inline TVFs for better performance.
Be mindful of performance, especially with multistatement TVFs.
TVFs help reduce code duplication by encapsulating reusable logic.
7.2.3. Inline Table-Valued Functions (iTVFs)
Inline Table-Valued Functions (iTVFs) return a table as a result from a single SELECT query, making
them simple and efficient.
Characteristics:
Contain only one SELECT statement.
Return a result set directly without the use of a table variable.
Optimized by SQL Server's query optimizer, leading to better performance compared to multistatement
TVFs.
Usage:
Used in queries like tables for SELECT, JOIN, WHERE, and other operations.
Ideal for encapsulating reusable, simple queries such as filtering or joining data.
 Modifying and Dropping:

Page 30 of 43
Microsoft SQL Server SETEC Institute

To modify an iTVF, you must drop and recreate it.

Dropped using the DROP FUNCTION statement.
 Best Practices:
Keep logic simple and avoid unnecessary complexity.
Use iTVFs for encapsulating reusable queries to improve code maintainability and clarity.

yty

Page 31 of 43
Microsoft SQL Server SETEC Institute

Chapter 8: Triggers and Views

8.1.Triggers

8.1.1. DML Triggers

DML Triggers in SQL Server are automatically executed in response to data manipulation events
(INSERT, UPDATE, DELETE) on a table or view.
 Types:
AFTER Triggers: Fired after the operation, typically used for auditing or enforcing business rules.
INSTEAD OF Triggers: Fired instead of the operation, allowing customization or blocking the
action.
 Trigger Logic:
INSERTED and DELETED are virtual tables used to access new and old values of rows during
INSERT, UPDATE, and DELETE operations.
 Examples:
AFTER Trigger: Used for auditing after an INSERT or UPDATE.
INSTEAD OF Trigger: Used to prevent duplicate data (e.g., checking for duplicate emails).
 Considerations:
Triggers can affect performance and may lead to recursion or unintended consequences.
They should be used carefully, ideally for simple logic and operations like auditing or enforcing
rules.
Dropping a Trigger: Use DROP TRIGGER to remove a trigger.
8.1.2. Instead of Triggers in SQL Server

 Definition:
INSTEAD OF triggers override the default INSERT, UPDATE, or DELETE actions and
execute custom logic defined by the user.
 Syntax:

CREATE TRIGGER TriggerName ON TableName

INSTEAD OF INSERT, UPDATE, DELETE

AS BEGIN

END;

Page 32 of 43
Microsoft SQL Server SETEC Institute

 Examples:
INSERT: Logs and then inserts data.
UPDATE: Logs and then applies updates.
DELETE: Logs a delete attempt or prevents deletion.
 Use Cases:
Views: Enable data modifications on views that map to multiple tables.
Data Validation: Implement custom validation before modifying data.
Logging: Track changes (insert, update, delete) for auditing.
Preventing Actions: Stop deletions or updates based on custom conditions.
 Limitations:
Can impact performance.
Can complicate the system if overused.
Cannot manage cascading effects of foreign keys.
Cannot access identity column values directly during insertions.
 Modifying and Dropping:
Triggers must be dropped and recreated for modifications.
Use DROP TRIGGER to remove a trigger.
8.1.3. Managing Triggers
 Creating Triggers:
Use CREATE TRIGGER to define triggers for INSERT, UPDATE, or DELETE on tables or
views.
 Modifying Triggers:
To modify a trigger, you must drop the existing one and create a new trigger with updated
logic.
 Creating Triggers:
Use the CREATE TRIGGER statement to define triggers that respond to INSERT, UPDATE, or
DELETE events.
 Example: AFTER INSERT trigger that performs actions after data is inserted.
 Modifying Triggers: Triggers cannot be modified directly. You must drop the existing trigger
using DROP TRIGGER and recreate it with the new logic.
 Disabling and Enabling Triggers: Temporarily disable triggers with DISABLE TRIGGER and
re-enable them with ENABLE TRIGGER for maintenance or bulk operations.
 Dropping Triggers: Use DROP TRIGGER to remove a trigger permanently from a table or
view.
 Viewing Trigger Information: Query the sys. triggers system catalog to get information about
existing triggers.
8.2.Views
8.2.1. Creating and Managing Views in SQL Server
 Creating Views:
A view is created using the CREATE VIEW statement, which encapsulates a SELECT query.
 Example:
CREATE VIEW ViewName AS

SELECT column1, column2 FROM TableName;

Page 33 of 43
Microsoft SQL Server SETEC Institute

 Using Views:
Views can be queried just like tables:

SELECT * FROM ViewName;

 Types of Views:
Simple Views: Based on a single table.
Complex Views: Involve multiple tables, joins, or aggregations.
Indexed Views: Have a clustered index for better performance.
 Modifying Views:
To update a view, use the ALTER VIEW statement:

ALTER VIEW ViewName AS

SELECT ... FROM ...;

 Dropping Views:
Views can be removed using the DROP VIEW statement

DROP VIEW ViewName;

 Updatable Views:
A view is updatable if it involves a single table and no aggregations or complex operations.
 Performance Considerations:
Views can add performance overhead due to their dynamic nature, especially for complex queries.
Indexed Views can improve performance but introduce maintenance overhead.
 Advantages:
Simplifies querying, improves data security, and ensures consistency.
 Disadvantages:
Performance impact for complex views and limitations on direct data modification for non-updatable
views.
8.2.2. Updatable Views
An updatable view allows direct INSERT, UPDATE, and DELETE operations on the
underlying table through the view.
Conditions for Updatable Views: Must be based on a single table.
Cannot use aggregations, DISTINCT, GROUP BY, or subqueries.
Should not include computed columns or UNION operators.
 Operations on Updatable Views:
INSERT: You can insert data through the view if it adheres to the table structure.
UPDATE: Directly updating rows in the underlying table via the view is allowed.
DELETE: Deleting rows in the base table can be done through the view.
 Limitations:
Multi-table or complex views (with joins or aggregations) are not updatable by default.
INSTEAD OF triggers can be used to make complex views updatable.
 Best Practices:
Keep views simple and based on a single table to maintain updatability.
Use triggers for complex views needing data modifications.

Page 34 of 43
Microsoft SQL Server SETEC Institute

8.2.3. Indexed Views in SQL Server

Indexed views are views that have a clustered index applied, allowing the results to be stored
physically for improved query performance. They are particularly beneficial for complex queries
involving aggregation or joins.
 Key Points:
Benefits: Faster query execution, reduced computation, and consistent data.
Restrictions: Can only reference tables in the same database, must use schema binding, and cannot
have operations like GROUP BY, DISTINCT, or outer joins.
Maintenance Overhead: Increases due to the need to update the indexed view when base table data
changes and extra disk space for storing the data.
Best Use: Ideal for read-heavy scenarios, reporting, and data warehousing.

yty

Page 35 of 43
Microsoft SQL Server SETEC Institute

Chapter 9: Security and Administration

9.1. SQL Server Security Model
9.1.1. Authentication Modes (Windows, SQL Server)
Windows Authentication:
Uses Windows credentials, secure and integrates with Windows security.
Best for Windows domain environments.
SQL Server Authentication:
Uses SQL Server-specific usernames and passwords.
Suitable for non-Windows users or cross-platform needs.
Mixed Mode:
Supports both Windows and SQL Server authentication.
Provides flexibility but requires careful security management.
9.1.2. Logins and Users
Logins:
Used to authenticate users at the SQL Server level.
Can be based on Windows Authentication or SQL Server Authentication.
A login must be created before it can access any database.
Users:
Mapped to a login within a specific database, allowing access to database objects.
Users can belong to roles (e.g., db_owner, db_datareader) to manage permissions.
Difference:
Login: Manages access to SQL Server.
User: Manages access to database objects within a specific database.
9.2. Permissions and Roles
9.2.1. Granting and Revoking Permissions in SQL Server
Granting Permissions:
Use the GRANT statement to provide a user or role access to perform specific actions on database
objects.
Revoking Permissions:
Use the REVOKE statement to remove previously granted permissions, but it doesn't restore any
denied permissions.
Denying Permissions:
Use the DENY statement to explicitly prevent a user or role from performing specific actions, even if
permissions were granted elsewhere.
9.2.2. Fixed Server Roles and Database Roles
Fixed Server Roles: These predefined roles manage permissions at the SQL Server instance level:
sysadmin: Full control over the SQL Server instance.
serveradmin: Can configure server settings.
securityadmin: Manages logins, roles, and permissions.
dbcreator: Can create, alter, and drop databases.
diskadmin: Manages database files.
processadmin: Manages SQL Server processes.
bulkadmin: Performs bulk import operations.
public: Default role with minimal permissions for all users.
Fixed Database Roles: These roles manage permissions within a specific database:
db_owner: Full control over the database.
db_accessadmin: Manages access to the database.

Page 36 of 43
Microsoft SQL Server SETEC Institute

db_securityadmin: Manages database-level security.

db_ddladmin: Executes DDL commands on database objects.
db_datareader: Can read data from all tables.
db_datawriter: Can modify data in all tables.
db_backupoperator: Can back up the database.
db_denydatareader and db_denydatawriter: Deny read or write access to data in all tables.
9.3. Backup and Recovery

9.3.1. Backup Types (Full, Differential, Transaction Log)

Full Backup: Captures the entire database, including all data and transaction logs. It’s the foundation
for other backup types.
Differential Backup: Captures only the changes since the last full backup, offering faster backups and
restores.
Transaction Log Backup: Captures all transaction log entries since the last log backup, enabling point-
in-time recovery.
9.3.2. Restoring Databases in SQL Server

Restore Options:
Complete Restore: Restore the full backup.
Restore with Differential Backup: After full restore, apply the latest differential backup.
Restore with Transaction Log Backup: Apply full, differential, and transaction log backups in sequence
for point-in-time recovery.
Recovery Models:
Simple: Only full backups are required.
Full: Full and transaction log backups are used for recovery.
Bulk-Logged: Similar to Full, but bulk operations are minimally logged.
NORECOVERY and RECOVERY:
NORECOVERY: Used when additional backups need to be applied.
RECOVERY: Marks the database as fully restored and available.

Page 37 of 43
Microsoft SQL Server SETEC Institute

9.3.3. Disaster Recovery Planning in SQL Server

Disaster recovery planning involves preparing for

database recovery in case of failure. Key elements
include:
Risk Assessment: Identifying potential risks and
defining Recovery Time Objective (RTO) and
Recovery Point Objective (RPO).
Backup Strategy: Implementing regular full,
differential, and transaction log backups, with offsite
or cloud storage.
High Availability: Using solutions like Database
Mirroring, Always On Availability Groups, and
Clustering for redundancy.
Testing: Regularly testing disaster recovery procedures and updating them based on the tests.
Documentation & Communication: Ensuring procedures are documented and roles are clearly defined.
Cloud Solutions: Leveraging cloud backup and services like Azure Site Recovery for scalable disaster
recovery.

yty

Page 38 of 43
Microsoft SQL Server SETEC Institute

Chapter 10: Advanced Topics

10.1. SQL Server Integration Services (SSIS)

10.1.1. Overview of SSIS

QL Server Integration Services (SSIS) is a comprehensive data integration and ETL (Extract,
Transform, Load) tool within Microsoft SQL Server, designed for building data workflows to manage
and automate data processing tasks.
ETL Processes: Extracts data from multiple sources, applies transformations, and loads it into target
systems like SQL Server or cloud services.
Control Flow & Data Flow: Manages the execution of tasks and data movement through
transformations.
Error Handling & Logging: Provides error handling and logging for monitoring and troubleshooting.
Performance Optimization: Supports parallel processing and buffering for handling large datasets.
Data Quality Services (DQS): Integrates with DQS for data profiling and cleansing.
Scalability & Extensibility: Scalable and supports custom scripting for complex tasks.
SSIS Packages: Deployable units of work for automation and scheduling of ETL tasks.
10.1.2. Creating and Managing SSIS Packages
Creating SSIS Packages:

SSIS Designer: Use the SQL Server Data Tools (SSDT) to design SSIS packages with a drag-and-drop
interface.
Control Flow: Define tasks (e.g., Execute SQL Task, Data Flow Task) that determine the order of
operations.
Data Flow: Set up sources, transformations, and destinations to move and modify data.
Variables and Expressions: Use variables for dynamic values and expressions for conditional logic
within tasks.
Error Handling: Set up event handlers and logging to capture errors during package execution.
Managing SSIS Packages:
Deploying Packages: Packages can be deployed to SSISDB, a system database on SQL Server, or to a
file system.
Scheduling: Use SQL Server Agent to schedule SSIS packages for automated execution.

Page 39 of 43
Microsoft SQL Server SETEC Institute

Monitoring and Logging: Track package performance and errors using built-in logging, SQL Server
Profiler, and SSISDB reports.
Version Control: Implement version control to manage updates and revisions to SSIS packages.
Executing Packages: Packages can be executed manually via SSMS, from the SQL Server Agent, or
through command-line utilities.
10.2.SQL Server Reporting Services (SSRS)

 Creating Reports

SSRS is a powerful tool for designing, deploying,

and managing reports in SQL Server. Reports can
be created in various formats, such as tabular,
matrix, chart, and parameterized reports.

Key Steps to Create Reports:

Set Up the Report Project: Install SQL Server Data
Tools (SSDT), define data sources, and create a
new report project.
Design Report Layout: Use the graphical interface to add tables, charts, and text boxes.
Define Parameters: Allow users to customize the report by adding filter options.
Add Expressions and Formatting: Implement dynamic content and control report appearance.
Preview the Report: Ensure that the report displays data correctly.
Deployment and Management:
Deploy the report to an SSRS server and organize reports in a folder structure.
Schedule reports using SQL Server Agent and create subscriptions for automatic delivery.
Manage report security and permissions to control access.
10.2.1. Deploying and Managing Reports
Deploying and managing reports in SQL Server Reporting Services (SSRS) involves the
following key steps:
 Deploying Reports:
Use SQL Server Data Tools (SSDT) to deploy reports directly to the SSRS server.
Alternatively, upload reports manually through the SSRS Web Portal.
 Managing Reports:
Data Sources: Configure shared or embedded data sources and manage authentication.

Page 40 of 43
Microsoft SQL Server SETEC Institute

Report Parameters: Configure parameters for user-driven filtering.

Permissions and Security: Assign role-based security to control access to reports and data sources.
Subscriptions: Set up scheduled or data-driven subscriptions to automate report delivery.
Caching and Snapshots: Improve report performance through caching and save static report data with
snapshots.
 Monitoring and Troubleshooting:
Use usage reports to track report performance and execution.
Troubleshoot using execution logs and optimize performance through efficient queries and caching.
10.3.SQL Server Analysis Services (SSAS)

10.3.1. Overview of SSAS

SQL Server Analysis Services (SSAS) is a powerful tool for data analysis and business
intelligence. It enables users to create and manage multidimensional (OLAP) models and tabular
models for efficient querying, reporting, and data mining. Key features include:
OLAP: Organizes data into cubes, making it fast to analyze large datasets.
Tabular Models: Uses relational data structures and in-memory technology for faster querying.
Data Mining: Uncovers patterns and predictions using algorithms like clustering and classification.
Querying: Utilizes MDX for OLAP queries and DAX for tabular model calculations.
Scalability and Performance: Supports large data volumes through partitioning and caching.
10.3.2. Creating and Managing OLAP Cubes
OLAP cubes in SQL Server, managed through SQL Server Analysis Services (SSAS), are designed for
multidimensional analysis, helping users analyze large datasets efficiently.
 Creating OLAP Cubes:
Set Up SSAS: Install and configure SSAS for OLAP cube functionality.
Define Data Source and View: Set up data sources and define the Data Source View (DSV) with
necessary tables and relationships.
Create Dimensions: Define dimensions such as time, product, and geography for analysis.
Define Measures: Specify numeric measures (e.g., sales) to be aggregated in the cube.
Cube Creation: Combine dimensions and measures to build the cube.
Process the Cube: Load data into the cube, either fully or incrementally.
 Managing OLAP Cubes:
Querying: Use MDX to query and analyze data from the cube.
Performance Tuning: Optimize the cube using aggregations, partitions, and indexes for faster query
responses.
Security: Define user roles and permissions for controlling access to the cube.
Data Refresh: Use scheduled processing to keep the cube's data up-to-date.

Page 41 of 43
Microsoft SQL Server SETEC Institute

 Storage and Deployment:

Manage cube storage and deploy cubes to the SSAS server for access via tools like Excel, Power BI, or
SSRS.

10.4. High Availability and Replication

10.4.1. Always On Availability Groups

Always On Availability Groups (AGs) is a high-availability and disaster recovery feature in
SQL Server, providing automatic failover and data redundancy.
 Key Features:
Multiple Replicas: One primary replica for write operations and multiple secondary replicas for
redundancy.
Synchronous and Asynchronous Commit: Syncs data between replicas either synchronously (for zero
data loss) or asynchronously (for higher performance with risk of data loss).
Automatic Failover: If the primary replica fails, a secondary replica can be promoted automatically
(with synchronous commit).
Read-Only Routing: Directs read-only queries to secondary replicas, offloading the primary replica.
No Shared Storage: Unlike Failover Cluster Instances, AGs do not require shared storage, each replica
uses its own storage.
 Architecture:
Primary Replica: Hosts write operations.
Secondary Replicas: Replicas of the primary database, which can be read-only or readable.
AG Listener: A virtual endpoint for connecting clients to the active replica.
 Benefits:
High availability, automatic failover, scalable read workloads, and minimal downtime.
Improved disaster recovery and business continuity.
10.4.2. Database Mirroring in SQL Server

Database Mirroring is a high-availability feature that

creates a real-time copy (mirror) of a database on a standby
server to provide failover protection. It involves three main
components: the Principal Server (primary database),
Mirror Server (standby database), and optionally a Witness
Server to enable automatic failover.

Page 42 of 43
Microsoft SQL Server SETEC Institute

 Key Features:
Synchronous and Asynchronous Mirroring: Synchronous mode ensures data consistency between the
principal and mirror, while asynchronous mode reduces latency at the cost of potential data loss during
a failure.
Automatic Failover: Automatic failover can occur when a Witness server is used, promoting the mirror
to the principal role in case of a failure.
Manual Failover: Administrators can initiate failover manually for maintenance or planned downtime.
10.4.3. Replication Types in SQL Server
 Snapshot Replication:
Overview: Sends a complete copy of the data at specified intervals.
Best for: Static data or infrequent updates.
Pros: Simple setup, ensures data consistency.
Cons: Inefficient for large or frequently changing data.
 Transactional Replication:
Overview: Replicates data changes (inserts, updates, deletes) in real-time.
Best for: Real-time updates and high-volume transactional systems.
Pros: Efficient for frequent updates, near real-time replication.
Cons: Requires more resources, more complex setup.
 Merge Replication:
Overview: Allows changes at both the publisher and subscribers, with conflict resolution.
Best for: Environments where both ends modify data (bi-directional).
Pros: Flexible, supports offline data updates.
Cons: Complex setup, potential overhead for conflict resolution.

yty

Thank You!

3
Page 43 of 43