SQL Server interview questions covering views, stored procedures & user-defined functions.
Open a question for the full answer, code and interview guidance.
A view is a named, stored SELECT definition that presents data as a virtual table. A regular view normally stores the query definition rather than a separate copy of the result data. Views are useful for abstraction, simplifying complex joins, presenting a stable interface to consumers, and restricting access to selected columns or rows. Performance still depends on the underlying tables, indexes, predicates, and execution plan.
Views can simplify complex queries, encapsulate reusable relational logic, expose only required columns, provide a security boundary in appropriate permission designs, and reduce direct dependency on underlying table structures. However, a view is not automatically a performance optimization. Complex nested views can make query behavior harder to understand, so views should be designed deliberately rather than used simply to hide poor query structure.
Some views are directly updatable, but not all views are. A simple view based on a single table may allow INSERT, UPDATE, or DELETE when SQL Server can unambiguously map the modification to the underlying table. Views containing certain constructs—such as aggregates, GROUP BY, DISTINCT, UNION, or calculations—may not be directly updatable. INSTEAD OF triggers can sometimes be used to define custom modification behavior, but they add complexity.
An indexed view is a view whose result is physically materialized through an index. The first index created on it must be a unique clustered index. Additional indexes can then be added. Indexed views have strict requirements, including schema binding and restrictions on allowed query constructs. They can improve specific aggregation or read-heavy workloads, but they add storage and write-maintenance cost because underlying data changes must also maintain the indexed view. They should be introduced only after measuring the workload and understanding SQL Server edition and optimizer behavior relevant to the environment.
WITH SCHEMABINDING binds the view definition to referenced objects so changes that would invalidate the view cannot be made without first addressing the schema-bound dependency. Schema-bound object references must follow SQL Server's required naming rules, including two-part names such as dbo.Products. SCHEMABINDING is required for indexed views and can also be used for other schema-bound modules.
A stored procedure is a named T-SQL program stored in the database. It can accept input parameters, return output parameters and result sets, execute multiple SQL statements, manage transactions, and contain procedural logic. Stored procedures are commonly used to centralize data-access operations, implement complex database workflows, reduce repeated SQL text, and expose controlled database operations to applications.
SET NOCOUNT ON prevents SQL Server from sending row-count messages such as '(10 rows affected)' after each statement in the procedure. This can reduce unnecessary network messages and avoids some client-side complications when procedures contain multiple statements. It does not prevent the application from retrieving an explicit count using COUNT, @@ROWCOUNT, output parameters, or a result set.
Input parameters allow callers to supply values to a stored procedure. They make procedures reusable and avoid embedding user values directly into SQL text. Proper parameterization is important for security and can help SQL Server reuse execution plans. Parameters should use data types and lengths compatible with the columns they are compared against.
An OUTPUT parameter allows a stored procedure to return a scalar value through a parameter. The parameter must be declared with OUTPUT in the procedure definition, and the caller must also specify OUTPUT when capturing the returned value. Output parameters are useful for returning values such as generated identifiers, counts, status codes, or calculated totals in addition to or instead of result sets.
A stored procedure can return an integer status value using RETURN. This is different from returning rows with SELECT and different from OUTPUT parameters. RETURN is conventionally used for status or result codes rather than returning business data. For example, zero can represent success while another integer represents a particular condition. Applications should not confuse the procedure return code with a SELECT result set.
A view represents a query and is normally consumed like a table in SELECT statements. A stored procedure is an executable database module that can accept parameters, run multiple statements, perform modifications, use transactions, and return multiple result sets or output values. Use a view when a reusable relational projection is needed. Use a stored procedure when the operation requires procedural behavior, parameters, multiple steps, controlled modifications, or an application-facing database command.
A user-defined function is a reusable database function created by developers. It accepts parameters and returns either a scalar value or a table. Major categories include scalar UDFs, inline table-valued functions, and multi-statement table-valued functions. SQL Server places restrictions on side effects inside functions; they are intended primarily for computations and reusable query expressions rather than arbitrary database modifications.
A scalar UDF returns a single value. It can encapsulate reusable calculations or formatting logic and can be called from queries where a scalar expression is valid. Historically, scalar UDFs could be expensive when executed row by row. Modern SQL Server versions can inline eligible scalar UDFs under certain conditions, but developers should still measure performance and avoid assuming that a function call is free.
An inline table-valued function (iTVF) returns a table from a single SELECT expression. It can accept parameters, making it similar to a parameterized view. Because its relational expression can often be incorporated effectively into the calling query by the optimizer, an inline TVF is generally preferable to a multi-statement TVF when the required logic can be expressed as one relational query.
A multi-statement table-valued function returns a table variable that is populated through multiple statements inside the function. It can express more procedural logic than an inline TVF, but it may provide the optimizer with less useful cardinality information and can perform poorly in some workloads. Use it only when its additional procedural flexibility is genuinely required and validate performance with realistic data.
A function must return a value or table and is designed to participate in SQL expressions or relational queries, subject to SQL Server's function restrictions. A stored procedure is invoked as an executable module and can perform a much wider range of operations. Stored procedures can return multiple result sets, use output parameters, execute dynamic SQL, and perform data modifications. Functions are more constrained and should not be treated as replacements for procedures. The appropriate choice depends on whether the logic needs to compose inside a query or execute as an independent database operation.
TRY...CATCH provides structured error handling in T-SQL. Statements that might fail are placed in a TRY block, while error-handling logic is placed in CATCH. Inside CATCH, functions such as ERROR_NUMBER, ERROR_MESSAGE, ERROR_LINE, ERROR_PROCEDURE, and ERROR_SEVERITY provide error details. THROW is commonly used to propagate an error after cleanup or transaction handling.
THROW raises an exception in T-SQL. Inside a CATCH block, THROW without arguments rethrows the current exception while preserving its error information. THROW can also create a new error when supplied with an error number of 50000 or greater, a message, and a state. It is generally preferred for modern T-SQL error handling over older RAISERROR patterns when its capabilities meet the requirement.
Dynamic SQL is T-SQL constructed and executed at runtime. It is useful when query structure itself must vary, such as dynamic column selection, optional object names, or certain advanced search/reporting requirements. SQL Server provides EXEC and sp_executesql. sp_executesql is generally preferred when user-supplied values can be parameterized because it improves security and can support execution-plan reuse. Object names such as table or column names cannot be passed as ordinary query parameters; when dynamic identifiers are required, they must be validated and safely delimited, commonly with QUOTENAME.
Parameterized queries keep SQL command structure separate from data values. User input is sent as a typed parameter rather than concatenated into executable SQL text, preventing the value from being interpreted as part of the SQL syntax. ASP.NET Core applications should use EF Core parameterization, ADO.NET SqlParameter, Dapper parameters, or parameterized stored procedures rather than concatenating untrusted input into SQL strings. Parameterization is a central SQL injection defense, although dynamic identifiers and intentionally dynamic query structure still require strict validation and safe construction.