Topics 43
Aggregate Functions & GROUP BY Always On Availability Groups Backup & Recovery Change Data Capture (CDC) Columnstore Indexes Common Table Expressions (CTEs) Constraints (Primary Key, Foreign Key, Check & Unique) Cursors Data Types & Schema Design Deadlocks Dynamic Data Masking Dynamic SQL Error Handling with TRY CATCH Full Text Search Hash Indexes & Hash Join Operations Indexes In-Memory OLTP (Memory-Optimized Tables) Isolation & Locking Joins JSON Support in SQL Server Linked Servers Merge Statement (Upsert) Normalization Partitioning Pivoting & Unpivoting Data Query Optimization & Plans Query Store Replication SQL Server Agent & Job Scheduling SQL Server Architecture & Editions SQL Server Profiler & Extended Events SQL Server Security & Permissions Stored Procedures & Functions Subqueries Temporary Tables & Table Variables Transactions & ACID Transparent Data Encryption Triggers T-SQL Fundamentals & Syntax User Defined Functions Views Window Functions XML Data Type & Querying

Stored Procedures & Functions

7 questions found

What is a stored procedure in SQL Server, and what are the main benefits of using one?

Beginner
A stored procedure is a saved, reusable collection of SQL statements stored directly in the database, offering benefits like improved performance through plan reuse, better security since users can be granted permission to execute a procedure without direct table access, and easier maintenance since the logic lives in one central place.
CREATE PROCEDURE GetCustomerOrders
  @CustomerId INT
AS
BEGIN
  SELECT * FROM Orders WHERE CustomerId = @CustomerId;
END;

EXEC GetCustomerOrders @CustomerId = 123;
Real-world example A company creates a stored procedure to retrieve a customer's orders, letting their application call a single, secure, reusable procedure instead of embedding raw SQL queries directly throughout the application code.

Common follow-ups: How do stored procedures help improve security compared to allowing direct table access?;Can a stored procedure call other stored procedures?

Dynamic SQL;Query Optimization & Plans

What is the difference between a scalar function and a table valued function in SQL Server?

Beginner
A scalar function returns a single value, such as a calculated number or a formatted string, and can be used anywhere a single value is expected, while a table valued function returns an entire result set that looks like a table, letting you use it in a FROM clause just like you would use a regular table.
-- Scalar function
CREATE FUNCTION GetFullName (@First VARCHAR(50), @Last VARCHAR(50))
RETURNS VARCHAR(101)
AS BEGIN RETURN @First + ' ' + @Last; END;

-- Table valued function
CREATE FUNCTION GetOrdersByCustomer (@CustomerId INT)
RETURNS TABLE
AS RETURN SELECT * FROM Orders WHERE CustomerId = @CustomerId;
Real-world example An application uses a scalar function to consistently format customer full names across many different reports, while using a table valued function to retrieve a customer's orders directly within a larger query's FROM clause.

Common follow-ups: Why might calling a scalar function on every row of a large table cause performance issues?;What is the difference between an inline and a multi statement table valued function?

Query Optimization & Plans;User Defined Functions

What are the main differences between a stored procedure and a function in terms of what each one can and cannot do?

