SQL Server continues to be one of the most potent relational database systems in today's data-driven world. However, the ability to write sophisticated SQL queries frequently distinguishes novices from pros. Business reports, analytics dashboards, and performance-driven apps are built on complex queries.

Joins, subqueries, window functions, CTEs, pivoting, and other real-world complex SQL Server queries will all be covered in this article with clear examples.

1. Using Subqueries for Conditional Data Retrieval
Scenario:
You need to find all employees whose salary is higher than the average salary of their department.
Query:
SELECT
    EmpName,
    DepartmentId,
    Salary
FROM Employees E
WHERE Salary > (
    SELECT AVG(Salary)
    FROM Employees
    WHERE DepartmentId = E.DepartmentId
);

Explanation:

• The inner query calculates the average salary per department.
• The outer query compares each employee’s salary to that departmental average.

This is a correlated subquery, as it depends on the outer query.

2. Combining Multiple Tables with JOINS
Scenario:
Retrieve all orders along with customer names and product details.
Query:
SELECT
    C.CustomerName,
    O.OrderId,
    P.ProductName,
    O.OrderDate,
    OD.Quantity,
    (OD.Quantity * OD.UnitPrice) AS TotalAmount
FROM Orders O
INNER JOIN Customers C ON O.CustomerId = C.CustomerId
INNER JOIN OrderDetails OD ON O.OrderId = OD.OrderId
INNER JOIN Products P ON OD.ProductId = P.ProductId
WHERE O.OrderDate >= '2025-01-01'
ORDER BY O.OrderDate DESC;

Explanation:
This query combines four tables using inner joins to give a comprehensive view of orders placed in 2025.

3. Ranking Data Using Window Functions
Scenario:
List top 3 highest-paid employees in each department.
Query:
SELECT
    DepartmentId,
    EmpName,
    Salary,
    RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS SalaryRank
FROM Employees
WHERE Salary IS NOT NULL

Then wrap it to filter top 3:
SELECT *
FROM (
    SELECT
        DepartmentId,
        EmpName,
        Salary,
        RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) AS SalaryRank
    FROM Employees
) AS Ranked
WHERE SalaryRank <= 3;

Explanation:
The RANK() function assigns ranking per department.
The outer query filters the top 3 salaries per department.

4. Simplifying Logic with Common Table Expressions (CTE)
Scenario:
Find employees who earn more than the average salary in their department (using CTE for clarity).
Query:
WITH DeptAverage AS (
    SELECT
        DepartmentId,
        AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY DepartmentId
)
SELECT
    E.EmpName,
    E.DepartmentId,
    E.Salary,
    D.AvgSalary
FROM Employees E
JOIN DeptAverage D ON E.DepartmentId = D.DepartmentId
WHERE E.Salary > D.AvgSalary;

Explanation:
The CTE (DeptAverage) simplifies complex nested subqueries, making the query more readable and maintainable.

5. Transforming Rows to Columns with PIVOT
Scenario:
Show total sales by product across different months.
Query:
SELECT *
FROM (
    SELECT
        ProductName,
        DATENAME(MONTH, OrderDate) AS [Month],
        (Quantity * UnitPrice) AS TotalSales
    FROM OrderDetails OD
    JOIN Orders O ON OD.OrderId = O.OrderId
    JOIN Products P ON OD.ProductId = P.ProductId
) AS SourceTable
PIVOT (
    SUM(TotalSales)
    FOR [Month] IN ([January], [February], [March], [April], [May], [June])
) AS PivotTable;

Explanation:
This query pivots monthly sales data into columns, allowing easier visualization and reporting.

6. Recursive Queries with CTE
Scenario:
Retrieve a hierarchical list of employees under a specific manager.
Query:
WITH EmployeeHierarchy AS (
    SELECT
        EmpId, EmpName, ManagerId
    FROM Employees
    WHERE ManagerId IS NULL  -- Top-level managers

    UNION ALL

    SELECT
        E.EmpId, E.EmpName, E.ManagerId
    FROM Employees E
    INNER JOIN EmployeeHierarchy EH ON E.ManagerId = EH.EmpId
)
SELECT * FROM EmployeeHierarchy;

