FTP tasks in SSIS (SQL Server Integration Services) are an important feature that is used to perform file operations across the server and client/local machine. FTP task in SSIS is used to automate the process of uploading and downloading files to/from the FTP server. This task ensures the secure transfer of files, automates data workflow, and improves its efficiency. FTP Task, when combined with other SSIS tasks, can integrate data from multiple sources, such as File System Task, Script Task, Execute SQL Task, etc.

Now, let’s see how to create an FTP Task.

FTP is basically a File Transfer Protocol; it’s basically transferring files from one server to another server or to a local machine.

In the SSIS package within the control flow, we can see the SSIS Toolbox, and there are numerous options. From there we can select FTP Task by drag and drop it on package.

Below is the screenshot, which shows what it looks like.

In the above screenshot, we can see a red sign popped up; it means the connection is empty.

Note: I have used a personal FTP server and hence you may not be able to access it, you can however check a few public FTP servers available online.

Now, let’s configure the FTP Task and establish a connection. We will go on the FTP task editor and do the following.

Add FTP Server to establish FTP connection: Enter the below credential to establish an FTP connection. (FTP server path: ftp://Peter:[email protected].x.x)
Server Name: 192.168.x.x
User ID: Peter
PWD: ****

The connection has been established.

Now, enter the following details in file transfer and hit ok; the red sign will go away.

• Add Operation as Send files
• Add local path: C:\Users\Peter\Documents\FTP_Local
• Add RemotePath: /

After hitting ok, you will see on the screen below that the red sign is gone. This means that both connections are established, and we can see this in Connection Manager.

The screenshot below shows that it was taken before executing the package, and you can see no files in the FTP Server are available currently.

Once step 2 is done, Execute the FTP Task by hitting the start button. The screenshot below shows packages executed successfully. Here, our objective is to see if the file moved from the source (local path) to the destination (FTP Server).

Now, I will check into the FTP server to see if the file moved to the destination or FTP Server. As you can see in the screenshot below, the file has indeed been moved to the FTP server.

Summary
There are several benefits of creating FTP Tasks in SSIS, for example.

• Automates the data transfer and reduces the time-consuming manual efforts.
• It helps integrate data from various sources by creating a workflow that automatically handles file transfers.
• Enables creation of FTP tasks for regular data backups and enhances data transfer security.

HostForLIFEASP.NET SQL Server 2022 Hosting

Performance is frequently the main issue in any SQL Server system when working with big datasets. The INSERT operation is one frequent operation that occasionally turns into a bottleneck. The time required to input data increases with its size, which can have a major effect on system performance and user experience in general. The usage of the TABLOCK hint is one of the many methods and improvements that SQL Server offers to help speed up data insertions. When working with huge datasets or when parallelism is crucial, this straightforward yet effective method can significantly increase the pace of your INSERT operations.

Let’s explore how using the TABLOCK hint works, its benefits, and how you can apply it to your own operations, with examples from the HFL database.

What is the TABLOCK Hint?
The TABLOCK hint is a table-level lock hint that forces SQL Server to take a schema modification (Sch-M) lock on the target table when performing an INSERT, UPDATE, or DELETE operation. This hint ensures that the table is locked for the duration of the operation, which can help speed up data loading by minimizing logging and reducing contention.

A key benefit of the TABLOCK hint is that it reduces the amount of log space used during the operation, as the minimal logging mechanism is activated. This means that SQL Server does not have to log each individual row insertion, but rather just the metadata for the bulk operation. As a result, this significantly reduces the overhead and speeds up data loading.

Additionally, because the table is locked at the schema level, it allows SQL Server to parallelize the operation, leading to faster execution times. This is particularly useful for large-scale data-loading tasks.

When to Use TABLOCK Hint
The TABLOCK hint is ideal for scenarios where:

• You are inserting a large number of rows into a table.
• You can afford to lock the table for the duration of the operation (i.e., no other transactions need access to the table while the insert is in progress).
• You want to reduce the logging overhead and speed up bulk insertions.
• You want to use parallel insertions to take advantage of SQL Server's ability to use multiple threads for data loading.

It’s also important to note that the TABLOCK hint works well with temporary tables, so you can take advantage of these performance benefits when working with temp tables, often used in ETL processes or batch operations.

Benefits of Using TABLOCK

• Improved Performance: The primary benefit of using the TABLOCK hint is the performance improvement during large INSERT operations. By reducing the amount of logging, SQL Server can insert rows much faster.
• Parallel Insertion: With TABLOCK, SQL Server can use parallelism to load the data, speeding up the operation of systems with sufficient resources.
• Reduced Logging Overhead: Since SQL Server logs less information, the system consumes less log space, which can be crucial when working with large datasets.
• Works with Temp Tables: You can apply TABLOCK to temporary tables as well, giving you the same performance benefits for in-memory operations.

Example
Let’s consider a scenario where we need to insert a large number of rows from the Sales.SalesOrderDetail table into the Sales.SalesOrderDetailTemp table in the HFL database.
Create table script for Sales.SalesOrderDetailTem
USE [HFL]
GO

DROP TABLE IF EXISTS [Sales].[SalesOrderDetailTemp]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [Sales].[SalesOrderDetailTemp](
    [SalesOrderID] [int] NOT NULL,
    [SalesOrderDetailID] [int] NOT NULL,
    [CarrierTrackingNumber] [nvarchar](25) NULL,
    [OrderQty] [smallint] NOT NULL,
    [ProductID] [int] NOT NULL,
    [SpecialOfferID] [int] NOT NULL,
    [UnitPrice] [money] NOT NULL,
    [UnitPriceDiscount] [money] NOT NULL,
    [LineTotal]  [money] NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SalesOrderDetailTemp_SalesOrderID_SalesOrderDetailTempID] PRIMARY KEY CLUSTERED
(
    [SalesOrderID] ASC,
    [SalesOrderDetailID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Without the TABLOCK hint, this operation may take a considerable amount of time, especially when the table is large and the database is under load.

Here’s a basic example of how you can speed up the INSERT operation by using the TABLOCK hint.
USE HFL
GO

SET STATISTICS TIME, IO ON

SET NOCOUNT ON

INSERT INTO Sales.SalesOrderDetailTemp
SELECT *
FROM Sales.SalesOrderDetail;

Truncate the table.
USE HFL
GO

TRUNCATE TABLE Sales.SalesOrderDetailTemp

Now, let’s modify the query to use the TABLOCK hint.
USE HFL
GO

SET STATISTICS TIME, IO ON

SET NOCOUNT ON

INSERT INTO Sales.SalesOrderDetailTemp
WITH (TABLOCK)
SELECT *
FROM Sales.SalesOrderDetail;

Comparison
Execution 1 (without TABLOCK) took longer, with higher CPU and elapsed time (204 ms and 284 ms), indicating a slower operation. Execution 2 (with TABLOCK) performed better, completing in 125 ms CPU time and 157 ms elapsed time, making the TABLOCK version more efficient in this case.
Considerations When Using TABLOCK

While the TABLOCK hint can greatly improve performance, it’s important to be aware of some considerations:

• Table Locking: The TABLOCK hint locks the entire table for the duration of the operation. This means that other transactions cannot access the table until the INSERT operation is complete, so be sure that this behavior aligns with your application’s requirements.
• Transaction Log Growth: Although TABLOCK reduces the amount of logging, it still logs certain details of the operation. If you’re inserting a massive amount of data, you may need to monitor transaction log growth and ensure that you have enough log space available.
• Not Suitable for OLTP Workloads: The TABLOCK hint is more suited to batch operations or bulk-loading scenarios. It may not be appropriate for transactional systems that require frequent concurrent access to the table.

Conclusion
If you are working with large datasets and want to speed up your INSERT operations in SQL Server, the TABLOCK hint can be a game-changer. By reducing logging overhead and enabling parallel insertions, it helps improve performance and can significantly reduce the time it takes to load data.

HostForLIFEASP.NET SQL Server 2022 Hosting

Triggers in SQL Server are special types of stored procedures that automatically execute or “fire” when certain events occur in the database.

Types of Triggers
DML Triggers
These are triggers that fire in response to data manipulation events such as INSERT, UPDATE, or DELETE operations on a table or view.

• AFTER Triggers: Execute after the associated DML operation is completed.
• INSTEAD OF Triggers: Execute in place of the associated DML operation.

DDL Triggers
These fire in response to data definition language (DDL) events such as CREATE, ALTER, or DROP statements.

Real-Time Use Case Example
Scenario. Imagine a retail company that wants to maintain an audit trail for all changes made to the Orders table. Every time an order is inserted, updated, or deleted, the company wants to log the details of the operation in an OrderAudit table.

Setting Up the Example
First, let’s create the Orders table and the OrderAudit table.
CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATETIME,
    Amount DECIMAL(10, 2)
);

CREATE TABLE OrderAudit (
    AuditID INT IDENTITY(1,1) PRIMARY KEY,
    OrderID INT,
    Operation VARCHAR(10),
    OldCustomerID INT,
    NewCustomerID INT,
    OldOrderDate DATETIME,
    NewOrderDate DATETIME,
    OldAmount DECIMAL(10, 2),
    NewAmount DECIMAL(10, 2),
    ChangeDate DATETIME DEFAULT GETDATE()
);

Creating Triggers
AFTER INSERT Trigger
CREATE TRIGGER trg_AfterInsert_Orders
ON Orders
AFTER INSERT
AS
BEGIN
    INSERT INTO OrderAudit (OrderID, Operation, NewCustomerID, NewOrderDate, NewAmount)
    SELECT OrderID, 'INSERT', CustomerID, OrderDate, Amount
    FROM inserted;
END;

-- Insert a new order
INSERT INTO Orders (OrderID, CustomerID, OrderDate, Amount)
VALUES (1, 101, '2024-01-01', 100.00);

-- Check audit logs
select * from  OrderAudit

Result
We can see we have a record inserted into the audit log table, and when any record is inserted into the order table, we will know what operation was performed and at what time it was executed.

AFTER INSERT Trigger: When a new order is inserted into the Orders table, the trg_AfterInsert_Orders trigger fires. It captures the new order details from the inserted pseudo-table and inserts a corresponding record into the OrderAudit table with the operation type 'INSERT'.

2. AFTER UPDATE Trigger
CREATE TRIGGER trg_AfterUpdate_Orders
ON Orders
AFTER UPDATE
AS
BEGIN
    INSERT INTO OrderAudit (OrderID, Operation, OldCustomerID, NewCustomerID, OldOrderDate, NewOrderDate, OldAmount, NewAmount)
    SELECT
        i.OrderID,
        'UPDATE',
        d.CustomerID,
        i.CustomerID,
        d.OrderDate,
        i.OrderDate,
        d.Amount,
        i.Amount
    FROM inserted i
    INNER JOIN deleted d ON i.OrderID = d.OrderID;
END;

-- Update the order
UPDATE Orders
SET Amount = 120.00
WHERE OrderID = 1;

-- Audit log
SELECT * FROM OrderAudit;

Result
we can see at this time, we updated the record, and we can see details of updated records, this is very helpful in case when we want to track how many time a order has been updated etc.

AFTER UPDATE Trigger: When an existing order is updated, the trg_AfterUpdate_Orders trigger fires. It captures both the old and new values by joining the inserted and deleted pseudo-tables and inserts a record into the OrderAudit table with the operation type 'UPDATE'.

3. AFTER DELETE Trigger
CREATE TRIGGER trg_AfterDelete_Orders
ON Orders
AFTER DELETE
AS
BEGIN
    INSERT INTO OrderAudit (OrderID, Operation, OldCustomerID, OldOrderDate, OldAmount)
    SELECT
        OrderID,
        'DELETE',
        CustomerID,
        OrderDate,
        Amount
    FROM deleted;
END;

-- Delete the order
DELETE FROM Orders
WHERE OrderID = 1;

-- Reading audit table
SELECT * FROM OrderAudit;

Result. We can see that order data has been deleted, and we can check logs to see what time it was deleted.

AFTER DELETE Trigger: When an order is deleted, the trg_AfterDelete_Orders trigger fires. It captures the details of the deleted order from the deleted pseudo-table and inserts a corresponding record into the OrderAudit table with the operation type 'DELETE'.

Dropping a Trigger
Just use DROP triggername.

Instead of Trigger
Let’s consider a table of Employees where we want to restrict the insertion of records such that the Salary must be above a certain threshold (e.g., $30,000). If the Salary is below this threshold, the insertion should be rejected.

We can use it instead of Trigger in this case.
CREATE TABLE Employees (
    EmployeeID INT IDENTITY(1,1) PRIMARY KEY,
    Name VARCHAR(100),
    Position VARCHAR(50),
    Salary DECIMAL(10, 2)
);


CREATE TRIGGER trg_InsteadOfInsert_Employees
ON Employees
INSTEAD OF INSERT
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted WHERE Salary < 30000)
    BEGIN
        -- Raise an error and roll back the transaction if the salary is below the threshold
        RAISERROR ('Salary must be at least $30,000', 16, 1);
        ROLLBACK TRANSACTION;
    END
    ELSE
    BEGIN
        -- Perform the actual insert if the condition is met
        INSERT INTO Employees (Name, Position, Salary)
        SELECT Name, Position, Salary
        FROM inserted;
    END
END;


--

--less than 90000
-- This insertion should  fail
INSERT INTO Employees (Name, Position, Salary)
VALUES ('peter scott', 'Developer', 89999);

Explanation

• The trigger intercepts the insert operation and checks if any of the inserted rows have a Salary below $90,000.
• If any rows do not meet the condition, an error is raised, and the transaction is rolled back, preventing the insertion.
• If all rows meet the condition, the insertion proceeds by inserting the rows into the Employees table.

Thanks. We have learned triggers.

HostForLIFEASP.NET SQL Server 2022 Hosting

We will learn what CTE is, how to construct it in SQL Server, and its applications in this article.

CTE: What is it?
"Common Table Expressions" is what CTE stands for. SQL servers utilize it to specify a temporary name result. The common table expression (CTE) is a temporary named result set that can be referenced within a Select, Insert, Update, or Delete operation. It was first introduced in SQL Server 2005. Another crucial point is that, like any other table, CTE results can always be accessed within the query even when they are not saved anywhere.

Why do we need CTE on the SQL server?
CTE is a powerful SQL feature that helps simplify queries. CTEs act as virtual tables (with records and columns) that are created during query execution, used by the query, and deleted after the query executes.

Syntax
[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
    expression_name [ ( column_name [ ,...n ] ) ]
    AS
    ( CTE_query_definition )

Let’s try to understand CTE Syntax.

Arguments
There are three arguments in the above syntax.

• expression_name.
• column_name.
• query_derfination.

Let’s understand one by one.

• expression_name: It should be a valid identifier for a common table expression. The expression_name must be different from the names of other common table expressions defined in the same WITH clause.
• column_name: Specifies a column name in the common table expression. Duplicate names are not allowed within a single CTE definition. There are two rules for column names. The first is the number of column names specified, which must match the number of columns in the result set of the query_definition, and the second one is The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.
• query_defination: Specifies a SELECT statement whose result set satisfies a common table expression.

Some Examples
Before starting to explain some CTE examples, we need to create a database and some tables under the database, so run the following script in your SQL server.
USE [master]
GO

/****** Object:  Database [db_MyPractice]    Script Date: 06-10-2024 01:18:01 ******/
CREATE DATABASE [db_MyPractice]
    CONTAINMENT = NONE
    ON PRIMARY
    (
        NAME = N'db_MyPractice',
        FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\db_MyPractice.mdf',
        SIZE = 8192KB,
        MAXSIZE = UNLIMITED,
        FILEGROWTH = 65536KB
    )
    LOG ON
    (
        NAME = N'db_MyPractice_log',
        FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL15.SQLEXPRESS\MSSQL\DATA\db_MyPractice_log.ldf',
        SIZE = 8192KB,
        MAXSIZE = 2048GB,
        FILEGROWTH = 65536KB
    )
    WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO

ALTER DATABASE [db_MyPractice] SET COMPATIBILITY_LEVEL = 150
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
BEGIN
    EXEC [db_MyPractice].[dbo].[sp_fulltext_database] @action = 'enable'
END
GO

ALTER DATABASE [db_MyPractice] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [db_MyPractice] SET ANSI_NULLS OFF
GO
ALTER DATABASE [db_MyPractice] SET ANSI_PADDING OFF
GO
ALTER DATABASE [db_MyPractice] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [db_MyPractice] SET ARITHABORT OFF
GO
ALTER DATABASE [db_MyPractice] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [db_MyPractice] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [db_MyPractice] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [db_MyPractice] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [db_MyPractice] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [db_MyPractice] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [db_MyPractice] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [db_MyPractice] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [db_MyPractice] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [db_MyPractice] SET DISABLE_BROKER
GO
ALTER DATABASE [db_MyPractice] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [db_MyPractice] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [db_MyPractice] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [db_MyPractice] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [db_MyPractice] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [db_MyPractice] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [db_MyPractice] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [db_MyPractice] SET RECOVERY SIMPLE
GO
ALTER DATABASE [db_MyPractice] SET MULTI_USER
GO
ALTER DATABASE [db_MyPractice] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [db_MyPractice] SET DB_CHAINING OFF
GO
ALTER DATABASE [db_MyPractice] SET FILESTREAM (NON_TRANSACTED_ACCESS = OFF)
GO
ALTER DATABASE [db_MyPractice] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [db_MyPractice] SET DELAYED_DURABILITY = DISABLED
GO
ALTER DATABASE [db_MyPractice] SET ACCELERATED_DATABASE_RECOVERY = OFF
GO
ALTER DATABASE [db_MyPractice] SET QUERY_STORE = OFF
GO

USE [db_MyPractice]
GO

/****** Object:  Table [dbo].[Employees]    Script Date: 06-10-2024 01:18:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Employees] (
    [EmployeeID] [smallint] NOT NULL,
      NOT NULL,
      NOT NULL,
      NOT NULL,
    [DeptID] [smallint] NOT NULL,
    [ManagerID] [smallint] NULL,
    CONSTRAINT [PK_EmployeeID] PRIMARY KEY CLUSTERED
    (
        [EmployeeID] ASC
    ) WITH (
        PAD_INDEX = OFF,
        STATISTICS_NORECOMPUTE = OFF,
        IGNORE_DUP_KEY = OFF,
        ALLOW_ROW_LOCKS = ON,
        ALLOW_PAGE_LOCKS = ON,
        OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF
    ) ON [PRIMARY]
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tbl_book]    Script Date: 06-10-2024 01:18:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_book] (
    [BookId] [bigint] IDENTITY(1,1) NOT NULL,
      NOT NULL,
      NOT NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[Tbl_Bookissued]    Script Date: 06-10-2024 01:18:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Tbl_Bookissued] (
    [transaction_id] [bigint] IDENTITY(1,1) NOT NULL,
    [StudentID] [bigint] NULL,
    [TeacherID] [bigint] NULL,
    [BookID] [bigint] NOT NULL,
    [issueDate] [date] NOT NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tbl_student]    Script Date: 06-10-2024 01:18:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_student] (
    [StudentID] [bigint] IDENTITY(1,1) NOT NULL,
      NOT NULL
) ON [PRIMARY]
GO

/****** Object:  Table [dbo].[tbl_teacher]    Script Date: 06-10-2024 01:18:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_teacher] (
    [TeacherID] [bigint] IDENTITY(1,1) NOT NULL,
      NOT NULL
) ON [PRIMARY]
GO

-- Insert statements
INSERT [dbo].[Employees] ([EmployeeID], [FirstName], [LastName], [Title], [DeptID], [ManagerID]) VALUES
(1, N'Ken', N'Sánchez', N'Chief Executive Officer', 16, NULL),
(16, N'David', N'Bradley', N'Marketing Manager', 4, 273),
(23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16),
(273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1),
(274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273),
(275, N'Michael', N'Blythe', N'Sales Representative', 3, 274),
(276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274),
(285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273),
(286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285)
GO

SET IDENTITY_INSERT [dbo].[tbl_book] ON
GO
INSERT [dbo].[tbl_book] ([BookId], [bookName], [className]) VALUES
(1, N'Maths', N'Class-1'),
(2, N'Maths', N'Class-2'),
(3, N'Maths', N'Class-3'),
(4, N'Maths', N'Class-4'),
(5, N'Maths', N'Class-5'),
(6, N'Maths', N'Class-6'),
(7, N'Hindi', N'Class-1'),
(8, N'Hindi', N'Class-2'),
(9, N'Hindi', N'Class-3'),
(10, N'Hindi', N'Class-4'),
(11, N'Hindi', N'Class-5'),
(12, N'Hindi', N'Class-6'),
(13, N'English', N'Class-1'),
(14, N'English', N'Class-2'),
(15, N'English', N'Class-3'),
(16, N'English', N'Class-4'),
(17, N'English', N'Class-5'),
(18, N'English', N'Class-6')
GO
SET IDENTITY_INSERT [dbo].[tbl_book] OFF

After running this script, you will have the following table. Following tables with data.

A. Create a common table expression: The following example shows the total number of books issued per year for each student during their all-academic years.
-- Define the CTE expression name and column list.
WITH book_Issue_CTE (StudentID, StudentName, BookID, AcademicYear)
AS
(
    -- Define the CTE query.
    SELECT
        BI.StudentID,
        StudentName,
        BookID,
        YEAR(issueDate) AS AcademicYear
    FROM
        Tbl_Bookissued BI
    JOIN
        tbl_student s ON BI.StudentID = s.StudentID
    WHERE
        BI.StudentID IS NOT NULL
)
-- Define the outer query referencing the CTE name.
SELECT
    StudentID,
    StudentName,
    COUNT(BookID) AS TotalBookIssued,
    AcademicYear
FROM
    book_Issue_CTE
GROUP BY
    AcademicYear, StudentID, StudentName
ORDER BY
    StudentID, AcademicYear;

B. Use a common table expression to limit counts and report averages: The following example shows the average number of books issued for all years for the students.
WITH Avg_book_Issue_CTE (StudentID, NumberOfBooks) AS
(
    SELECT StudentID, COUNT(*)
    FROM Tbl_Bookissued
    WHERE StudentID IS NOT NULL
    GROUP BY StudentID
)
SELECT AVG(NumberOfBooks) AS "Average book issued Per Student"
FROM Avg_book_Issue_CTE;

C. Use multiple CTE definitions in a single query: The following example shows how to define more than one CTE in a single query. Notice that a comma is used to separate the CTE query definitions.

In the example below, we are fetching records of books issued to teachers and students.
WITH book_issued_student_CTE (StudentID, BookIssued, IssueYear) AS
-- Define the first CTE query.
(
    SELECT StudentID, COUNT(BookID) AS BookIssued, YEAR(issueDate) AS IssueYear
    FROM Tbl_Bookissued
    WHERE StudentID IS NOT NULL
    GROUP BY StudentID, YEAR(issueDate)
),

-- Define the second CTE query, which returns sales quota data by year for each sales person.
book_issued_Teacher_CTE (TeacherID, BookIssued, IssueYear) AS
(
    SELECT TeacherID, COUNT(BookID) AS BookIssued, YEAR(issueDate) AS IssueYear
    FROM Tbl_Bookissued
    WHERE TeacherID IS NOT NULL
    GROUP BY TeacherID, YEAR(issueDate)
)

-- Define the outer query by referencing columns from both CTEs.
SELECT CTE.StudentID,
       StudentName,
       NULL AS TeacherID,
       '' AS TeacherName,
       BookIssued,
       IssueYear
FROM book_issued_student_CTE CTE
JOIN tbl_student s ON CTE.StudentID = s.StudentID

UNION ALL

SELECT '',
       '',
       CTE.TeacherID,
       TeacherName,
       BookIssued,
       IssueYear
FROM book_issued_Teacher_CTE CTE
JOIN tbl_teacher t ON CTE.TeacherID = t.TeacherID

ORDER BY CTE.StudentID, TeacherID;

D. Use a recursive common table expression to display multiple levels of recursion: The following example shows managers and the employees reporting to them. The number of levels returned is limited to two.

Create one more table to proceed with this example.
-- Create an Employee table.
CREATE TABLE dbo.Employees
(
    EmployeeID SMALLINT NOT NULL,
    FirstName NVARCHAR(30) NOT NULL,
    LastName NVARCHAR(40) NOT NULL,
    Title NVARCHAR(50) NOT NULL,
    DeptID SMALLINT NOT NULL,
    ManagerID SMALLINT NULL,

    CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC),
    CONSTRAINT FK_Employees_ManagerID_EmployeeID
        FOREIGN KEY (ManagerID) REFERENCES dbo.Employees (EmployeeID)
);
-- Populate the table with values.
INSERT INTO dbo.Employees
VALUES
    (1, N'Ken', N'Sánchez', N'Chief Executive Officer', 16, NULL),
    (273, N'Brian', N'Welcker', N'Vice President of Sales', 3, 1),
    (274, N'Stephen', N'Jiang', N'North American Sales Manager', 3, 273),
    (275, N'Michael', N'Blythe', N'Sales Representative', 3, 274),
    (276, N'Linda', N'Mitchell', N'Sales Representative', 3, 274),
    (285, N'Syed', N'Abbas', N'Pacific Sales Manager', 3, 273),
    (286, N'Lynn', N'Tsoflias', N'Sales Representative', 3, 285),
    (16, N'David', N'Bradley', N'Marketing Manager', 4, 273),
    (23, N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);

After running this script we have the following data.

The following example shows the hierarchical list of managers and the employees who report to them.
WITH DirectReports(ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
    SELECT
        ManagerID,
        EmployeeID,
        Title,
        0 AS EmployeeLevel
    FROM dbo.Employees
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT
        e.ManagerID,
        e.EmployeeID,
        e.Title,
        EmployeeLevel + 1
    FROM dbo.Employees AS e
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)

SELECT
    ManagerID,
    EmployeeID,
    Title,
    EmployeeLevel
FROM DirectReports
ORDER BY ManagerID;

The output of this CTE will be.

E. Use a recursive common table expression to display two levels of recursion: The following example shows managers and the employees reporting to them. The number of levels returned is limited to two.
WITH DirectReports (ManagerID, EmployeeID, Title, EmployeeLevel) AS
(
    SELECT ManagerID, EmployeeID, Title, 0 AS EmployeeLevel
    FROM dbo.Employees
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, e.Title, EmployeeLevel + 1
    FROM dbo.Employees AS e
    INNER JOIN DirectReports AS d
        ON e.ManagerID = d.EmployeeID
)
SELECT ManagerID, EmployeeID, Title, EmployeeLevel
FROM DirectReports
WHERE EmployeeLevel <= 2;

The result of this CTE will be.

HostForLIFEASP.NET SQL Server 2022 Hosting

In order to guarantee the accuracy, consistency, and dependability of the data used for analysis, reporting, or machine learning, data cleansing is an essential stage in the data preparation process. Inaccurate judgments, faulty models, and ineffective decision-making can result from low-quality data.

1. Removing Duplicates
Duplicates in datasets can skew results, inflate counts, or cause redundancy. SQL Server provides ways to remove duplicates using DISTINCT or GROUP BY.
Example. Identify and remove duplicate rows in the Person.EmailAddress table.

If you want to remove duplicates permanently, you can use CTE (Common Table Expressions) with ROW_NUMBER().
USE Hostforlife;
GO

SELECT DISTINCT
    EmailAddressID,
    EmailAddress
FROM
    Person.EmailAddress;

WITH CTE AS (
    SELECT
        EmailAddressID,
        EmailAddress,
        ROW_NUMBER() OVER (PARTITION BY EmailAddress ORDER BY EmailAddressID) AS RowNum
    FROM
        Person.EmailAddress
)
DELETE FROM CTE
WHERE RowNum > 1;

2. Handling Missing Values
Missing values can impact analysis and decision-making. Use SQL functions like IS NULL, COALESCE, or CASE to identify and handle missing data. Example. Replace missing PhoneNumber values in the Person.Use the personPhone table with a default value or remove rows with missing values.
USE Hostforlife;
GO

SELECT
    BusinessEntityID,
    PhoneNumber,
    COALESCE(PhoneNumber, 'Unknown') AS CleanedPhoneNumber
FROM
    Person.PersonPhone;

DELETE FROM
    Person.PersonPhone
WHERE
    PhoneNumber IS NULL;

3. Correcting Data Inconsistencies
Inconsistencies like variations in case or formatting can lead to errors in joins or grouping. SQL Server string functions like UPPER, LOWER, or REPLACE can help standardize data.

Example. Standardize FirstName values in the Person.Person table to uppercase. Replace incorrect substrings in email addresses.
USE Hostforlife;
GO

UPDATE Person.Person
SET FirstName = UPPER(FirstName);

UPDATE Person.EmailAddress
SET EmailAddress = REPLACE(EmailAddress, '@Hostforlife.eu', '@Hostforlife.eu');

4. Standardizing Data Formats
Standardized data formats ensure consistency and compatibility across systems. Functions like CAST and CONVERT are often used for this purpose. Example. Convert ModifiedDate in the Sales.SalesOrderHeader table to a specific format. Here, the date format 101 converts the date to MM/DD/YYYY.

USE Hostforlife
GO

SELECT SalesOrderID,
       CONVERT(VARCHAR(10), ModifiedDate, 101) AS FormattedDate
FROM Sales.SalesOrderHeader;

5. Removing Outliers
Outliers can distort statistical analyses and trends. Use statistical functions and filtering to identify and exclude them.

Example. Remove outliers based on TotalDue in the Sales.SalesOrderHeader table.
USE Hostforlife
GO

SELECT *
FROM Sales.SalesOrderHeader
WHERE TotalDue BETWEEN
      (SELECT PERCENTILE_CONT(0.05) WITHIN GROUP (ORDER BY TotalDue) OVER ()) AND
      (SELECT PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY TotalDue) OVER ());

6. Validating Data
Data validation ensures data integrity by applying constraints or rules. SQL Server constraints like NOT NULL, UNIQUE, and CHECK are essential for enforcing data quality.
Example. Enforce data integrity when creating a new table.
USE Hostforlife;
GO

CREATE TABLE Sales.Promotions (
    PromotionID INT PRIMARY KEY,
    PromotionName NVARCHAR(100) NOT NULL,
    DiscountPercentage DECIMAL(5, 2) CHECK (DiscountPercentage BETWEEN 0 AND 100),
    StartDate DATE NOT NULL,
    EndDate DATE NOT NULL,
    CONSTRAINT CK_Promotions_EndDate CHECK (EndDate > StartDate)
);

Alternatively, validate existing data using conditional queries.
SELECT *
FROM Sales.Promotions
WHERE DiscountPercentage < 0
   OR DiscountPercentage > 100;

Conclusion
Data cleansing is an ongoing process and a crucial component of the data lifecycle. By removing duplicates, handling missing values, correcting inconsistencies, standardizing formats, removing outliers, and validating data, you can significantly improve the quality of your data. These techniques, demonstrated using the AdventureWorks2022 database, can be applied to real-world datasets to ensure accurate and actionable insights. By incorporating these practices into your data workflows, you can ensure that your analysis, reports, and machine learning models are built on a solid foundation of clean data.

HostForLIFEASP.NET SQL Server 2022 Hosting

In SQL Server, a temporary table is a unique kind of table that is used to hold data for a short time. When it is no longer required, it is automatically removed after existing for the duration of a batch or database session. The tempdb system database contains temporary tables.

Types of Temporary Tables
SQL Server supports two types of temporary tables.

• Local Temporary Table (#)
  • Only visible to the session or connection that created it.
  • Automatically deleted when the session ends.

• Global Temporary Table (##)
  • Visible to all sessions and connections.
  • Deleted only when the last session referencing it is closed.

Procedure for utilizing a temporary table

Step 1. Syntax for Creating a Temporary Table.
CREATE TABLE #TempTableName
(
    Column1 DataType PRIMARY KEY,  -- Example of a primary key
    Column2 DataType,
    Column3 DataType
);

Step 2. Example of Using a Temporary Table in a Stored Procedure.
CREATE PROCEDURE StudentDetailsTempTable
AS
BEGIN
    -- Step 1: Create a temporary table
    CREATE TABLE #StudentDetails
    (
        StudentID INT PRIMARY KEY,           

-- Primary key example
        StudentName NVARCHAR(100),
        Course NVARCHAR(100),
        Fees DECIMAL(18,2)
    );

    -- Step 2: Insert data into the temporary table
    INSERT INTO #StudentDetails (StudentID, StudentName, Course, Fees)
    VALUES
        (1, 'Peter', 'MBA-IT', 160000.00),
        (2, 'Leon', 'MBA-Economics', 180000.00),
        (3, 'Alex', 'Master in technology in cs', 150000.00);

    -- Step 3: Select data from the temporary table
    SELECT * FROM #StudentDetails;

    -- Step 4: Temporary table will be dropped automatically after procedure ends
END;

Notes

• Scope: A temporary table established within a stored procedure is confined to that specific procedure and is automatically removed upon the completion of the procedure's execution.
• Multiple Sessions: Temporary tables that begin with a # symbol are restricted to the session in which they were created. For access across different sessions, a global temporary table can be created using ##; however, local tables are generally considered safer for use within stored procedures.

When to Utilize Temporary Tables?

• Intermediate Results: Temporary tables are advantageous for storing intermediate results of queries temporarily within a stored procedure.
• Data Transformation: They serve the purpose of manipulating or aggregating data prior to delivering the final result set.
• Performance: In certain scenarios, the use of temporary tables can enhance performance, particularly when managing complex joins or aggregations involving large datasets.

Process for utilizing a Table variable
A table variable in SQL Server is a distinct category of variable designed to hold a temporary collection of data organized in a table structure. It is defined through the DECLARE statement utilizing the TABLE data type. While table variables share similarities with temporary tables, they exhibit notable differences in terms of scope, performance, and application.Limitations

1. Prohibition of DDL Operations: DDL commands such as ALTER or DROP are not permitted on table variables.
2. Restriction on Explicit Indexes: Only indexes that are established through constraints are permissible.
3. Inefficiency with Large Data Sets: Table variables are not as effective as temporary tables when managing large volumes of data.

Step 1. To create a table variable, the appropriate syntax involves using the DECLARE statement in conjunction with the TABLE. Syntax for Declaring a Table Variable.
DECLARE @TableName TABLE
(
    Column1 DataType PRIMARY KEY,  -- Primary key example
    Column2 DataType,
    Column3 DataType
);

Step 2. Defining and Utilizing a Table Variable.
CREATE PROCEDURE StudentDetailsTableVariable
AS
BEGIN
    DECLARE @StudentDetailsTable TABLE
    (
        StudentID INT PRIMARY KEY,            -- Primary key example
        StudentName NVARCHAR(100),
        Course NVARCHAR(100),
        Fees DECIMAL(18,2)
    );

    -- Insert data into the table variable
    INSERT INTO @StudentDetailsTable (StudentID, StudentName, Course, Fees)
    VALUES
        (1, 'Peter', 'BSC', 60000.00),
        (2, 'Leon', 'BA', 80000.00),
        (3, 'Alex', 'Data Science', 50000.00);

    -- Select data from the table variable
    SELECT * FROM @StudentDetailsTable;
END;

• Scope: A temporary table established within a stored procedure is confined to that specific procedure and is automatically removed upon the completion of the procedure's execution.
• Multiple Sessions: Temporary tables that begin with a # symbol are restricted to the session in which they were created. For access across different sessions, a global temporary table can be created using ##; however, local tables are generally considered safer for use within stored procedures.

When to Utilize Temporary Tables?

• Intermediate Results: Temporary tables are advantageous for storing intermediate results of queries temporarily within a stored procedure.
• Data Transformation: They serve the purpose of manipulating or aggregating data prior to delivering the final result set.
• Performance: In certain scenarios, the use of temporary tables can enhance performance, particularly when managing complex joins or aggregations involving large datasets.

Process for utilizing a Table variable

A table variable in SQL Server is a distinct category of variable designed to hold a temporary collection of data organized in a table structure. It is defined through the DECLARE statement utilizing the TABLE data type. While table variables share similarities with temporary tables, they exhibit notable differences in terms of scope, performance, and application.

Limitations

• Prohibition of DDL Operations: DDL commands such as ALTER or DROP are not permitted on table variables.
• Restriction on Explicit Indexes: Only indexes that are established through constraints are permissible.
• Inefficiency with Large Data Sets: Table variables are not as effective as temporary tables when managing large volumes of data.

Step 1. To create a table variable, the appropriate syntax involves using the DECLARE statement in conjunction with the TABLE. Syntax for Declaring a Table Variable.
DECLARE @TableName TABLE
(
    Column1 DataType PRIMARY KEY,  -- Primary key example
    Column2 DataType,
    Column3 DataType
);

Step 2. Defining and Utilizing a Table Variable.
CREATE PROCEDURE StudentDetailsTableVariable
AS
BEGIN
    DECLARE @StudentDetailsTable TABLE
    (
        StudentID INT PRIMARY KEY,            -- Primary key example
        StudentName NVARCHAR(100),
        Course NVARCHAR(100),
        Fees DECIMAL(18,2)
    );

    -- Insert data into the table variable
    INSERT INTO @StudentDetailsTable (StudentID, StudentName, Course, Fees)
    VALUES
        (1, 'AmIt', 'BSC', 60000.00),
        (2, 'Nagpal', 'BA', 80000.00),
        (3, 'Prashant', 'Data Science', 50000.00);

    -- Select data from the table variable
    SELECT * FROM @StudentDetailsTable;
END;

When to Utilize Table Variables?

• Small Data Sets: Table variables are more effective for handling small amounts of data.
• Short Lifespan: These variables are automatically removed upon the completion of the batch or procedure.
• Stored Procedures: They function optimally within the context of a stored procedure or batch.

 Key Distinctions Between Table Variables and Temporary Tables

HostForLIFEASP.NET SQL Server 2022 Hosting

Microsoft SQL Server 2022 introduces powerful new T-SQL functions that enhance developer productivity and make data manipulation faster and more intuitive. These enhancements are designed to streamline complex queries and add new flexibility to SQL Server’s capabilities, making it a more versatile tool for modern data management.

1. IS [NOT] DISTINCT FROM Comparison
The IS [NOT] DISTINCT FROM feature simplifies null-safe comparisons between columns. By treating NULL values as comparable, it eliminates the need for complex ISNULL or COALESCE functions, making comparisons more intuitive.

2. DATE_BUCKET Function
The DATE_BUCKET function is a valuable addition for time series data analysis. It allows users to “bucket” data by specifying intervals, which is particularly useful for aggregating data over a fixed time span, such as minutes, hours, or days. This function is a game-changer for reporting and analytics on time-based data.

3. DATETRUNC Function
The DATETRUNC function truncates a datetime to a specified precision, such as day, month, or year, making it easier to group data at different time granularities. This simplification can reduce code complexity when working with datetime calculations.

4. LEAST and GREATEST Functions
SQL Server 2022 introduces LEAST and GREATEST functions, which return the smallest or largest value from a list of expressions. This new functionality allows for easier comparisons and is highly efficient for complex conditional logic.

5. STRING_SPLIT with Ordinal Option
The updated STRING_SPLIT function now includes an ordinal parameter, allowing users to retain the original sequence of split elements. This improvement is crucial when ordering and reconstructing data based on position.

6. Enhanced TRIM Function
SQL Server 2022 expands the TRIM function to allow multiple characters to be trimmed from a string, not just whitespace. This enhancement makes it more flexible for cleaning and formatting data in place.

7. GENERATE_SERIES Function
The GENERATE_SERIES function allows users to create a range of values in a single query, simplifying tasks like generating time series or producing sequences without needing complex loops or temp tables.

8. Windowing Function Enhancement
New windowing capabilities enhance functions like LAG and LEAD, making them more efficient and performant. These improvements offer more control and flexibility for analytic functions within partitions.

9. BIT Functions
SQL Server 2022 also introduces bitwise functions that simplify the manipulation of binary data. These include BIT_AND, BIT_OR, and BIT_XOR, which provide streamlined methods for bitwise calculations and are particularly useful in fields that require binary data manipulation.

Conclusion
These T-SQL enhancements reflect Microsoft’s focus on making SQL Server more powerful and developer-friendly. With each function, SQL Server users gain new tools for cleaner syntax, better performance, and easier data handling, enabling more efficient workflows and advanced analytics. If you’re looking to leverage the full capabilities of SQL Server 2022, these features are a must-know.

HostForLIFEASP.NET SQL Server 2022 Hosting

When working with SubQuery, the behavior or requirement might not be the save as ordinary query.

This query is working:

However, as a subquery it does not working any more:

The reason is the subquery needs an alias: when we added it, the original error is gone, but a new one is coming:

This error is due to the missing column name. That is good for ordinary query, but not for subquery:

adding column name, it is working now:

Adding "ORDER BY", this does not work, with errors:

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

As suggested, adding TOP it is working

HostForLIFEASP.NET SQL Server 2022 Hosting

A number of noteworthy T-SQL improvements included in SQL Server 2022 simplify data handling, particularly when utilizing analytics, window functions, and sliding aggregations. These new features can be used by database administrators, data engineers, and SQL developers to streamline complicated data operations, enhance readability, and maximize query performance.

Key Enhancements in SQL Server 2022 Window Functions

• OVER with ORDER BY for Aggregate Functions: Allows running and sliding aggregations.
• Sliding Aggregations at Account Level: Maximize window rows and apply at a granular level.
• WINDOW Clause: Reduces code duplication.
• IGNORE NULLS: Optimizes data analysis for first and last values.
• IGNORE VALUES for NULLs in Aggregations: Adds flexibility in handling NULL data points.

We will use the table below for our examples.CREATE TABLE dbo.AccountTransactions (
    AccountID INT,
    TransactionDate DATE,
    Amount DECIMAL(10, 2)
);

INSERT INTO dbo.AccountTransactions (AccountID, TransactionDate, Amount)
VALUES
    (1, '2023-01-01', 100.00),
    (1, '2023-01-02', 150.00),
    (1, '2023-01-03', 200.00),
    (1, '2023-01-04', NULL),
    (1, '2023-01-05', 300.00),
    (2, '2023-01-01', 500.00),
    (2, '2023-01-02', 700.00),
    (2, '2023-01-03', NULL),
    (2, '2023-01-04', 800.00),
    (2, '2023-01-05', 900.00);
1. Using OVER with ORDER BY for Aggregate Functions
In SQL Server 2012, Microsoft introduced the ability to use OVER with ORDER BY for aggregate functions, which allows for running and sliding aggregations. This feature lets you compute cumulative totals, moving averages, and other metrics across an ordered dataset without needing a self-join or correlated subquery.Example. Running Total of Transactions by Account
The following query calculates a running total of transaction amounts per AccountID, ordered by TransactionDate.
SELECT
    AccountID,
    TransactionDate,
    Amount,
    SUM(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate) AS RunningTotal
FROM dbo.AccountTransactions
ORDER BY AccountID, TransactionDate;

/*
The SUM function is paired with OVER (PARTITION BY AccountID ORDER BY TransactionDate). For each row, it calculates a running total of the Amount column by partitioning the data by AccountID and ordering it by TransactionDate.
*/
Output

Use Case: Running totals are helpful for calculating cumulative spending per account, which is common in financial reporting or customer analytics.

2. Sliding Aggregations with a Limit on Rows per Window
Sliding aggregations are a specific type of windowed calculation where only a fixed number of preceding or following rows are considered. In SQL Server 2022, this can be particularly useful for rolling averages or sums over a specified window of rows.

Example. Three-Row Sliding Sum
In this example, we calculate a sliding sum of the amount for each of the three most recent transactions of each AccountID.
SELECT
    AccountID,
    TransactionDate,
    Amount,
    SUM(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate ROWS 2 PRECEDING) AS SlidingSum
FROM dbo.AccountTransactions
ORDER BY AccountID, TransactionDate;

/*
The ROWS 2 PRECEDING clause limits the window to the current row and the previous two rows. This is a rolling three-row sum, which helps in understanding recent trends in transaction amounts.
*/

Output

Use Case. Financial analysts might use a sliding aggregation to calculate moving averages, which is useful for smoothing out trends over a defined period

3. Using the WINDOW Clause to Eliminate Code Duplication
The WINDOW clause, introduced in SQL Server 2022, allows you to define a window frame once and reference it multiple times. This feature helps reduce code duplication and improves readability, especially in queries with multiple windowed calculations.

Example. Defining and Using a Window
Here, we define a window once using the WINDOW clause and reference it for both the running total and the sliding sum.
SELECT
    AccountID,
    TransactionDate,
    Amount,
    SUM(Amount) OVER w AS RunningTotal,
    AVG(Amount) OVER w AS RollingAverage
FROM dbo.AccountTransactions
WINDOW w AS (PARTITION BY AccountID ORDER BY TransactionDate ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
ORDER BY AccountID, TransactionDate;

/*
The WINDOW clause defines a reusable window called w, which can then be applied to different aggregates, reducing code repetition and making modifications easier.
*/

Output

Use Case. The WINDOW clause is particularly valuable in analytics queries where multiple window functions need to use the same partition and order criteria.

4. Using IGNORE NULLS in FIRST_VALUE and LAST_VALUE Functions
The IGNORE NULLS option, introduced in SQL Server 2022, allows window functions like FIRST_VALUE and LAST_VALUE to skip NULL values, making it easier to retrieve non-null values in sequences with missing data.

Example. Getting the Last Non-NULL Value per Account
In this example, we use LAST_VALUE with IGNORE NULLS to fetch the most recent non-null transaction amount for each account.
SELECT
    AccountID,
    TransactionDate,
    Amount,
    LAST_VALUE(Amount) IGNORE NULLS OVER (PARTITION BY AccountID ORDER BY TransactionDate) AS LastNonNullAmount
FROM dbo.AccountTransactions
ORDER BY AccountID, TransactionDate;

/*
Without IGNORE NULLS, the LAST_VALUE function would return a NULL value if the last row in the partition contained NULL. With IGNORE NULLS, SQL Server skips over the NULL values and returns the most recent non-null value instead.
*/

Output

Use Case. This is particularly useful for handling incomplete or intermittent data, such as filling in missing stock prices, temperature readings, or any sequential time-based metric.

5. Handling NULLs with the IGNORE NULLS Option for Aggregations
SQL Server 2022's IGNORE NULLS option can also be applied to functions like SUM and AVG, making it easier to handle datasets with missing values without affecting calculations.

Example. Average Transaction Amount Ignoring NULLs
The following query calculates the average transaction amount for each account while ignoring NULL values.
SELECT
    AccountID,
    TransactionDate,
    Amount,
    SUM(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) /
    NULLIF(COUNT(Amount) OVER (PARTITION BY AccountID ORDER BY TransactionDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW), 0)
    AS AverageAmount
FROM dbo.AccountTransactions
ORDER BY AccountID, TransactionDate;

/*
With IGNORE NULLS, the calculation ignores rows where Amount is NULL, providing a more accurate average.
*/

Output

Use Case: NULL values are common in datasets, and having the ability to ignore them directly in aggregation functions simplifies code and improves result accuracy.

Conclusion
SQL Server 2022 brings powerful enhancements to window functions, making them more flexible and capable of handling real-world scenarios in SQL development. From running totals and sliding windows to null handling with IGNORE NULLS, these features improve the clarity, efficiency, and precision of windowed calculations. By leveraging these new tools, SQL developers, data engineers, and DBAs can build more expressive and performant SQL queries, making SQL Server 2022 a strong choice for data-intensive applications.

HostForLIFEASP.NET SQL Server 2022 Hosting

In my series of essays about SQL Server's ranking functions, this is the last one. The Row Number, Rank, and DenseRank() functions were covered in earlier talks. It's time to finally talk about the idea behind the NTile() method. The following will be the first data setup for the discussion.

The NTile function
The NTile function takes an integer as an input and divides the records of the result set into that number of groups. For example, NTile(5) will divide a result set of 10 records into 5 groups with two records in each group. In case the result set is not exactly divisible into an equal number of records, it assigns more records to the starting groups and less to the following ones.

For example, if we have 11 records and we apply NTile(5), then it will divide the result set into 5 groups with 3 records in the first group and 2 records in the rest of the 4 groups.

The main point is that within each group, each record will have the same number or rank.

Again, we will be discussing the concept with two cases.

Case 1. Ranking entire result set using Order By clause
Our entire query will remain the same except the ranking function is changed to NTile and we will divide the result set into 4 groups.
SELECT
    NTILE(4) OVER (ORDER BY DepartmentName DESC) AS GeneratedRank,
    MemberName,
    Gender,
    DepartmentName
FROM
    dbo.DepartmentMembers;

Execute the query and see the results. It divides the 9 records into 4 groups with 3 records in the first group and 2 records in the rest of the 3 groups. Within each group, the records have the same rank or number assigned to them.

It might seem quite similar to the Dense_Rank, but it is not. Dense_Rank orders the data by the column specified in the order by clause and then assigns the rank. But here, the order by clause only matters for the starting point of dividing the result set into groups. The results are divided into groups, depending on the size provided by the function parameter and the ranking is assigned to the records based on the group number. See the queries below that compare both functions on the same data.

Case 2. Ranking partitioned result set using Partition By Clause
The query remains the same, except the Partition By clause is also added. So the query changes to.
SELECT
    NTILE(4) OVER (PARTITION BY Gender ORDER BY DepartmentName DESC) AS GeneratedRank,
    MemberName,
    Gender,
    DepartmentName
FROM
    dbo.DepartmentMembers;

This time, the result set is first partitioned into 2 result sets based on Gender, and then within each result set, the results are further divided by the NTile function depending on the size defined. The logic of dividing each result set remains the same as we discussed above. the only difference is that in case 1, it was applied on the entire results set, but in this case, it is applied to two result sets 1 and 2. See the results below.

As we can see above, the entire result set was divided into two sets, based on the partition by Gender. Further, within each result set, they are divided into 4 groups. For result set 1, we have 5 records. So the first group in the first result set is divided into 2 records and the remaining 3 records are divided into 1 record each. For result set 2, the record set is divided into 4 equal groups of 1 record each, since there was a total of 4 records. So this was about the NTile function in SQL Server. I hope you enjoyed reading this.

HostForLIFEASP.NET SQL Server 2022 Hosting