DML — Retrieving, Inserting & Updating Data
SELECT
SELECT FirstName, LastName FROM Employees
The single most central command in SQL for retrieving data. Naming specific columns saves server resources and returns only the exact data you need.
FROM
SELECT City FROM Customers
Directs the SQL engine to the data source — which table, view, or set of tables to search. Without it, the query has no direction.
INSERT INTO
INSERT INTO Customers (Name, City) VALUES ('Dan', 'Haifa')For inserting new rows into a table. Important: an exact match between the column order and the value order in VALUES.
UPDATE
UPDATE Products SET Price = 150 WHERE ID = 1
For editing existing data. Critical: always use WHERE — without it you will overwrite every row in the table!
DELETE
DELETE FROM Orders WHERE Status = 'Canceled'
Removes rows permanently. There is a risk of mass deletion if you forget WHERE.
DISTINCT
SELECT DISTINCT City FROM Customers
Cleans up duplicates and returns a list of unique values only.
SELECT INTO
SELECT * INTO BackupTable FROM OriginalTable
Creates a new table and copies data into it at the same time. For quick backups and temporary work.
INSERT INTO SELECT
INSERT INTO Archive SELECT * FROM Orders WHERE Year = 2020
Reads from one table and pushes into an existing one. For archiving and data-migration processes.
MERGE
MERGE Target t USING Source s ON t.ID = s.ID WHEN MATCHED THEN UPDATE...
"Upsert" — finds a match: updates it; missing: inserts it. Saves complex logic.
AS (Alias)
SELECT FirstName AS Name FROM Employees e
A temporary name for a column or table. For clearer headings and to shorten long names in a JOIN.
Filtering & Logical Operators
WHERE
SELECT * FROM Products WHERE Price > 100
The primary filtering engine. It checks every row and passes on only records for which the condition is true.
AND
WHERE Category = 'Toys' AND Price < 50
Requires all conditions to hold at once. Only a row that satisfies them all is shown.
OR
WHERE City = 'Haifa' OR City = 'Eilat'
It is enough for one of the conditions to hold. Be sure to use parentheses when combining with AND.
NOT
WHERE NOT Category = 'Food'
Negates a condition. Useful when it is easier to define what you do not want.
IN
WHERE Department IN ('HR', 'Sales', 'IT')A clean replacement for a chain of ORs. Takes a list of values and checks whether the value matches one of them.
NOT IN
WHERE Country NOT IN ('USA', 'UK')Ensures the value is not in the list. For filtering out and excluding a set of IDs.
BETWEEN
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31'
A continuous filtering range, inclusive of the bounds. Common for date and price ranges.
NOT BETWEEN
WHERE Age NOT BETWEEN 20 AND 30
Retrieves data outside the range. For finding statistical outliers.
LIKE
WHERE Email LIKE '%@gmail.com'
Flexible string search. % = any sequence of characters. _ = a single character.
NOT LIKE
WHERE Address NOT LIKE '%PO Box%'
Filters out records that contain a pattern. For data cleaning.
IS NULL
WHERE DeliveryDate IS NULL
NULL = "the data does not exist." You cannot compare it with =. IS NULL finds empty cells.
IS NOT NULL
WHERE PhoneNumber IS NOT NULL
Finds rows that contain some content. For cleaning reports and analyzing complete data.
= equals
WHERE Status = 'Active'
The basic comparison operator. Requires an exact match.
<> / != not equal
WHERE Role <> 'Admin'
The two syntaxes are identical. Shows everything that is not the specified value.
> greater than
WHERE Amount > 1000
Values higher than the reference point. Does not include the value itself.
< less than
WHERE InventoryLevel < 10
Values lower than the reference point. For alerts on stock that is about to run out.
>= greater than or equal
WHERE Age >= 18
Includes the threshold value. The correct way to define a minimum bar.
<= less than or equal
WHERE Discount <= 0.5
Sets a ceiling. Products with a discount of up to 50%, inclusive.
EXISTS
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustID = c.ID)
Checks "does it exist?". Very fast — it stops at the first match. Preferable to a JOIN in many cases.
NOT EXISTS
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustID = c.ID)
Entities that have no trace in another table. Usually better than NOT IN for performance.
ANY
WHERE Price > ANY (SELECT Price FROM Competitors)
A comparison against a set of results. It is enough for the condition to hold against at least one value.
ALL
WHERE Price > ALL (SELECT Price FROM Competitors)
Stricter — the condition must hold against every value in the list, without exception.
Sorting, Grouping & Output Formatting
ORDER BY
ORDER BY LastName, FirstName
Organizes results in a logical order. Without ORDER BY, SQL returns rows in an arbitrary order.
ASC
ORDER BY Price ASC
Ascending sort — smallest to largest, A to Z. The default; can be omitted.
DESC
ORDER BY OrderDate DESC
Descending sort. Puts the most recent, most profitable, or highest first.
TOP
SELECT TOP 10 * FROM Customers ORDER BY Score DESC
Only the first X rows (SQL Server). With ORDER BY DESC — "the top 10."
LIMIT
SELECT * FROM Customers ORDER BY Score DESC LIMIT 10
The equivalent of TOP in MySQL/PostgreSQL. Always at the end of the query.
OFFSET
ORDER BY ID OFFSET 20 ROWS
Skips X rows. Part of the pagination mechanism — paging through result pages.
FETCH NEXT
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
Complements OFFSET. Defines how many rows to fetch after the skip.
GROUP BY
SELECT Category, COUNT(*) FROM Products GROUP BY Category
Creates groups from records that share the same value and enables aggregate calculations per group.
HAVING
GROUP BY Category HAVING COUNT(*) > 50
The "WHERE for groups." It filters after the calculation is done. Required when using aggregate functions.
ROLLUP
GROUP BY ROLLUP (Department)
Automatically adds a "grand total" row at the bottom. Saves a separate calculation.
CUBE
GROUP BY CUBE (Department, JobTitle)
Creates summary rows for every possible combination. For analytical matrix reports.
GROUPING SETS
GROUP BY GROUPING SETS ((Dept), (Title), ())
Precise control — specifies exactly which summary levels to generate. More efficient than CUBE.
Aggregate Functions
COUNT(col)
SELECT COUNT(Email) FROM Customers
Counts the values in a column. Ignores NULL rows. 8 emails out of 10 rows → returns 8.
COUNT(*)
SELECT COUNT(*) FROM Orders
Counts the physical number of rows, including NULL. The fast way to know "how many records are there?".
SUM()
SELECT SUM(Amount) FROM Sales WHERE Year = 2023
Adds numeric values into a total. The beating heart of financial reports.
AVG()
SELECT AVG(Salary) FROM Employees
An arithmetic average that ignores NULL. For average salary, wait times, age, and more.
MIN()
SELECT MIN(Price) FROM Products
The smallest value. Also for the earliest date or the first name alphabetically.
MAX()
SELECT MAX(OrderDate) FROM Orders
The largest value. For the most profitable deal or the most recent timestamp.
Joins & Set Operations
INNER JOIN
SELECT c.Name, o.Amount FROM Customers c INNER JOIN Orders o ON c.ID = o.CustID
Only rows with a full match on both sides. The safest choice for reports on active transactions.
LEFT JOIN
SELECT c.Name, o.Amount FROM Customers c LEFT JOIN Orders o ON c.ID = o.CustID
All rows from the left table, even without a match (NULL on the right side). For finding customers with no orders.
RIGHT JOIN
SELECT c.Name, o.Amount FROM Customers c RIGHT JOIN Orders o ON c.ID = o.CustID
All rows from the right table. Usually it is preferable to swap the order and use LEFT.
FULL OUTER JOIN
SELECT c.Name, o.Amount FROM Customers c FULL OUTER JOIN Orders o ON c.ID = o.CustID
Everything from everywhere — matches plus gaps from both sides. For auditing and finding mismatches.
CROSS JOIN
SELECT Colors.Name, Sizes.Name FROM Colors CROSS JOIN Sizes
A Cartesian product — every row × every row. 3 colors × 3 sizes = 9 rows.
SELF JOIN
SELECT e1.Name AS Emp, e2.Name AS Mgr FROM Employees e1 JOIN Employees e2 ON e1.MgrID = e2.ID
Joining a table to itself. For handling manager–employee hierarchies within the same table.
ON
JOIN Orders o ON c.ID = o.CustomerID
Defines the join condition — which columns represent the same information in both tables.
USING
JOIN Orders USING (CustomerID)
A shorthand for ON when the linking column has the same name in both tables.
UNION
SELECT City FROM Customers UNION SELECT City FROM Suppliers
Stacks queries vertically and removes duplicates. Both must have the same column structure.
UNION ALL
SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers
A union without removing duplicates. Faster — preferable when there is no overlap.
INTERSECT
SELECT ProductID FROM Sales2023 INTERSECT SELECT ProductID FROM Sales2024
Intersection — only rows that appear in both queries. For loyal customers who bought in both years.
EXCEPT / MINUS
SELECT ProductID FROM Sales2023 EXCEPT SELECT ProductID FROM Sales2024
Subtraction — rows in the first query that are not in the second. Products sold in 2023 only.
DDL — Defining Structure & Infrastructure
CREATE DATABASE
CREATE DATABASE CompanyCRM
Creates a new database. Allocates space, files, and logs on the server.
DROP DATABASE
DROP DATABASE CompanyCRM
Deletes the entire DB — including all tables and data. Irreversible without a backup!
CREATE TABLE
CREATE TABLE Users (ID INT, Name VARCHAR(50))
Creates a new table and defines its column structure and data types.
ALTER TABLE
ALTER TABLE Users ADD Email VARCHAR(100)
Structural changes to an existing table — adding columns, dropping, changing a data type.
DROP TABLE
DROP TABLE Users
Total deletion — the data and the structure are removed. You will need to CREATE from scratch.
TRUNCATE TABLE
TRUNCATE TABLE Logs
Empties the content quickly. The structure remains. Faster than DELETE — for resetting a system.
CREATE INDEX
CREATE INDEX idx_ln ON Employees (LastName)
A "table of contents" for a column. Lets you jump straight to a record instead of scanning everything.
DROP INDEX
DROP INDEX idx_ln ON Employees
Drops an index. Indexes slow down writes — removing unused ones improves performance.
CREATE VIEW
CREATE VIEW ActiveUsers AS SELECT * FROM Users WHERE Status = 'Active'
A "virtual table" — saves a complex query under a name. Hides the logic from users.
DROP VIEW
DROP VIEW ActiveUsers
Drops a view. The underlying data remains untouched.
CREATE PROCEDURE
CREATE PROCEDURE GetUser (@ID INT) AS BEGIN SELECT * FROM Users WHERE ID=@ID END
A small program stored in the DB. Improves security and prevents SQL Injection.
DROP PROCEDURE
DROP PROCEDURE GetUser
Removes a stored procedure. For version upgrades and clearing out old code.
CREATE FUNCTION
CREATE FUNCTION CalcTax (@V DECIMAL) RETURNS DECIMAL AS BEGIN RETURN @V * 1.17 END
A custom function for a recurring calculation. Returns a clean value and is used inside queries.
PRIMARY KEY
CREATE TABLE Users (ID INT PRIMARY KEY, Name VARCHAR(50))
A unique "ID card" for each row. Prevents duplicates and requires a value.
FOREIGN KEY
CONSTRAINT FK_User FOREIGN KEY (UserID) REFERENCES Users(ID)
Creates a rigid link between tables. Prevents entering fictitious IDs that do not exist.
UNIQUE
CREATE TABLE Users (Email VARCHAR(100) UNIQUE)
All values in the column must be distinct. For unique emails and phone numbers.
NOT NULL
CREATE TABLE Users (Name VARCHAR(50) NOT NULL)
Requires a value in the column. Prevents creating customers without a name or products without a price.
CHECK
CREATE TABLE Products (Price DECIMAL CHECK (Price > 0))
Checks whether the value meets a business rule. Does not allow a negative price.
DEFAULT
CREATE TABLE Users (Status VARCHAR(20) DEFAULT 'Pending')
Fills in an automatic value if the user did not enter one. Instead of leaving the cell NULL.
IDENTITY / AUTO_INCREMENT
CREATE TABLE Users (ID INT IDENTITY(1,1) PRIMARY KEY)
An automatic running counter (1,2,3...). Each new row gets a unique ID.
Transactions & Security Permissions
GRANT
GRANT SELECT, INSERT ON Users TO AnalystRole
Grants access permissions. You can give "read only" and prevent changes.
REVOKE
REVOKE INSERT ON Users FROM AnalystRole
Revokes rights that were granted. For changing roles and protecting sensitive data.
BEGIN TRAN
BEGIN TRAN; UPDATE Accounts SET Balance = Balance - 100...
Opens a unit of work — all operations are managed as one. Until committed, they are tentative.
COMMIT
COMMIT
Seals the transaction and makes all operations final and irreversible.
ROLLBACK
ROLLBACK
The lifesaver. Undoes all changes since the start of the transaction and reverts to the previous state.
SAVEPOINT
SAVEPOINT Stage1
A temporary save point inside a huge transaction. A later failure rolls back only to this point.
Text Manipulation Functions
CONCAT()
SELECT CONCAT(FirstName, ' ', LastName) FROM Employees
Concatenates fields and text. Handles NULL gracefully — it ignores them.
CONCAT_WS()
SELECT CONCAT_WS(', ', City, State, Country) FROM Addresses"Concatenate With Separator." Defines one separator and joins everything automatically.
SUBSTRING()
SELECT SUBSTRING(IDNumber, 1, 4) FROM Customers
Cuts text by position: column name, starting character, how many characters.
LEN() / LENGTH()
WHERE LEN(Password) < 8
Counts how many characters are in the text. For checking short passwords and validating data.
UPPER()
SELECT UPPER(CountryCode) FROM Countries
Converts to uppercase. For normalizing comparisons — both sides to UPPER = a consistent search.
LOWER()
SELECT LOWER(Email) FROM Users
Converts to lowercase. A common convention for emails.
TRIM()
SELECT TRIM(Username) FROM Accounts
Trims spaces from the start and end. For "junk" from copy-paste.
LTRIM()
SELECT LTRIM(Comment) FROM Feedback
Trims spaces from the left side only.
RTRIM()
SELECT RTRIM(Comment) FROM Feedback
Trims spaces from the end of the string only.
REPLACE()
SELECT REPLACE(Phone, '-', '') FROM Customers
Replaces every occurrence of a pattern. For removing dashes from phone numbers.
REVERSE()
SELECT REVERSE(StringCol) FROM Strings
Reverses the order of the characters. A trick for finding the position of the last character.
LEFT()
SELECT LEFT(OrderNum, 2) FROM Orders
X characters from the left side. Shorter than SUBSTRING for extracting from the start.
RIGHT()
SELECT RIGHT(CreditCard, 4) FROM Payments
X characters from the right side. For the last 4 digits of a credit card.
CHARINDEX()
SELECT CHARINDEX('@', Email) FROM UsersSearches for a character/word and returns a numeric position. For cutting emails of varying lengths.
PATINDEX()
SELECT PATINDEX('%[0-9]%', Address) FROM CustomersFinds smart patterns (SQL Server). For locating the position of the first digit in an address.
FORMAT()
SELECT FORMAT(Price, 'C', 'en-US') FROM Products
Formats values into nice text — commas in millions, currency symbols, date formats.
Date & Time Functions
GETDATE() / NOW()
SELECT GETDATE()
The current date and time down to the millisecond. An automatic timestamp for new records.
CURRENT_DATE
SELECT CURRENT_DATE
Today's date only, without the time. For conditions that care only about the date.
CURRENT_TIME
SELECT CURRENT_TIME
The current time without the date. For efficiency reports and daily load.
DATEADD()
SELECT DATEADD(day, 30, OrderDate) AS DueDate FROM Orders
Adds (or, with a minus, subtracts) time to a date. For computing due dates.
DATEDIFF()
SELECT DATEDIFF(day, OrderDate, ShipDate) FROM Orders
The gap between two dates. For response times, customer age, days until shipment.
DATEPART()
SELECT DATEPART(quarter, OrderDate) FROM Sales
Extracts a specific element as a number (quarter = 1-4). Essential for GROUP BY by quarter.
YEAR()
SELECT YEAR(OrderDate) FROM Sales
Isolates the year. For annual revenue reports (2021, 2022, 2023...).
MONTH()
SELECT MONTH(OrderDate) FROM Sales
The month number (1-12). For seasonality analysis across years.
DAY()
SELECT DAY(BirthDate) FROM Customers
The day of the month (1-31). For birthdays and monthly credit-card payments.
EOMONTH()
SELECT EOMONTH(OrderDate) AS EndOfMonth FROM Sales
Jumps to the last day of the month. Solves "how many days are in the month." For financial closes.
ISDATE()
SELECT * FROM Import WHERE ISDATE(RawText) = 1
Checks whether text is a valid date. Protects against bad data from forms.
Math Functions
ROUND()
SELECT ROUND(Price, 2) FROM Products
Rounds to X decimal places. For accurate invoices.
CEILING()
SELECT CEILING(4.1) -- Returns 5
Rounds any fraction up to the next whole number. 4.1 buses → you must rent 5.
FLOOR()
SELECT FLOOR(4.9) -- Returns 4
Truncates down to the lower whole number. 4.9 packs → you can only pack 4.
ABS()
SELECT ABS(Income - Expense) FROM Books
Absolute value — a negative becomes positive. For computing the size of gaps.
POWER()
SELECT POWER(Radius, 2) FROM Shapes
Exponentiation. For compound interest and area calculations.
SQRT()
SELECT SQRT(Area) FROM Squares
Square root. For distance calculations and standard deviations.
RAND()
SELECT RAND()
A random number between 0 and 1. For raffles and random sampling.
SIGN()
SELECT SIGN(NetProfit) FROM Reports
1 (positive), -1 (negative), 0. For a quick profit/loss indicator.
PI()
SELECT PI() * POWER(Radius, 2) AS Area
A built-in π constant. For precise geometry calculations.
MOD() / %
WHERE EmployeeID % 2 = 0
The division remainder. For finding even-numbered rows and splitting lists into equal lanes.
Smart Conditionals & NULL Handling
CASE
SELECT CASE WHEN Score > 90 THEN 'A' ELSE 'F' END FROM Tests
Branching logic inside the query. Builds a computed column that categorizes data in real time.
WHEN / THEN
CASE WHEN condition THEN result END
The CASE axis. WHEN = a condition to test, THEN = the output if the condition is true.
COALESCE()
SELECT COALESCE(Mobile, HomePhone, 'No Phone') FROM Contacts
Returns the first value that is not NULL. A "priority waterfall" for missing data.
ISNULL() / IFNULL()
SELECT ISNULL(Discount, 0) FROM Sales
If NULL, returns a fallback value. Essential in calculations — NULL breaks math operations.
NULLIF()
SELECT Amount / NULLIF(Quantity, 0) FROM Inventory
If two values are identical → returns NULL. Prevents a divide-by-zero crash.
Data-Type Conversions & Upgrades
IIF()
SELECT IIF(Stock > 0, 'In Stock', 'Out of Stock') FROM Products
A shorthand CASE for two states. SQL Server. Three inputs: condition, true, false.
CAST()
SELECT CAST(Price AS INT) FROM Products
The standard tool for converting a data type. Number to text for concatenation, text to number for addition.
CONVERT()
SELECT CONVERT(VARCHAR(10), OrderDate, 103) FROM Orders
CAST's sibling, with formatting ability. It can format dates while converting.
TRY_CAST()
SELECT TRY_CAST(StringVal AS INT) FROM DirtyData
A safe conversion. Gibberish → NULL instead of a crash. The query keeps running.
TRY_CONVERT()
SELECT TRY_CONVERT(DATE, StringDate) FROM DirtyData
A safe CONVERT. For ingesting data from unreliable external sources.
Window Functions
OVER()
SELECT Name, SUM(Salary) OVER() AS Total FROM Employees
The gateway to window functions. Aggregate calculations on every record without GROUP BY. Salary + total company payroll together.
PARTITION BY
SUM(Salary) OVER(PARTITION BY Department)
Splits the window into sub-groups. Department payroll instead of the whole company. Resets for each group.
ROW_NUMBER()
ROW_NUMBER() OVER(ORDER BY Salary DESC)
Unique sequential numbering (1,2,3...). Ignores ties. For forcing an order and removing duplicates.
RANK()
RANK() OVER(ORDER BY Salary DESC)
Competitive ranking. A tie → both get rank 2. The next → jumps to 4 (skipping 3).
DENSE_RANK()
DENSE_RANK() OVER(ORDER BY Salary DESC)
Dense ranking. After a tie at rank 2, the next gets 3 (not 4). For credit ratings and subscriptions.
NTILE()
NTILE(4) OVER(ORDER BY Salary)
Splits into X equal groups. NTILE(4) = four quartiles. For marketing segmentation.
LEAD()
LEAD(Revenue) OVER(ORDER BY Month)
Brings a value from the next row. Next month's revenue alongside the current one — without a JOIN.
LAG()
LAG(Revenue) OVER(ORDER BY Month)
Brings a value from the previous row. Yesterday alongside today — to compute % growth in a single query.
FIRST_VALUE()
FIRST_VALUE(Name) OVER(ORDER BY HireDate)
The first record in the window, for every row. The most senior employee alongside each employee.
LAST_VALUE()
LAST_VALUE(Name) OVER(ORDER BY HireDate)
The last record in the window. The final stage of an order alongside all the intermediate stages.
Advanced Structures & Architecture
WITH (CTE)
WITH HighVal AS (SELECT * FROM Orders WHERE Amount > 1000) SELECT * FROM HighVal
A temporary working table that lives across the query. Turns complicated code into readable steps.
PIVOT
PIVOT(SUM(Amount) FOR Year IN ([2022], [2023])) AS pvt
Turns rows into columns. For Excel-like reports — months of the year as columns.
UNPIVOT
UNPIVOT (Amount FOR Year IN ([2022], [2023])) AS unpvt
Turns columns back into rows. For normalizing matrices from external sources.
EXEC / EXECUTE
EXEC GetUser @UserID = 5
Runs stored procedures. Also for executing dynamic code built as text at run time.
DECLARE
DECLARE @Year INT = 2023; SELECT * FROM Sales WHERE Year = @Year
Creates variables in scripts. Changing one value at the top affects all the code below.
JSON & XML — Semi-Structured Data
FOR JSON
SELECT Name, Age FROM Users FOR JSON AUTO
Turns SQL results into JSON format. Lets web developers use the data directly in JavaScript without further conversion.
OPENJSON()
SELECT * FROM OPENJSON(@JsonString)
Converts a JSON string into a regular table with rows and columns. For handling data from external APIs.
JSON_VALUE()
SELECT JSON_VALUE(JsonCol, '$.Address.City') FROM Customers
Extracts a single value from JSON by path. For pulling a specific field out of a large JSON column.
JSON_QUERY()
SELECT JSON_QUERY(JsonCol, '$.Hobbies') FROM Customers
Extracts a complex object or array from JSON. Unlike JSON_VALUE, which returns a simple value.
JSON_MODIFY()
UPDATE Users SET JsonCol = JSON_MODIFY(JsonCol, '$.Status', 'VIP')
Edits a specific value inside JSON without rewriting the whole document.
ISJSON()
SELECT * FROM Logs WHERE ISJSON(RawData) = 1
Checks whether a string is valid JSON. Returns 1 if valid, 0 if malformed. For filtering out bad data.
FOR XML
SELECT Name FROM Users FOR XML PATH('User')Converts table results into XML format. Still common in banks, governments, and legacy systems.
OPENXML()
SELECT * FROM OPENXML(@doc, '/Users/User')
Converts an XML string into a relational table. For handling data from old XML interfaces.
Advanced String Functions
STRING_AGG()
SELECT Dept, STRING_AGG(Name, ', ') FROM Employees GROUP BY Dept
Combines multiple rows into one string with a separator. "5 employees" → "Dan, Yael, Ron, Michal, Noa."
STRING_SPLIT()
SELECT value FROM STRING_SPLIT('Apple,Banana,Orange', ',')Breaks a string into separate rows by a separator. The opposite of STRING_AGG.
REPLICATE()
SELECT REPLICATE('0', 5) + OrderNumber FROM OrdersRepeats characters X times. For padding numbers with leading zeros (000045).
SPACE()
SELECT FirstName + SPACE(5) + LastName FROM Users
Generates X blank spaces. More readable than typing spaces by hand.
SOUNDEX()
WHERE SOUNDEX(Name) = SOUNDEX('Smith')Converts a word into a phonetic (sound) code. For finding misspelled names — "Smythe" = "Smith."
DIFFERENCE()
SELECT DIFFERENCE(Name, 'Michael') FROM Users
A 0-4 score for the phonetic similarity between two words. 4 = sound-identical. For "did you mean...?" engines.
QUOTENAME()
SELECT QUOTENAME(TableName) FROM Sys.Tables
Wraps an object name in square brackets. Prevents errors for names with spaces and guards against SQL Injection.
TRANSLATE()
SELECT TRANSLATE(Phone, '()-', ' ') FROM Contacts
Replaces multiple characters at once. For cleaning punctuation out of phone numbers in a single query.
ASCII()
SELECT ASCII(LEFT(Password, 1)) FROM Logins
Returns the ASCII code of the first character. For checking whether a password starts with an uppercase letter.
CHAR()
SELECT 'Hello' + CHAR(13) + 'World'
Converts a numeric code into a character. CHAR(13) = newline, CHAR(9) = Tab. For characters you cannot type.
System Functions & Identifiers
DB_NAME()
SELECT DB_NAME()
Returns the name of the current database. For identifying where code runs on servers with multiple databases.
OBJECT_ID()
IF OBJECT_ID('TempTable') IS NOT NULL DROP TABLE TempTableChecks whether a table/object exists. Prevents "table does not exist" errors before a DROP.
OBJECT_NAME()
SELECT OBJECT_NAME(1234567)
Converts an internal ID into a readable name. For analyzing system tables and tracking performance.
@@VERSION
SELECT @@VERSION
Returns the SQL Server and OS version. For checking compatibility before running code.
@@ROWCOUNT
UPDATE Users SET Status=1; SELECT @@ROWCOUNT
The number of rows affected by the last command. For indicating success/failure of an UPDATE/DELETE.
@@ERROR
IF @@ERROR <> 0 PRINT 'Error occurred'
The error code of the last command. 0 = success. Mostly replaced by TRY...CATCH.
@@IDENTITY
INSERT INTO Orders(Amt) VALUES(50); SELECT @@IDENTITY
The last auto-generated ID. For getting an order number after an INSERT.
SCOPE_IDENTITY()
SELECT SCOPE_IDENTITY()
Like @@IDENTITY but safer — returns an ID only from the current scope, not from triggers.
IDENT_CURRENT()
SELECT IDENT_CURRENT('Orders')The last ID in a specific table — from any connection. For monitoring how fast a table grows.
HOST_NAME()
SELECT HOST_NAME()
The name of the machine the connection came from. For audit systems tracking who accessed from where.
SUSER_SNAME()
SELECT SUSER_SNAME()
The current user's name. For building Row-Level Security — each employee sees only their own data.
@@SERVERNAME
SELECT @@SERVERNAME
The SQL server's name. For multi-server applications that need to know which one they are connected to.
Error Handling & Flow Control
TRY...CATCH
BEGIN TRY ... END TRY BEGIN CATCH ... END CATCH
An error-handling mechanism. Code in TRY that fails moves to CATCH instead of crashing. The most important tool for procedures.
THROW
BEGIN CATCH THROW; END CATCH
Re-throws the error to the calling application. For logging the error in the DB while still showing an error to the user.
RAISERROR
RAISERROR('Out of stock', 16, 1)Raises a custom, deliberate error. For halting a process when there is a business problem (stock = 0) even without a SQL error.
ERROR_MESSAGE()
INSERT INTO Logs VALUES (ERROR_MESSAGE())
The full error text inside CATCH. For saving the error details to a log table.
ERROR_NUMBER()
IF ERROR_NUMBER() = 2627 PRINT 'Duplicate'
The numeric error code. For handling errors differently by type — duplicate vs. permissions vs. connection.
ERROR_LINE()
SELECT ERROR_LINE() AS FailedAtRow
The line of code that failed. Saves hours of debugging in long procedures.
WHILE
WHILE @Counter < 10 BEGIN ... SET @Counter = @Counter + 1 END
A loop in SQL. For processing data step by step in cases where a set-based operation is not possible.
BREAK
IF @Value = 0 BREAK
Stops a WHILE loop immediately. For an emergency exit from a loop when an error is found.
CONTINUE
IF @Skip = 1 CONTINUE
Skips to the next iteration of the loop. For skipping irrelevant records without stopping everything.
RETURN
IF @User IS NULL RETURN
Stops an entire procedure. A "gatekeeper" — if the input is invalid, exit immediately.
WAITFOR DELAY
WAITFOR DELAY '00:00:10'
A delay of X time. For processing data in batches with pauses so as not to choke the server.
WAITFOR TIME
WAITFOR TIME '02:00:00'
Waits until a specific time. For running nightly maintenance tasks.
Advanced Security Permissions
CREATE LOGIN
CREATE LOGIN JohnDoe WITH PASSWORD = 'Pass1'
Creates access to the SQL server. The first step — "entering the building." It does not yet grant access to the data.
CREATE USER
CREATE USER JohnDoeUser FOR LOGIN JohnDoe
Links a login to a specific database. An "employee badge for a particular room" after entering the building.
ALTER LOGIN / USER
ALTER LOGIN JohnDoe DISABLE
Edits or freezes a user. For instantly revoking access when an employee leaves, without deleting their history.
CREATE ROLE
CREATE ROLE DataAnalysts
Creates a permissions group. Manage one group instead of each user separately — scalable and clean.
DENY
DENY DELETE ON Orders TO DataAnalysts
Aggressive blocking. It overrides any other GRANT — even for a member of an admin group.
EXECUTE AS
EXECUTE AS USER = 'AdminUser'
Impersonates another user to test permissions. Without needing that user's password.
REVERT
REVERT
Cancels EXECUTE AS and returns to the original identity. Without disconnecting and reconnecting.
Maintenance & Optimization
WITH (NOLOCK)
SELECT * FROM MassiveTable WITH (NOLOCK)
Reading without waiting for locks. Faster, but it may return data that is mid-update (a dirty read).
OPTION (RECOMPILE)
SELECT * FROM Users WHERE ID = @Var OPTION (RECOMPILE)
Forces a recompute of the execution plan. For slow queries where the server "remembered" a bad plan.
OPTION (MAXDOP)
SELECT * FROM HugeTable OPTION (MAXDOP 1)
Limits how many CPU cores the query uses. Prevents one query from paralyzing the whole server.
SET NOCOUNT ON
SET NOCOUNT ON; UPDATE Users SET Name='X'
Silences "rows affected" messages. Reduces bandwidth load in procedures with thousands of operations.
DBCC CHECKDB
DBCC CHECKDB('MyDatabase')Scans the integrity of all data on disk. Finds file damage (corruption). Run it weekly.
UPDATE STATISTICS
UPDATE STATISTICS Customers
Updates the statistical map of the data. Speeds up queries that have started running slowly.
ALTER INDEX REBUILD
ALTER INDEX ALL ON Customers REBUILD
Rebuilds an index from scratch. Removes fragmentation and restores "day one" speed. The table gets locked.
ALTER INDEX REORGANIZE
ALTER INDEX idx_name ON Customers REORGANIZE
Reorganizes without locking. Gentler than REBUILD. For quiet daily maintenance.
DBCC FREEPROCCACHE
DBCC FREEPROCCACHE
Clears all execution-plan memory. For resetting a server behaving slowly due to plan sniffing.
DBCC SHRINKDATABASE
DBCC SHRINKDATABASE('MyDB')Shrinks the DB and returns disk space to the OS. After deleting large amounts of data.
Advanced Dates & Time Zones
DATEFROMPARTS()
SELECT DATEFROMPARTS(2023, 10, 31)
Builds a date from 3 separate numbers (year, month, day). For merging separate fields from an app.
TIMEFROMPARTS()
SELECT TIMEFROMPARTS(14, 30, 0, 0, 0)
Builds a time from 5 numbers. For time-clock systems with separate hour/minute fields.
DATETIME2FROMPARTS()
SELECT DATETIME2FROMPARTS(2023,1,1,12,0,0,0,0)
Builds a full DATETIME2 (year+month+day+hour+minute+second) at high precision.
SYSUTCDATETIME()
SELECT SYSUTCDATETIME()
The current time in universal UTC. A must for global apps with users worldwide.
AT TIME ZONE
SELECT OrderDate AT TIME ZONE 'Israel Standard Time'
Converts a date to a specific time zone, including daylight saving. Without manual calculations.
SWITCHOFFSET()
SELECT SWITCHOFFSET(OrderDate, '+03:00')
Manually shifts the time-zone offset. For displaying a date in the user's local time.
TODATETIMEOFFSET()
SELECT TODATETIMEOFFSET(GETDATE(), '+02:00')
Turns a plain date into a DateTimeOffset with a time-zone identity. For global calculations.
DATENAME()
SELECT DATENAME(month, GETDATE())
Returns a textual name (January, Monday). Unlike DATEPART, which returns a number.
Temp Tables, GUIDs & Sequences
CREATE TABLE #Temp
CREATE TABLE #UserStats (ID INT, Score INT)
A local temporary table — it lives only in the current connection. For intermediate calculations without polluting the DB.
CREATE TABLE ##Temp
CREATE TABLE ##GlobalCache (Data VARCHAR(100))
A global temporary table — accessible to all connections. For sharing cache data between users.
DECLARE @Table TABLE
DECLARE @MyList TABLE (EmpID INT)
A table in RAM only. Very fast for small amounts — no writing to disk.
NEWID()
INSERT INTO Users (ID) VALUES (NEWID())
Generates a globally unique GUID. For secure, unguessable IDs and merging data across servers.
NEWSEQUENTIALID()
DEFAULT NEWSEQUENTIALID()
A GUID that runs in ascending order. Solves NEWID's performance problem — keeps the index ordered.
CREATE SEQUENCE
CREATE SEQUENCE InvoiceSeq AS INT START WITH 1
An independent counter not tied to a table. A running number that several tables can share.
NEXT VALUE FOR
INSERT INTO Invoices (ID) VALUES (NEXT VALUE FOR InvoiceSeq)
Fetches the next number from a SEQUENCE. For numbered invoices without duplicates.
FORMATMESSAGE()
FORMATMESSAGE('Welcome %s, code: %i', 'Dan', 123)Injects variables into a text template. For building readable, centralized error messages.
Advanced Window Functions
PERCENT_RANK()
PERCENT_RANK() OVER(ORDER BY Score)
A relative rank in percent (0-1). "Above 85% of the class" — without needing to know the class size.
CUME_DIST()
CUME_DIST() OVER(ORDER BY Score)
Cumulative distribution — the fraction of the data lower than or equal to the current value. For bell curves.
PERCENTILE_CONT()
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER()
A percentile with interpolation. Returns the average of two values if there is no exact middle value.
PERCENTILE_DISC()
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Salary) OVER()
An exact percentile — returns only a value that actually exists in the data. No interpolation.
GROUPING()
SELECT Dept, GROUPING(Dept), SUM(Salary) GROUP BY ROLLUP(Dept)
Returns 1 for ROLLUP summary rows. To distinguish a real NULL from a "grand total" row.
GROUPING_ID()
GROUPING_ID(Dep, Job) GROUP BY CUBE(Dep, Job)
A binary code identifying the grouping type in CUBE. For ordering the rows of a multidimensional matrix.
CHOOSE()
SELECT CHOOSE(MonthNum, 'Jan', 'Feb', 'Mar'...) FROM Sales
Picks a value by index from a list. CHOOSE(2,...) = the second item. A short alternative to CASE.
ANY_VALUE()
SELECT Category, ANY_VALUE(ManagerName) FROM Items GROUP BY Category
Returns some value from the group. When they are all identical — faster than MAX(Name) as an indirect trick.
NULLS FIRST / LAST
ORDER BY DeliveryDate ASC NULLS LAST
Controls where NULLs appear in the sort. Prevents blanks from jumping to the top of the report. Support: Postgres/MySQL.
Dynamic PIVOT
EXEC(@DynamicPivotQuery)
A PIVOT that builds itself dynamically from the data. For pivot tables whose columns are not known in advance.
Data Duplication & Table Operations (Advanced DML)
SELECT TOP WITH TIES
SELECT TOP 3 WITH TIES * FROM Orders ORDER BY Amount DESC
Returns the TOP N including ties. If 4 rows are tied for 3rd place — all of them are returned.
INSERT OR IGNORE
INSERT OR IGNORE INTO Users VALUES(1, 'Dan')
Adds a row, and if it already exists — silently ignores it, no error. SQLite/MySQL.
INSERT OR REPLACE
INSERT OR REPLACE INTO Users VALUES(1, 'Dan')
Adds a row, and if it exists — replaces it. A shortcut for upsert in SQLite.
UPDATE FROM (JOIN)
UPDATE t SET t.Price = s.NewPrice FROM Products t JOIN Source s ON t.ID = s.ID
Updates a table using data from another table via a JOIN. For syncing prices from an external source.
DELETE WITH JOIN
DELETE t FROM Orders t JOIN Cancelled c ON t.ID = c.ID
Deletes rows by a condition from a joined table. More precise than NOT IN.
OUTPUT (SQL Server)
DELETE FROM Orders OUTPUT DELETED.* WHERE Status='Old'
Returns the rows that were deleted/updated during the operation. For documentation and a change log.
RETURNING (PostgreSQL)
INSERT INTO Users(Name) VALUES('Dan') RETURNING IDReturns values from the row that was created. PostgreSQL's equivalent of OUTPUT — for getting the new ID.
EXCEPT ALL
SELECT ID FROM A EXCEPT ALL SELECT ID FROM B
Subtraction with duplicates — does not remove duplicates from the result. Faster than a plain EXCEPT.
INTERSECT ALL
SELECT ID FROM A INTERSECT ALL SELECT ID FROM B
Intersection with duplicates — keeps all occurrences. PostgreSQL/advanced engines.
TABLESAMPLE
SELECT * FROM Customers TABLESAMPLE (10 PERCENT)
Returns a random sample of X percent of the table. For quick tests on huge tables.
Advanced Data Analysis (Analytics)
RATIO_TO_REPORT()
RATIO_TO_REPORT(Amount) OVER()
A percentage of the total. Each row divided by the grand total — for share of sales out of the company total.
SUM OVER (Running Total)
SUM(Amount) OVER(ORDER BY Date ROWS UNBOUNDED PRECEDING)
A running total — each row sums everything before it.
AVG OVER (Moving Avg)
AVG(Amount) OVER(ORDER BY Date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)
A 7-day moving average. For smoothing seasonality and trends in sales data.
MIN/MAX OVER (Running)
MAX(Price) OVER(ORDER BY Date ROWS UNBOUNDED PRECEDING)
A running max/min — "what was the peak so far?" at every point in time.
COUNT DISTINCT (Approx)
APPROX_COUNT_DISTINCT(CustomerID)
An approximate distinct count (~2% error). 10× faster than COUNT DISTINCT on Big Data.
LISTAGG() / GROUP_CONCAT
LISTAGG(Name, ', ') WITHIN GROUP (ORDER BY Name)
Combines rows into an ordered list. Oracle/PostgreSQL — STRING_AGG with internal sorting.
PIVOT with SUM/COUNT/AVG
SUM(Amount) FOR Quarter IN ([Q1],[Q2],[Q3],[Q4])
A pivot with various functions — not just SUM. For flexible multi-dimensional reports.
CROSS APPLY
SELECT * FROM Customers c CROSS APPLY (SELECT TOP 3 * FROM Orders o WHERE o.CustID=c.ID) t
Runs a subquery for each row. Like a JOIN but correlated — for TOP N per customer.
OUTER APPLY
SELECT * FROM Customers c OUTER APPLY (...) t
Like CROSS APPLY but also returns rows with no match (like a LEFT JOIN).
GENERATE_SERIES()
SELECT * FROM GENERATE_SERIES(1, 12)
Generates a sequence of numbers/dates. For calendars and reports without "holes" on missing dates.
Full-Text Search & Text Searching
CONTAINS()
SELECT * FROM Articles WHERE CONTAINS(Body, 'SQL AND performance')
Advanced full-text search. Faster than LIKE because it uses a text index. Supports AND/OR/NOT.
FREETEXT()
SELECT * FROM Docs WHERE FREETEXT(Content, 'database optimization')
Free-form search — also finds inflections and related words. Less precise than CONTAINS but more flexible.
CONTAINSTABLE()
SELECT k.RANK, t.* FROM Docs t JOIN CONTAINSTABLE(Docs,*,'SQL') k ON t.ID=k.[KEY]
Like CONTAINS but returns a relevance rank. For search engines with a match score.
FREETEXTTABLE()
JOIN FREETEXTTABLE(Docs,*,'query') k ON t.ID=k.[KEY]
FREETEXT with ranking. For Google-style search — returns results sorted by relevance.
MATCH...AGAINST (MySQL)
WHERE MATCH(title, body) AGAINST('SQL performance')Full-Text Search in MySQL. Requires a FULLTEXT index on the columns. Much faster than LIKE.
LIKE with Escape
WHERE Name LIKE '100\%' ESCAPE '\'
Searches for a literal % character (not a wildcard). For searching "100%" as literal text.
REGEXP / RLIKE (MySQL)
WHERE Email REGEXP '^[a-zA-Z]+@'
A search with a regular expression. For validating complex formats — emails, phones, product codes.
SIMILAR TO (PostgreSQL)
WHERE Code SIMILAR TO '[A-Z]{3}-[0-9]+'A regular expression in SQL-Standard style. In PostgreSQL — between LIKE and REGEXP.
Advanced Indexes & Optimization
CREATE FILTERED INDEX
CREATE INDEX idx_active ON Users(Name) WHERE Status='Active'
An index on a subset only. Smaller and faster — for queries that always filter by a fixed condition.
CREATE COLUMNSTORE INDEX
CREATE COLUMNSTORE INDEX idx_col ON Sales(Date, Amount)
A columnar index for analytics. 10-100× faster on GROUP BY/SUM in a data warehouse.
CREATE COVERING INDEX
CREATE INDEX idx_cov ON Orders(CustID) INCLUDE(Amount, Date)
An index with extra columns in INCLUDE. Avoids reading the table itself — a faster query.
EXPLAIN / EXECUTION PLAN
EXPLAIN SELECT * FROM Users WHERE ID=1
Shows the query's execution plan. For spotting an unnecessary table scan and missing indexes.
SET STATISTICS IO ON
SET STATISTICS IO ON; SELECT ...
Shows how many disk pages were read. For finding high-I/O queries that cause slowness.
SET STATISTICS TIME ON
SET STATISTICS TIME ON; SELECT ...
Shows CPU and parse time. For precise performance analysis of slow queries.
WITH (FORCESCAN)
SELECT * FROM T WITH (FORCESCAN)
Forces a table scan even when an index exists. For cases where a full scan is faster (small tables).
WITH (FORCESEEK)
SELECT * FROM T WITH (FORCESEEK)
Forces the use of an index. When the engine errs and chooses a scan — FORCESEEK fixes it.
sp_updatestats
EXEC sp_updatestats
Updates statistics for all tables in the DB. After a large data load.
Advanced Stored Procedures & Functions
CREATE OR ALTER PROCEDURE
CREATE OR ALTER PROCEDURE GetOrders AS ...
Creates a procedure or replaces an existing one in a single command. Without a separate DROP-CREATE.
Procedure with OUTPUT
CREATE PROCEDURE Calc (@In INT, @Out INT OUTPUT) AS SET @Out = @In * 2
A procedure that returns a value through an OUTPUT parameter. For calculations that need to return more than one value.
Procedure with DEFAULT
CREATE PROCEDURE GetData (@Days INT = 30) AS ...
A parameter with a default. You can call it without the parameter — it automatically takes 30 days.
Inline Table-Valued Function
CREATE FUNCTION GetOrders(@ID INT) RETURNS TABLE AS RETURN (SELECT * FROM Orders WHERE CustID=@ID)
A function that returns a table. You can JOIN against it like a regular table. Very fast.
Multi-Statement TVF
CREATE FUNCTION GetData() RETURNS @t TABLE(...) AS BEGIN INSERT INTO @t ... RETURN END
A function with complex logic that returns a table. More flexible than inline but slower.
EXEC with variables
EXEC (@SQL)
Runs dynamic SQL from a string. For building flexible queries at run time.
sp_executesql
EXEC sp_executesql @SQL, '@ID INT', @ID=5
Dynamic SQL with parameters. Safer than a plain EXEC — prevents SQL Injection.
sys.procedures
SELECT * FROM sys.procedures
A list of all procedures in the DB. For documentation and reviewing existing code.
Triggers
CREATE TRIGGER (AFTER INSERT)
CREATE TRIGGER trg_ins ON Orders AFTER INSERT AS ...
A trigger that runs automatically after an INSERT. For automatic events — sending an email, updating a log.
CREATE TRIGGER (INSTEAD OF)
CREATE TRIGGER trg_v ON MyView INSTEAD OF INSERT AS ...
Replaces another action. To allow an INSERT on a view that does not support it directly.
INSERTED / DELETED tables
SELECT * FROM INSERTED; SELECT * FROM DELETED
Virtual tables inside a trigger holding the inserted/deleted data. For saving old values.
DISABLE TRIGGER
DISABLE TRIGGER trg_ins ON Orders
Temporarily disables a trigger. For bulk data imports without firing automatic logic.
DDL TRIGGER
CREATE TRIGGER trg_ddl ON DATABASE FOR DROP_TABLE AS ...
A trigger on DDL operations like DROP TABLE. For preventing accidental deletions in a production environment.
Partitioning — Splitting Tables
CREATE PARTITION FUNCTION
CREATE PARTITION FUNCTION pf_year(INT) AS RANGE RIGHT FOR VALUES(2021,2022,2023)
Defines ranges for partitioning. For splitting a huge table into parts by year/month.
CREATE PARTITION SCHEME
CREATE PARTITION SCHEME ps_year AS PARTITION pf_year ALL TO ([PRIMARY])
Assigns filegroups to each partition. The second step in creating a partitioned table.
CREATE TABLE ON Partition
CREATE TABLE Sales(Date INT) ON ps_year(Date)
Creates a partitioned table. SQL automatically manages where a row is stored based on its value.
Partition Elimination
SELECT * FROM Sales WHERE Year=2023
SQL reads only the relevant partition and skips the rest. 10×+ faster on huge tables.
ALTER TABLE SWITCH PARTITION
ALTER TABLE Sales SWITCH PARTITION 1 TO SalesArchive
Moves an entire partition between tables in O(1). For instant archiving of old data.
Temporal Tables — Tables with History
CREATE SYSTEM-VERSIONED TABLE
CREATE TABLE Products(ID INT, Price DECIMAL, ValidFrom DATETIME2 GENERATED ALWAYS AS ROW START, ValidTo DATETIME2 GENERATED ALWAYS AS ROW END, PERIOD FOR SYSTEM_TIME(ValidFrom,ValidTo)) WITH (SYSTEM_VERSIONING=ON)
A table that manages history automatically. SQL automatically keeps every old version.
FOR SYSTEM_TIME AS OF
SELECT * FROM Products FOR SYSTEM_TIME AS OF '2023-01-01'
Returns what the data was on a given date. "Time travel" — seeing the DB as it was in the past.
FOR SYSTEM_TIME BETWEEN
FOR SYSTEM_TIME BETWEEN '2022-01-01' AND '2023-01-01'
All changes within a date range. For auditing and tracking data changes over time.
FOR SYSTEM_TIME ALL
SELECT * FROM Products FOR SYSTEM_TIME ALL
Every version of every row, ever. For displaying a full timeline of changes.
ALTER TABLE OFF System Versioning
ALTER TABLE Products SET (SYSTEM_VERSIONING=OFF)
Turns off history management. For cleaning up historical data and structural changes.
Graph Database & Hierarchies
CREATE NODE TABLE
CREATE TABLE Person(ID INT) AS NODE
A node table (SQL Graph). For representing social networks, graphs, and dependencies.
CREATE EDGE TABLE
CREATE TABLE Knows AS EDGE
An edge table in a graph. Connects two nodes — Person KNOWS Person.
MATCH (Graph Query)
SELECT p1.Name, p2.Name FROM Person p1, Knows, Person p2 WHERE MATCH(p1-(Knows)->p2)
A graph query — who knows whom. For analyzing social networks and dependency graphs.
Recursive CTE - Tree
WITH RECURSIVE tree AS(SELECT ID, ParentID, 0 lvl FROM Cats WHERE ParentID IS NULL UNION ALL SELECT c.ID, c.ParentID, t.lvl+1 FROM Cats c JOIN tree t ON c.ParentID=t.ID)
A recursive hierarchical tree. For nested categories, org charts, and product BOM trees.
PATH with CTE
'' AS path ... t.path+'/'+c.Name
Builds a full path in a tree (Root/Branch/Leaf). For displaying breadcrumbs and analyzing tree depth.
Geography & Location (Spatial)
geometry/geography types
Location geography
Special data types for GPS points, lines, and polygons. For maps and distances.
STDistance()
Loc1.STDistance(Loc2)
The distance between two geographic points, in meters. For dispatching the nearest delivery.
STContains()
Zone.STContains(Point)
Checks whether a point is inside an area. For the question "is the customer inside the delivery zone?".
STIntersects()
Area1.STIntersects(Area2)
Whether two areas intersect. For detecting overlap between service zones.
geography::Point()
geography::Point(31.77, 35.21, 4326)
Creates a GPS point (latitude, longitude). For storing customer and branch locations.
Row-Level Security & Dynamic Masking
CREATE SECURITY POLICY
CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE dbo.fn_filter(SalesRep) ON Orders
Row-Level Security — each employee sees only their own rows. Automatic and transparent to the application.
CREATE FUNCTION (RLS Predicate)
CREATE FUNCTION fn_filter(@User nvarchar(50)) RETURNS TABLE AS RETURN SELECT 1 r WHERE @User=USER_NAME()
A filter function for RLS. Returns 1 (allowed) or 0 (blocked) for each row.
Dynamic Data Masking
Email VARCHAR(100) MASKED WITH (FUNCTION='email()')
Automatically masks sensitive data: a***@***.com. Analysts see the mask, the DBA sees everything.
ADD MASK
ALTER TABLE Customers ALTER COLUMN Phone ADD MASKED WITH (FUNCTION='partial(0,"XXX-XXX-",4)')
Adds a mask to an existing column. XXX-XXX-1234 — only the last 4 digits are shown.
GRANT UNMASK
GRANT UNMASK TO AdminUser
Lets a specific user see the real data. For a "need to know" policy.
Always Encrypted & Transparent Data Encryption
Always Encrypted
SSN ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY=CEK, ENCRYPTION_TYPE=Deterministic)
Column-level encryption. Even the DBA cannot see the data — only the application.
Transparent Data Encryption
ALTER DATABASE MyDB SET ENCRYPTION ON
Encrypts all the DB files on disk. Protects against physical theft of files from the server.
BACKUP ENCRYPTION
BACKUP DATABASE MyDB TO DISK='backup.bak' WITH ENCRYPTION
Encrypts backup files. Without the key, the backup is worthless to an attacker.
OPEN SYMMETRIC KEY
OPEN SYMMETRIC KEY MyKey DECRYPTION BY PASSWORD='pass'
Opens an encryption key for use. Before manually encrypting/decrypting data.
ENCRYPTBYKEY / DECRYPTBYKEY
ENCRYPTBYKEY(KEY_GUID('MyKey'), CreditCard)Manual encryption and decryption of a field. For encrypted storage with on-demand recovery.
High Availability & Replication
ALTER DATABASE SET RECOVERY FULL
ALTER DATABASE MyDB SET RECOVERY FULL
Full recovery mode — keeps the entire operation log. A must for a production environment with a high SLA.
BACKUP LOG
BACKUP LOG MyDB TO DISK='log.bak'
A transaction-log backup. Enables restoring to an exact point in time — Point-in-Time Recovery.
RESTORE DATABASE WITH STANDBY
RESTORE DATABASE MyDB WITH STANDBY='undo.bak'
A restore in read-only mode with the option to continue. For backup servers you can read from.
RESTORE WITH NORECOVERY
RESTORE DATABASE MyDB WITH NORECOVERY
A restore that allows further backups to be applied. For a recovery chain from multiple backups.
sys.dm_exec_requests
SELECT * FROM sys.dm_exec_requests
All queries currently running on the server. For finding stuck queries and blocking.
DMVs — Dynamic Management Views
sys.dm_exec_query_stats
SELECT TOP 10 * FROM sys.dm_exec_query_stats ORDER BY total_elapsed_time DESC
The slowest queries since the server started. For finding performance problems.
sys.dm_index_usage_stats
SELECT * FROM sys.dm_db_index_usage_stats WHERE user_seeks=0
Unused indexes. Candidates for deletion — they save space and speed up writes.
sys.dm_missing_index_details
SELECT * FROM sys.dm_db_missing_index_details
Missing indexes the server recommends. For a quick performance boost.
sys.dm_exec_connections
SELECT * FROM sys.dm_exec_connections
All active connections to the server. For monitoring load and finding stuck connections.
sys.dm_os_wait_stats
SELECT * FROM sys.dm_os_wait_stats ORDER BY wait_time_ms DESC
The longest wait types on the server. For finding bottlenecks (CPU, I/O, locks).
Cloud SQL & Modern Features
EXTERNAL DATA SOURCE
CREATE EXTERNAL DATA SOURCE BlobStorage WITH (TYPE=BLOB_STORAGE, LOCATION='https://...')
Defines an external data source (Azure Blob, S3). For reading files from the cloud directly in SQL.
OPENROWSET (Blob)
SELECT * FROM OPENROWSET(BULK 'data.csv', DATA_SOURCE='BlobStorage', FORMAT='CSV') t
Reads CSV/Parquet directly from the cloud. In Azure Synapse and SQL Server 2019+.
PolyBase
SELECT * FROM ExternalTable
Queries against Hadoop, Azure, and S3 as if they were regular SQL tables. For Big Data analytics.
OPENDATASOURCE
SELECT * FROM OPENDATASOURCE('SQLNCLI', 'server=OtherServer;uid=sa;pwd=p').DB.dbo.TableOne-off access to another SQL server without a linked server. For rare cross-server queries.
LINKED SERVER
EXEC sp_addlinkedserver @server='OtherServer'
A permanent connection to an external server. For regular queries across servers and different databases.
Schemas, Metadata & Object Management
CREATE SCHEMA
CREATE SCHEMA Sales
Creates a namespace for objects. Sales.Orders vs HR.Employees — organization and permission management.
INFORMATION_SCHEMA
SELECT * FROM INFORMATION_SCHEMA.TABLES
Information about all tables, columns, and relationships. ANSI-standard — works across all SQL engines.
sys.columns
SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('Orders')All of a table's columns with their data types. For documentation and building dynamic SQL.
sys.foreign_keys
SELECT * FROM sys.foreign_keys
All foreign keys in the DB. For documenting relationships and an automatic ERD graph.
sp_help
EXEC sp_help 'Orders'
Complete information about a table — columns, indexes, keys. The table's "ID card."