Explanation:
This recursive CTE walks through employee-manager relationships to display a full organizational hierarchy.

Performance Tip
When dealing with large datasets:

• Use indexes on join and filter columns.
• Avoid using SELECT *; only fetch required columns.
• Check execution plans using SQL Server Management Studio (SSMS).
• Use temp tables or CTEs for readability and modularization.

Conclusion
Complex SQL queries are powerful tools for solving real-world data problems — from business intelligence reports to analytics dashboards. By mastering subqueries, CTEs, window functions, and pivots, you can transform SQL Server from a data store into an insight engine.

Keep practicing on realistic datasets like AdventureWorks or Northwind — and soon, you’ll write elegant and efficient SQL like a pro!

Next Steps:

• Try rewriting one of your existing reports using a CTE.
• Use RANK() and PIVOT for analytics dashboards.
• Explore query optimization using SET STATISTICS IO, TIME ON.

SQL Authentication
SQL Authentication, also known as SQL Login, stores credentials (username and password) inside the SQL Server instance. It’s a database-managed authentication model, independent of Active Directory or Windows accounts.

Features

• Uses login name and password stored in SQL Server.
• Ideal for non-domain clients or cross-platform applications.
• Supports backward compatibility for legacy systems.

Advantages

• Works outside of domain networks.
• Simple to set up for service accounts or third-party tools.
• Enables easy app-level authentication control.

Disadvantages

• Passwords must be managed manually.
• No centralized policy enforcement (e.g., MFA).
• Slightly higher attack surface if passwords are weak.

Best Practices

• Use strong password policies and encrypt connections (TLS).
• Restrict login privileges.
• Periodically rotate credentials and audit logins.

Windows Authentication
Windows Authentication (also known as Integrated Security or Trusted Connection) uses the Windows or Active Directory identity of the user to authenticate.
It’s the most secure and preferred option for enterprise setups.

Features

• Uses Kerberos or NTLM protocols for verification.
• Credentials are not sent directly to SQL Server.
• Enables Single Sign-On (SSO) via Active Directory.

Advantages

• Centralized identity control via AD.
• Strong password and lockout policies automatically applied.
• Easy role-based access using AD groups.

Disadvantages

• Requires domain membership or trust relationships.
• Not ideal for external or Linux-based clients.

Best Practices

• Prefer Kerberos over NTLM for better security.
• Use AD groups for permission management.
• Audit AD memberships regularly.

Other Authentication Methods (Short Notes)

Summary Table

In order to generate a data backup from the current tables (Tables 1 and 2),

SELECT INTO and INSERT INTO are two popular SQL techniques that you can employ.

Whether or not backup tables are already in place determines the option.

1. Quick Backup with Table Creation using SELECT INTO
Using this method, data from the source table is directly copied into a new table.

SELECT * INTO table1_Backup FROM table1;
SELECT * INTO table2_Backup FROM table2;

• Note: This will fail if table1_Backup or table2_Backup already exist.
• Ideal for one-time backups or quick cloning.

 2. Using INSERT INTO – Backup into Existing Tables

Use this method when the backup tables are already created (i.e., the schema is predefined).

INSERT INTO table1_Backup SELECT * FROM table1;
INSERT INTO table2_Backup SELECT * FROM table2;

• No error if backup tables already exist.
• Useful for regular/daily backups where the structure is fixed.

Summary

Data is often spread across several SQL Servers or even distinct database systems (such as Oracle, MySQL, or PostgreSQL) in enterprise settings.  SQL Server provides a robust feature called Linked Server that enables you to access and manipulate data across several servers as if they were a single database, eliminating the need for manual data exporting and import.

Using real-world examples, this article describes what a linked server is, how to set it up, and how to query remote data.

