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

The syntax, application cases, and basic examples of these two new T-SQL enhancements—LEAST and GREATEST—will be covered in this article. We will also look at how they differ from conventional methods and how they affect regular database operations. Many querying jobs can be made more efficient by these functions for SQL developers, data engineers, and DBAs.

Introduction
Before introducing these functions in SQL Server 2022, finding the minimum or maximum value from a set of columns or expressions required more complex logic or verbose CASE statements. The LEAST and GREATEST functions simplify these operations, offering more intuitive and concise ways to achieve the desired outcomes.

Syntax Overview
    LEAST: Returns the smallest value from a list of expressions.

    LEAST (
        expression1,
        expression2,
        ...,
        expressionN
    )

GREATEST: Returns the largest value from a list of expressions.

GREATEST (
    expression1,
    expression2,
    ...,
    expressionN
)

Both functions can compare multiple arguments (expressions), and their arguments can be any data type that supports comparison (numeric, date, or even string types). The result will be of the same data type as the input expressions.

LEAST and GREATEST vs. Traditional Methods
Prior to SQL Server 2022, SQL developers typically had to use CASE statements or a combination of MIN and MAX functions along with complex logic to compare multiple columns or values. While these methods worked, they were cumbersome and often led to less readable queries.

Examples of Traditional Methods
To compare multiple columns before SQL Server 2022.
SELECT
    CASE
        WHEN Column1 <= Column2 AND Column1 <= Column3 THEN Column1
        WHEN Column2 <= Column1 AND Column2 <= Column3 THEN Column2
        ELSE Column3
    END AS SmallestValue,

    CASE
        WHEN Column1 >= Column2 AND Column1 >= Column3 THEN Column1
        WHEN Column2 >= Column1 AND Column2 >= Column3 THEN Column2
        ELSE Column3
    END AS LargestValue
FROM MyTable;

This logic can quickly become unwieldy if you have many columns to compare. Now, with the new functions in SQL Server 2022, this task becomes much simpler.

Using LEAST and GREATEST
The same example uses LEAST and GREATEST.
SELECT
    LEAST(Column1, Column2, Column3) AS SmallestValue,
    GREATEST(Column1, Column2, Column3) AS LargestValue
FROM
    MyTable;

Examples
Example 1. Using LEAST to Compare Different Tax Rates

Let's assume you want to compare the TaxAmt for several sales orders across different order dates and determine the minimum tax amount for each sales order. The Sales. The salesOrderHeader table includes details about each sales order, including the tax amount.

We’ll use the LEAST function to identify the minimum tax amount for several orders.
USE [Hostforlife];
GO

SELECT
    SalesOrderID,
    LEAST(TaxAmt, Freight, SubTotal) AS SmallestAmount
FROM
    Sales.SalesOrderHeader WITH (NOLOCK)
WHERE
    SalesOrderID IN (43659, 43660, 43661);

Output

Example 2. Use GREATEST to Find the Maximum Bonus, Sick Leave, and Vacation Hours
Let’s now use the HumanResources.Employee table. We want to compare each employee's VacationHours, SickLeaveHours, and Bonus (we’ll assume a fixed bonus column for demonstration) and find out which of these values is the largest for each employee.
USE [Hostforlife];
GO

SELECT
    BusinessEntityID,
    GREATEST(VacationHours, SickLeaveHours, 10) AS MaxBenefit
FROM
    HumanResources.Employee
WHERE
    BusinessEntityID BETWEEN 1 AND 10;

Output

Performance Considerations
While the LEAST and GREATEST functions simplify queries, it’s important to consider their performance impact in large datasets or complex queries. Typically, these functions are efficient, especially compared to more verbose alternatives such as CASE statements. However, in cases where you're comparing large datasets or many columns, performance tuning may still be necessary.

Use Cases for DBAs, Data Engineers, and SQL Developers
For SQL developers, LEAST and GREATEST streamline common query patterns, making code easier to read and maintain. This is especially beneficial when writing queries that compare multiple values across different columns.

For data engineers, these functions simplify data pipeline transformations, where selecting the minimum or maximum value from a set of columns is common. Whether dealing with dates, numeric data, or even strings, LEAST and GREATEST can reduce the complexity of transformation logic.

