Tech Point Fundamentals

Sunday, August 7, 2022

C# Interview Questions and Answers - Part 12

CSharp-Interview-Questions ,
C# Interview Questions and Answers - Part 12

C# Interview Questions and Answers - Part 12

csharp-interview-questions-and-answers-part1

Are you preparing for the C# Interview? If yes, then you are at the right place. This is the C# Interview Questions and Answers article series. Here we will see the Top 150+ C# Interview Questions with Answers. 

Please visit our YouTube Channel for Interviews and other videos by below link:



Please read the complete Design Patterns Interview Questions and Answers series here.


Introduction


This is the 12th part of this C# Interview Questions and Answers article series. Each part contains 10 C# Interview Questions with Answers. Please read all the C# Interview Questions list here.

I will highly recommend you to please read the previous parts over here before continuing the current part:





C# Interview Questions and Answers - Part 12


Q121. What is the difference between final, finally, and finalize?

The basic difference between final, finally, and finalize is that the final is an access modifier used for restriction, finally is a code block to execute a set of statements whether an exception is triggered or not, and finalize is the method of the object class to release the unmanaged resource.



Final:

Java has the final keyword but C# does not have any final keyword. However, for the sealed method, the compiler uses the final modifier internally. Please see the IL code generated by the compiler here for a C# sealed method.

C# use the sealed keyword for the same purpose. But unlike Java, a sealed keyword can only be applied to method and class, not on property or variable. In Java, the final keyword can be used for class, method, property, and variables as well.

The Final or sealed modifier is used to apply restrictions on a class or method. The final class or sealed can't be inherited, a final method (sealed) can't be overridden further and the final variable value can't be changed.

Please read the sealed class and sealed method article here for more detail.








Finally:

Finally is a code block that is used after the try-catch block or after the try block.  The final block is used to execute a given set of statements, whether an exception is thrown or not thrown. 

The finally block generally contains the logic to release the resource. The Iterator pattern and using dispose pattern always use the try-finally exception handling pattern. Please watch the try-finally video here for more detail.








Finalizer:

Finalize is a method of Object class. The Finalize in C# is used to free unmanaged resources like database connections etc. The finalize method is used to perform clean-up processing just before the object is garbage collected. 

It is invoked by the GC just before an object is discarded by the garbage collector. Please read the Finalizer article here for more detail.



Q122. What is the difference between First() and FirstOrDefualt() in C#?

Both First() and FirstOrDefualt() are used to get the first record of the object collection.

The major difference between First and FirstOrDefault is that First() will throw an exception if there is no result data for the supplied criteria whereas FirstOrDefault() will return the default value (null) if there is no result data.

So the First() method will throw System.InvalidOperationException exception if there is no result data but FirstOrDefault() returns the default value i.e null if there is no result data.



Q123. What is the volatile keyword? What is the use of volatile keywords in C#?

The volatile keyword indicates that a field might be modified by multiple threads that are executing at the same time. So the volatile keyword is used to tell the compiler not to use any optimization on the volatile marked variables.

JIT compiler always does some optimization strategy while compiling the code. The optimization techniques used by the JIT might lead to unpredictable results when your .Net program is trying to perform non-volatile reads of data in a multithreaded scenario. 

However, in C# all memory writes are volatile irrespective of whether you are writing data to a volatile or a non-volatile object. But the ambiguity happens when you are reading data.

When you are reading data that is non-volatile, the executing thread may or may not always get the latest value.  If the object is volatile, the thread always gets the most up-to-date value








The volatile keyword in C# is used to inform the JIT compiler that the value of the variable should never be cached because it might be changed by the operating system, the hardware, or a concurrently executing thread. So the compiler avoids using any optimizations on the variable that might lead to data conflicts, i.e. to different threads accessing different values of the variable.

While using the volatile keyword, you must have to follow some rules:

  1. You can use the volatile keyword with any reference, pointer, and enum types.
  2. When you specify a reference type object as volatile, only the pointer address is volatile, not the value of the instance.
  3. You can also use the volatile modifier with byte, short, int, char, float, and bool types.
  4. But a local instance variable cannot be declared as volatile. 
  5. Also, a double variable cannot be volatile because it is 64 bits in size, larger than the word size on x86 systems. If you need to make a double variable volatile, you should wrap it inside in class.

Please watch the volatile video here for more details.



Q124. What is the difference between Deferred Execution vs Immediate Execution? or What is the difference between Lazy Operators vs Greedy Operators?

LINQ provides two different behaviors of Query Execution i.e. Deferred Execution and Immediate Execution. By default, LINQ uses deferred execution. So LINQ query is not executed when constructed but when enumerated.

When we write a LINQ query, it doesn’t execute by itself. It executes only when we access the query results means the execution of the query is deferred until the query variable is iterated over in a loop.

There are two types of query executions in LINQ:



Immediate Execution:

  1. In Immediate Execution Query is executed at the point of its declaration.
  2. Greedy Operators are used for Immediate Execution.
  3. For example Aggregate Functions (Count, Average, Min, Max, Sum) and Element Operators (First, Last, FirstOrDefault, LastOrDefault, Single, ToList, ToArray, ToDictionary, etc) are used for Immediate Execution.



Deferred Execution:

  1. Deferred execution means that the evaluation of an expression is delayed until its realized value is actually required. So a Deferred Execution doesn’t compute the result until the caller actually uses it.
  2. In Deferred Execution Query is not executed at the point of its declaration. 
  3. Lazy Operators are used for deferred execution. For example Projection Operator (Select, SelectMany) and Restriction Operator ( Where, Paging Operator like Take, Skip) are the Lazy Operators.
  4. Deferred execution greatly improves performance by avoiding unnecessary execution.  It is applicable on any in-memory collection as well as remote LINQ providers.
  5. Deferred execution re-evaluates each execution i.e is called lazy evaluation. So it always gives you the latest data.
  6. Deferred execution is supported directly in the C# language by the yield keyword when used within an iterator block. So you can implement deferred execution for your custom extension methods for IEnumerable using the yield keyword of C#. Please read more about the yield statement here.

The LINQ technologies make extensive use of deferred execution in both the members of the core System.Linq classes and in the extension methods in the various LINQ namespaces, such as System.Xml.Linq.Extensions.



Q125. What is the difference between Lazy Loading vs Early Loading in C#? 

Eager Evaluation vs Lazy Evaluation OR Lazy Loading vs Eager Loading:

When you write a method that implements deferred execution, you also have to decide whether to implement the method using lazy evaluation or eager evaluation. Lazy Loading and Explicit Loading both are types of deferred execution.

Lazy Loading says that “don’t do the work until you absolutely have to.” So in Lazy Loading, a query is not executed, until it is requested.  In Entity Framework, you can turn off the lazy loading feature by setting the LazyLoadingEnabled property of the ContextOptions on context to false.

context.ContextOptions.LazyLoadingEnabled = false;



Eager Loading says that “do all the work in advance”. So in Eager Loading, the LINQ query is executed when any Conversion operator like ToArray(), ToList(), ToDictionary(), ToLookup() is called. In LINQ to an Entity, we can also use the Include() method to make Eager Loading.

context.Contacts.Include("User.UserRole");

Explicit Loading says that “do all the work even with lazy loading disabled”.  So even with lazy loading disabled, it is still possible to lazily load related entities. For that, we have to make an explicit call to the Load() method. 

context.Entry(post).Reference(p => p.UserList).Load();

In Lazy Evaluation, every element of the source collection is processed during each call to the iterator. Lazy evaluation usually gives better performance because it distributes overhead processing evenly throughout the evaluation of the collection and minimizes the use of temporary data.

On the other hand in Eager Evaluation, the first call to the iterator will result in the entire collection being processed.  Due to this a temporary copy of the source collection might also be required for example the OrderBy clause.



Advantage of Deferred Execution:

  1. Deferred Execution avoids unnecessary query execution and hence improves performance.
  2. Query construction and Query execution are decoupled, so we can create the LINQ query in several steps.
  3. Deferred Execution prevents loading all the data from remote to in memory, so it enhances the performance of the application.
  4. A deferred execution query is always reevaluated when you re-enumerate so we always get the latest data.



Q126. What is the difference between Late Binding and Lazy Loading? Are both the same? 

Lazy Loading vs Late Binding:

Lazy Loading is the query execution concept where a query is not executed until it is requested. Projection Operator (Select, SelectMany) and Restriction Operator ( Where, Paging Operator like Take, Skip) are the Lazy Operators which are used in Lazy Loading.

On the other hand, Late Binding is the concept of binding a method to the object at run-time. It can be achieved by method overriding. 

So Lazy Loading and Late Binding both are not the same. Please read more about the late binding here for more details.








Q127. What is the difference between Early Binding and Early Loading? Are both the same?

Eager Loading vs Early Binding:

Eager Loading is the query execution concept where a query is executed immediately where it is declared. Greedy Operators like Aggregate Functions (Count, Average, Min, Max, Sum) and Element Operators (First, Last, FirstOrDefault, LastOrDefault, Single, ToList, ToArray, ToDictionary, etc) are used for Eager Loading.

On the other hand, Early Binding is the concept of binding a method to the object at compile time. It can be achieved by method overloading.

So Eager Loading and Early Binding are not the same. Please read more about the late binding here for more details.



Q128. What is the difference between "this" vs "base" keyword in C#?

Both this and base are used to refer to the context of a class member i.e current or base class.

The "base" Keyword:

  1. The "base" keyword is used to access the base class members from a derived class.
  2. The "base" keyword can be used to call a base class method that has been overridden in the derived class. Please read more here.
  3. The"base" keyword is used to call the base class constructor or you can say to pass the parameters to the base class constructors. Please read more here.
  4. The "base" keyword cannot be used in a static class or for a static member. Please read more here.