What is a Linked Server?
A Linked Server in SQL Server allows you to connect to another database server instance (on the same network or remote) and execute distributed queries (SELECT, INSERT, UPDATE, DELETE) against OLE DB data sources outside of the local SQL Server.

It enables:

• Cross-server queries
• Centralized data access
• Remote procedure execution (EXECUTE AT)
• Joining tables from different servers

Setting Up a Linked Server
You can create a Linked Server via SQL Server Management Studio (SSMS) or T-SQL script.

Method 1: Using SSMS GUI
Open SSMS → Expand Server Objects → Right-click on Linked Servers → Choose New Linked Server

In the dialog box:

• Linked server: Enter an alias name (e.g., LinkedServer_Prod)
• Server type: Choose SQL Server or Other data source
• Provider: Select Microsoft OLE DB Provider for SQL Server
• Data source: Enter remote server name or IP

Go to the Security tab and configure credentials:

• Option 1: Use the current user’s security context
• Option 2: Specify a remote login and password

Click OK to create the Linked Server.

Method 2: Using T-SQL Script
Here’s how to create a Linked Server using SQL script:
EXEC sp_addlinkedserver
    @server = 'LinkedServer_Prod',
    @srvproduct = '',
    @provider = 'SQLNCLI',
    @datasrc = '192.168.1.100'; -- Remote Server IP or Name

EXEC sp_addlinkedsrvlogin
    @rmtsrvname = 'LinkedServer_Prod',
    @useself = 'false',
    @locallogin = NULL,
    @rmtuser = 'sa',
    @rmtpassword = 'StrongPassword123';

Example: Querying Data from a Linked Server
Once the Linked Server is created, you can query it using four-part naming convention:
[LinkedServerName].[DatabaseName].[SchemaName].[TableName]

Example 1: Simple SELECT query
SELECT TOP 10 *
FROM LinkedServer_Prod.SalesDB.dbo.Customers;

Example 2: Joining Local and Remote Tables
SELECT
    a.OrderID,
    a.CustomerID,
    b.CustomerName
FROM LocalDB.dbo.Orders a
INNER JOIN LinkedServer_Prod.SalesDB.dbo.Customers b
    ON a.CustomerID = b.CustomerID;

Example 3: Executing Remote Stored Procedure
EXEC LinkedServer_Prod.SalesDB.dbo.sp_GetTopCustomers @TopCount = 5;

Updating Remote Data
You can even insert or update remote tables via Linked Server.

Example 4: Insert into remote table
INSERT INTO LinkedServer_Prod.SalesDB.dbo.Customers (CustomerName, City)
VALUES ('Peter', 'London');

Example 5: Update remote data
UPDATE LinkedServer_Prod.SalesDB.dbo.Customers
SET City = 'Udaipur'
WHERE CustomerID = 101;

Best Practices

• Use SQL authentication with strong passwords for remote login.
• Enable RPC and RPC Out only if needed.
• Use OPENQUERY() for performance optimization with complex joins.
• Limit access by creating specific database roles and permissions.

Using OPENQUERY (Performance Tip)
Instead of the four-part naming method, use OPENQUERY to push the query execution to the remote server:
SELECT *
FROM OPENQUERY(LinkedServer_Prod, 'SELECT CustomerName, City FROM SalesDB.dbo.Customers WHERE City = ''London''');

This approach reduces data transfer and often performs faster.

Removing a Linked Server
When you no longer need a Linked Server, remove it safely:
EXEC sp_dropserver 'LinkedServer_Prod', 'droplogins';

Conclusion
Linked Servers in SQL Server are a powerful way to integrate and access distributed data sources without complex ETL processes. With proper configuration, they can significantly improve data collaboration and reduce maintenance efforts across multiple systems.

However, always monitor performance and secure connections to prevent unauthorized access.