DBAs will also appreciate the reduced complexity when working with large, production-level queries that must compare multiple values or optimize reporting views. These functions can reduce the need for additional table scans or subqueries in complex reports.

Conclusion
Whether you're comparing sales figures, tracking project dates, or handling any scenario that involves finding the smallest or largest value among multiple expressions, LEAST and GREATEST make the job easier. By understanding their behavior, particularly with respect to NULL values, you can ensure that your queries are both efficient and accurate.

HostForLIFEASP.NET SQL Server 2022 Hosting

The most recent version of Microsoft's well-liked relational database management system, SQL Server 2022, contains a number of new features and improvements targeted at enhancing database management, query efficiency, and developer productivity. The addition of the IS DISTINCT FROM and IS NOT DISTINCT FROM predicates, which provide a more thorough and user-friendly method of handling comparisons across nullable columns in SQL queries, is one such enhancement.

Dealing with NULL values in SQL can be challenging for database administrators (DBAs), data engineers, and SQL developers, particularly when it comes to comparisons. Conventional equality operators (=) and their negation (<>) frequently handle NULL values in an ineffective manner, causing confusion and unexpected outcomes. SQL Server 2022 introduces IS DISTINCT FROM and IS NOT DISTINCT FROM, which offer a more dependable and expressive method of handling NULL values and comparing nullable columns.

The Problem with NULL Comparisons in SQL
It's important to understand the challenges that arise when dealing with NULL values in SQL. In SQL, NULL represents an unknown or missing value, and it behaves differently than other values in the database.

The standard equality (=) and inequality (<>) operators do not behave as expected when NULL values are involved.

• When comparing two NULL values using =, the result is not TRUE, but rather UNKNOWN, because SQL treats NULL as an unknown value.
• Similarly, comparing a NULL value with a non-NULL value using <> results in UNKNOWN, not TRUE.

Consider the following example. This behavior stems from the fact that NULL represents an unknown value, and comparing two unknown values logically does not lead to a definitive TRUE or FALSE result. Instead, SQL returns UNKNOWN, which is treated as FALSE in most boolean contexts.

Traditional Solutions: IS NULL and IS NOT NULL
To work around this issue, SQL developers typically use the IS NULL and IS NOT NULL checks when comparing nullable columns. While this approach works, it can be verbose and error-prone, especially in complex queries involving multiple nullable columns.

The New IS DISTINCT FROM and IS NOT DISTINCT FROM Predicates
SQL Server 2022 addresses the challenge of NULL comparisons by introducing the IS DISTINCT FROM and IS NOT DISTINCT FROM predicates, which offer a more straightforward and intuitive way to compare nullable columns.

IS DISTINCT FROM: The IS DISTINCT FROM predicate compares two values and returns TRUE if the values are different, even when one or both of the values are NULL. In other words, IS DISTINCT FROM treats NULL as a regular value, and two NULL values are considered equal.

Syntax

value1 IS DISTINCT FROM value2

--TRUE if the two values are different, including cases where one value is NULL and the other is not.
--FALSE if the two values are the same, including cases where both values are NULL.

IS NOT DISTINCT FROM: The IS NOT DISTINCT FROM predicate is the logical opposite of IS DISTINCT FROM. It returns TRUE if the two values are the same, even when both values are NULL.

Syntax
value1 IS NOT DISTINCT FROM value2

--TRUE if the two values are the same, including cases where both values are NULL.
--FALSE if the two values are different, including cases where one value is NULL and the other is not.

Example Use Case

Consider the following example, where we have a table Employees with nullable columns Salary and Bonus. We want to compare the Salary and Bonus columns and determine hether they are distinct or not, even when NULL values are involved.

To compare the Salary and Bonus columns using traditional SQL operators, we would need to write a verbose query.
SELECT
    EmployeeID,
    CASE
        WHEN Salary IS NULL AND Bonus IS NULL THEN 'Equal'
        WHEN Salary = Bonus THEN 'Equal'
        ELSE 'Distinct'
    END AS ComparisonResult
FROM dbo.Employees

Using IS DISTINCT FROM, the query becomes much simpler.
SELECT
    EmployeeID,
    CASE
        WHEN Salary IS DISTINCT FROM Bonus THEN 'Distinct'
        ELSE 'Equal'
    END AS ComparisonResult
FROM dbo.Employees

Output

Advantages

