Stored Procedures & Functions Interview Questions

Stored procedures can perform actions and return multiple result sets but don't necessarily return values, while functions must return a value/table and can be used in SELECT statements. Functions are more limited in what they can do (e.g., can't modify data in most cases) but are more flexible in queries.

SQL supports Scalar functions (return single value), Table-valued functions (return table result set), and Aggregate functions (operate on multiple values). User-defined functions can be either scalar or table-valued, while built-in functions come in all three types.

Error handling in stored procedures uses TRY-CATCH blocks, ERROR_NUMBER(), ERROR_MESSAGE(), and RAISERROR/THROW statements. Implement appropriate error logging, transaction management, and status returns to calling applications.

Benefits include: better security through encapsulation and permissions, reduced network traffic, code reuse, easier maintenance, cached execution plans, and the ability to implement complex business logic at the database level.

Optimize by using appropriate indexes, avoiding parameter sniffing issues, implementing proper error handling, using SET NOCOUNT ON, minimizing network roundtrips, and considering query plan reuse. Monitor and analyze execution plans for potential improvements.

Parameter sniffing occurs when SQL Server reuses a cached plan optimized for specific parameter values. Handle it using OPTION (RECOMPILE), local variables, or dynamic SQL in specific cases. Consider data distribution when choosing a solution.

Implement dynamic SQL using sp_executesql with parameterization to prevent SQL injection. Properly escape identifiers, validate inputs, and consider performance implications. Avoid string concatenation with user inputs.

Use appropriate data types, provide default values when sensible, validate inputs, use meaningful parameter names, document parameters clearly, and consider NULL handling. Implement proper parameter validation logic.

Implement explicit transactions with proper error handling, consider nested transaction levels, use appropriate isolation levels, and handle deadlock scenarios. Ensure proper cleanup in error cases.

Inline table-valued functions return table results based on a single SELECT statement. They often perform better than multi-statement functions because they can be treated like views and participate in query optimization.

Handle large results using pagination, batch processing, table-valued parameters, temporary tables, or table variables. Consider memory usage, network bandwidth, and client application capabilities.

Consider EXECUTE permissions, ownership chaining, module signing, dynamic SQL security, and principle of least privilege. Implement proper input validation and avoid SQL injection vulnerabilities.

Implement logging using dedicated log tables, error handling blocks, and appropriate detail levels. Consider performance impact, retention policies, and monitoring requirements.

sp_executesql supports parameterization and better plan reuse, while EXEC is simpler but more vulnerable to SQL injection. sp_executesql is preferred for dynamic SQL due to security and performance benefits.

Handle concurrency using appropriate isolation levels, locking hints, transaction management, and deadlock prevention strategies. Consider implementing retry logic for deadlock victims.

Keep functions deterministic when possible, avoid excessive complexity, consider performance impact in queries, use appropriate return types, and document behavior clearly. Avoid side effects in functions.

Use schema versioning, naming conventions, source control, and proper documentation. Consider backward compatibility, deployment strategies, and rollback procedures.

CLR stored procedures are implemented in .NET languages and useful for complex calculations, string operations, or external resource access. Consider security implications and performance overhead compared to T-SQL.

Handle NULLs using ISNULL/COALESCE, appropriate function logic, and clear documentation of NULL behavior. Consider impact on query optimization and result accuracy.

Consider transaction handling, error propagation, parameter passing, and performance impact. Manage transaction scope and error handling appropriately across nested calls.

Implement paging using OFFSET-FETCH, ROW_NUMBER(), or other ranking functions. Consider performance with large datasets, sort stability, and total count requirements.

Consider scope, reuse, indexing strategy, and cleanup of temporary tables. Balance between table variables and temporary tables based on size and complexity.

Implement progress reporting, batch processing, appropriate transaction handling, and monitoring capabilities. Consider timeout handling and cancelation support.

Scalar functions operate on a single value and return a single value, while aggregate functions operate on sets of values and return a single summary value. Scalar functions can be used in SELECT lists and WHERE clauses.

Implement retry logic using WHILE loops, error handling, appropriate wait times, and maximum retry counts. Consider transient error conditions and implement appropriate backoff strategies.

Consider execution order, deterministic operations, identity column handling, and timestamp handling. Ensure procedures work consistently across primary and secondary servers.

Implement appropriate encryption, use secure parameter passing, avoid logging sensitive data, and consider data masking requirements. Follow security best practices for handling confidential information.

Document purpose, parameters, return values, error conditions, dependencies, and usage examples. Include version history, performance considerations, and any special handling requirements.

Design procedures to produce the same result regardless of multiple executions. Use appropriate checks, handle existing data, and implement proper transaction management for consistency.