📘 Comprehensive T-SQL Knowledge Base

Beginner & Intermediate Levels — Detailed Answers with Examples

🔰 Beginner Level

Basic Queries & Syntax

1. SELECT * vs SELECT specific columns? When to avoid SELECT *?

SELECT * retrieves all columns from a table. Specific columns only fetch the columns you name.

Performance: * reads unnecessary data, increasing I/O and memory usage.
Maintainability: If table schema changes (new/dropped columns), * breaks applications expecting fixed column order.
Best Practice: Always list columns explicitly in production code.

-- ❌ Avoid
SELECT * FROM Sales.Orders;

-- ✅ Recommended
SELECT OrderID, CustomerID, OrderDate FROM Sales.Orders;

2. Retrieve distinct values from a column

SELECT DISTINCT City FROM Customers;

City
-------
London
Berlin
Madrid

3. Difference between WHERE and HAVING

WHERE filters rows before aggregation. HAVING filters groups after aggregation with GROUP BY.

-- WHERE filters individual orders
SELECT CustomerID, COUNT(*) AS OrderCount
FROM Orders
WHERE OrderDate > '2023-01-01'
GROUP BY CustomerID
HAVING COUNT(*) > 2;  -- HAVING filters aggregated groups

4. Aliases (AS) for columns and tables

-- Column alias
SELECT FirstName + ' ' + LastName AS FullName FROM Employees;

-- Table alias (useful for joins)
SELECT o.OrderID, c.CustomerName
FROM Orders AS o
INNER JOIN Customers AS c ON o.CustomerID = c.CustomerID;

Rules: AS is optional but recommended. Use square brackets [Full Name] or double quotes for spaces.

5. Order of execution in a SELECT statement

Logical processing order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY

SELECT TOP 5 CustomerID, COUNT(*) AS Cnt   -- 5. SELECT
FROM Orders                                  -- 1. FROM
WHERE OrderDate > '2023-01-01'               -- 2. WHERE
GROUP BY CustomerID                          -- 3. GROUP BY
HAVING COUNT(*) > 1                          -- 4. HAVING
ORDER BY Cnt DESC;                           -- 6. ORDER BY

6. Concatenate strings: + vs CONCAT()

-- Using + (NULL propagation risk)
SELECT 'Hello' + NULL;  -- Result: NULL

-- Using CONCAT() (ignores NULLs)
SELECT CONCAT('Hello', NULL, 'World');  -- Result: HelloWorld

+ operator returns NULL if any operand is NULL. CONCAT treats NULL as empty string.

7. TOP vs OFFSET-FETCH

-- TOP (legacy, no easy pagination)
SELECT TOP 10 * FROM Products ORDER BY Price DESC;

-- OFFSET-FETCH (standard, page 2 with 10 rows)
SELECT * FROM Products ORDER BY Price DESC
OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

8. Sort results ascending/descending

SELECT ProductName, Price FROM Products ORDER BY Price ASC;  -- ascending default
SELECT ProductName, Price FROM Products ORDER BY Price DESC; -- descending

9. Different ways to comment code

-- Single line comment

/*
   Multi-line comment
   block
*/

10. IN, BETWEEN, LIKE operators

-- IN
SELECT * FROM Products WHERE CategoryID IN (1, 2, 3);

-- BETWEEN (inclusive)
SELECT * FROM Orders WHERE OrderDate BETWEEN '2023-01-01' AND '2023-01-31';

-- LIKE (wildcards % and _)
SELECT * FROM Customers WHERE LastName LIKE 'Sm%';   -- starts with Sm
SELECT * FROM Customers WHERE Phone LIKE '555-___';  -- single char

Data Types

11. CHAR vs VARCHAR, NCHAR vs NVARCHAR

Type	Storage	Use Case
CHAR(n)	Fixed length, pads with spaces	State codes ('CA'), fixed-length data
VARCHAR(n)	Variable length, 2 bytes overhead	Names, emails
NCHAR(n)	Fixed Unicode (double bytes)	Multilingual fixed data
NVARCHAR(n)	Variable Unicode	International text, emoji

N prefix stores Unicode, essential for global applications.

12. DECIMAL vs FLOAT

-- DECIMAL (exact numeric, precise)
DECLARE @d DECIMAL(10,2) = 12345678.12;

-- FLOAT (approximate, floating point)
DECLARE @f FLOAT = 1.123456789012345;

Use DECIMAL for money, quantities. FLOAT for scientific calculations.

13. DATETIME, DATETIME2, DATE

