T-SQL

T-SQL (Transact-SQL) is an extension of SQL used in Microsoft SQL Server. It adds procedural programming features like variables, loops, error handling, and transactions.

• T-SQL supports procedural logic (e.g., IF, WHILE loops).
• Includes additional built-in functions.
• Supports error handling (TRY?CATCH).
• Allows stored procedures, triggers, and transactions.

• DQL (Data Query Language): SELECT
• DDL (Data Definition Language): CREATE, ALTER, DROP
• DML (Data Manipulation Language): INSERT, UPDATE, DELETE
• DCL (Data Control Language): GRANT, REVOKE
• TCL (Transaction Control Language): COMMIT, ROLLBACK, SAVEPOINT

• CHAR(n): Fixed-length (e.g., CHAR(10) always uses 10 bytes).
• VARCHAR(n): Variable-length, saves space by using only required bytes.

DECLARE @EmployeeName NVARCHAR(50);
SET @EmployeeName = 'John Doe';
PRINT @EmployeeName;

• INNER JOIN ? Returns matching rows from both tables.
• LEFT JOIN ? Returns all rows from the left table + matching rows from the right.
• RIGHT JOIN ? Returns all rows from the right table + matching rows from the left.
• FULL JOIN ? Returns all rows from both tables.
• CROSS JOIN ? Returns the Cartesian product of both tables.

SELECT e.EmployeeID, e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.DepartmentID = d.DepartmentID;

A Primary Key uniquely identifies each record in a table. It cannot contain NULL values and must be unique.

A Foreign Key establishes a relationship between two tables by referencing the Primary Key of another table.

Use ISNULL or COALESCE:

SELECT ISNULL(Salary, 0) FROM Employees;
SELECT COALESCE(Salary, Bonus, 0) FROM Employees;

• ISNULL allows only two parameters.
• COALESCE allows multiple parameters and returns the first non-null value.

Stored Procedures are precompiled SQL scripts that improve performance and reuse queries.

CREATE PROCEDURE GetEmployees
AS
BEGIN
    SELECT * FROM Employees;
END;
EXEC GetEmployees;

Functions return a single value or a table. Example of a scalar function:

CREATE FUNCTION GetTotalSalary(@EmpID INT)
RETURNS INT
AS
BEGIN
    DECLARE @Total INT;
    SELECT @Total = Salary FROM Employees WHERE EmployeeID = @EmpID;
    RETURN @Total;
END;

A Trigger is a special stored procedure that automatically executes when an event (INSERT, UPDATE, DELETE) occurs.

CREATE TRIGGER trgAfterInsert
ON Employees
AFTER INSERT
AS
BEGIN
    PRINT 'New Employee Added!';
END;

Transactions ensure ACID properties (Atomicity, Consistency, Isolation, Durability).

BEGIN TRANSACTION;
UPDATE Employees SET Salary = Salary + 1000 WHERE EmployeeID = 1;

IF @@ERROR = 0
    COMMIT;
ELSE
    ROLLBACK;

• UNION removes duplicates.
• UNION ALL keeps duplicates.

SELECT City FROM Customers
UNION
SELECT City FROM Suppliers;

A CTE is a temporary result set used for recursive queries.

WITH EmployeeCTE AS (
    SELECT EmployeeID, Name FROM Employees WHERE DepartmentID = 1
)
SELECT * FROM EmployeeCTE;

WITH EmployeeHierarchy AS (
    SELECT EmployeeID, ManagerID, Name FROM Employees WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.Name
    FROM Employees e
    JOIN EmployeeHierarchy eh ON e.ManagerID = eh.EmployeeID
)
SELECT * FROM EmployeeHierarchy;

A Pivot table transforms rows into columns.

SELECT *
FROM (SELECT EmployeeID, Department, Salary FROM Employees) AS SourceTable
PIVOT (SUM(Salary) FOR Department IN ([HR], [IT], [Finance])) AS PivotTable;

• WHERE filters rows before aggregation.
• HAVING filters rows after aggregation.

SELECT Department, COUNT(*) 
FROM Employees 
WHERE Salary > 50000 
GROUP BY Department
HAVING COUNT(*) > 5;

The RANK() function assigns a ranking to each row within a partition. Ties get the same rank, but the next rank is skipped.

