Tech Point Fundamentals

Friday, August 5, 2022

SQL Interview Questions and Answers - Part 11

SQL Interview Questions and Answers - Part 11

sql-interview-questions-answers

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 10th 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 11


Q90. What is the stored procedure? What are the features of a stored procedure?

SQL Server stored procedure is a batch of statements grouped as a logical unit and stored in the database. They are stored as named objects in the Database. The stored procedure is also termed as proc, sproc, StoredProc, StoreProc, and SP.

Stored procedures can consolidate and centralize logic that was originally implemented in applications. To save time and memory, extensive or complex processing that requires the execution of several SQL statements can be saved into stored procedures, and all applications call the procedures. 

When you call a stored procedure for the first time, SQL Server creates an execution plan and stores it in the cache. In the subsequent executions of the stored procedure, SQL Server reuses the plan so that the stored procedure can execute very fast with reliable performance.



Stored-procedure flow-control statements typically include IF, WHILE, LOOP, REPEAT, CASE statements, and more. Stored procedures can receive variables, return results or modify variables and return them, depending on how and where the variable is declared.

The SQL Database stores the stored procedures as named objects. So every stored procedure name must be unique. Procedure names must comply with the rules for identifiers and must be unique within the schema. Each procedure in SQL Server always contains a name, parameter lists, and Transact-SQL statements. 

In short a stored procedure:

  1. Accept input parameters and return multiple values in the form of output parameters to the calling procedure or batch.
  2. Contain TSQL statements that perform operations in the database, including calling other procedures.
  3. Finally, return a status value to a calling procedure or batch to indicate success or failure.



Stored Procedure Fundamental Points:

  1. Stored Procedure uses the deferred execution. So it does not check for the existence of any object at the time of build and creation. It only checks for the syntax of the code.
  2. It requires CREATE PROCEDURE permission in the database and ALTER permission on the schema in which the procedure is being created.
  3. The EXECUTE AS clause can be specified in the CREATE PROCEDURE statement to enable impersonating another user or enable users or applications to perform certain database activities without needing direct permissions on the underlying objects and commands.
  4. Procedures can be encrypted, helping to obfuscate the source code.
  5. We can invoke other stored procedures and functions from the stored procedure.
  6. We can invoke the stored procedures by using triggers, other procedures, and applications.
  7. We can use both DML commands (Select, Insert, Update, Delete) and DDL commands (CREATE, TRUNCATE, DROP) in stored procedures.
  8. Stored Procedures can not be used with the SELECT, WHERE, or HAVING clause like functions.


  9. We can use Dynamic SQL Query in the stored procedure.
  10. We can use a temp table, table variable, and table-valued type in the stored procedures.
  11. We can implement Input Validation and Exception Handling Mechanish with TRY CATCH in the stored procedure. 

IF OBJECT_ID ( 'TechPoint.procGetEmployees', 'P' ) IS NOT NULL
    DROP PROCEDURE TechPoint.procGetEmployees;
GO

CREATE PROC or PROCEDURE procGetEmployees
AS
BEGIN
            SELECT * FROM Employee;
END

The AS keyword distinguishes the stored procedure's heading and body. The BEGIN and END keywords that accompany a single statement in a stored procedure are optional. However, it's a good idea to include them to make the code more understandable.




Q91. While you creating stored procedures you have seen some SET Options like ANSI_NULLS, NOCOUNT, QUOTED IDENTIFIER, etc. What is the purpose of these SET Options in SP?

When you create the Stored Procedure the default template contains the following SET Statements:

SET ANSI_NULLS ON: 

It Specifies ISO-compliant behavior of the Equals (=) and Not Equal To (<>) comparison operators when they are used with null values in SQL Server. 

When ANSI_NULLS is OFF, the Equals (=) and Not Equal To (<>) comparison operators do not follow the ISO standard. 

When ANSI_NULLS is ON, a SELECT statement that uses WHERE column_name = NULL returns zero rows even if there are null values in column_name.

Please visit here for more details.



SET NOCOUNT ON: 

This statement prevents the message that displays the number of rows affected by SQL queries from being shown. NOCOUNT tells the server not to count the number of affected rows. 

NOCOUNT denotes that the count is turned off. So when SET NOCOUNT ON is set, no message would appear indicating the number of rows affected. So setting NOCOUNT to ON will prevent displaying the messages of the number of rows affected. 

This turns off messages that SQL Server sends back to the client after any SELECT, INSERT, UPDATE, MERGE, and DELETE statements are executed. It is best practice to set it ON however there is no measurable performance benefit however on today's hardware.