-- DATETIME: 1753-9999, accuracy 3.33ms, 8 bytes
-- DATETIME2: 0001-9999, 100ns precision, 6-8 bytes
-- DATE: 0001-9999, date only, 3 bytes

SELECT CAST(GETDATE() AS DATE);        -- 2023-10-05
SELECT CAST(GETDATE() AS DATETIME2);   -- 2023-10-05 14:30:45.1234567

14. Maximum size for VARCHAR(MAX)

2^31-1 bytes (2 GB). Used for large text storage.

15. Handle NULL in calculations and comparisons

-- NULL arithmetic yields NULL
SELECT 10 + NULL;  -- NULL

-- Use ISNULL/COALESCE
SELECT ISNULL(Price, 0) + Tax FROM Products;

-- NULL comparison uses IS NULL, not =
SELECT * FROM Customers WHERE Region IS NULL;

16. INT, BIGINT, SMALLINT, TINYINT

Type	Range	Storage
TINYINT	0 to 255	1 byte
SMALLINT	-32,768 to 32,767	2 bytes
INT	-2.1B to 2.1B	4 bytes
BIGINT	-9.2 quintillion to 9.2 quintillion	8 bytes

17. CAST and CONVERT

SELECT CAST(123.45 AS INT);               -- 123
SELECT CONVERT(VARCHAR, GETDATE(), 101);  -- '01/15/2023' (style format)

CONVERT has a style parameter for dates, CAST is ANSI standard.

Basic Functions

18. COUNT(*), COUNT(column), COUNT(DISTINCT column)

SELECT COUNT(*) FROM Employees;           -- counts all rows
SELECT COUNT(Region) FROM Employees;      -- counts non-NULL regions
SELECT COUNT(DISTINCT City) FROM Employees; -- unique cities

19. LEFT, RIGHT, SUBSTRING, LEN

SELECT LEFT('Hello World', 5);      -- 'Hello'
SELECT RIGHT('Hello World', 5);     -- 'World'
SELECT SUBSTRING('Database', 2, 4); -- 'atab'
SELECT LEN('Test  ');               -- 4 (excludes trailing spaces)

20. ISNULL() vs COALESCE()

-- ISNULL takes 2 args, returns first non-null, uses first arg data type
SELECT ISNULL(NULL, 'Fallback');

-- COALESCE takes multiple args, returns first non-null, data type precedence
SELECT COALESCE(NULL, NULL, 'Third', 'Fourth');

21. GETDATE(), DATEADD(), DATEDIFF()

SELECT GETDATE();                               -- current datetime
SELECT DATEADD(DAY, 7, '2023-01-01');           -- add 7 days
SELECT DATEDIFF(DAY, '2023-01-01', GETDATE());  -- difference in days

22. UPPER(), LOWER(), TRIM(), LTRIM(), RTRIM()

SELECT UPPER('sql');          -- 'SQL'
SELECT LOWER('SERVER');       -- 'server'
SELECT TRIM('  text  ');      -- 'text' (removes both sides, SQL Server 2017+)
SELECT LTRIM('  left');       -- 'left'
SELECT RTRIM('right  ');      -- 'right'

🟡 Intermediate Level

JOINs

23. All types of JOINs

-- INNER JOIN: matching rows only
SELECT o.OrderID, c.CustomerName
FROM Orders o INNER JOIN Customers c ON o.CustomerID = c.CustomerID;

-- LEFT JOIN: all from left, matching from right
SELECT c.CustomerName, o.OrderID
FROM Customers c LEFT JOIN Orders o ON c.CustomerID = o.CustomerID;

-- RIGHT JOIN: all from right
-- FULL OUTER JOIN: all rows from both
-- CROSS JOIN: Cartesian product

24. Self-join

-- Find employees and their managers
SELECT e1.FirstName AS Employee, e2.FirstName AS Manager
FROM Employees e1
LEFT JOIN Employees e2 ON e1.ManagerID = e2.EmployeeID;

25. JOIN syntax vs old comma syntax

Modern JOIN ... ON separates relation conditions from filtering WHERE. Old comma style mixes both, risking unintended cross joins.

26. ON vs WHERE in JOINs

-- ON defines how tables relate
SELECT * FROM Orders o
LEFT JOIN Customers c ON o.CustomerID = c.CustomerID AND c.Active = 1;
-- WHERE filters result after join

27. Join using multiple conditions

SELECT * FROM TableA a
INNER JOIN TableB b ON a.ID = b.A_ID AND a.Status = b.Status;

28. Performance impact of joining large tables

Ensure indexes on join columns. Avoid functions on join columns. Consider hash vs merge join hints only if necessary.

Subqueries

