SQL Reference

Partition

SELECT product_category, sales_date, revenue
, ROW_NUMBER() OVER (PARTITION BY product_category ORDER BY revenue DESC) AS rn
FROM sales;

SELECT CustomerID, SalesPersonID
, COUNT(*) OVER (PARTITION BY SalesPersonID) AS CountBySalesPersonID
FROM Sales.SalesOrderHeader AS soh
WHERE soh.CustomerID IN (29718, 30052, 29890);
SELECT * FROM table_name;

Date format

-- Example: Get current date in MM/DD/YYYY format (style 101)
SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS FormattedDate; 
-- Output: 02/21/2026

-- Example: Get current date in DD/MM/YYYY format (style 103)
SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS FormattedDate; 
-- Output: 21/02/2026

-- Example: Get current date in YYYY.MM.DD format (style 102)
SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS FormattedDate; 
-- Output: 2026.02.21

    Microsoft Link - Date Numbers
https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-ver17

--Add one year to the current date
SELECT DATEADD(year, 1, GETDATE());

--Add one year to a date column
SELECT DATEADD(year, 1, ) AS NextYearDate FROM Orders;

--Add one year and one day to the current date
SELECT DATEADD(day, 1, DATEADD(year, 1, GETDATE())) AS OneYearAndOneDayLater;

-- Example: Custom format using FORMAT()
SELECT FORMAT(GETDATE(), 'MMMM dd, yyyy (dddd)') AS FormattedDate;
-- Output: February 21, 2026 (Saturday)

Pading

-- Method 1
SELECT FORMAT(number_to_format, '0000') AS PaddedNumber;

SELECT FORMAT(5, '0000') AS PaddedNumber;  -- Result: '0005'
SELECT FORMAT(123, '0000') AS PaddedNumber; -- Result: '0123'
SELECT FORMAT(12345, '0000') AS PaddedNumber; -- Result: '12345' (original length is preserved if longer)

-- Method 2
SELECT RIGHT(REPLICATE('0', desired_length) + CAST(number_to_format AS VARCHAR(max_length)), desired_length) AS PaddedNumber;

Stuff

-- Syntax
STUFF ( character_expression , start , length , replace_with_expression )

-- Replacing a part of a string:
SELECT STUFF('abcdef', 2, 3, 'ijklmn');
-- Output: aijklmnef 

-- Inserting a string (deleting zero characters):
SELECT STUFF('SQL Tutorial', 1, 0, 'HTML ');
-- Output: HTML SQL Tutorial

-- Removing a part of a string (inserting an empty string):
SELECT STUFF('Hello, world', 6, 6, '');
-- Output: Hello,

-- Creating a Comma-Separated list
-- Sample data setup
DECLARE @T TABLE(Col1 VARCHAR(1), Col2 INT);
INSERT INTO @T(Col1, Col2) VALUES ('A', 123), ('A', 456), ('A', 789), ('B', 111);

-- Query to aggregate and format the data
SELECT 
    t.Col1,
    STUFF((
        SELECT ',' + CAST(t1.Col2 AS VARCHAR(10))
        FROM @T AS t1
        WHERE t1.Col1 = t.Col1
        FOR XML PATH('') -- Concatenates all Col2 values with a leading comma
    ), 1, 1, '') AS Col2List -- Removes the very first comma and replaces it with an empty string
FROM 
    @T AS t
GROUP BY 
    t.Col1;

-- Output:
-- Col1 | Col2List
-- A    | 123,456,789
-- B    | 111

Database name as a variable

DECLARE @dbName NVARCHAR(128);
DECLARE @sqlQuery NVARCHAR(MAX);

SET @dbName = N'YourTargetDatabaseName'; -- Set your database name here

-- Construct the dynamic SQL command
SET @sqlQuery = N'
USE ' + QUOTENAME(@dbName) + N';
-- Insert the rest of your T-SQL scripts here
SELECT DB_NAME() AS CurrentDatabase;
SELECT * FROM dbo.YourTable;
';

-- Execute the dynamic SQL
EXEC sp_executesql @sqlQuery;

Select queries to run in a multi-query file

-- 1. Declare the control variable
DECLARE @RunLevel INT;

-- 2. Set the value (e.g., 1 for basic, 2 for full)
SET @RunLevel = 2;

-- 3. Use IF logic to control execution
IF @RunLevel >= 1
BEGIN
    PRINT 'Running basic queries...';
    -- Insert your basic queries here
    SELECT 'Basic' AS Running;
END

IF @RunLevel = 2
BEGIN
    PRINT 'Running advanced queries...';
    -- Insert your advanced queries here
    SELECT 'Advanced' AS Running;
END

Modify "Select queries to run in a multi-query file" to stored procedure with paremeter

CREATE PROCEDURE dbo.RunQueriesByLevel
    @RunLevel INT = 1   -- Default run level
AS
BEGIN
    SET NOCOUNT ON;

    -- Level 1: Basic queries
    IF @RunLevel >= 1
    BEGIN
        PRINT 'Running basic queries...';

        -- Insert your basic queries here
        SELECT 'Basic' AS Running;
    END

    -- Level 2: Advanced queries
    IF @RunLevel = 2
    BEGIN
        PRINT 'Running advanced queries...';

        -- Insert your advanced queries here
        SELECT 'Advanced' AS Running;
    END
END;
GO

-- Execute Stored PROCEDURE
EXEC dbo.RunQueriesByLevel @RunLevel = 2;
EXEC dbo.RunQueriesByLevel;   -- defaults to 1

Extract numbers from a string

-- Method 1
SELECT REGEXP_REPLACE(column_name, '[^0-9]', '') AS extracted_numbers
FROM your_table;
-- Method 2
SELECT REGEXP_SUBSTR(column_name, '[0-9]+') AS first_number_sequence
FROM your_table;

OPENROWSET Examples

-- Using OPENROWSET to query a CSV file with a WHERE clause
SELECT *
FROM OPENROWSET(
    BULK 'C:\Data\inventory.csv',
    FORMAT = 'CSV',
    PARSER_VERSION = '2.0'
) AS rows
WHERE status = 'active';

-- Querying a remote SQL Server using OPENROWSET
SELECT *
FROM OPENROWSET(
    'SQLNCLI',
    'Server=MyServer;Trusted_Connection=yes;',
    'SELECT id, name, status FROM MyDatabase.dbo.inventory'
) AS remoteData
WHERE status = 'active';

-- Querying a remote SQL Server using OPENROWSET (with EXEC)
SELECT *
FROM OPENROWSET(
    'SQLNCLI',
    'Server=MyServer;Trusted_Connection=yes;',
    'Exec inv_check'
) AS remoteData
WHERE status = 'active';