Please visit here for more details.

SET QUOTED_IDENTIFIER ON: 

It causes SQL Server to follow the ISO rules regarding quotation mark delimiting identifiers and literal strings. Please visit here for more details.








Fundamental Points of SET OPTIONS:

The Database Engine saves the settings of both SET QUOTED_IDENTIFIER and SET ANSI_NULLS when a Transact-SQL procedure is created or modified. These original settings are used when the procedure is executed. Therefore, any client session settings for SET QUOTED_IDENTIFIER and SET ANSI_NULLS are ignored when the procedure is running.

Other SET options, such as SET ARITHABORT, SET ANSI_WARNINGS, or SET ANSI_PADDINGS are not saved when a procedure is created or modified. If the logic of the procedure depends on a particular setting, include a SET statement at the start of the procedure to guarantee the appropriate setting. 



When a SET statement is executed from a procedure, the setting remains in effect only until the procedure has finished running. The setting is then restored to the value the procedure had when it was called. This enables individual clients to set the options they want without affecting the logic of the procedure.

Any SET statement can be specified inside a procedure, except SET SHOWPLAN_TEXT and SET SHOWPLAN_ALL. These must be the only statements in the batch. The SET option chosen remains in effect during the execution of the procedure and then reverts to its former setting.

However, the SET ANSI_WARNINGS is not honored when passing parameters in a procedure, user-defined function, or when declaring and setting variables in a batch statement. For example, if a variable is defined as char(3), and then set to a value larger than three characters, the data is truncated to the defined size and the INSERT or UPDATE statement succeeds.








Q92. What do you understand by the fully qualified name (three-part identifiers) of the stored procedure? When the fully qualified SP name is required?

Procedures are schema-bound. The name of the schema to which the procedure belongs. If a schema name is not specified when the procedure is created, the default schema of the user who is creating the procedure is automatically assigned.

When we have several procedures with the same name in different databases. It is very important to use a fully qualified name. 

The fully qualified name includes the schema name or database name + schema name also along with the actual stored procedure name. A fully qualified name is always required when you want to invoke a stored procedure of another database from the current database. 

Schema Name is the name of your database or schema. By default, a procedure is associated with the current database, but we can also create it into another database by specifying the Database name.

EXEC [procGetUsers];
EXEC [dbo].[procGetUsers]; 
EXEC [TechPoint].[dbo].[procGetUsers];



In the above example, the last one is the fully qualified SP name where [TechPoint] is the database name and [dbo] is the scheme owner name. However, all are working but the below one will not work:

EXEC [TechPoint].[procGetUsers];

You will get the below error: Could not find stored procedure 'TechPoint.procGetUsers'.

System procedures begin with the prefix sp_. Because they logically appear in all user- and system-defined databases, they can be executed from any database without having to fully qualify the procedure name. However, Microsoft recommends schema-qualifying all system procedure names with the sys schema name to prevent name conflicts.



When executing a user-defined procedure, we recommend qualifying the procedure name with the schema name. This practice gives a small performance boost because the Database Engine does not have to search multiple schemas. It also prevents executing the wrong procedure if a database has procedures with the same name in multiple schemas.

If a nonqualified user-defined procedure is specified, the Database Engine searches for the procedure in the following order:

  1. The [sys] schema of the current database.
  2. The caller's default schema if it is executed in a batch or in Dynamic SQL
  3. The [dbo] schema in the current database.




Q93. What are the advantages and disadvantages of using stored procedures?

Advantages of Stored Procedures:

  1. Easy to maintain and deploy because all the scripts are in one place. Updates and tracking of dependencies based on schema changes become easier.
  2. Isolation Testing because SP can be tested independent of the application.
  3. Isolation of Business Rules by encapsulating business logic at one place
  4. Reduced network traffic because commands in a procedure are executed as a single batch of code. This can significantly reduce network traffic between the server and client because only the call to execute the procedure is sent across the network.
  5. Provide Reusability
  6. Provide better security and delegate access-rights
  7. Enhance the Performance because SP is cached on the server.
  8. Reduce Extra Compilation Overhead as it is precompiled and prevent DSQL from the client at run time



