SQL - Other

Query Profiling Statements

Many database management systems offer built-in SQL statements for profiling queries. For example, in MySQL, you can use the EXPLAIN statement to see how the database optimizer plans to execute a query. In PostgreSQL, you can use EXPLAIN ANALYZE for even more detailed information


-- Explain a slow query to see its execution plan
EXPLAIN SELECT * FROM your_table WHERE condition;

-- Analyze a slow query in more detail
EXPLAIN ANALYZE SELECT * FROM your_table WHERE condition;

RANK()

Returns the rank of each row within the partition of a result set. The rank of a row is one plus the number of ranks that come before the row in question.


SELECT
	product_id,
	product_name,
	list_price,
	RANK () OVER ( 
		ORDER BY list_price DESC
	) price_rank 
FROM
	production.products;

Recursive Queries

Recursive queries traverse hierarchical or graph-like structures.


WITH RECURSIVE employee_hierarchy AS (
    SELECT employee_id, manager_id FROM employees WHERE manager_id IS NULL
    UNION ALL
    SELECT e.employee_id, e.manager_id
    FROM employees e
    JOIN employee_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM employee_hierarchy;


Retrieve Table and Column Names in SQL Server

This query retrieves the names of all tables and their corresponding columns in the current SQL Server database. It joins sys.tables (which stores information about all user-defined tables) with sys.columns (which stores information about table columns) using the object_id. This helps in understanding the structure of the database schema.

SELECT 
    T.name AS TableName, 
    C.name AS ColumnName
FROM sys.tables T
JOIN sys.columns C 
    ON T.object_id = C.object_id;


Retrieve Top 10 Longest Running Queries in SQL Server

This query fetches the top 10 longest-running queries based on total elapsed time from the SQL Server dynamic management views (DMVs). It provides details such as the SQL handle, total execution time, execution count, and the actual query text.

SELECT TOP 10
    qs.sql_handle,
    qs.total_elapsed_time,
    qs.execution_count,
    SUBSTRING(qt.text, qs.statement_start_offset / 2 + 1,
        ((CASE qs.statement_end_offset
            WHEN -1 THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2
            ELSE qs.statement_end_offset END) - qs.statement_start_offset) / 2 + 1
    ) AS query_text
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
ORDER BY qs.total_elapsed_time DESC;


  • The sys.dm_exec_query_stats DMV stores performance statistics of cached query plans.
  • The sys.dm_exec_sql_text function retrieves the SQL text associated with a specific query plan.
  • The query orders results in descending order by total elapsed time, helping identify slow-performing queries for optimization.

Rollup and Cube for Aggregated Summaries

ROLLUP and CUBE generate hierarchical or multidimensional aggregated summaries.


SELECT product_id, region, SUM(sales)
FROM sales_data
GROUP BY ROLLUP (product_id, region);


ROW_NUMBER()

The ROW_NUMBER() is a window function that assigns a sequential integer to each row within the partition of a result set. The row number starts with 1 for the first row in each partition.


SELECT 
   ROW_NUMBER() OVER (
	ORDER BY first_name
   ) row_num,
   first_name, 
   last_name, 
   city
FROM 
   sales.customers;

Split String

STRING_SPLIT (string, separator)

SQL Date/Time Storage and Precision Guide

datetime

Stores date and time; range from 1753 to 9999; about 3ms precision; uses 8 bytes; suitable for legacy systems.


datetime2

Improved version of datetime; range from 0001 to 9999; supports up to 7-digit fractional seconds; uses 6–8 bytes; preferred for new applications.


datetimeoffset

Like datetime2 but includes time zone offset; ideal for global or multi-time zone data; uses 8–10 bytes.


date

Stores only the date (no time); range from 0001 to 9999; uses 3 bytes; useful for birthdates, holidays, and any date-only fields.


time

Stores only the time (no date); supports precision up to 7 fractional seconds; uses 3–5 bytes; ideal for durations or business hours.

SQL Operation Order

FROM

SELECT *
FROM employees;


JOIN (and ON)

SELECT e.name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;


WHERE

SELECT name, salary
FROM employees
WHERE salary > 50000;


GROUP BY

SELECT department_id, COUNT(*)
FROM employees
GROUP BY department_id;


ROLLUP, CUBE, GROUPING SETS

// ROLLUP: Adds subtotal rows.
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, job_id);

// CUBE: Adds subtotal rows for all combinations.
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY CUBE(department_id, job_id);

// GROUPING SETS: Allows custom grouping combinations.
SELECT department_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS ((department_id), (job_id), ());


HAVING

SELECT department_id, AVG(salary)
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 60000;


OVER (e.g., Window Functions)

SELECT name, salary, 
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;


SELECT DISTINCT

SELECT DISTINCT job_id
FROM employees;


UNION, INTERSECT, EXCEPT

// UNION
SELECT name FROM employees
UNION
SELECT name FROM contractors;

// INTERSECT
SELECT name FROM employees
INTERSECT
SELECT name FROM contractors;

// EXCEPT
SELECT name FROM employees
EXCEPT
SELECT name FROM contractors;


ORDER BY

SELECT name, salary
FROM employees
ORDER BY salary DESC;


OFFSET, FETCH FIRST/NEXT ROWS ONLY

SELECT name, salary
FROM employees
ORDER BY salary DESC
OFFSET 10 ROWS
FETCH NEXT 5 ROWS ONLY;


LIMIT, TOP

// LIMIT
SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

// TOP
SELECT TOP 5 name, salary
FROM employees
ORDER BY salary DESC;

SQL Query Runs Fast in SSMS but Slow in Your App? Here's Why!

ARITHABORT


The main culprit or I would say rescuer is a SQL Server setting called ARTIHABORT! ARITHABORT is a SQL Server session-level setting that controls how SQL Server handles arithmetic errors, such as division by zero or overflow errors.


  • When ARITHABORT = ON, SQL Server immediately terminates a query if an arithmetic error occurs.
  • When ARITHABORT = OFF, SQL Server continues execution and returns a warning instead of terminating the query.


Based on ARITHABORT ON or OFF, SQL Server generates different plans. A suboptimal execution plan be chosen when running from the application, causing a performance difference. Also, with ARITHABORT OFF, SQL Server may not use the same indexed path as with ARITHABORT ON, leading to slower performance. As ARITHABORT controls the arithmetic operation flow, it significantly affect the execution plan path.


In SSMS, it is set to ON by default. But from application, we need to explicitly set it to ON to get the same query execution time. From ADO.NET, you can set it like below before executing any query:

using (SqlCommand comm = new SqlCommand("SET ARITHABORT ON", connection))
{
    comm.ExecuteNonQuery();
}


Or, set it at the beginning of any Stored Procedure as

SET ARITHABORT ON;


This will get you the same execution time in all cases. But then again, SQL Server or any other relational database is an overly complex and vast arena to handle. Sometimes, you may have to check with OPTION (RECOMPILE) to get all environments a fresh set of plans. Thing about parameter sniffing as well.

SQL Server Databases Overview

This query retrieves general information about all databases on your SQL Server instance. It provides details such as the database name, ID, creation date, and state description. You can use this table to monitor and manage databases, including checking their status, creation time, and availability.

SELECT name, database_id, create_date, state_desc
FROM sys.databases;