29. Subquery in SELECT, FROM, WHERE

-- SELECT subquery (must return single value)
SELECT ProductName, (SELECT AVG(Price) FROM Products) AS AvgPrice FROM Products;

-- FROM subquery (derived table)
SELECT * FROM (SELECT CustomerID, COUNT(*) cnt FROM Orders GROUP BY CustomerID) AS Sub;

-- WHERE subquery
SELECT * FROM Products WHERE Price > (SELECT AVG(Price) FROM Products);

30. Correlated subquery

-- Re-executed for each outer row
SELECT ProductName, Price FROM Products p
WHERE Price > (SELECT AVG(Price) FROM Products WHERE CategoryID = p.CategoryID);

31. EXISTS and NOT EXISTS

SELECT CustomerName FROM Customers c
WHERE EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID);

32. IN vs EXISTS vs JOIN performance

EXISTS often faster for large lists; it stops after first match. JOIN can be efficient with proper indexes.

33. Subquery returning multiple columns

-- Use derived table or APPLY
SELECT * FROM Orders o
CROSS APPLY (SELECT TOP 1 ProductName, Price FROM Products WHERE ProductID = o.ProductID) p;

GROUP BY & Aggregation

34. GROUP BY with SUM, AVG, MIN, MAX

SELECT CategoryID, SUM(Price) AS Total, AVG(Price) AS AvgPrice
FROM Products GROUP BY CategoryID;

35. WHERE, GROUP BY, HAVING filtering

WHERE filters rows, GROUP BY creates groups, HAVING filters groups.

36. GROUP BY multiple columns

SELECT CategoryID, SupplierID, COUNT(*) FROM Products
GROUP BY CategoryID, SupplierID;

37. GROUPING SETS, CUBE, ROLLUP

SELECT CategoryID, SupplierID, SUM(Price)
FROM Products
GROUP BY GROUPING SETS ((CategoryID), (SupplierID), ());  -- subtotals

38. Running totals (pre/post 2012)

-- Post 2012 window function
SELECT OrderDate, Amount,
       SUM(Amount) OVER (ORDER BY OrderDate) AS RunningTotal
FROM Transactions;

-- Pre 2012: self-join or quirky update

39. NULLs in GROUP BY

NULLs are grouped together as one group.

Common Table Expressions (CTEs)

40. What is a CTE?

WITH SalesCTE AS (
    SELECT CustomerID, SUM(Amount) Total FROM Orders GROUP BY CustomerID
)
SELECT * FROM SalesCTE WHERE Total > 1000;

41. CTE vs temp table vs table variable

CTE: inline, not persisted. Temp table: physical, indexed. Table variable: memory-optimized, limited scope.

42. Recursive CTE

WITH OrgChart AS (
    SELECT EmployeeID, ManagerID, 0 AS Level FROM Employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, Level + 1
    FROM Employees e INNER JOIN OrgChart o ON e.ManagerID = o.EmployeeID
)
SELECT * FROM OrgChart;

43. CTE limitations

Single-use scope, cannot have indexes, only referenced immediately after definition.

44. Multiple CTEs

WITH CTE1 AS (...), CTE2 AS (...)
SELECT * FROM CTE1 JOIN CTE2 ON ...

Window Functions

45. ROW_NUMBER() vs RANK() vs DENSE_RANK() vs NTILE()

SELECT ProductName, Price,
       ROW_NUMBER() OVER (ORDER BY Price) AS RowNum,
       RANK() OVER (ORDER BY Price) AS RankVal,
       DENSE_RANK() OVER (ORDER BY Price) AS DenseVal,
       NTILE(4) OVER (ORDER BY Price) AS Quartile
FROM Products;

46. PARTITION BY

SELECT CategoryID, ProductName, Price,
       AVG(Price) OVER (PARTITION BY CategoryID) AS AvgByCat
FROM Products;

47. ROWS vs RANGE in window frames

ROWS counts physical rows, RANGE considers value duplicates.

48. LAG() and LEAD()

SELECT OrderDate, Amount,
       LAG(Amount) OVER (ORDER BY OrderDate) AS PrevAmount,
       LEAD(Amount) OVER (ORDER BY OrderDate) AS NextAmount
FROM Orders;

49. FIRST_VALUE() and LAST_VALUE()

SELECT ProductName, Price,
       FIRST_VALUE(Price) OVER (ORDER BY Price) AS Cheapest,
       LAST_VALUE(Price) OVER (ORDER BY Price ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) AS MostExpensive
FROM Products;

50. Default window frame

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (affects LAST_VALUE behavior).

Comprehensive T-SQL Knowledge Base