SQL Interview Questions and Answers - Part 12
Are you preparing for the SQL Interview? If yes, then you are at the right place. This is the SQL Interview Questions and Answers article series. Here we will see the Top 100+ SQL Interview Questions with Answers.
Please visit our YouTube Channel for Interviews and other videos by below link:
Please read the complete Design Pattern, C#, MVC, WebAPI, and .Net Framework Interview Questions and Answers article series here.
Introduction
This is the 12th part of the SQL Interview Questions and Answers article series. Each part contains eight to ten SQL Interview Questions with Answers. Please read all the SQL Interview Questions list here.
I will highly recommend to please read the previous parts over here before continuing the current part:
SQL Interview Questions and Answers - Part 12
Q100. How can you return value from a stored procedure?
There are three ways of returning data from a procedure to a calling program: result sets, output parameters, and return codes.
To trap non-fatal errors in a called stored procedure, the called procedure must have some way to communicate back to the calling procedure that an error has occurred. To do this, pass a value back via the RETURN statement, or use an OUTPUT parameter. Either way works, but once you adopt a method, all the procedures in a system must use it
Using RETURN Statement:
By default when we execute a stored procedure in SQL Server, it returns an integer value and this value indicates the execution status of the stored procedure. The value zero indicates that the procedure is completed successfully and the non-zero values indicate an error.
So every stored procedure returns an integer return code to the caller. If the stored procedure does not explicitly set a value for the return code, the return code is 0. We cannot return more than one value using the return status variable in SQL Server.
A procedure can return an integer value called a return code to indicate the execution status of a procedure. You specify the return code for a procedure using the RETURN statement. Before the introduction of TRY/CATCH/THROW error handling in TSQL return codes was sometimes required to determine the success or failure of stored procedures.
But the stored procedure return value data type can only be an integer and can not be changed. Therefore, we can not assign any data type other than an integer value, otherwise, the assigned value is converted implicitly into an integer or get a data type conversion error.
If a NULL value is assigned to the stored procedure's return value when changing it. SQL Server automatically converts NULL values into 0. So you can never return a NULL value from the stored procedure.
DECLARE @return_value int
EXEC @return_value = [dbo].[procGetUsers]
SELECT 'Return Value' = @return_value
Using OUTPUT Parameter:
If we want multiple return values, we can use the OUT parameter. We can define output parameters instead of the return value in the stored procedure and can assign several data types to it as well unlike the RETURN command.
DECLARE @return_value int, @factorial int
EXEC @return_value = [dbo].[GetFactorial] @Num = 5, @factorial = @factorial OUTPUT
SELECT @factorial as N'@factorial'
SELECT 'Return Value' = @return_value
The advantage of the OUT parameter is that OUTPUT parameters can be of any type while a return value can only be of integer type.
The second advantage of the OUT parameter is that you can return multiple values by defining multiple OUT parameters.
Using Result SET:
If you include a SELECT statement in the body of a stored procedure the rows specified by the SELECT statement will be sent directly to the client. If multiple such SELECT statements are run during the execution of the stored procedure, multiple result sets will be sent to the client.
CREATE PROCEDURE procMultipleResults
AS
SELECT TOP(10) Id, Lastname, FirstName FROM Employee;
SELECT TOP(10) Id, RoleName FROM RoleMaster;
GO
This behavior also applies to nested Transact-SQL batches, nested stored procedures, and top-level Transact-SQL batches.
Q101. What is a temporary stored procedure? What is the use of a temp stored procedure?
Temporary Stored Procedure:
Like the temporary table, we can create temporary procedures as well. Temporary procedures are a form of user-defined procedures. The temporary procedures are the same as permanent procedures, except temporary procedures are stored in the "TempDB" database.
There are two types of temporary procedures: Local and Global. They differ from each other in their names, their visibility, and their availability. But Temporary names cannot be specified for CLR procedures.
Local Temporary Stored Procedures have a single pound sign (#) as the first character of their names. They are visible only to the current user connection, and they are deleted when the connection is closed.
Local Temporary Stored Procedure name cannot exceed 116 characters(including #). SQL adds the pseudo-random generated suffix in the local temp stored procedures like local temp tables. This is done to avoid name clashes in situations when the same name is used in more than one session. SQL Server reserves 14 characters for this suffix.
CREATE PROCEDURE #TempSP
AS
BEGIN
PRINT 'Local temp stored procedure'
END
Global Temporary Stored Procedures have two-pound signs (##) as the first two characters of their names. They are visible to any user after they are created, and they are deleted at the end of the last session using the procedure.
Global Temporary Stored Procedure name cannot exceed 128 characters (including ##).
CREATE PROCEDURE ##TempSP
AS
BEGIN
PRINT 'Global temp stored procedure'
END
Q102. What is the difference between recursive vs nested stored procedures?
Nested Procedures means procedure within a procedure. So when the one stored procedure calls another stored procedure it is called stored procedure nesting.
This is also known as a switching yard because we often create one stored procedure that executes one of many SPs based on what parameters are sent in. The nesting level increases each time a stored procedure calls another SP.
The nesting limit in SQL server is up to 32 procedures; however, there is no limit on the number of procedures that can be called from a single procedure. The nesting level of the current procedure can be returned using the @@Nestlevel function.
When the nest level exceeds 32 the transaction will stop with an error. SQL Server didn’t bother to check if the stored procedure at nesting level 33 existed or not since executing it would have exceeded the max nesting level. So the SQL with throw an error if the stored procedure that would have caused the max nesting level to be exceeded does not exist.
On the other hand, you can also have a stored procedure that calls itself. This type of nesting is called recursion and you will get an error if you exceed the max nesting level.
Recursion is when a procedure calls itself until a stop condition is reached. However, due to limitations on nesting levels, recursion is also limited to 32 calls. So a stored procedure can call itself up to the maximum nesting level of 32.
CREATE PROCEDURE GetFactorial
(
@Num INT,
@factorial INT OUTPUT
)
AS
BEGIN
DECLARE @n_1 int
DECLARE @factemp int
IF @Num != 1
BEGIN
SELECT @n_1 = @Num - 1
EXEC GetFactorial @n_1, @factemp OUTPUT
SELECT @factorial = @Num * @factemp
END
ELSE
BEGIN
SELECT @factorial = 1
END
END
You can execute the above SP as follows:
DECLARE @factorial INT
Exec GetFactorial 4, @factorial OUTPUT
SELECT @factorial;
Q103. What is the use of the WITH RECOMPILE option in the stored procedure? Why one should use it?
RECOMPILE options indicate that the Database Engine does not cache a query plan for this procedure, forcing it to be compiled each time it is executed. This option cannot be used when FOR REPLICATION is specified or for CLR procedures.
There are three ways to do this: WITH RECOMPILE option in the procedure definition or when the procedure is called, the RECOMPILE query hint on individual statements, or by using the sp_recompile system stored procedure.
The WITH RECOMPILE clause is helpful when the parameters supplied to the procedure are not typical, and when a new execution plan should not be cached or stored in memory.
WITH RECOMPILE Option:
If this option is used when the procedure definition is created, it requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.
CREATE PROCEDURE procGetEmployees
WITH RECOMPILE
AS
BEGIN
-- TSQL Operation
END
If WITH RECOMPILE option is used in an EXECUTE statement, it requires to EXECUTE permissions on the procedure.
Permissions are not required on the EXECUTE statement itself but execute permissions are required on the procedure referenced in the EXECUTE statement.
EXECUTE procGetUserHistory WITH RECOMPILE;
RECOMPILE Query Hint:
This feature is used when the procedure is created and the hint is included in Transact-SQL statements in the procedure. Therefore, it requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.
It is used to instruct the Database Engine to discard query plans for individual queries inside a procedure, use the RECOMPILE query hint in the definition of the query.
SELECT DISTINCT City as CityName
FROM Address
WHERE EmpID = 104 OPTION (RECOMPILE);
sp_recompile System Stored Procedure: It requires ALTER permission on the specified procedure.
EXEC sp_recompile N'dbo.procGetUserHistory';
Why RECOMPILE:
When a procedure is compiled for the first time or recompiled, the procedure's query plan is optimized for the current state of the database and its objects. If a database undergoes significant changes to its data or structure, recompiling a procedure updates and optimizes the procedure's query plan for those changes. This can improve the procedure's processing performance.
There are times when procedure recompilation must be forced and other times when it occurs automatically. Automatic recompiling occurs whenever SQL Server is restarted. It also occurs if an underlying table referenced by the procedure has undergone physical design changes.
Another reason to force a procedure to recompile is to counteract the "parameter sniffing" behavior of procedure compilation. When SQL Server executes procedures, any parameter values that are used by the procedure when it compiles are included as part of generating the query plan.
SQL Server features statement-level recompilation of procedures. When SQL Server recompiles stored procedures, only the statement that caused the recompilation is compiled, instead of the complete procedure.
If certain queries in a procedure regularly use typical or temporary values, procedure performance can be improved by using the RECOMPILE query hint inside those queries
Q104. How can you ENCRYPT and DECRYPT the stored procedure?
In SQL Server, you can encrypt a stored procedure at the time you create it, or you can alter it later to include encryption. To create a stored procedure with T-SQL, you can use the WITH ENCRYPTION option.
The WITH ENCRYPTION option indicates that SQL Server converts the original text of the CREATE PROCEDURE statement to an obfuscated format. The output of the obfuscation is not directly visible in any of the catalog views in SQL Server. This option is not valid for CLR procedures.
The WITH ENCRYPTION option obfuscates (Make obscure or unclear) the definition of the procedure when querying the system catalog or using metadata functions i.e sp_helptext or sys.sql_modules.
Users who have no access to system tables or database files cannot retrieve the obfuscated text. However, the text is available to privileged users who can either access system tables over the DAC port or directly access database files.
Also, users who can attach a debugger to the server process can retrieve the decrypted procedure from memory at runtime. Procedures created with this option cannot be published as part of SQL Server replication.
CREATE PROCEDURE procGetEmployees
WITH ENCRYPTION
AS
BEGIN
-- TSQL Operation
END
Q105. How can you improve the performance of a stored procedure?
Following are some best practices that should be followed while creating a stored procedure.
- Use the SET NOCOUNT ON statement as the first statement in the body of the procedure. This turns off messages that SQL Server sends back to the client.
- Use schema names when creating or referencing database objects in the procedure. It takes less processing time for the Database Engine to resolve object names if it does not have to search multiple schemas.
- Avoid wrapping functions around columns specified in the WHERE and JOIN clauses. Doing so makes the columns non-deterministic and prevents the query processor from using indexes.
- Reduce the no of JOINs and use the columns which have proper indexes.
- Avoid using scalar functions in SELECT statements that return many rows of data. Because the scalar function must be applied to every row, the resulting behavior is like row-based processing and degrades performance.
- Avoid the use of SELECT *. Instead, specify the required column names. This can prevent some Database Engine errors that stop procedure execution.
- Use the UNION ALL operator instead of the UNION or OR operators, unless there is a specific need for distinct values.
- Avoid processing or returning too much data. Narrow the results as early as possible in the procedure code.
- Use explicit transactions by using BEGIN/COMMIT TRANSACTION and keep transactions as short as possible. Longer transactions mean longer record locking and a greater potential for deadlock.
- Use the Transact-SQL TRY/CATCH feature for error handling inside a procedure. This not only creates less performance overhead, but it also makes error reporting more accurate with significantly less programming.
- Use the DEFAULT keyword on all table columns that are referenced by CREATE TABLE or ALTER TABLE Transact-SQL statements in the body of the procedure. This prevents passing NULL to columns that do not allow null values.
- Use NULL or NOT NULL for each column in a temporary table. The ANSI_DFLT_ON and ANSI_DFLT_OFF options control the way the Database Engine assigns the NULL or NOT NULL attributes to columns when these attributes are not specified in a CREATE TABLE or ALTER TABLE statement.
Q106. What is SCHEMABINDING in stored procedure? If all the stored procedures are bounded to the schema by default, then why do we need to do it explicitly?
The SCHEMABINDING option specifies that the schema is bound to the database objects that it references. It ensures that tables that are referenced by a procedure cannot be dropped or altered.
In addition to function, WITH SCHEMABINDING can also be used with views and procedures, and there can be tangible benefits there that don't involve performance. Mainly these revolve around either preventing unauthorized or inadvertent changes or preventing unexpected results.
SCHEMABINDING is a little bit different in Stored procedures. Only natively compiled stored procedures can be schema bound. In fact, natively compiled stored procedures must be schema bound.
You can’t create a natively compiled stored procedure without schema binding it. But if you try to schema bind a regular i.e non-natively compiled procedure, you’ll get an error.
CREATE PROCEDURE dbo.procGetUserDetails
WITH SCHEMABINDING, NATIVE_COMPILATION
AS
BEGIN ATOMIC WITH (
TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'us_english'
)
SELECT *
FROM [dbo].[Employee]
WHERE ID = 1;
END;
In the above example along with the WITH SCHEMABINDING argument, I have also included NATIVE_COMPILATION, which indicates that the procedure is natively compiled.
Also, notice the ATOMIC WITH block. This is required for natively compiled stored procedures. It specifies the transaction isolation level for the stored procedure.
Q107. How can you write a stored procedure which executes automatically when SQL Server restarts?
There are two different ways to execute a stored procedure:
- The first and most common approach is for an application or user to call the procedure.
- The second approach is to set the procedure to run automatically when an instance of SQL Server starts.
When a procedure is called by an application or user, the Transact-SQL EXECUTE or EXEC keyword is explicitly stated in the call. The procedure can be called and executed without the EXEC keyword if the procedure is the first statement in the Transact-SQL batch.
Some procedures can execute automatically when SQL Server starts. The procedures must be created by the system administrator in the master database and executed under the sysadmin fixed server role as a background process. The procedures cannot have any input or output parameters.
Execute Stored Procedures Automatically:
Procedures marked for automatic execution are executed every time SQL Server starts and the master database is recovered during that startup process. A procedure that is automatically executed operates with the same permissions as members of the sysadmin fixed server role.
So only the system administrator (SA) can mark a procedure to execute automatically. In addition, the procedure must be in the master database, owned by sa, and cannot have input or output parameters. Any error messages generated by the procedure are written to the SQL Server error log.
There is no limit to the number of startup procedures you can have, but be aware that each consumes one worker thread while executing. If you must execute multiple procedures at startup but do not need to execute them in parallel, make one procedure the startup procedure and have that procedure call the other procedures. This uses only one worker thread.
Do not return any result sets from a procedure that is executed automatically. Because the procedure is being executed by SQL Server instead of an application or user, there is nowhere for the result sets to go.
Setting up procedures to execute automatically can be useful for performing database maintenance operations or for having procedures run continuously as background processes.
Another use for automatic execution is to have the procedure perform system or maintenance tasks in tempdb, such as creating a global temporary table. This makes sure that such a temporary table will always exist when tempdb is re-created during SQL Server startup.
EXEC sp_procoption @ProcName = N'<procedure name>'
, @OptionName = 'startup'
, @OptionValue = 'on';
GO
Q108. Is stored procedure names case-sensitive?
The calling database collation is used when matching system procedure names. For this reason, always use the exact case of system procedure names in procedure calls.
EXEC SP_heLP; -- Will fail to resolve because SP_heLP does not equal sp_help
To display the exact system procedure names, query the sys.system_objects and sys.system_parameters catalog views.
If a user-defined procedure has the same name as a system procedure, the user-defined procedure might not ever execute.
Q109. What is CLR stored procedure? What is the use of the CLR procedure?
CLR stored procedure is a special type of procedure that is based on the CLR (Common Language Runtime) in the .net framework.
CLR integration of procedure was introduced with SQL Server 2008 and allows for the procedure to be coded in one of .NET languages like C#, Visual Basic, and F#.
Q110. What is the use of EXECUTE AS clause in the stored procedure?
By default, a session starts when a user logs in and ends when the user logs off. All operations during a session are subject to permission checks against that user.
When an EXECUTE AS statement is run, the execution context of the session is switched to the specified login or user name. After the context switch, permissions are checked against the login and user security tokens for that account instead of the person calling the EXECUTE AS statement.
In essence, the user or login account is impersonated for the duration of the session or module execution, or the context switch is explicitly reverted.
The EXECUTE AS clause is used to specify the security context in which a procedure can be executed.
CREATE PROCEDURE GetEmployeProfile
WITH EXECUTE AS CALLER
AS
BEGIN
END
In the above example, the option CALLER specifies that the procedure can be executed in the context of the user that calls it.
To Be Continued Part-13...
Recommended Articles
Thanks for visiting this page. Please follow and join us on LinkedIn, Facebook, Telegram, Quora, YouTube, Twitter, Pinterest, Tumbler, and VK for regular updates.
Posts
