Top SQL Server Interview Questions and Answers (2025)

Question: What is the ROW_NUMBER() function in SQL?

Answer:

The ROW_NUMBER() function in SQL is a window function that assigns a unique sequential integer to rows within a result set. The numbering starts at 1 for the first row and increments by 1 for each subsequent row in the specified order. It is typically used to assign a “rank” or “row number” to rows in a result set based on a particular order.

Key Points:

1. Window Function: ROW_NUMBER() is often used in the context of a window, which means that it can operate over a subset of data (called a “window”) defined by the PARTITION BY clause. If no partitioning is needed, it can be used over the entire result set.
2. Order: The ROW_NUMBER() function requires an ORDER BY clause to determine the sequence in which the row numbers are assigned.
3. Uniqueness: Each row gets a unique row number within the specified partition (or the entire result set if no partition is defined).

---

Syntax:

SELECT ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_num
FROM table_name;

• PARTITION BY column1: (Optional) Divides the result set into partitions to which the ROW_NUMBER() function is applied. If omitted, the entire result set is treated as a single partition.
• ORDER BY column2: Defines the order in which the row numbers are assigned.
• row_num: The alias for the column that holds the row numbers.

---

Example 1: Simple ROW_NUMBER() with no partition

Query:

SELECT EmployeeID, Name, Department,
       ROW_NUMBER() OVER (ORDER BY EmployeeID) AS row_num
FROM Employees;

Result:

EmployeeID	Name	Department	row_num
1	John	HR	1
2	Sarah	IT	2
3	David	Finance	3
4	Mike	HR	4

• In this example, the rows are ordered by EmployeeID, and a unique sequential row number is assigned to each row.

---

Example 2: Using ROW_NUMBER() with PARTITION BY

Query:

SELECT EmployeeID, Name, Department,
       ROW_NUMBER() OVER (PARTITION BY Department ORDER BY EmployeeID) AS row_num
FROM Employees;

Result:

EmployeeID	Name	Department	row_num
1	John	HR	1
4	Mike	HR	2
2	Sarah	IT	1
3	David	Finance	1

• In this example, the ROW_NUMBER() function is applied to each department separately (due to PARTITION BY Department).
• The row number is assigned to each row within each department, and the order within each department is determined by the EmployeeID.

---

Use Cases:

1. Pagination: ROW_NUMBER() is commonly used for paginating query results. For example, if you want to retrieve a specific “page” of records, you can use ROW_NUMBER() to assign row numbers and filter the rows for the desired page.

   Example for pagination:

   SELECT * 
   FROM (
       SELECT EmployeeID, Name, Department,
              ROW_NUMBER() OVER (ORDER BY EmployeeID) AS row_num
       FROM Employees
   ) AS numbered_employees
   WHERE row_num BETWEEN 11 AND 20;

2. Ranking: It can be used to assign a sequential rank to rows, especially in cases where you need to identify the “top N” rows or a specific rank of rows in a dataset.

   Example for ranking:

   SELECT EmployeeID, Name, Salary,
          ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rank
   FROM Employees;

3. Identifying duplicates: ROW_NUMBER() can help identify duplicates by partitioning the data and ordering it to check for repeat values.

---

Limitations:

• Not suitable for ties: Unlike RANK() or DENSE_RANK(), ROW_NUMBER() does not handle ties. Each row gets a unique number, even if rows have the same values.
• Performance: Depending on the size of the dataset and the complexity of the query, the ROW_NUMBER() function can impact query performance, especially when partitioning and ordering over large datasets.

---

Conclusion:

The ROW_NUMBER() function is a versatile SQL function useful for creating unique sequential numbers for rows within a result set. It is particularly useful for tasks like pagination, ranking, or partitioning data for analysis.

Question: How do you fetch only odd or even rows from a table in SQL?

Answer:

In SQL, you can fetch odd or even rows by utilizing the ROW_NUMBER() window function. This function assigns a unique sequential integer to rows within a result set, which can then be used to filter for odd or even rows.

Here’s how you can approach it:

Steps:

1. Assign Row Numbers: Use the ROW_NUMBER() function to assign a sequential number to each row in the result set.
2. Filter Rows: After assigning row numbers, use the modulus operator (%) to filter for odd or even rows.

---

Example 1: Fetching Odd Rows (1st, 3rd, 5th, etc.)

Query:

WITH NumberedRows AS (
    SELECT EmployeeID, Name, Department,
           ROW_NUMBER() OVER (ORDER BY EmployeeID) AS row_num
    FROM Employees
)
SELECT EmployeeID, Name, Department
FROM NumberedRows
WHERE row_num % 2 <> 0;

Explanation:

• ROW_NUMBER() assigns a unique sequential number to each row ordered by EmployeeID.
• WHERE row_num % 2 <> 0 filters out the even-numbered rows and keeps only the odd-numbered ones (i.e., rows with odd row numbers).

---

Example 2: Fetching Even Rows (2nd, 4th, 6th, etc.)

Query:

WITH NumberedRows AS (
    SELECT EmployeeID, Name, Department,
           ROW_NUMBER() OVER (ORDER BY EmployeeID) AS row_num
    FROM Employees
)
SELECT EmployeeID, Name, Department
FROM NumberedRows
WHERE row_num % 2 = 0;

Explanation:

• ROW_NUMBER() again assigns a sequential number to each row.
• WHERE row_num % 2 = 0 filters out the odd-numbered rows and keeps only the even-numbered ones (i.e., rows with even row numbers).

---

Notes:

• The ROW_NUMBER() function is useful when you need to work with a specific order, such as ordering by a primary key (EmployeeID in this example). You can change the ORDER BY clause inside the ROW_NUMBER() function depending on how you want to order the rows.
• These queries can be modified to handle partitions (e.g., PARTITION BY Department in case you want to fetch odd or even rows per department).

---

Conclusion:

By using the ROW_NUMBER() function with the modulus operator (%), you can easily filter and fetch either odd or even rows from a table. This approach provides flexibility for more complex queries where you may want to work with specific rows based on their order.

Read More

If you can’t get enough from this article, Aihirely has plenty more related information, such as SQL interview questions, SQL interview experiences, and details about various SQL job positions. Click here to check it out.