SQL - Stored Procedure, Functions & Others

What is the difference between Stored Procedure and Functions (at least 3)?

Feature	Stored Procedure (SP)	Function (UDF)
Return Value	May or may not return a value	Must return a value
Parameters	Can have input and output parameters	Only input parameters
Invocation	Can call functions inside SP	Cannot call SP inside a function
Usage in SQL Statements	Cannot be used in SELECT, INSERT, etc.	Can be used in SQL statements
Example Usage	Not usable in: SELECT * FROM ...	Usable: SELECT *, dbo.fnCountry(city.long)
Exception Handling	Supports TRY-CATCH	Does not support TRY-CATCH
Transactions	Can use transactions	Cannot use transactions

-- Stored Procedure
CREATE PROCEDURE proc_name
(@Ename VARCHAR(50),
 @EId INT OUTPUT)
AS
BEGIN
    INSERT INTO Employee (EmpName)
    VALUES (@Ename)

    SELECT @EId = SCOPE_IDENTITY()
END

-- User-Defined Function
CREATE FUNCTION function_name
(parameters) -- only input parameters
RETURNS data_type
AS
BEGIN
    -- SQL statements
    RETURN value
END

How to optimize a Stored Procedure or SQL Query?

1. Use SET NOCOUNT ON

2. Specify column names instead of using . SELECT FROM table1 SELECT col1, col2 FROM table1

3. Use schema name before objects or table names. SELECT EmpID, Name FROM Employee SELECT EmpID, Name FROM dbo.Employee

4. Use EXISTS () instead of COUNT (). SELECT Count(1) FROM dbo.Employee IF( EXISTS (SELECT 1 FROM db.Employees))

5. Use TRANSACTION when required only.

6. Do not use DYNAMIC QUERIES. They are vulnerable to SQL Injections.

-- 1. Suppress row count messages to improve performance
SET NOCOUNT ON;

-- 2. Avoid SELECT *; specify columns explicitly
-- Bad:
SELECT * FROM table1;
-- Good:
SELECT col1, col2 FROM table1;

-- 3. Always use schema-qualified names
-- Bad:
SELECT EmpID, Name FROM Employee;
-- Good:
SELECT EmpID, Name FROM dbo.Employee;

-- 4. Use EXISTS for existence checks instead of COUNT
-- Bad:
IF (SELECT COUNT(1) FROM dbo.Employee) > 0
-- Good:
IF EXISTS (SELECT 1 FROM dbo.Employee)

-- 5. Use transactions only when necessary
BEGIN TRANSACTION;
    -- SQL operations
COMMIT TRANSACTION;

-- 6. Avoid dynamic SQL to prevent SQL injection
-- Bad:
EXEC('SELECT * FROM ' + @tableName);
-- Good:
-- Use parameterized queries or stored procedures

What is a Cursor? Why to avoid them?

• ❖ A database Cursor is a control which enables traversal/ iteration over the rows or records in the table

1. Declare

2. Open

3. Fetch using while loop

4. Close

5. Deallocate

• A cursor is a MEMORY resident set of pointers. Meaning it occupies lots of memory from your system which is not good for performance.

-- Declare variables to hold cursor output
DECLARE
    @product_name VARCHAR(MAX),
    @list_price DECIMAL;

-- Declare the cursor
DECLARE cursor_product CURSOR
FOR
    SELECT product_name, list_price
    FROM product;

-- Open the cursor
OPEN cursor_product;

-- Fetch the first row
FETCH NEXT FROM cursor_product
INTO @product_name, @list_price;

-- Loop through the result set
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT @product_name + CAST(@list_price AS VARCHAR);
    
    FETCH NEXT FROM cursor_product
    INTO @product_name, @list_price;
END;

-- Close and deallocate the cursor
CLOSE cursor_product;
DEALLOCATE cursor_product;

What is the difference between scope_identity and @@identity?

Same

• Both are used to get the last value generated in the identity column of the table

Difference

• @@IDENTITY function returns the last identity value generated within the current session, regardless of the scope

  • Same session and able to do different scope

  • This will return a value generated by an INSERT statement in a trigger, stored procedure or batch of T-SQL statements.

• The scope_identity() function returns the last identity created in the same session and the same scope.

  • Same session and scope

  • Mostly we use scope_identity() function inside stored procedures.

What is CTE in SQL Server?

A Common Table Expression, is a TEMPORARY named result set, that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

-- Define the CTE
WITH AvgSalaryByDept AS (
    SELECT Department, AVG(Salary) AS AvgSalary
    FROM Employees
    GROUP BY Department
)

-- Use the CTE in the main query
SELECT e.EmployeeID, e.Name, e.Department, a.AvgSalary
FROM Employees e
JOIN AvgSalaryByDept a
    ON e.Department = a.Department;

What is the difference between Delete, Truncate and Drop commands?

Command	Classification	Functionality	Rollback Capability
DELETE	DML	Deletes one or more rows based on a WHERE condition; does not remove schema	Can be rolled back
TRUNCATE	DDL	Deletes all rows from a table; does not remove schema	Cannot be rolled back
DROP	DDL	Deletes all rows and removes the table structure/schema	Cannot be rolled back

Example Command	Description
DELETE FROM Employees WHERE Emp_Id = 7;	Deletes specific row(s)
TRUNCATE TABLE Employees;	Deletes all rows, keeps table structure
DROP TABLE Employees;	Deletes entire table including structure

How to get the Nth highest salary of an employee?

-- Step 1: Select top 3 distinct salaries in descending order
WITH TopSalaries AS (
    SELECT DISTINCT TOP 3 Salary
    FROM tbl_Employees
    ORDER BY Salary DESC
)

-- Step 2: Order the result in ascending order
SELECT Salary
FROM TopSalaries
ORDER BY Salary ASC;

-- Step 3: Select the lowest among the top 3 salaries
SELECT TOP 1 Salary
FROM TopSalaries
ORDER BY Salary ASC;

-- Alt
SELECT TOP 1 SALARY
FROM (
SELECT DISTINCT TOP 3 SALARY
FROM tbl_Employees
ORDER BY SALARY DESC
) RESULT
ORDER BY SALARY

What are ACID properties?

ACID properties are used when you are handling transactions in SQL.

• Atomicity: Each transaction is "all or nothing"—either fully completed or fully rolled back.

• Consistency: Ensures data remains valid and adheres to all defined rules and constraints.

• Isolation: Transactions operate independently without interfering with each other.

• Durability: Once a transaction is committed, its changes persist—even in the event of a system failure.

What are Magic Tables in SQL Server?

• Magic tables are the temporary logical tables that are created by the SQL server, whenever there are insertion or deletion or update( D.M.L) operations.

• The use of magic tables are TRIGGERS

Types of magic tables

1. INSERTED – When any insert query executed, then the recently inserted row gets added to the INSERTED magic table.

2. DELETED – When any delete query executed, then the recently deleted row gets added to the DELETED magic table.