50 questions
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.
IF, WHILE loops).TRY?CATCH).SELECTCREATE, ALTER, DROPINSERT, UPDATE, DELETEGRANT, REVOKECOMMIT, ROLLBACK, SAVEPOINTCHAR(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;
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.
| Feature | DELETE | TRUNCATE |
|---|---|---|
| Removes specific rows? | Yes (uses WHERE) | No (removes all rows) |
| Logs transaction? | Yes | Minimal logging |
| Can be rolled back? | Yes | No (unless inside a transaction) |
| Resets identity column? | No | Yes |
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;
CREATE CLUSTERED INDEX idx_EmployeeID ON Employees(EmployeeID);
CREATE NONCLUSTERED INDEX idx_Name ON Employees(Name);
| Feature | Table Variable (@table) | Temp Table (#table) |
|---|---|---|
| Scope | Only within batch | Exists within session |
| Transactions | Not logged | Fully logged |
| Indexes | Limited support | Can have indexes |
| Performance | Faster for small data | Better for large data |
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;
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;
| Feature | DATETIME | DATETIME2 |
|---|---|---|
| Precision | 3.33ms | Up to 100ns |
| Storage | 8 bytes | 6-8 bytes |
| Recommended? | No | Yes (more accurate) |
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 stringDECLARE @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);
Sign in to join the discussion and post comments.
Sign in