CASE statements allow conditional expressions directly within queries.

SELECT 
    customer_id,
    CASE 
        WHEN amount > 100 THEN 'High'
        ELSE 'Low'
    END AS customer_category
FROM 
    sales;

JSON functions allow you to store, query, and manipulate JSON data within SQL tables.

SELECT 
    json_data->>'customer_name' AS customer_name,
    json_data->>'order_amount' AS order_amount
FROM 
    orders
WHERE 
    json_data->>'status' = 'shipped';

MERGE combines INSERT and UPDATE operations to handle conflicts during data upserts.

MERGE INTO customers AS target
USING incoming_data AS source
ON target.id = source.id
WHEN MATCHED THEN UPDATE SET name = source.name
WHEN NOT MATCHED THEN INSERT (id, name) VALUES (source.id, source.name);

Temporary tables store intermediate results within a session and are automatically dropped afterward.

CREATE TEMPORARY TABLE temp_sales AS
SELECT * FROM sales WHERE sale_date > '2024-01-01';

Views abstract the underlying table structure and restrict access to sensitive data.

CREATE VIEW public_user_data AS
SELECT user_id, user_name FROM users;

WINDOW FRAME allows fine-grained control over the rows included in a calculation within a window function.

SELECT 
    order_id,
    order_date,
    SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS rolling_sum
FROM 
    orders;

To see currently executing requests on the SQL Server instance, you can query the sys.dm_exec_requests dynamic management view.

SELECT * FROM sys.dm_exec_requests;

You can identify blocking transactions by querying the sys.dm_tran_locks dynamic management view.

SELECT * FROM sys.dm_tran_locks WHERE request_session_id IN 
(SELECT session_id FROM sys.dm_exec_requests WHERE blocking_session_id <> 0);

You can use the sys.dm_exec_connections dynamic management view to retrieve information about current connections to the SQL Server instance.

SELECT * FROM sys.dm_exec_connections;

The sys.dm_exec_sessions dynamic management view provides information about the sessions that are established on the SQL Server instance.

SELECT * FROM sys.dm_exec_sessions;

To monitor which sessions are waiting for a particular resource, you can use the sys.dm_os_waiting_tasks dynamic management view.

SELECT * FROM sys.dm_os_waiting_tasks;

Window Functions perform calculations across a subset of rows (called a "window") that are defined relative to the current row. They allow you to perform operations like ranking, running totals, and differences between rows, all while retaining individual rows in the result set.

SELECT 
    customer_id,
    purchase_date,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS running_total
FROM 
    purchase_data;

• SUM(amount) calculates a running total for each customer.
• OVER (PARTITION BY customer_id ORDER BY purchase_date) defines the "window" of rows over which the calculation is performed, grouped by customer_id and ordered by purchase_date.