Disadvantages of Stored Procedures:

  1. The CREATE PROCEDURE statement cannot be combined with other Transact-SQL statements in a single batch.
  2. A procedure can reference the tables that do not yet exist because at creation time, only syntax checking is performed. However, the procedure fails at execution time if the referenced tables do not exist.
  3. You cannot specify a function name as a parameter default value or as the value passed to a parameter when executing a procedure.
  4. If the procedure makes changes on a remote instance of SQL Server, the changes cannot be rolled back. Remote procedures do not take part in transactions.
  5. Some important statements cannot be used anywhere in the body of a stored procedure. For example CREATE SCHEMA, USE database_name, CREATE or ALTER VIEW, CREATE or ALTER PROCEDURE, CREATE or ALTER FUNCTION, CREATE or ALTER TRIGGER, CREATE RULE, SET SHOWPLAN_ALL, SET PARSEONLY, SET SHOWPLAN_TEXT, etc.



Q94. How can you manage the stored procedure? Can you tell me some metadata objects which are used to view and manage the SP?

1. The "EXEC" or "EXECUTE" command is used to execute the stored procedure.

EXEC spName;

2. The
ALTER PROCEDURE statement is used to modify the existing stored procedure.

3. The system stored procedure "sp_rename" is used to change the stored procedure name. 

sp_rename 'OldSPName' , 'NewSPName';

3. The DROP PROCEDURE or DROP PROC statement is used to delete a stored procedure.

IF OBJECT_ID ('spGetEmployee', 'P') IS NOT NULL     
DROP PROCEDURE 'spGetEmployee'; 

4. The system stored procedure "
sp_helptext" is used to view the definition of a stored procedure. 

sp_helptext 'spGetEmployee';



5. The system function "
OBJECT_DEFINITION" is also used to get the definition of a Stored Procedure. 

SELECT OBJECT_DEFINITION (OBJECT_ID(N'spGetEmployee'));

6. The system stored procedure sp_helptext is not supported in Azure Synapse Analytics. So you have to use sys.sql_modules to check the definition of stored procedure. 

You can also use this catalog module to check the other information which is used within the SP like collation, ANSI NULLS, QUOTED IDENTIFIERS, SCHEMA BOUND, RECOMPILE, etc.

SELECT *
FROM sys.sql_modules 
WHERE object_id = (OBJECT_ID(N'dbo.spGetEmployee'));



7. The "sys.procedures" is used to list out all stored procedures names in SQL Server.

SELECT * FROM sys.procedures; 

8. We can also use "sys.sql_modules" which returns the definition of all Transact-SQL procedures. However, the text of a procedure created with the ENCRYPTION option cannot be viewed by using the sys.sql_modules catalog view.

SELECT * FROM  sys.sql_modules;

9. However the best way for listing all user-defined stored procedures in a database is to use the ROUTINES information schema view because it also provides other information as well like created on, last modified on, routine, catalog, etc.

SELECT *  
FROM INFORMATION_SCHEMA.ROUTINES  
WHERE ROUTINE_TYPE = 'PROCEDURE'; 



10. Another way to return a list of stored procedures is to query the sys.objects system catalog view.

SELECT  
SCHEMA_NAME(schema_id) AS [Schema], *    
FROM sys.objects  
WHERE type = 'P';

11. The nesting level of the current procedure can be returned using @@Nestlevel function.

SELECT @@NESTLEVEL

12. We can see the stored procedure cached plan details by using sys.dm_exec_cached_plans DMV. 

 SELECT
    DatabaseName=db_name(SQT.dbid), 
    ObjectName=Object_Name(SQT.objectid),
    SQT.text, 
    SQP.query_plan,
    cacheobjtype, 
    objtype,
*
FROM sys.dm_exec_cached_plans CP
CROSS APPLY sys.dm_exec_sql_text(CP.plan_handle) SQT
CROSS APPLY sys.dm_exec_query_plan(CP.plan_handle) SQP
WHERE SQT.objectid=object_id('procGetUserDetails');



13. We can use sys.assembly_modules to view the information about a CLR procedure.

SELECT * FROM  sys.assembly_modules;

14. We can use sys.parameters to view the information about all the parameters that are defined in all the procedures in the database.

SELECT * FROM  sys.parameters;

15. We can use "sp_depends" SP to get the dependent object details of the stored procedure.

EXEC sp_depends @objname = N'procGetUsers';  




Q95. What are different types of stored procedures?

Based on category there are the following two types of stored procedures:

System-Defined Stored Procedures: 

The System Stored Procedures are created and executed by SQL Server for the server administrative activities. These stored procedures are already defined in SQL Server. 

System procedures begin with the prefix sp_. They logically appear in all user-defined and system-defined databases.

System stored procedures are physically stored in the internal, hidden Resource database and logically appear in the [sys] schema of every system and user-defined database. This procedure starts with the sp_ prefix.