• Simplified Code: As demonstrated in the previous example, the IS DISTINCT FROM and IS NOT DISTINCT FROM predicates allow for more concise and readable SQL queries. By eliminating the need for IS NULL and IS NOT NULL checks, these predicates reduce the verbosity of the code and make it easier to understand.
• More Intuitive Handling of NULL Values: These predicates provide a more intuitive way to compare nullable columns, as they treat NULL values as regular values. This is especially useful in scenarios where developers need to compare nullable columns without worrying about the special handling of NULL.
• Improved Consistency: The traditional behavior of SQL's equality (=) and inequality (<>) operators can be confusing when NULL values are involved. By using IS DISTINCT FROM and IS NOT DISTINCT FROM, developers can ensure consistent behavior in their queries, regardless of whether NULL values are present.

Use Cases
1. Data Deduplication
In data deduplication tasks, developers often need to identify and remove duplicate rows from a table. When nullable columns are involved, the IS DISTINCT FROM predicate can help ensure that NULL values are treated consistently.

For example, to identify duplicate rows in a table with nullable columns.
WITH DuplicateRows AS (
    SELECT
        EmployeeID,
        ROW_NUMBER() OVER (PARTITION BY Salary, Bonus ORDER BY EmployeeID) AS RowNum
    FROM dbo.Employees
    WHERE Salary IS NOT DISTINCT FROM Bonus
)
DELETE FROM DuplicateRows WHERE RowNum > 1

2. Conditional Updates and Inserts
In scenarios where developers need to perform conditional updates or inserts based on comparisons of nullable columns, IS DISTINCT FROM and IS NOT DISTINCT FROM can simplify the logic and ensure accurate results.

For example, to update the Bonus column only if it is distinct from the Salary column.
UPDATE dbo.Employees
SET Bonus = Salary * 0.1
WHERE Salary IS DISTINCT FROM Bonus;

3. Data Comparison and Synchronization
In data comparison and synchronization tasks, developers often need to compare rows in two different tables to identify changes or discrepancies. When nullable columns are involved, the traditional comparison operators may not behave as expected, leading to incorrect results.

For example, consider two tables, dbo.Employees and dbo.Employees1, which stores employee salary information. We want to compare the two tables and identify rows where the salary has changed. In this query, IS DISTINCT FROM ensures that the comparison works correctly, even when one or both Salary values are NULL.
SELECT
    a.EmployeeID,
    a.Salary AS SalaryInTableA,
    b.Salary AS SalaryInTableB
FROM
    dbo.Employees a
JOIN
    dbo.Employees1 b
    ON a.EmployeeID = b.EmployeeID
WHERE
    a.Salary IS DISTINCT FROM b.Salary;

Conclusion
The introduction of the IS DISTINCT FROM and IS NOT DISTINCT FROM predicates in SQL Server 2022 is a welcome enhancement for SQL developers, data engineers, and DBAs. These new predicates simplify the process of comparing nullable columns, reduce the complexity of SQL queries, and provide more intuitive and consistent.

HostForLIFEASP.NET SQL Server 2022 Hosting

We will study the many kinds of SQL keys today. In essence, SQL has the following kinds of keys.
Keys: Primary, Foreign, Unique, Composite, Alternate, Surrogate, and Composite

1. Principal Key
This is the SQL table's fundamental key. It designates a certain table row. A primary key must have unique values when defined, meaning that data must be taken into consideration when putting it into the table. Additionally, it cannot be null, meaning that null values cannot be added to the main key column.

CREATE TABLE Employees (
    EmployeeID   INT           PRIMARY KEY,
    Name         VARCHAR(100)  NOT NULL,
    EmployeeNo   VARCHAR(100)  NOT NULL,
    Email        VARCHAR(200)  NOT NULL,
    Active       BIT           NOT NULL,
    Phone        VARCHAR(20)   NULL,
    CreatedDate  DATETIME      NOT NULL
);

2. Foreign Key
This key is related to the Primary key. A foreign key is basically the primary key in another table. It is building a connection among tables.

CREATE TABLE Employees (
    EmployeeID   INT          PRIMARY KEY,
    Name         VARCHAR(100) NOT NULL,
    EmployeeNo   VARCHAR(100) NOT NULL,
    Email        VARCHAR(200) NOT NULL,
    Active       BIT          NOT NULL,
    Phone        VARCHAR(20)  NULL,
    CreatedDate  DATETIME     NOT NULL
);

