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;

-- GET_EMPLOYEE_LIST 'E','first_name','DESC',0,20
CREATE OR ALTER   PROCEDURE [dbo].[GET_EMPLOYEE_LIST]
@SEARCH_TEXT AS VARCHAR(50)='',
@SORT_COLUMN_NAME AS VARCHAR(50)='',
@SORT_COLUMN_DIRECTION AS VARCHAR(50)='',
@START_INDEX AS INT=0,
@PAGE_SIZE AS INT=10
AS
BEGIN
 DECLARE @QUERY AS VARCHAR(MAX)='',@ORDER_QUERY AS VARCHAR(MAX)='',@CONDITIONS AS VARCHAR(MAX)='',
 @PAGINATION AS VARCHAR(MAX)=''

 SET @QUERY='SELECT * FROM Employees '

 -- SEARCH OPERATION
 IF(ISNULL(@SEARCH_TEXT,'')<>'')
 BEGIN
  IF(ISDATE(@SEARCH_TEXT)=1) SET @CONDITIONS=' WHERE CAST(date_of_birth AS DATE)=CAST('+@SEARCH_TEXT+'AS DATE)'
  ELSE IF(ISNUMERIC(@SEARCH_TEXT)=1)
  BEGIN
   SET @CONDITIONS=' WHERE salary='+@SEARCH_TEXT+' OR phone_number= CAST('+@SEARCH_TEXT+'AS VARCHAR(50))'
  END
  ELSE
  BEGIN
   SET @CONDITIONS='
   WHERE
   first_name LIKE ''%'+@SEARCH_TEXT+'%''
   OR first_name +'' ''+last_name LIKE ''%'+@SEARCH_TEXT+'%''
   OR last_name LIKE ''%'+@SEARCH_TEXT+'%''
   OR email LIKE ''%'+@SEARCH_TEXT+'%''
   OR gender LIKE ''%'+@SEARCH_TEXT+'%''
   OR department LIKE ''%'+@SEARCH_TEXT+'%''
   OR phone_number LIKE ''%'+@SEARCH_TEXT+'%''
  '
  END
 END

 -- SORT OPERATION
 IF(ISNULL(@SORT_COLUMN_NAME,'')<>'' AND ISNULL(@SORT_COLUMN_DIRECTION,'')<>'')
 BEGIN
  SET @ORDER_QUERY=' ORDER BY '+@SORT_COLUMN_NAME+' '+@SORT_COLUMN_DIRECTION
 END
 ELSE SET @ORDER_QUERY=' ORDER BY ID ASC'

 -- PAGINATION OPERATION
 IF(@PAGE_SIZE>0)
 BEGIN
  SET @PAGINATION=' OFFSET '+(CAST(@START_INDEX AS varchar(10)))+' ROWS
  FETCH NEXT '+(CAST(@PAGE_SIZE AS varchar(10)))+' ROWS ONLY'
 END

 IF(@CONDITIONS<>'') SET @QUERY+=@CONDITIONS
 IF(@ORDER_QUERY<>'') SET @QUERY+=@ORDER_QUERY
 IF(@PAGINATION<>'') SET @QUERY+=@PAGINATION

 PRINT(@QUERY)
 EXEC(@QUERY)
END

Stored procedures are precompiled SQL scripts that can be executed with parameters to automate tasks.

CREATE PROCEDURE GetCustomerOrders (IN customer_id INT)
BEGIN
    SELECT * FROM orders WHERE customer_id = customer_id;
END;

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.