For example: [sys].[sp_who], [sys].[sp_who2], [sys].[sp_depends], [sys].[sp_statistics], [sys].[sp_lock], [sys].[sp_tables], [sys].[sp_columns], [sys].[sp_databases], [sys].[sp_help],  [sys].[sp_indexes], [sys].[sp_monitor], [sys].[sp_server_info], [sys].[sp_sqlexec].



In addition, the "MSDB" database also contains system stored procedures in the [dbo] schema that are used for scheduling alerts and jobs. 

For example: [dbo].[sp_add_alert], [dbo].[sp_add_job], [dbo].[sp_add_jobschedule], [dbo].[sp_addtask], [dbo].[sp_agent_start_job], [dbo].[sp_delete_alert], [dbo].[sp_delete_job], [dbo].[sp_droptask], [dbo].[sp_delete_proxy], [dbo].[sp_enlist_tsx]

Apart from this SQL Server supports the extended stored procedure. The extended system procedures provide an interface from SQL Server to external programs for various maintenance activities. 

These extended stored procedures use the xp_ prefix. They are stored in the Master database. Basically, these are used to call programs that reside on the server automatically from a stored procedure or a trigger run by the server.

For example: [sys].[xp_logininfo], [sys].[xp_grantlogin], [sys].[xp_revokelogin],[sys].[sp_xtp_control_proc_exec_stats], [sys].[sp_xtp_control_query_exec_stats]


CLR Stored 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#.



User-Defined T-SQL Stored Procedures:

A user-defined procedure can be created in a user-defined database or in all system databases except the Resource database. 

It takes parameters and returns the result to the caller. These procedures handle INSERT, UPDATE, and DELETE statements with or without parameters and output row data.

However, based on functionality and implementation there are the following types of user-defined stored procedures:

  1. Normal Stored Procedure
  2. Parameterized Stored Procedure
  3. Nested Stored Procedure
  4. Recursive Stored Procedure
  5. Recompiled Stored Procedure
  6. Encrypted Stored Procedure
  7. Temporary Stored Procedure



Q96. Why the "sp_ " prefix should be avoided while creating a user-defined Stored Procedure?

Because system procedures start with the prefix sp_, Microsoft recommends that you do not use this prefix when naming user-defined procedures. The prefix sp_ is reserved for system stored procedures that ship with SQL Server. This rule checks for the creation of stored procedures with names starting with "sp_".

According to Microsoft: "Avoid the use of the sp_ prefix when naming procedures. This prefix is used by SQL Server to designate system procedures. Using the prefix can cause application code to break if there is a system procedure with the same name."

Additionally, Microsoft suggests that 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. 



It also prevents permission and access problems caused by a user's default schema being assigned when objects are created without specifying the schema. It also prevents executing the wrong procedure if a database has procedures with the same name in multiple schemas.

Whenever SQL Server encounters a procedure name starting with sp_, it first tries to locate the procedure in the master database, then it looks for any qualifiers (database, owner) provided, then it tries [dbo] as the owner. 

So you can really save time in locating the stored procedure by avoiding the "sp_" prefix. If a user-defined procedure has the same name as a system procedure, the user-defined procedure might not ever execute.

System procedures begin with the prefix sp_. Because they logically appear in all user- and system-defined databases, they can be executed from any database without having to fully qualify the procedure name. However, Microsoft recommends schema-qualifying all system procedure names with the sys schema name to prevent name conflicts.



Q97. What is Parameterized Stored Procedure? What are the different ways to pass parameters in stored procedures? 

A stored procedure that contains at least one input parameter is known as a parameterized stored procedure. All Transact-SQL data types can be used as parameters.  

By specifying procedure parameters, calling programs are able to pass values into the body of the procedure. Those values can be used for a variety of purposes during procedure execution. 

All the parameters are defined using @ sign, then the data type like @userID INT.  The parameter name must comply with the rules for identifiers. 

Parameters are local to the procedure i.e. the same parameter names can be used in other procedures. You can see all the parameter names of all the procedures by using sys.parameters.



SP Parameter Fundamental Points:

  1. One or more parameters can be declared but the maximum limit is 2,100.
  2. The parameter name must begin with a single @ character and must be unique in the scope of the procedure.
  3. You can also use the user-defined table type (TVP) to create table-valued parameters. Table-valued parameters can only be INPUT parameters and must be accompanied by the READONLY keyword.
  4. A cursor data type can only be OUTPUT parameters and must be accompanied by the VARYING keyword. 
  5. Table-valued or cursor data types cannot be used as parameters in CLR stored procedures.


  6. The parameter values supplied with a procedure call must be constants or a variable; a function name cannot be used as a parameter value. Variables can be user-defined or system variables such as @@spid.
  7. If one parameter value is supplied in the form @parameter = value, all subsequent parameters must be supplied in this manner. 
  8. If the parameter values are not passed in the form @parameter =value, the values must be supplied in the identical order (left to right) as the parameters are listed in the CREATE PROCEDURE statement.