One of the most common problems in SQL development is retrieving the top N rows per group. For example, if you’re working with sales data, you might want to fetch the top 3 sales per customer or the highest salary per department. This query pattern is widely used in reporting, analytics, and data visualization.

In this guide, we’ll explain step by step how to write SQL queries to get top N records per group using simple and advanced techniques. We’ll cover approaches for popular databases like SQL Server, MySQL, and PostgreSQL.

Why Do We Need Top N Records Per Group?
Imagine you have an Orders table, and you want to get the top 2 recent orders for each customer. Without grouping, you’d only get the overall top records. With grouping, you can analyze data per category, user, or department.

Real-world use cases include:

• Getting the top 3 highest-paid employees per department
• Fetching the latest 5 transactions per user
• Retrieving the top 2 selling products in each region

Method 1. Using ROW_NUMBER() in SQL Server
The most common solution uses the ROW_NUMBER() window function.

Example: Top 2 orders per customer
SELECT *
FROM (
    SELECT
        CustomerID,
        OrderID,
        OrderDate,
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS RowNum
    FROM Orders
) AS Ranked
WHERE RowNum <= 2;

Explanation

• ROW_NUMBER() assigns a unique number to each row within a group.
• PARTITION BY CustomerID ensures numbering starts fresh for each customer.
• ORDER BY OrderDate DESC sorts records so the most recent orders are ranked first.
• Finally, WHERE RowNum <= 2 filters top 2 per group.

Method 2. Using RANK() or DENSE_RANK()
Sometimes you want ties to be included. Instead of ROW_NUMBER(), you can use RANK() or DENSE_RANK().
SELECT *
FROM (
    SELECT
        Department,
        EmployeeName,
        Salary,
        RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
    FROM Employees
) AS Ranked
WHERE Rank <= 3;

Difference
RANK() allows skipping numbers in case of ties.
DENSE_RANK() keeps numbers continuous.

Method 3. Using CROSS APPLY (SQL Server)
Another efficient way in SQL Server is with CROSS APPLY.
SELECT c.CustomerID, o.OrderID, o.OrderDate
FROM Customers c
CROSS APPLY (
    SELECT TOP 2 *
    FROM Orders o
    WHERE o.CustomerID = c.CustomerID
    ORDER BY o.OrderDate DESC
) o;

Why it works: For each customer, SQL applies a subquery to fetch top 2 rows.

Method 4. MySQL and PostgreSQL (LIMIT + Subquery)
In MySQL and PostgreSQL (before window functions were supported), developers used correlated subqueries.
SELECT o1.*
FROM Orders o1
WHERE (
   SELECT COUNT(*)
   FROM Orders o2
   WHERE o2.CustomerID = o1.CustomerID
     AND o2.OrderDate > o1.OrderDate
) < 2;

Here, each row checks how many newer rows exist. If fewer than 2, then it’s in the top 2.

Common Mistakes

• Forgetting PARTITION BY, which groups the data.
• Using TOP without applying grouping logic.
• Not ordering correctly, which gives wrong results.

Best Practices

• Always use ROW_NUMBER() when you need unique ordering.
• Use RANK() or DENSE_RANK() if ties matter.
• Make sure you filter correctly (WHERE RowNum <= N).
• For very large datasets, ensure indexes exist on partition and order by columns.

SQL Server Skill Challenge, Test What You Learned!
Now that you’ve learned how to fetch top N records per group in SQL, it’s time to put your skills to the test! 🎉

Take the SQL Server Skill Challenge and apply what you’ve learned. You’ll earn Sharp Tokens 🏆 as a reward for your knowledge!

Don’t just read—apply your skills and get rewarded today!.

Conclusion
Getting the top N records per group in SQL is a common but tricky problem. With the right use of window functions (ROW_NUMBER, RANK, DENSE_RANK) or techniques like CROSS APPLY and LIMIT, you can write efficient queries for SQL Server, MySQL, and PostgreSQL.

HostForLIFEASP.NET SQL Server 2022 Hosting