Intermediate
A stored procedure can perform data modifications like INSERT, UPDATE, and DELETE, can have output parameters, and cannot be used directly inside a SELECT statement, while a function must return a value, generally cannot modify data in the database, but can be used directly within a SELECT statement or a WHERE clause, making them suited for different kinds of tasks.
-- A function cannot modify data
CREATE FUNCTION CalculateDiscount (@Amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
AS BEGIN RETURN @Amount * 0.9; END;

-- A procedure can modify data
CREATE PROCEDURE ApplyDiscount @OrderId INT
AS BEGIN UPDATE Orders SET Amount = dbo.CalculateDiscount(Amount) WHERE OrderId = @OrderId; END;
Real-world example A pricing system uses a function to calculate a discounted price that can be directly used inside reporting queries, while using a separate stored procedure to actually apply and save that discount to a specific order in the database.

Common follow-ups: Why can't a function be used to modify data directly?;When would you choose a stored procedure over a function for a specific task?

User Defined Functions;Query Optimization & Plans

How do you use output parameters in a stored procedure to return a value back to the calling code, in addition to or instead of a normal result set?

Intermediate
You declare a parameter with the OUTPUT keyword in both the procedure definition and when calling it, and any value assigned to that parameter inside the procedure becomes available to the calling code after the procedure finishes executing, which is useful for returning things like a newly generated identity value or a status code.
CREATE PROCEDURE CreateOrder
  @CustomerId INT,
  @NewOrderId INT OUTPUT
AS
BEGIN
  INSERT INTO Orders (CustomerId) VALUES (@CustomerId);
  SET @NewOrderId = SCOPE_IDENTITY();
END;

DECLARE @OrderId INT;
EXEC CreateOrder @CustomerId = 123, @NewOrderId = @OrderId OUTPUT;
Real-world example An order creation procedure returns the newly generated order id back to the calling application using an output parameter, letting the application immediately know exactly which order was just created.

Common follow-ups: What is the difference between SCOPE_IDENTITY and @@IDENTITY?;Can a stored procedure have multiple output parameters?

Data Types & Schema Design;Error Handling with TRY CATCH

How does SQL Server compile and cache execution plans for stored procedures, and how does this affect performance compared to ad hoc queries?

Advanced
When a stored procedure runs for the first time, SQL Server compiles an execution plan and caches it for reuse on subsequent calls, avoiding the overhead of recompiling the same logic repeatedly, which generally gives stored procedures a performance advantage over ad hoc queries that may generate a new, uncached plan every time they run with slightly different text.
-- View cached execution plans for stored procedures
SELECT p.name, s.execution_count, s.total_worker_time
FROM sys.procedures p
JOIN sys.dm_exec_procedure_stats s ON p.object_id = s.object_id;
Real-world example A team moves frequently executed ad hoc queries into stored procedures, noticing improved consistency in performance since the execution plans are now properly cached and reused instead of being recompiled on every single call.

Common follow-ups: What can cause a stored procedure's cached plan to become invalidated and need recompilation?;How do you view how many times a specific stored procedure has actually been executed?

Query Optimization & Plans;Query Store

How would you design a stored procedure to handle a complex business process involving multiple steps, proper error handling, and transaction management together?

Advanced
You would wrap the multiple steps inside a transaction, use a TRY CATCH block to properly handle any errors that occur, roll back the transaction if something fails partway through, log meaningful error details for troubleshooting, and use THROW to communicate the failure back to the calling application, ensuring the entire process either fully succeeds or leaves no partial changes behind.
CREATE PROCEDURE ProcessOrder @CustomerId INT, @Amount DECIMAL(10,2)
AS
BEGIN
  BEGIN TRY
    BEGIN TRANSACTION;
    INSERT INTO Orders (CustomerId, Amount) VALUES (@CustomerId, @Amount);
    UPDATE Customers SET LastOrderDate = GETDATE() WHERE CustomerId = @CustomerId;
    COMMIT TRANSACTION;
  END TRY
  BEGIN CATCH
    IF XACT_STATE() <> 0 ROLLBACK TRANSACTION;
    THROW;
  END CATCH
END;
Real-world example An order processing procedure safely updates both the orders and customers tables together within a single transaction, automatically rolling back both changes if any part of the process fails unexpectedly.

Common follow-ups: How do you test that this kind of procedure correctly rolls back under different failure scenarios?;What additional logging would be valuable to add to a procedure like this?

Transactions & ACID;Error Handling with TRY CATCH

What is the difference between using EXEC and calling a stored procedure through parameterized application code, and why does this distinction matter for security?

Intermediate
Calling a stored procedure properly through parameterized application code passes user input as distinct parameters that SQL Server treats strictly as data, while carelessly using EXEC to build and run a dynamic string that includes concatenated user input can expose your application to SQL injection if that input is not properly validated and escaped first.
-- Safe: proper parameterized call
EXEC GetCustomerOrders @CustomerId = 123;

-- Risky if built from unsanitized user input
EXEC ('SELECT * FROM Orders WHERE CustomerId = ' + @UserInput);
Real-world example A development team reviews their codebase and finds a legacy piece of code building a dynamic EXEC statement directly from user input, refactoring it to use a proper parameterized stored procedure call instead to eliminate the SQL injection risk.

Common follow-ups: How do you audit an existing codebase for unsafe dynamic SQL patterns like this?;What tools can help automatically detect potential SQL injection vulnerabilities?

Dynamic SQL;SQL Server Security & Permissions