CREATE TABLE EmployeeFiles (
    EmployeeFileID INT          PRIMARY KEY,
    EmployeeID     INT,
    CreatedDate    DATETIME     NOT NULL,
    FOREIGN KEY (EmployeeID) REFERENCES Employees(EmployeeID)
);

3. Unique Key
A unique key in the table ensures that the value in the row is unique overall. It does almost the same as the primary key, but it allows a null value for the column. For example, in the table below, the email will be unique for all records no duplicate email can be inserted in this table.

CREATE TABLE Employees (
    EmployeeID   INT          PRIMARY KEY,
    Name         VARCHAR(100) NOT NULL,
    EmployeeNo   VARCHAR(100) NOT NULL,
    Email        VARCHAR(200) NOT NULL UNIQUE,
    Active       BIT          NOT NULL,
    Phone        VARCHAR(20)  NULL,
    CreatedDate  DATETIME     NOT NULL
);

4. Composite Key
When there is a requirement of uniqueness on the basis of 2 columns, then a Composite key is used. It is basically a primary key on multiple columns. For example, in the below query data will be inserted on the basis of the uniqueness of EmployeeNo and Email.

CREATE TABLE Employees (
    EmployeeID   INT IDENTITY(1,1),
    Name         VARCHAR(100) NOT NULL,
    EmployeeNo   VARCHAR(100) NOT NULL,
    Email        VARCHAR(200) NOT NULL,
    Active       BIT          NOT NULL,
    Phone        VARCHAR(20)  NULL,
    CreatedDate  DATETIME     NOT NULL,
    PRIMARY KEY (EmployeeNo, Email)
);

5. Alternate Key
When multiple keys are added to a table except the Primary key, then all keys (except the Primary Key) are identified as Alternate Keys. In the following query, EmployeeNo and Email are alternate keys.

CREATE TABLE Employees (
    EmployeeID   INT          PRIMARY KEY,
    Name         VARCHAR(100) NOT NULL,
    EmployeeNo   VARCHAR(100) NOT NULL UNIQUE,
    Email        VARCHAR(200) NOT NULL UNIQUE,
    Active       BIT          NOT NULL,
    Phone        VARCHAR(20)  NULL,
    CreatedDate  DATETIME     NOT NULL
);

6. Surrogate Key
When the primary key of the table serves as a unique identifier, it is known as a Surrogate key. It may be database generated (as in the below query) or by another application (not supplied by the user). It is more often a database generated.
CREATE TABLE Employees (
    EmployeeID   INT IDENTITY(1,1) PRIMARY KEY,
    Name         VARCHAR(100)      NOT NULL,
    EmployeeNo   VARCHAR(100)      NOT NULL UNIQUE,
    Email        VARCHAR(200)      NOT NULL UNIQUE,
    Active       BIT               NOT NULL,
    Phone        VARCHAR(20)       NULL,
    CreatedDate  DATETIME          NOT NULL
);

I hope you find it simple and helpful. Thank you!

HostForLIFEASP.NET SQL Server 2022 Hosting

Concurrency
The capacity of various components or units of a program, algorithm, or problem to be executed partially or out of sequence without changing the result is known as concurrency in computer science.

Transaction
A transaction is a collection of operations that performs a single logical function in a database application. Multiple transactions execute at the same time can cause the concurrency issue.

Transaction Properties
A database management system (DBMS) is considered a relational database management system (RDBMS) if it follows the transactional properties, ACID, a set of properties of database transactions intended to guarantee data validity..

• A: Atomicity
  • Transactions are often composed of multiple statements. Atomicity guarantees that each transaction is treated as a single "unit", which either succeeds completely or fails completely:
• C: Consistency
  • Consistency ensures that a transaction can only bring the database from one consistent state to another, preserving database invariants: any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.
• I: Isolation
  • Transactions are often executed concurrently (e.g., multiple transactions reading and writing to a table at the same time). Isolation ensures that concurrent execution of transactions leaves the database in the same state that would have been obtained if the transactions were executed sequentially.
• D: Durability
  • Durability guarantees that once a transaction has been committed, it will remain committed even in the case of a system failure (e.g., power outage or crash)

