SQL Server STRING_AGG() function to concatenate rows of strings into one string with a specified separator. It does not add the separator at the end of the result string. Syntax is STRING_AGG ( input_string, separator ) [ order_clause ]

• input_string is any type that can be converted VARCHAR and NVARCHAR when concatenation.
• separator is the separator for the result string. It can be a literal or variable.
• order_clause specifies the sort order of concatenated results using WITHIN GROUP clause.

To generate lists of emails of customers by the city

SELECT
    city, 
    STRING_AGG(email,';') email_list
FROM
    sales.customers
GROUP BY
    city;

To sort the email list, you use the WITHIN GROUP clause

SELECT
    city, 
    STRING_AGG(email,';') 
        WITHIN GROUP (ORDER BY email) email_list
FROM
    sales.customers
GROUP BY
    city;

The STRING_SPLIT() function is a table-valued function that splits a string into a table that consists of rows of substrings based on a specified separator. Syntax is STRING_SPLIT ( string , separator [ , enable_ordinal ] )

select value from string_split('hakan','a')

Output:
h 
k 
n

The STUFF() function deletes a part of a string and then inserts another part into the string.

Syntax: STUFF(string, start, length, new_string)

string: The string to be modified
start: The position in string to start to delete some characters
length: The number of characters to delete from string
new_string: The new string to insert into string at the start position

SELECT STUFF('SQL is important', 5, 2, 'is really ');

Output:
SQL is really important

Time-series analysis uses functions like LAG and LEAD to compare rows in sequence.

SELECT 
    customer_id,
    purchase_date,
    amount,
    LAG(amount) OVER (PARTITION BY customer_id ORDER BY purchase_date) AS previous_purchase
FROM 
    sales;

The sys.dm_exec_requests view provides information about currently executing requests (queries or commands). Each row represents a single query or command being executed by a session.

Key Columns:

• session_id: The ID of the session making the request.
• request_id: A unique identifier for the request within a session.
• database_id: The ID of the database being accessed by the request.
• blocking_session_id: The ID of the session that is blocking the current request (if any).
• command: The current command being executed (e.g., SELECT, INSERT).
• wait_type: If the request is waiting, this column shows the type of wait (e.g., lock, I/O).
• start_time: The time the request started execution.

Use Case:

sys.dm_exec_requests is useful for monitoring active queries or requests, identifying blocking sessions, and analyzing long-running queries.

Example Query:

SELECT session_id, request_id, database_id, blocking_session_id, command, start_time
FROM sys.dm_exec_requests;

Triggers automatically execute a specified action in response to certain events on a table, such as INSERT, UPDATE, or DELETE.

CREATE TRIGGER update_inventory
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
    UPDATE inventory SET quantity = quantity - NEW.quantity_sold WHERE product_id = NEW.product_id;
END;

TRIM is introduced in SQL Server (starting with 2017).

In older version of SQL Server to perform trim you have to use LTRIM and RTRIM like following.

RTRIM(LTRIM(@variable))

The TRUNCATE TABLE command deletes the data inside a table, but not the table itself.

TRUNCATE TABLE Categories;

Primary Key

A Primary key is a table attribute or column that identify every record present in a table uniquely.

There can be only one primary key attribute in a table and primary key column elements can't be same or Null values.

Super Key

It is the set of all columns which help to identify rows in a table uniquely.

Candidate key

A candidate key is a column or a set of columns that can qualify as a primary key in the database. There can be multiple candidate keys in a table and only one candidate key can qualify as the primary key.

Composite Key

It is a set of two or more attributes that helps to identify each tuple in a table uniquely. Any key such as super, primary, or candidate key etc. can be called composite key if it has more than one attributes.

Foreign Key

A foreign key is a key used to link two tables together. This is sometimes also called as a referencing key.



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);