SQL Server Date and Time Functions
SELECT DATEADD(day, 5, ‘2024-07-30’); –Add Days to a Date
SELECT DATEADD(day, -5, ‘2024-07-30’); –Subtract Days from a Date
SELECT DATEDIFF(day, ‘2024-07-25’, ‘2024-07-30’); –Calculate the Difference in Days Between Two Dates
SELECT DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0);–Get the First Day of the Month
SELECT DATEADD(day, -1, DATEADD(month, DATEDIFF(month, 0, GETDATE()) + 1, 0)); –Get the Last Day of the Month
SELECT YEAR(‘2024-07-30’); — Extract Year from a date
SELECT MONTH(‘2024-07-30’); — Extract Month from a date
SELECT DAY(‘2024-07-30’); — Extract Day from a date
SELECT FORMAT(GETDATE(), ‘yyyy-MM-dd’); –Format a Date
SELECT DATEPART(week, GETDATE()); –Get the Week Number of the Year
SQL Server String Functions
SELECT ‘Hello’ + ‘ ‘ + ‘World’; –Concatenation
SELECT SUBSTRING(‘Hello World’, 1, 5); –Substring
SELECT LEN(‘Hello World’); –String Length
SELECT UPPER(‘Hello World’); –Uppercase
SELECT LOWER(‘Hello World’); –Lowercase
SELECT TRIM(‘ Hello World ‘); –Trim Whitespace
SELECT REPLACE(‘Hello World’, ‘World’, ‘SQL’); –Replace Substring
SELECT CHARINDEX(‘World’, ‘Hello World’); –Find Position of Substring
SELECT REPLICATE(‘Hello’, 3); –Repeat a String
SELECT REVERSE(‘Hello World’); –Reverse a String
SELECT LEFT(‘Hello’ + REPLICATE(‘ ‘, 10), 10); –Pad a String
SELECT RIGHT(REPLICATE(‘ ‘, 10) + ‘Hello’, 10); –Pad a String
SELECT LEFT(‘Hello World’, 5); — Left Substring
SELECT RIGHT(‘Hello World’, 5); — Right Substring
–Extract domain name from email address
SELECT SUBSTRING(‘bob@yahoo.com’, CHARINDEX(‘@’, ‘bob@yahoo.com’) + 1, LEN(‘bob@yahoo.com’) – CHARINDEX(‘@’, ‘bob@yahoo.com’)) AS domain; — SQL Server
SQL Server Window Functions
Row_Number – Assigns a unique sequential integer to rows within a partition of a result set.
SELECT ID, auto_name, Score, ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNum FROM example_table;
Rank – Assigns a rank to each row within a partition of a result set, with gaps in the ranking where there are ties.
SELECT ID, auto_name, Score, RANK() OVER (ORDER BY Score DESC) AS Rank FROM example_table;
Dense_Rank – Similar to RANK(), but without gaps in the ranking sequence.
SELECT ID, auto_name, Score, DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank FROM example_table;
NTILE(n) – Divides rows into a specified number of approximately equal groups, and assigns a number to each group.
SELECT ID, auto_name, Score, NTILE(4) OVER (ORDER BY Score DESC) AS NTile FROM example_table;
Lead() – Provides access to a row at a specified physical offset following that position.
SELECT ID, auto_name, Score, LEAD(Score, 1) OVER (ORDER BY Score DESC) AS NextScore FROM example_table;
Lag() – Provides access to a row at a specified physical offset prior to that position.
SELECT ID, auto_name, Score, LAG(Score, 1) OVER (ORDER BY Score DESC) AS PreviousScore FROM example_table;
First_VALUE() – Returns the first value in an ordered set of values.
SELECT ID, auto_name, Score, FIRST_VALUE(Score) OVER (ORDER BY Score DESC) AS FirstScore FROM example_table;
LAST_VALUE() – Returns the last value in an ordered set of values.
SELECT ID, auto_name, Score, LAST_VALUE(Score) OVER (ORDER BY Score DESC ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS LastScore FROM example_table;
CUME_DIST() – Calculates the cumulative distribution of a value in a set of values.
SELECT ID, auto_name, Score, CUME_DIST() OVER (ORDER BY Score DESC) AS CumulativeDistribution FROM example_table;
PERCENT_RANK() – Calculates the relative rank of a row within a group of rows.
SELECT ID, auto_name, Score, PERCENT_RANK() OVER (ORDER BY Score DESC) AS PercentRank FROM example_table;
SQL Server Aggregate Functions
SELECT
COUNT(*) AS TotalSales,
SUM(Quantity) AS TotalQuantity,
AVG(Price) AS AveragePrice,
MAX(Price) AS MaxPrice,
MIN(Price) AS MinPrice
FROM Sales;
SELECT
ProductName,
SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY ProductName
HAVING SUM(Quantity) > 20;
LIST ALL TABLES IN EACH DATABASE ON A SQL SERVER ALONG WITH A COUNT OF RECORDS FOR EACH TABLE
DECLARE @dbName NVARCHAR(255)
DECLARE @sql NVARCHAR(MAX)
— Create a temporary table to store the results
IF OBJECT_ID(‘tempdb..#TableCounts’) IS NOT NULL DROP TABLE #TableCounts
CREATE TABLE #TableCounts (
DatabaseName NVARCHAR(255),
SchemaName NVARCHAR(255),
TableName NVARCHAR(255),
RecordCount BIGINT
)
— Cursor to iterate through each database
DECLARE db_cursor CURSOR FOR
SELECT name
FROM sys.databases
WHERE state_desc = ‘ONLINE’
AND name NOT IN (‘master’, ‘tempdb’, ‘model’, ‘msdb’) — Exclude system databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @dbName
WHILE @@FETCH_STATUS = 0
BEGIN
— Construct dynamic SQL for each database
SET @sql = ‘
USE [‘ + @dbName + ‘];
INSERT INTO #TableCounts (DatabaseName, SchemaName, TableName, RecordCount)
SELECT ”’ + @dbName + ”’, s.name, t.name, p.rows
FROM ‘ + @dbName + ‘.sys.tables t
JOIN ‘ + @dbName + ‘.sys.schemas s ON t.schema_id = s.schema_id
JOIN ‘ + @dbName + ‘.sys.partitions p ON t.object_id = p.object_id
WHERE p.index_id IN (0,1) — heap or clustered index
GROUP BY s.name, t.name, p.rows;’
EXEC sp_executesql @sql
FETCH NEXT FROM db_cursor INTO @dbName
END
CLOSE db_cursor
DEALLOCATE db_cursor
— Select the results
SELECT * FROM #TableCounts
ORDER BY DatabaseName, SchemaName, TableName
Inner Join
This will return only the matching records between Customers and Orders based on CustomerID.
SELECT
C.CustomerID,
C.CustomerName,
O.OrderID,
O.OrderDate,
O.Amount
FROM
Customers C
INNER JOIN
Orders O
ON
C.CustomerID = O.CustomerID;
Left Join (Left Outer Join)
This will return all records from the Customers table and the matching records from the Orders table.
If there’s no match, NULL values will be shown for the Orders table columns.
SELECT
C.CustomerID,
C.CustomerName,
O.OrderID,
O.OrderDate,
O.Amount
FROM
Customers C
LEFT JOIN
Orders O
ON
C.CustomerID = O.CustomerID;
Right Join (Right Outer Join)
This will return all records from the Orders table and the matching records from the Customers table.
If there’s no match, NULL values will be shown for the Customers table columns.
SELECT
C.CustomerID,
C.CustomerName,
O.OrderID,
O.OrderDate,
O.Amount
FROM
Customers C
RIGHT JOIN
Orders O
ON
C.CustomerID = O.CustomerID;
Full Join (Full Outer Join)
This will return all records when there is a match in either Customers or Orders.
If there is no match, NULL values will be returned for the columns from the table that doesn’t have a match.
SELECT
C.CustomerID,
C.CustomerName,
O.OrderID,
O.OrderDate,
O.Amount
FROM
Customers C
FULL JOIN
Orders O
ON
C.CustomerID = O.CustomerID;