Concurrency Problem Types
The concurrency problem mostly arises when both the users try to write the same data, or when one is writing and the other is reading. Apart from this logic, there are some common types of concurrency problems:

• Dirty Reads
  • This problem occurs when another process reads the changed, but uncommitted data. For instance, if one process has changed data but not committed it yet, another process is able to read the same data. This leads to the inconsistent state for the reader.
• Lost Updates
  • This problem occurs when two processes try to manipulate the same data simultaneously. This problem can lead to data loss, or the second process might overwrite the first processs change.
• Non-repeatable Reads
  • This problem occurs when one process is reading the data, and another process is writing the data. In non-repeatable reads, the first process reading the value might get two different values, as the changed data is read a second time because the second process changes the data.
• Phantom Reads
  • If two same queries executed by two users show different output, then it would be a Phantom Read problem. For instance, If user A select a query to read some data, at the same time the user B insert some new data but the user A only get able to read the old data at the first attempt, but when user A re-query the same statement then he/she gets a different set of data.

Solve Concurrency Problems
SQL Server provides 5 different levels of transaction isolation to overcome these Concurrency problems. These 5 isolation levels work on two major concurrency models:

• Pessimistic model -
  • Readers can block writers, and
  • Writers can block readers.
• Optimistic model -
  • Readers cannot block writers, and
  • Writers cannot block readers, but
  • Writer can block another writer.

Note that readers are users are performing the SELECT operations. Writers are users are performing INSERT, ALTER, UPDATE, S.E.T. operations.

5 Isolation Level
In a SQL server, a query can have five different isolation levels:

• Read Uncommitted --- pessimistic
  • One transaction is allowed to read the data that is about to be changed by the commit of another process.
    • Allows the dirty read problem.
• Read Committed --- pessimistic ---  This option is the SQL Server default.
  • Only allowed to read data that is committed,
    • Eliminates the dirty read problem.
      • In reading data if there are concurrent transactions that can delete or write data, the read is blocked until other work is complete.
• Repeatable Read --- pessimistic
  • All Transactions but Insert has to wait till another transaction's update or read query is complete.
    • Eliminates the Non-Repeatable Read problem, but
    • Phantom data exists
• Serializable --- pessimistic
  • Prevent the Phantom Read problem.
    • The highest level of isolation in the pessimistic model.
    • Can ask any transaction to wait until the current transaction completes.
• Snapshot  --- optimistic
  • Snapshot follows the optimistic model of concurrency, and this level of isolation takes a snapshot of the current data and uses it as a copy for the different transactions. Here each transaction has its copy of data, so if a user tries to perform a transaction like an update or insert, it asks him to re-verify all the operation before the process gets started executing.

These levels are ordered in terms of the separation of work by two different processes, from minimal separation to maximal.

HostForLIFEASP.NET SQL Server 2022 Hosting

When working with SQL Server, there may be instances where keeping column synchronization across two or more tables is required. If you have two tables, Member and MemberProfiles, each containing User data, it is imperative that any changes made in one table are reflected in the other. You can accomplish this with the help of a few trustworthy and effective approaches. This article will cover the three most widely used techniques: employing triggers, writing SQL scripts that use the INSERT, UPDATE, and DELETE commands, and utilizing the MERGE statement.

1. Making Use of Triggers
Triggers are distinct SQL statements that execute automatically each time a table is changed. Tables' columns can be synchronized with triggers to ensure that modifications made in one table are reflected in another. To keep the Name field in the Members table and the MemberProfiles table in sync, for example, you may use an AFTER UPDATE trigger.

CREATE TRIGGER trgUpdateMemberProfiles
ON Members
AFTER UPDATE
AS
BEGIN
    UPDATE MemberProfiles
    SET Name = u.Name
    FROM MemberProfiles p
    JOIN inserted u ON p.UserId = u.UserId
END;

Whenever the Members table is updated, this trigger is triggered automatically. It synchronizes the changed Name in the Members table with the Name column in the MemberProfiles table using the inserted table, which temporarily stores updated rows. Although this approach is effective for basic synchronization tasks, as the system grows larger, it may become challenging to manage intricate logic using triggers.