The "this" Keyword:

  1. A "this" keyword refers to the current instance of the class. So it is generally ignored as it is implicitly there.
  2. It is also used to differentiate between the method's formal parameters and class fields if they both have the same name.
  3. A  “this” keyword is also used to call another constructor from a constructor in the same class. This is known as constructor chaining. Please read more here.
  4. A "this" keyword is also used to define the first binding parameter in the extension method. Please read more here.
  5. A "this" keyword is used to define the indexers. Please read more here.








Q129. What is the difference between foreach loop and for loop in C#?

In C# for loop is used to perform any repetitive task for "n" number of times. Here we can specify the initial value and increment or decrement operation on each iteration explicitly. We can also specify multiple multiple loop variables to control the iterations.

On the other hand, foreach loop is also used for doing any task n number of times but it does not provide any control variable explicitly. The loop control value is decided automatically depending on the total collection of arrays or list generic or ArrayList or object collections.



For Statement:

  1. The for statement executes a statement or a block of statements while a specified Boolean expression evaluates to true. 
  2. The for loop has three sections, the initialization section, condition section, and iterator section. However, all the sections of the for statement are optional.
  3. A for statement uses the loop initialization variables and loop termination logic explicitly. However, if you don't declare a loop variable in the initializer section, you can use zero or more of the expressions from the preceding list in the initializer section as well.
  4. The initializer section is executed only once, before entering the loop.
  5. A condition section is a boolean expression that determines whether the next iteration in the loop should be executed or not. It can contain zero or more expressions separated by commas.
  6. The iterator section defines what happens after each execution of the body of the loop. The iterator section contains increment or decrement on the counter.
  7. At last, the body of the loop must be a single statement or a block of statements.



Foreach Statement:

  1. The foreach statement executes a statement or a block of statements for each element in an instance of the type that implements the System.Collections.IEnumerable or System.Collections.Generic.IEnumerable<T> interface.
  2. If the foreach statement is applied to null, a NullReferenceException is thrown. 
  3. If the source collection of the foreach statement is empty, the body of the foreach statement isn't executed and skipped.
  4. Beginning with C# 8.0, you can use the await foreach statement to consume an asynchronous stream of data, that is, the collection type that implements the IAsyncEnumerable<T> interface.
  5. You can use the var keyword to let the compiler infer the type of an iteration variable in the foreach statement. However, you can also explicitly specify the type of iteration variable.



For Loop vs ForEach Loop:

  1. The foreach statement enumerates the elements of a collection and executes its body for each element of the collection. On the other hand a for statement executes its body while a specified Boolean expression evaluates to true.
  2. In for loop, we iterate the collection in both forward and backward directions, But in the foreach loop, we can iterate a collection only in the forward direction, not in a backward direction.
  3. In terms of a variable declaration, foreach loop has five variable declarations whereas for loop only have three variable declarations.
  4. A foreach loop also creates a copy of a collection temporarily whereas the for loop doesn’t do.
  5. Foreach loops do not keep track of the index. So we cannot obtain array index in the ForEach loop but we can do that in the for loop.
  6. Foreach loop is always forward readonly so they are not appropriate when you want to modify the collection but a for loop can be used in this scenario.



Q130. What is the difference between instance field and property in the C# class?

You can store value using either a field or a property and retrieve the value back from them. You can even protect both fields and properties using access modifiers such as private or protected. Both can be defined in class and structure. 

The main difference between a field and a property is that a field is a variable of any type that is declared directly in the class while a property is a member that provides a flexible mechanism to read, write or compute the value of a private field. An interface cannot define an instance field but a class or struct can have instance fields.

Instance fields are actual variables that store a particular piece of information. Properties offer another level of abstraction to expose the fields. This additional abstraction layer in properties enables the developers to apply access modifiers separately for getters and setters.








The properties do not have storage locations. The properties have accessors that contain the executable statements to read the values and to set the values. The accessor declarations can contain a get accessor and a set accessor. Also, you cannot apply any validation logic on instance fields but you can do that on property.

For auto-properties that you use, the compiler will generate backing fields and associated methods to access the fields for you. By using property, you can restrict the external user to either set the value(only set accessor) or read (only get accessor) the value or both (both get and set accessor).

A field is a variable of any type that is declared directly in a class or struct. Fields are members of their containing type. A class or struct may have instance fields, static fields, or both. Instance fields are specific to an instance of a type while a static field belongs to the type itself, and is shared among all instances of that type.

Please watch the instance field vs property video here for more details.



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.

 

Read more »
By at No comments:
Labels:

Friday, August 5, 2022

SQL Interview Questions and Answers - Part 11

SQL-Interview-Questions , sql-stored-procedure
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 LinkedIn, Facebook, Telegram, Quora, YouTube, Twitter, Pinterest, Tumbler, and VK  for regular updates.

    

Read more »
By at No comments:
Labels: ,