The value of each declared parameter must be supplied by the user when the procedure is called unless a default value for the parameter is defined or the value is set to equal another parameter. However, if a procedure contains table-valued parameters, and the parameter is missing in the call, an empty table is passed in. 

There are the following types of parameters for a stored procedure:



IN Type Parameter: 

It is the default parameter that will receive input value from the program. Input parameters allow the caller to pass a data value to the stored procedure or function. 

The value of an IN parameter is a constant; it can't be changed or reassigned within the module.

OUTPUT Type Parameter: 

Output parameters allow the stored procedure to pass a data value or a cursor variable back to the caller. User-defined functions cannot specify output parameters.

An OUT parameter must be a variable, not a constant. It can be found only on the left-hand side of an assignment in the module. 

You cannot assign a default value to an OUT parameter outside of the module's body. In other words, an OUT parameter behaves like an uninitialized variable.

READONLY Parameter:

The Readonly parameter is the same as IN parameter of C#. Readonly indicates that the parameter cannot be updated or modified within the body of the procedure. If the parameter type is a table-valued type, READONLY must be specified. i.e @TVP TvpParameter READONLY









Q98. What is the OUTPUT parameter in the stored procedure? Can the OUT parameter be defined as Default as well? 

Procedure parameters can also return values to the calling program if the parameter is marked as an OUTPUT parameter. A stored procedure in SQL that has an output parameter will return a value after the execution completion.

Output parameters allow the stored procedure to pass a data value or a cursor variable back to the caller. User-defined functions cannot specify output parameters.

OUT Parameters are used to send output values to the caller same as C# OUT parameter.  To specify an output parameter, the OUTPUT keyword must be specified in the definition of the parameter in the CREATE PROCEDURE statement. For example:

@LastRecordID INT OUTPUT  

The calling program must also use the OUTPUT keyword when executing the procedure to save the parameter's value in a variable that can be used in the calling program. For example:
 
DECLARE @ID INT;  
EXECUTE procInsertRecord @ID OUT,
SELECT @ID AS LastInsertedRecord;



The OUTPUT parameters are used to return values to the caller of the procedure but text, ntext, and image parameters cannot be used as OUTPUT parameters unless the procedure is a CLR procedure.

The OUTPUT variable must be defined during the procedure creation and also during the use of the variable. The parameter name and variable name do not have to match. However, the data type and parameter positioning must match.

A table-value data type cannot be specified as an OUTPUT parameter of a procedure. An output parameter can be a cursor placeholder unless the procedure is a CLR procedure. 

Transact-SQL procedures can use the cursor data type only for output parameters. If the cursor data type is specified for a parameter, both the varying and output keywords must be specified for that parameter in the procedure definition. 

A parameter can be specified as only output, but if the varying keyword is specified in the parameter declaration, the data type must be cursor and the output keyword must also be specified.



Q99. What is the Default Parameter in the stored procedure?

Default or Optional Parameters:

If you do not pass the parameter to the stored procedure while invoking the SP, you will get an error. Default parameters are used to pass parameter values during the creation itself. 

A parameter is considered optional if the parameter has a default value specified when it is declared. It is not necessary to provide a value for an optional parameter in a procedure call.

The default value must be a constant or it can be NULL.  If no value can be specified appropriately as a default for the parameter, specify NULL as the default. 



If the default value is a character string that contains embedded blanks or punctuation, or if it starts with a number (for example, 6xxx), it must be enclosed in single, straight quotation marks.

Thus, it will take the default parameter value whenever you execute the procedure if the user explicitly does not provide any other value and provide the results accordingly. 

For example  @Address nvarchar(30) = NULL

Although parameters for which defaults have been supplied can be omitted, the list of parameters can only be truncated. For example, if a procedure has five parameters, both the fourth and the fifth parameters can be omitted. However, the fourth parameter cannot be skipped as long as the fifth parameter is included, unless the parameters are supplied in the form @parameter =value.





To Be Continued Part-12...


Recommended Articles






Thanks for visiting this page. Please follow and join us on LinkedInFacebookTelegramQuoraYouTubeTwitterPinterestTumbler, and VK  for regular updates.

    

No comments:

Post a Comment

Please do not enter any HTML. JavaScript or spam link in the comment box.