2. SQL Scripts
Writing SQL queries to perform the INSERT, UPDATE, and DELETE actions is a more difficult method of maintaining column consistency between tables. Developers have complete control over the timing and mechanism of synchronization when using this method. You can manually create queries that insert the same data into the MemberProfiles table when you insert a new row into the Members table.
CREATE PROCEDURE AddMemberAndProfile
    @UserId INT,
    @Name NVARCHAR(100),
    @Email NVARCHAR(100),
    @Age INT
AS
BEGIN
    BEGIN TRANSACTION;
    BEGIN TRY
        INSERT INTO Members(UserId, Name, Email)
        VALUES (@UserId, @Name, @Email);
        INSERT INTO MemberProfiles (UserId, Name, Age)
        VALUES (@UserId, @Name, @Age);
        COMMIT;
    END TRY
    BEGIN CATCH
        ROLLBACK;
        THROW;
    END CATCH;
END;

The two insertions are handled as a single atomic transaction when BEGIN TRANSACTION is used. To ensure consistency, if one fails, the other gets rolled back. This method gives you flexibility and control, but it also necessitates writing and updating manual queries, which can get difficult as your application gets bigger.

3. Using the MERGE Statement (Best for Automation)
An effective method for automating table synchronization is the MERGE statement. It's a great option for quickly and easily maintaining table synchronization because it integrates INSERT, UPDATE, and DELETE actions into a single query. A row is checked to see if it already exists in both tables-> if it does-> it is updated. If not then its inserted.
MERGE INTO MemberProfiles AS Target
USING (SELECT UserId, Name FROM Members) AS Source
ON Target.UserId = Source.UserId
WHEN MATCHED THEN
    UPDATE SET Target.Name = Source.Name
WHEN NOT MATCHED BY TARGET THEN
    INSERT (UserId, Name)
    VALUES (Source.UserId, Source.Name);

Here, the MemberProfiles table is the Target and the Members table is the Source. The Name column in the MemberProfiles table is updated if a matching UserId is discovered. A new row is introduced if no match is discovered. Because it eliminates the need to write several queries, this solution is perfect for developers searching for a simplified, automated approach to data synchronization.
Which Method Should You Apply?

Triggers: Ideal for basic synchronization if you want the second table to automatically maintain synchronization at all times.
SQL Scripts: Excellent if you want complete control over the timing and method of data synchronization, but they involve more manual effort.
MERGE Statement: The most effective way to handle complex situations with a single, clear query and automate the procedure.

Conclusion
Data consistency requires that columns in SQL Server tables be consistent. Every approach has its own benefits, whether it is using the potent MERGE command for efficiency, manual SQL scripts for control, or triggers for automation. Select the method that assures reliable synchronization and best suits the requirements of your application.

HostForLIFEASP.NET SQL Server 2022 Hosting

Subqueries are a fundamental component of SQL that let you execute intricate queries within of more comprehensive queries. They are an effective tool in SQL for carrying out intricate data analysis and manipulation. There are three primary categories of subqueries: scalar subqueries, correlated subqueries, and EXISTS/NOT EXISTS subqueries.

Correlated Subqueries
Correlated subqueries are subqueries that reference a column from the outer query. This makes them dependent on the outer query's results, meaning the subquery is executed once for each row of the outer query.

Example. Finding employees whose salary is greater than the average salary of their department.

SELECT employee_name, salary
FROM employees
WHERE salary > (
  SELECT AVG(salary)
  FROM employees AS dept_avg
  WHERE dept_avg.department = employees.department
);

In this example, the subquery calculates the average salary for each department, and the outer query compares each employee's salary against that average. The subquery depends on the DepartmentID from the outer query, making it correlated.

When to Use?
When you need to compare each row in a table against a calculated value from related data.
For filtering data based on conditions that vary with each row.

Scalar Subqueries
A scalar subquery returns a single value, making it ideal for use in expressions where a single value is required. Scalar subqueries can be used in SELECT, WHERE, and ORDER BY clauses, among others.

Example. Finding the average salary in the company.
SELECT employee.name, (SELECT AVG(salary) FROM employee) AS average_salary
FROM employee;

Here, the subquery calculates the average salary for all employees, and the result is assigned to the average_salary column in the outer query.

When to Use?
When you need to include a single value in your main query, such as a count, average, or specific calculation.
For calculating values dynamically based on related data.

EXISTS and NOT EXISTS

EXISTS and NOT EXISTS subqueries are used to check for the existence or non-existence of rows in a subquery. These operators return a boolean value (TRUE or FALSE), making them useful for conditional queries.