SELECT Name, Salary, 
       RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees;

CASE is used for conditional logic.

SELECT Name, Salary,
    CASE 
        WHEN Salary > 70000 THEN 'High'
        WHEN Salary BETWEEN 40000 AND 70000 THEN 'Medium'
        ELSE 'Low'
    END AS SalaryCategory
FROM Employees;

Window functions perform calculations across a subset of rows related to the current row.

SELECT Name, Salary, 
       AVG(Salary) OVER (PARTITION BY Department) AS AvgSalary
FROM Employees;

• A Clustered Index determines the physical order of records in a table.
• Only one clustered index per table.
• Improves query performance when filtering large data.

CREATE CLUSTERED INDEX idx_EmployeeID ON Employees(EmployeeID);

• A Non-Clustered Index is a separate structure that stores pointers to the data.
• A table can have multiple non-clustered indexes.

CREATE NONCLUSTERED INDEX idx_Name ON Employees(Name);

• Use indexes on frequently searched columns.
• Avoid **SELECT *** (select only required columns).
• Use joins efficiently (avoid unnecessary ones).
• Use proper data types and avoid implicit conversions.
• Use EXISTS instead of IN for large datasets.
• Check execution plans.

DECLARE @TempTable TABLE (ID INT, Name NVARCHAR(50));
INSERT INTO @TempTable VALUES (1, 'John');
CREATE TABLE #TempTable (ID INT, Name NVARCHAR(50));
INSERT INTO #TempTable VALUES (2, 'Jane');

SELECT Name, COUNT(*) 
FROM Employees
GROUP BY Name
HAVING COUNT(*) > 1;

• Returns rows present in the first query but not in the second.
• Works like MINUS in Oracle.

SELECT Name FROM Employees
EXCEPT
SELECT Name FROM RetiredEmployees;

SELECT MAX(Salary) 
FROM Employees
WHERE Salary < (SELECT MAX(Salary) FROM Employees);

WITH CTE AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY Name ORDER BY EmployeeID) AS RowNum
    FROM Employees
)
DELETE FROM CTE WHERE RowNum > 1;

DECLARE @dt1 DATETIME = '2024-02-05 10:30:45';
DECLARE @dt2 DATETIME2 = '2024-02-05 10:30:45.123456';

A Deadlock occurs when two processes wait for each other to release locks, causing a loop.

SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id <> 0;

Cursors allow row-by-row processing but are slow.

DECLARE CursorExample CURSOR FOR 
SELECT Name FROM Employees;
OPEN CursorExample;
FETCH NEXT FROM CursorExample;

• SP_EXECUTESQL supports parameters (better for security).
• EXEC runs dynamic SQL as a string

DECLARE @SQL NVARCHAR(MAX) = 'SELECT * FROM Employees';
EXEC SP_EXECUTESQL @SQL;

DECLARE @SQL NVARCHAR(1000);
SET @SQL = 'SELECT * FROM Employees WHERE Name = ''John''';
EXEC SP_EXECUTESQL @SQL;

• RETURN can return only integers.
• OUTPUT can return multiple values.

ALTER TABLE Employees ADD EncryptedSSN VARBINARY(MAX);

Fill Factor determines how much space is left empty in each index page.

CREATE INDEX idx_Test ON Employees(Name) WITH (FILLFACTOR = 80);

MERGE INTO Employees AS Target
USING NewEmployees AS Source
ON Target.ID = Source.ID
WHEN MATCHED THEN 
    UPDATE SET Target.Salary = Source.Salary
WHEN NOT MATCHED THEN 
    INSERT (ID, Name, Salary) VALUES (Source.ID, Source.Name, Source.Salary);

INSERTED and DELETED tables store temporary data during triggers.

EXEC sp_depends 'Employees';

Techniques to improve performance using indexes, execution plans, and query rewrites.

Checks database integrity.

DBCC CHECKDB();

A visual representation of how SQL Server executes queries.

SET SHOWPLAN_XML ON;

Views that have clustered indexes.

SELECT PARSE('2024-02-05' AS DATETIME2);

• Use Clustered & Non-Clustered Indexes
• Use Covering Indexes
• Avoid Full Table Scans
• Use Filtered Indexes