SELECT t1.id + 1 AS missing_id
FROM table_name t1
LEFT JOIN table_name t2
ON t1.id + 1 = t2.id
WHERE t2.id IS NULL;

• Join each row to the next expected row.
• Filter out rows where the next ID exists.

sys.dm_os_wait_stats is a Dynamic Management View that provides information about the waits that are occurring in SQL Server. It captures data on the different types of waits and their durations, which helps in identifying performance bottlenecks.

Key Columns:

• wait_type: The type of wait that occurred (e.g., PAGEIOLATCH_EX, CXPACKET).
• wait_time_ms: Total time (in milliseconds) that the wait has occurred.
• waiting_tasks_count: Number of tasks currently waiting for this wait type.
• signal_wait_time_ms: Time spent waiting for signals (often related to synchronization).
• resource_wait_time_ms: Time spent waiting for resources.

Use Case:

Analyzing wait statistics helps diagnose performance issues by showing where SQL Server spends time waiting for resources. For instance, high waits on PAGEIOLATCH might indicate slow disk I/O, while high waits on CXPACKET might suggest parallelism issues.

Example Query:

SELECT 
    wait_type, 
    wait_time_ms, 
    waiting_tasks_count, 
    signal_wait_time_ms, 
    resource_wait_time_ms
FROM sys.dm_os_wait_stats
WHERE wait_type NOT LIKE 'SQLTRACE%' -- Exclude irrelevant wait types
ORDER BY wait_time_ms DESC;

Dynamic SQL generates and executes SQL statements dynamically during runtime.

SET @query = 'SELECT * FROM ' + table_name;
PREPARE stmt FROM @query;
EXECUTE stmt;

The EXISTS operator is used to test for the existence of any record in a subquery.

The EXISTS operator returns TRUE if the subquery returns one or more records.

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition);

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%column name%' ORDER BY TABLE_NAME

The FIND_IN_SET() function returns the position of a string within a list of strings.

Syntax

FIND_IN_SET(string, string_list)

Parameter Values

     string - Required. The string to search for
string_list - Required. The list of string values to be searched (separated by commas)

Return Values

If string is not found in string_list, this function returns 0
If string or string_list is NULL, this function returns NULL
If string_list is an empty string (""), this function returns 0

Example

SELECT FIND_IN_SET("q", "s,q,l");

SELECT column_name, COUNT(*)
FROM table_name
GROUP BY column_name
HAVING COUNT(*) > 1;

• Group rows by the column you're checking.
• Use HAVING to filter groups with multiple rows.

SELECT a.*, b.*
FROM reservations a
JOIN reservations b
ON a.start_date < b.end_date
AND a.end_date > b.start_date
AND a.id <> b.id;

• Check if the date ranges intersect.
• Exclude the same row from matching itself.

SELECT AVG(salary) AS median
FROM (
    SELECT salary
    FROM employees
    ORDER BY salary
    LIMIT 2 - (SELECT COUNT(*) FROM employees) % 2
    OFFSET (SELECT (COUNT(*) - 1) / 2 FROM employees)
) subquery;

• Find the middle values using LIMIT and OFFSET.
• Average them if necessary to compute the median.

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET n - 1;

• Use ORDER BY to sort salaries.
• Skip n-1 rows using OFFSET.

SELECT MAX(salary) AS second_highest_salary
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

• The inner query finds the highest salary.
• The outer query finds the maximum salary that is less than the highest.

Full-text search enables efficient text searching within large textual datasets using indexes.

SELECT 
    title
FROM 
    articles
WHERE 
    MATCH(content) AGAINST('SQL optimization' IN NATURAL LANGUAGE MODE);