Example. Finding departments that have employees earning more than 100,000.
SELECT department
FROM departments
WHERE EXISTS (
  SELECT 1
  FROM employees
  WHERE employees.department = departments.department
    AND employees.salary > 100000
);

In this query, the outer query returns departments. The subquery checks if there are any employees for that department who have a salary greater than 100,000.

When to Use?
Use EXISTS when you need to check if a related record exists.
Use NOT EXISTS to filter out records that have a related record.

Conclusion

Subqueries are a fundamental tool in SQL for performing complex data manipulations and retrieving specific subsets of data. By understanding the different types of subqueries, you can effectively leverage them to solve a wide range of data-related problems.

HostForLIFEASP.NET SQL Server 2022 Hosting

With the help of SQL triggers, you can set up automatic batch SQL code execution for when particular events take place in your database. They are especially helpful for tracking modifications to your database tables, automating administrative activities, and preserving data integrity. This article will explain SQL triggers and provide an example of how to use them to log changes made to a product table into a product_log table, allowing you to follow such changes.

An SQL Trigger: What Is It?
A SQL trigger is a unique kind of stored procedure that is "triggered"—or automatically carried out—in response to specific events on a given database or view. INSERT, UPDATE, and DELETE actions are examples of these events. Triggers facilitate the automation of processes including updating relevant data, enforcing rules, and logging.

Components of a SQL Trigger

• Trigger Name: A unique name to identify the trigger.
• Trigger Timing: Specifies when the trigger should fire (e.g., AFTER, BEFORE).
• Trigger Event: The database operation that activates the trigger (INSERT, UPDATE, DELETE).
• Trigger Action: The SQL code that is executed when the trigger is fired.

Example Scenario
Suppose we have a table called [inventory].[products] where product details are stored. We want to track all changes made to this table by logging these changes into a product_log table.

Here’s how you can set this up.

Step 1. Create the Log Table
First, create a table to store the log entries. This table will capture the details of any changes made to the products table.
CREATE TABLE inventory.product_log (
    log_id INT IDENTITY(1,1) PRIMARY KEY,
    action_type NVARCHAR(50),
    product_id INT,
    product_name VARCHAR(255),
    quantity INT,
    price DECIMAL(18, 2),
    change_date DATETIME DEFAULT GETDATE()
);

log_id: A unique identifier for each log entry.
action_type: Describes the type of action that occurred (INSERT, UPDATE).
product_id: The ID of the product that was changed.
product_name: The name of the product.
quantity: The quantity of the product.
price: The price of the product.
change_date: The date and time when the change occurred.

Step 2. Create the Trigger
Next, create a trigger that logs changes to the products table.
CREATE TRIGGER trg_productLog
ON inventory.products
AFTER INSERT, UPDATE
AS
BEGIN
    -- Log the inserted records
    INSERT INTO inventory.product_log (
        action_type,
        product_id,
        product_name,
        quantity,
        price
    )
    SELECT
        CASE
            WHEN EXISTS (SELECT * FROM inserted i WHERE i.product_id IS NOT NULL)
                THEN 'INSERT'
            ELSE 'UPDATE'
        END AS action_type,
        i.product_id,
        i.product_name,
        i.quantity,
        i.price
    FROM inserted i;
END;

AFTER INSERT, UPDATE: The trigger will fire after a row is inserted or updated in the products table.
INSERTED Table: This system-defined table contains the new or updated rows from the products table.
action_type: Determines whether the action was an INSERT or UPDATE.

Step 3. Test the Trigger
To test the trigger, insert or update a row in the products table.
-- Insert a new product
INSERT INTO inventory.products
    (product_id, product_name, quantity, price)
VALUES
    (1, 'Product A', 100, 19.99);

-- Update an existing product
UPDATE inventory.products
SET quantity = 150,
    price = 17.99
WHERE product_id = 1;

After running these statements, check the product_log table to see the log entries.
SELECT *
FROM inventory.product_log;

Conclusion
When it comes to automating and monitoring changes in your database, SQL triggers are indispensable. You may create a trigger that logs changes made to a table by following the above instructions. This will improve your ability to manage and audit your database by keeping a clear record of any data alterations.

HostForLIFEASP.NET SQL Server 2022 Hosting