DML — retrieving, inserting, and updating data
SELECT
SELECT FirstName, LastName FROM Employees
The most central command in SQL for retrieving data. Specifying specific columns saves server resources and returns only the exact information needed.
FROM
SELECT City FROM Customers
Directs the SQL engine to the data source — which table, view, or set of tables to look in. Without it, the query has no direction.
INSERT INTO
INSERT INTO Customers (Name, City) VALUES ('Dan', 'Haifa')for feeding new rows into the table. Important: an exact match between the column order and the order of values in VALUES.
UPDATE
UPDATE Products SET Price = 150 WHERE ID = 1
For editing existing data. Critical: always with WHERE — without it you'll overwrite every row in the table!
DELETE
DELETE FROM Orders WHERE Status = 'Canceled'
Removes rows permanently. There's a risk of mass deletion if you forget WHERE.
DISTINCT
SELECT DISTINCT City FROM Customers
Removes 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
Pulls from one table and pushes into an existing table. For archiving processes and data transfer.
MERGE
MERGE Target t USING Source s ON t.ID = s.ID WHEN MATCHED THEN UPDATE...
"Upsert" — finds a match: updates. Missing: inserts. Saves complex logic.
AS (Alias)
SELECT FirstName AS Name FROM Employees e
A temporary name for a column or table. For clearer headers and shortening long names in a JOIN.
Filtering and logical operators
WHERE
SELECT * FROM Products WHERE Price > 100
The main filtering engine. Checks every row and passes through only records for which the condition is true.
AND
WHERE Category = 'Toys' AND Price < 50
Requires all conditions to hold at the same time. Only a row that satisfies all of them will be shown.
OR
WHERE City = 'Haifa' OR City = 'Eilat'
It's enough for one of the conditions to hold. It's important to use parentheses when combining with AND.
NOT
WHERE NOT Category = 'Food'
Negation of a condition. Useful when it's easier to define what you don't want.
IN
WHERE Department IN ('HR', 'Sales', 'IT')A clean replacement for a chain of OR. Takes a list of values and checks if the value matches one of them.
NOT IN
WHERE Country NOT IN ('USA', 'UK')Verifies that the value isn't in the list. For filtering and excluding a group of IDs.
BETWEEN
WHERE OrderDate BETWEEN '2023-01-01' AND '2023-12-31'
A continuous filter range including boundaries. Common for date and price ranges.
NOT BETWEEN
WHERE Age NOT BETWEEN 20 AND 30
Fetches data outside the range. For detecting 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 containing a pattern. For data cleaning.
IS NULL
WHERE DeliveryDate IS NULL
NULL = "the information doesn't exist". You can't compare it with =. IS NULL locates empty cells.
IS NOT NULL
WHERE PhoneNumber IS NOT NULL
Locates rows that have some content. For cleaning reports and analyzing over whole data.
= equals
WHERE Status = 'Active'
The basic comparison operator. Requires an exact match.
<> / != different
WHERE Role <> 'Admin'
Both syntaxes are identical. Shows everything that isn't the defined value.
> greater than
WHERE Amount > 1000
Values higher than the reference point. Doesn't include the value itself.
< less than
WHERE InventoryLevel < 10
Values below the reference point. For alerts on inventory about to run out.
>= greater than or equal
WHERE Age >= 18
Includes the threshold point. The correct way to define an entry threshold.
<= 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 — stops at the first match. Preferable to JOIN in many cases.
NOT EXISTS
WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustID = c.ID)
Entities that have no match in another table. Usually preferable to NOT IN performance-wise.
ANY
WHERE Price > ANY (SELECT Price FROM Competitors)
Comparison against a set of results. It's 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 with no exception.
Sorting, grouping, and formatting the view
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 — from smallest to largest, A to Z. The default, can be omitted.
DESC
ORDER BY OrderDate DESC
Descending sort. For the most recent date, the most profitable, the highest — first.
TOP
SELECT TOP 10 * FROM Customers ORDER BY Score DESC
The first X rows only (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 between result pages.
FETCH NEXT
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY
Complements OFFSET. Sets how many rows to fetch after the skip.
GROUP BY
SELECT Category, COUNT(*) FROM Products GROUP BY Category
creates groups from records with an identical value and enables aggregate calculations for each group.
HAVING
GROUP BY Category HAVING COUNT(*) > 50
"The WHERE of the groups." Filters after the calculation has finished. It requires aggregation 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.
Aggregation functions
COUNT(col)
SELECT COUNT(Email) FROM Customers
Counts data 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
Arithmetic average while ignoring 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 alphabetically first name.
MAX()
SELECT MAX(OrderDate) FROM Orders
The highest value. For the most profitable deal, for the most recent timestamp.
Connections (JOINs) and union 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 active-transaction reports.
LEFT JOIN
SELECT c.Name, o.Amount FROM Customers c LEFT JOIN Orders o ON c.ID = o.CustID
All rows of the left table, including those with no match (NULL on the right). Used to find 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 of the right table. Usually it's preferable to switch 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 + gaps from both sides. For auditing and finding mismatches.
CROSS JOIN
SELECT Colors.Name, Sizes.Name FROM Colors CROSS JOIN Sizes
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 in 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 joining column has the same name in both tables.
UNION
SELECT City FROM Customers UNION SELECT City FROM Suppliers
Combines queries vertically and removes duplicates. Requires 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's no overlap.
INTERSECT
SELECT ProductID FROM Sales2023 INTERSECT SELECT ProductID FROM Sales2024
Intersect — 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 aren't in the second. Products sold only in 2023.
DDL — Defining Structure and 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 DB entirely — 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 the column structure and data types.
ALTER TABLE
ALTER TABLE Users ADD Email VARCHAR(100)
Structural changes to an existing table — adding columns, deleting, changing data type.
DROP TABLE
DROP TABLE Users
Total deletion — the data and the structure are deleted. We'll need CREATE from scratch.
TRUNCATE TABLE
TRUNCATE TABLE Logs
Empties content quickly. The structure stays. Faster than DELETE — for resetting a system.
CREATE INDEX
CREATE INDEX idx_ln ON Employees (LastName)
A "table of contents" for a column. Allows jumping directly to a record instead of scanning everything.
DROP INDEX
DROP INDEX idx_ln ON Employees
deletes an index. Indexes slow down writes — deleting unused ones improves performance.
CREATE VIEW
CREATE VIEW ActiveUsers AS SELECT * FROM Users WHERE Status = 'Active'
A "virtual table" — saves a complex query as a name. Hides logic from users.
DROP VIEW
DROP VIEW ActiveUsers
Deletes the View. The original data remains unaffected.
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 removing old code.
CREATE FUNCTION
CREATE FUNCTION CalcTax (@V DECIMAL) RETURNS DECIMAL AS BEGIN RETURN @V * 1.17 END
A function suited for repeated calculation. Returns a clean value and is used in lookups.
PRIMARY KEY
CREATE TABLE Users (ID INT PRIMARY KEY, Name VARCHAR(50))
A unique "ID" for every row. Prevents duplicates and requires an existing value.
FOREIGN KEY
CONSTRAINT FK_User FOREIGN KEY (UserID) REFERENCES Users(ID)
Creates a hard link between tables. Prevents entering fictitious IDs that don't exist.
UNIQUE
CREATE TABLE Users (Email VARCHAR(100) UNIQUE)
All values in the column must be different. 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 business rules. Doesn't allow a negative price.
DEFAULT
CREATE TABLE Users (Status VARCHAR(20) DEFAULT 'Pending')
Fills in an automatic value if the user didn't enter one. Instead of the cell staying NULL.
IDENTITY / AUTO_INCREMENT
CREATE TABLE Users (ID INT IDENTITY(1,1) PRIMARY KEY)
An automatic running counter (1,2,3...). Every new row gets a unique ID.
Transactions and 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 granted permissions. For changing roles and protecting sensitive data.
BEGIN TRAN
BEGIN TRAN; UPDATE Accounts SET Balance = Balance - 100...
Opens a block of work — all the operations are managed as a unit. Until committed, they're conditional.
COMMIT
COMMIT
Seals the transaction and locks in all the operations permanently and irreversibly.
ROLLBACK
ROLLBACK
The safety net. Cancels all changes from the start of the transaction and returns to the previous state.
SAVEPOINT
SAVEPOINT Stage1
A temporary savepoint in 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 NULLs gracefully — ignores them.
CONCAT_WS()
SELECT CONCAT_WS(', ', City, State, Country) FROM Addresses"Concatenate With Separator". Sets a single delimiter and joins everything automatically.
SUBSTRING()
SELECT SUBSTRING(IDNumber, 1, 4) FROM Customers
Cutting text by position: column name, starting character, number of characters.
LEN() / LENGTH()
WHERE LEN(Password) < 8
Counts how many characters are in the text. For short-password control and data validation.
UPPER()
SELECT UPPER(CountryCode) FROM Countries
Converts to uppercase. For normalizing comparisons — both sides to UPPER = a uniform search.
LOWER()
SELECT LOWER(Email) FROM Users
Converts to lowercase. Common convention for emails.
TRIM()
SELECT TRIM(Username) FROM Accounts
Trims spaces from the start and end. For "cleaning up" 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 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 splitting emails of varying lengths.
PATINDEX()
SELECT PATINDEX('%[0-9]%', Address) FROM CustomersLocates smart patterns (SQL Server). For identifying 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 and time processing functions
GETDATE() / NOW()
SELECT GETDATE()
Current date and time down to the millisecond. Automatic timestamp for new records.
CURRENT_DATE
SELECT CURRENT_DATE
Just today's date without the time. For conditions that only care about the date.
CURRENT_TIME
SELECT CURRENT_TIME
The current time without the date. For efficiency and daily load reports.
DATEADD()
SELECT DATEADD(day, 30, OrderDate) AS DueDate FROM Orders
Adds (or subtracts, with a minus) time to a date. For calculating due dates.
DATEDIFF()
SELECT DATEDIFF(day, OrderDate, ShipDate) FROM Orders
A gap between two dates. For response times, customer age, days to 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 a 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
Day of the month (1-31). For birthdays and monthly credit 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 closings.
ISDATE()
SELECT * FROM Import WHERE ISDATE(RawText) = 1
Checks whether a text is a valid date. Guards against bad data from forms.
Mathematical functions
ROUND()
SELECT ROUND(Price, 2) FROM Products
Rounds to X decimal points. For precise invoices.
CEILING()
SELECT CEILING(4.1) -- Returns 5
Rounds every fraction up to a whole. 4.1 buses → you need to rent 5.
FLOOR()
SELECT FLOOR(4.9) -- Returns 4
Truncates down to a whole number. 4.9 packages → you can only pack 4.
ABS()
SELECT ABS(Income - Expense) FROM Books
Absolute value — negative becomes positive. For calculating the size of gaps.
POWER()
SELECT POWER(Radius, 2) FROM Shapes
Power. For compound interest, area calculations.
SQRT()
SELECT SQRT(Area) FROM Squares
Square root. For distance and standard deviation calculations.
RAND()
SELECT RAND()
A random number between 0 and 1. For draws 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
Built-in constant π. For precise geometry calculations.
MOD() / %
WHERE EmployeeID % 2 = 0
Division remainder. For finding even rows and splitting lists into equal-sized paths.
Smart conditionals and 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 = the condition to check, THEN = the output if the condition is true.
COALESCE()
SELECT COALESCE(Mobile, HomePhone, 'No Phone') FROM Contacts
Returns the first value that isn't NULL. A "priority cascade" for missing data.
ISNULL() / IFNULL()
SELECT ISNULL(Discount, 0) FROM Sales
If NULL, returns a fallback value. Essential in calculations — NULL breaks mathematical operations.
NULLIF()
SELECT Amount / NULLIF(Quantity, 0) FROM Inventory
If two values are identical → returns NULL. Prevents a division-by-zero crash.
Data type conversions and upgrades
IIF()
SELECT IIF(Stock > 0, 'In Stock', 'Out of Stock') FROM Products
Shortened CASE for two states. SQL Server. Three inputs: condition, true, false.
CAST()
SELECT CAST(Price AS INT) FROM Products
A standard tool for converting data type. Number to text for concatenation, text to number for addition.
CONVERT()
SELECT CONVERT(VARCHAR(10), OrderDate, 103) FROM Orders
CAST's sister with formatting ability. 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 — window functions
OVER()
SELECT Name, SUM(Salary) OVER() AS Total FROM Employees
Gateway to window functions. Aggregate calculations on every record without GROUP BY. Salary + total company salary together.
PARTITION BY
SUM(Salary) OVER(PARTITION BY Department)
Splits the window into subgroups. Department salary total instead of the whole company. Resets in every 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)
Competition ranking. Tie → both get place 2. Next → jumps to 4 (skips 3).
DENSE_RANK()
DENSE_RANK() OVER(ORDER BY Salary DESC)
Dense ranking. After a tie for 2nd place, the next gets 3 (not 4). For credit and subscriptions.
NTILE()
NTILE(4) OVER(ORDER BY Salary)
Divides 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 next to today — for calculating % growth in a single query.
FIRST_VALUE()
FIRST_VALUE(Name) OVER(ORDER BY HireDate)
The first record in the window for each row. The most senior employee next to every 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 and Architecture
WITH (CTE)
WITH HighVal AS (SELECT * FROM Orders WHERE Amount > 1000) SELECT * FROM HighVal
A temporary working table that hovers over the whole query. Turns complicated code into readable steps.
PIVOT
PIVOT(SUM(Amount) FOR Year IN ([2022], [2023])) AS pvt
Converts rows to columns. For reports like Excel — months of the year as columns.
UNPIVOT
UNPIVOT (Amount FOR Year IN ([2022], [2023])) AS unpvt
Converts columns back to rows. For normalizing matrices from external sources.
EXEC / EXECUTE
EXEC GetUser @UserID = 5
Executes stored procedures. Also for running dynamic code built as text at runtime.
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 and XML — semi-structured data
FOR JSON
SELECT Name, Age FROM Users FOR JSON AUTO
Converts SQL results to JSON format. Lets web developers use the data directly in JavaScript with no further conversions.
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 from a large JSON column.
JSON_QUERY()
SELECT JSON_QUERY(JsonCol, '$.Hobbies') FROM Customers
Extracts a complex object or array from JSON. As opposed to 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 entire document.
ISJSON()
SELECT * FROM Logs WHERE ISJSON(RawData) = 1
Checks whether a string is valid JSON. Returns 1 if valid, 0 if broken. For filtering bad data.
FOR XML
SELECT Name FROM Users FOR XML PATH('User')Converts table results to XML format. Still common in banks, governments, and legacy systems.
OPENXML()
SELECT * FROM OPENXML(@doc, '/Users/User')
Converts an XML string to 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 several rows into a single string with a delimiter. "5 employees" → "Dan, Yael, Ron, Michal, Noa".
STRING_SPLIT()
SELECT value FROM STRING_SPLIT('Apple,Banana,Orange', ',')Splits a string into separate rows by a delimiter. 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 manually.
SOUNDEX()
WHERE SOUNDEX(Name) = SOUNDEX('Smith')Converts a word to a phonetic code (sound). For finding names with spelling mistakes — "Smythe" = "Smith".
DIFFERENCE()
SELECT DIFFERENCE(Name, 'Michael') FROM Users
A 0-4 score for phonetic similarity between two words. 4 = identical sounds. 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 SQL injection.
TRANSLATE()
SELECT TRANSLATE(Phone, '()-', ' ') FROM Contacts
Replaces multiple characters at once. For cleaning punctuation marks from 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 to a character. CHAR(13) = line break, CHAR(9) = Tab. For characters you cannot type.
System functions and identifiers
DB_NAME()
SELECT DB_NAME()
Returns the name of the current database. For identifying where code is running on servers with multiple databases.
OBJECT_ID()
IF OBJECT_ID('TempTable') IS NOT NULL DROP TABLE TempTableChecks whether a table/object exists. Prevents "table doesn't exist" errors before DROP.
OBJECT_NAME()
SELECT OBJECT_NAME(1234567)
Converts an internal ID to a readable name. For analyzing system tables and tracking performance.
@@VERSION
SELECT @@VERSION
Returns the SQL Server version and operating system. For checking compatibility before running code.
@@ROWCOUNT
UPDATE Users SET Status=1; SELECT @@ROWCOUNT
The number of rows affected by the last command. For signaling success/failure of 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 automatic ID generated for the last row. For getting the 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 a table's growth rate.
HOST_NAME()
SELECT HOST_NAME()
The name of the computer the connection was made from. For Audit systems to trace who accessed from where.
SUSER_SNAME()
SELECT SUSER_SNAME()
the current username. For building Row Level Security — each employee sees only their own data.
@@SERVERNAME
SELECT @@SERVERNAME
The SQL server name. For applications with multiple servers that need to know which one they're connected to.
Error Handling and 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. To log the error in the DB and still show an error to the user.
RAISERROR
RAISERROR('Out of stock', 16, 1)raises a deliberate custom error. For stopping a process on a business problem (0 stock) even without a SQL error.
ERROR_MESSAGE()
INSERT INTO Logs VALUES (ERROR_MESSAGE())
The full error text inside CATCH. For saving error details to a log table.
ERROR_NUMBER()
IF ERROR_NUMBER() = 2627 PRINT 'duplicate'
A 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 step-by-step data processing in cases where a SET-based operation isn't possible.
BREAK
IF @Value = 0 BREAK
Immediately stops a WHILE loop. 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 isn't valid, 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 jobs.
Advanced security permissions
CREATE LOGIN
CREATE LOGIN JohnDoe WITH PASSWORD = 'Pass1'
creates access to the SQL server. The first step — "entering the building". It still does not grant access to the data.
CREATE USER
CREATE USER JohnDoeUser FOR LOGIN JohnDoe
Connects a login to a specific database. An "employee badge for a specific room" after entering the building.
ALTER LOGIN / USER
ALTER LOGIN JohnDoe DISABLE
Editing or freezing a user. For immediately revoking access when an employee leaves, without deleting history.
CREATE ROLE
CREATE ROLE DataAnalysts
Creates a permissions group. Manage one instead of every user individually — scalable and clean.
DENY
DENY DELETE ON Orders TO DataAnalysts
Aggressive blocking. Overrides every other GRANT — even for an admin group member.
EXECUTE AS
EXECUTE AS USER = 'AdminUser'
Impersonating 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 and optimization
WITH (NOLOCK)
SELECT * FROM MassiveTable WITH (NOLOCK)
A read without waiting for locks. Faster, but may return data that's mid-update (Dirty Read).
OPTION (RECOMPILE)
SELECT * FROM Users WHERE ID = @Var OPTION (RECOMPILE)
Forces a recalculation 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 will use. Prevents one query from paralyzing the whole server.
SET NOCOUNT ON
SET NOCOUNT ON; UPDATE Users SET Name='X'
Suppresses "rows affected" messages. Reduces bandwidth load in procedures with thousands of operations.
DBCC CHECKDB
DBCC CHECKDB('MyDatabase')Scans the integrity of all the data on disk. Finds file damage (corruption). Run it every week.
UPDATE STATISTICS
UPDATE STATISTICS Customers
Updates the statistical map of the data. Speeds up queries that started running slowly.
ALTER INDEX REBUILD
ALTER INDEX ALL ON Customers REBUILD
Builds 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
Reorganizing without locking. Gentler than REBUILD. For quiet day-to-day maintenance.
DBCC FREEPROCCACHE
DBCC FREEPROCCACHE
Clears all execution plan memory. For resetting a server that's behaving slowly due to Plan Sniffing.
DBCC SHRINKDATABASE
DBCC SHRINKDATABASE('MyDB')Shrinks the DB and returns disk space to the operating system. After deleting large amounts of data.
Advanced dates and time zones
DATEFROMPARTS()
SELECT DATEFROMPARTS(2023, 10, 31)
Builds a date from 3 separate numbers (year, month, day). For merging separate fields from an application.
TIMEFROMPARTS()
SELECT TIMEFROMPARTS(14, 30, 0, 0, 0)
Builds a time from 5 numbers. For attendance 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) with high precision.
SYSUTCDATETIME()
SELECT SYSUTCDATETIME()
The current time in universal UTC. Essential for global applications with users all over the world.
AT TIME ZONE
SELECT OrderDate AT TIME ZONE 'Israel Standard Time'
Converts a date to a specific time zone, including daylight saving/standard time. No manual calculations.
SWITCHOFFSET()
SELECT SWITCHOFFSET(OrderDate, '+03:00')
Manual timezone offset change. For displaying the 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). Different from DATEPART, which returns a number.
Temporary tables, GUIDs, and sequences
CREATE TABLE #Temp
CREATE TABLE #UserStats (ID INT, Score INT)
A local temporary table — 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 cached data between users.
DECLARE @Table TABLE
DECLARE @MyList TABLE (EmpID INT)
A table in RAM only. Very fast for small quantities — no disk writes.
NEWID()
INSERT INTO Users (ID) VALUES (NEWID())
generates a globally unique GUID. For secure, unguessable identities and for merging data across servers.
NEWSEQUENTIALID()
DEFAULT NEWSEQUENTIALID()
A GUID that runs in ascending order. Solves the performance problem of NEWID — 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 multiple tables can share.
NEXT VALUE FOR
INSERT INTO Invoices (ID) VALUES (NEXT VALUE FOR InvoiceSeq)
Pulls the next number from SEQUENCE. For invoice numbering without duplicates.
FORMATMESSAGE()
FORMATMESSAGE('Welcome %s, code: %i', 'Dan', 123)Injecting variables into a text template. For building readable, centralized error messages.
Advanced window functions
PERCENT_RANK()
PERCENT_RANK() OVER(ORDER BY Score)
Relative ranking in percentages (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 data that's lower/equal to the current value. For Bell curves.
PERCENTILE_CONT()
PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY Salary) OVER()
Median with interpolation. Returns the average of two values if there's no exact middle value.
PERCENTILE_DISC()
PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY Salary) OVER()
An exact median — returns only a value that actually exists in the database. 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 multi-dimensional matrix.
CHOOSE()
SELECT CHOOSE(MonthNum, 'Jan', 'Feb', 'Mar'...) FROM Sales
Selects a value by index from a list. CHOOSE(2,...) = the second item. A short replacement for 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 NULL appears in the sort order. Prevents empty values from jumping to the top of the report. Support: Postgres/MySQL.
Dynamic PIVOT
EXEC(@DynamicPivotQuery)
A PIVOT that builds itself dynamically based on the data. For pivot tables whose columns aren't known in advance.
Duplicating data and working with tables (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 without an 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
Updating a table using data from another table with 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
Deleting rows by a condition from a linked table. More precise deletion 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 created row. The PostgreSQL equivalent of OUTPUT — for getting a new ID.
EXCEPT ALL
SELECT ID FROM A EXCEPT ALL SELECT ID FROM B
Subtraction with duplicates — doesn't remove duplicates from the result. Faster than regular 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()
Percent of grand total. Each row divided by the total sum — for percent of sales out of the whole company.
SUM OVER (Running Total)
SUM(Amount) OVER(ORDER BY Date ROWS UNBOUNDED PRECEDING)
Running total — every 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)
Running maximum/minimum — "what was the peak so far?" at every point in time.
COUNT DISTINCT (Approx)
APPROX_COUNT_DISTINCT(CustomerID)
Approximate distinct count (~2% error). 10x faster than COUNT DISTINCT on Big Data.
LISTAGG() / GROUP_CONCAT
LISTAGG(Name, ', ') WITHIN GROUP (ORDER BY Name)
Combining 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])
Pivot with different 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 every row. Like a JOIN but with correlation — for the TOP N per customer.
OUTER APPLY
SELECT * FROM Customers c OUTER APPLY (...) t
Like CROSS APPLY but also returns rows with no match (like LEFT JOIN).
GENERATE_SERIES()
SELECT * FROM GENERATE_SERIES(1, 12)
Generates a sequence of numbers/dates. For calendars, for reports without "gaps" in missing dates.
Full-Text Search and text search
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-text 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 ranking (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 '\'
Searching for the % character as a literal string (not a wildcard). For searching "100%" as literal text.
REGEXP / RLIKE (MySQL)
WHERE Email REGEXP '^[a-zA-Z]+@'
Search with Regular Expressions. For validating complex formats — emails, phone numbers, 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 and 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 columnstore index for analytics. 10-100x faster for GROUP BY/SUM in a Data Warehouse.
CREATE COVERING INDEX
CREATE INDEX idx_cov ON Orders(CustID) INCLUDE(Amount, Date)
An index with additional 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 identifying unnecessary Table Scans and missing indexes.
SET STATISTICS IO ON
SET STATISTICS IO ON; SELECT ...
Shows how many disk pages were read. For finding queries with high I/O that cause slowness.
SET STATISTICS TIME ON
SET STATISTICS TIME ON; SELECT ...
Shows CPU time and parsing. For precise performance analysis of slow queries.
WITH (FORCESCAN)
SELECT * FROM T WITH (FORCESCAN)
Forces a Table Scan even when there's an index. 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 gets it wrong and chooses a Scan — FORCESEEK fixes that.
sp_updatestats
EXEC sp_updatestats
Updates statistics for all the tables in the DB. After a large data load.
Stored Procedures and Advanced Functions
CREATE OR ALTER PROCEDURE
CREATE OR ALTER PROCEDURE GetOrders AS ...
Creates a procedure or replaces an existing one in a single command. No 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 a parameter — it will automatically take 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 on 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)
Running dynamic SQL from a string. For building flexible queries at runtime.
sp_executesql
EXEC sp_executesql @SQL, '@ID INT', @ID=5
Dynamic SQL with parameters. Safer than plain EXEC — prevents SQL Injection.
sys.procedures
SELECT * FROM sys.procedures
A list of all the procedures in the DB. For documentation and checking existing code.
Triggers — triggers
CREATE TRIGGER (AFTER INSERT)
CREATE TRIGGER trg_ins ON Orders AFTER INSERT AS ...
A trigger that runs automatically after INSERT. For automatic events — sending email, updating a log.
CREATE TRIGGER (INSTEAD OF)
CREATE TRIGGER trg_v ON MyView INSTEAD OF INSERT AS ...
Replaces another operation. To allow INSERT on a View that doesn't support it directly.
INSERTED / DELETED tables
SELECT * FROM INSERTED; SELECT * FROM DELETED
Virtual tables inside a trigger with the data that was inserted/deleted. For keeping old values.
DISABLE TRIGGER
DISABLE TRIGGER trg_ins ON Orders
Temporarily disables a trigger. For bulk data import without triggering automatic logic.
DDL TRIGGER
CREATE TRIGGER trg_ddl ON DATABASE FOR DROP_TABLE AS ...
A trigger on DDL operations like DROP TABLE. To prevent 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 pieces 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)
Creating a partitioned table. SQL automatically manages where a row is stored based on the value.
Partition Elimination
SELECT * FROM Sales WHERE Year=2023
SQL reads only the relevant partition and skips the rest. 10x+ faster on huge tables.
ALTER TABLE SWITCH PARTITION
ALTER TABLE Sales SWITCH PARTITION 1 TO SalesArchive
Moving a whole 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 maintains automatic history. SQL automatically saves every old version.
FOR SYSTEM_TIME AS OF
SELECT * FROM Products FOR SYSTEM_TIME AS OF '2023-01-01'
Returns what the data looked like at a specific date. "Time Travel" — to see 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 in 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 showing a full timeline of changes.
ALTER TABLE OFF System Versioning
ALTER TABLE Products SET (SYSTEM_VERSIONING=OFF)
Disables history management. For cleaning up historical data and structural changes.
Graph Database and Hierarchies
CREATE NODE TABLE
CREATE TABLE Person(ID INT) AS NODE
A relationships table (SQL Graph). For representing social networks, graphs, and dependencies.
CREATE EDGE TABLE
CREATE TABLE Knows AS EDGE
The 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, organizations, product BOM trees.
PATH with CTE
'' AS path ... t.path+'/'+c.Name
Building a full path in a tree (Root/Branch/Leaf). For displaying a breadcrumb and analyzing tree depth.
Geography and Location (Spatial)
geometry/geography types
Location geography
Special data types for GPS points, lines and polygons. For maps and distances.
STDistance()
Loc1.STDistance(Loc2)
Distance between two geographic points in meters. For dispatching the nearest delivery.
STContains()
Zone.STContains(Point)
Checking whether a point lies inside a region. For the question "is the customer inside the delivery area?".
STIntersects()
Area1.STIntersects(Area2)
Whether two areas intersect. For detecting overlap between service areas.
geography::Point()
geography::Point(31.77, 35.21, 4326)
Creates a GPS point (latitude, longitude). For storing customer and branch locations.
Row-Level Security and Dynamic Masking
CREATE SECURITY POLICY
CREATE SECURITY POLICY SalesFilter ADD FILTER PREDICATE dbo.fn_filter(SalesRep) ON Orders
Row Level Security — every employee sees only their own rows. Automatic, 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 hides sensitive data: a***@***.com. Analysts see it masked, the DBA sees everything.
ADD MASK
ALTER TABLE Customers ALTER COLUMN Phone ADD MASKED WITH (FUNCTION='partial(0,"XXX-XXX-",4)')
Adding masking to an existing column. XXX-XXX-1234 — you only see the last 4 digits.
GRANT UNMASK
GRANT UNMASK TO AdminUser
Lets a specific user see real data. For a "need to know" policy.
Always Encrypted and Transparent Data Encryption
Always Encrypted
SSN ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY=CEK, ENCRYPTION_TYPE=Deterministic)
Column-level encryption. Even a DBA can't see the data — only the application can.
Transparent Data Encryption
ALTER DATABASE MyDB SET ENCRYPTION ON
Encrypting 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
Encrypting 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 manual encryption/decryption of data.
ENCRYPTBYKEY / DECRYPTBYKEY
ENCRYPTBYKEY(KEY_GUID('MyKey'), CreditCard)Manual encryption and decryption of a field. For encrypted storage with recovery as needed.
High Availability and Replication
ALTER DATABASE SET RECOVERY FULL
ALTER DATABASE MyDB SET RECOVERY FULL
Full recovery mode — keeps the entire operations log. Required for a production environment with a high SLA.
BACKUP LOG
BACKUP LOG MyDB TO DISK='log.bak'
Transaction log backup. Enables restoring to a precise point in time — Point in Time Recovery.
RESTORE DATABASE WITH STANDBY
RESTORE DATABASE MyDB WITH STANDBY='undo.bak'
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 continue. For a restore chain from multiple backups.
sys.dm_exec_requests
SELECT * FROM sys.dm_exec_requests
All the 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 issues.
sys.dm_index_usage_stats
SELECT * FROM sys.dm_db_index_usage_stats WHERE user_seeks=0
Unused indexes. Candidates for deletion — save space and speed up writes.
sys.dm_missing_index_details
SELECT * FROM sys.dm_db_missing_index_details
Missing indexes that the server recommends. For a quick performance improvement.
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 locating bottlenecks (CPU, I/O, locks).
Cloud SQL and 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
Reading CSV/Parquet directly from the cloud. In Azure Synapse and SQL Server 2019+.
PolyBase
SELECT * FROM ExternalTable
Queries on Hadoop, Azure, 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-time 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 routine queries between servers and between different databases.
Schemas, metadata, and 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 in every SQL engine.
sys.columns
SELECT * FROM sys.columns WHERE object_id=OBJECT_ID('Orders')All the columns of a table with data types. For documentation and building dynamic SQL.
sys.foreign_keys
SELECT * FROM sys.foreign_keys
All the foreign keys in the DB. For documenting relationships and an automatic ERD graph.
sp_help
EXEC sp_help 'Orders'
Full information about a table — columns, indexes, keys. The table's "ID card".