Tech Point Fundamentals

Friday, August 19, 2022

SQL Interview Questions and Answers - Part 13

SQL Interview Questions and Answers - Part 13

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 13th part of the SQL Interview Questions and Answers article series. Each part contains 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 13


Q111. What is SQL Function? What are the different types of functions in SQL?

SQL Functions are methods used to perform some TSQL data operations on the result set. There are so many factors and parameters based on which you can split or categorize the SQL Function.

SQL Function Type Based on Type:

  1. System SQL Functions 
  2. User-Defined Functions (UDF)

SQL Function Type Based of Result:

  1. Deterministic SQL Function
  2. Non-Deterministic SQL Functions



SQL Function Type Based on Return Type:

  1. Scalar SQL Function
  2. Non-Scaler SQL Functions

SQL Function Type Based on Functionality:

  1. Aggregate SQL Function
  2. Ranking Functions
  3. Analytic Functions
  4. Rowset Functions

SQL Function Type Based on Implementation:

  1. Inline Function
  2. Multistatement Function
  3. Table-Valued Function
  4. Schema-Bound Functions



Q112. What is the difference between Deterministic and Non-Deterministic SQL Functions?

SQL Server built-in system functions can be either deterministic or nondeterministic. 

Functions are Deterministic when they always return the same result any time they are called by using a specific set of input values. For example COALESCE, DATALENGTH, ISNULL, NULLIF, SQUARE, DATEADD, DATEDIFF, POWER, etc all are Deterministic SQL Functions.

Functions are NonDeterministic when they could return different results every time they are called, even with the same specific set of input values. For example, GETDATE, CURRENT_TIMESTAMP, ROW_NUMBER, RAND, RANK, DENSE_RANK, etc are Nondeterministic SQL Functions.



Q113. What are the different System SQL Functions?

SQL has many in-built functions used to perform different tasks like string concatenations, mathematical calculations, etc.

Aggregate SQL Functions: 

The Aggregate Functions in SQL perform calculations on a group of values and then return a single value. They are allowed in the select list or the HAVING clause of a SELECT statement. 

You can use aggregation in combination with the GROUP BY clause to calculate the aggregation on categories of rows. Use the OVER clause to calculate the aggregation on a specific range of values. 

The OVER clause cannot follow the GROUPING or GROUPING_ID aggregations.
All aggregate functions are deterministic, which means they always return the same value when they run on the same input values. For example SUM(), AVG(), COUNT() etc.








Analytic SQL Functions:

Analytic functions compute an aggregate value based on a group of rows. However, unlike aggregate functions, analytic functions can return multiple rows for each group. 

You can use analytic functions to compute moving averages, running totals, percentages, or top-N results within a group.

Ranking SQL Functions:

Ranking functions return a ranking value for each row in a partition. Depending on the function that is used, some rows might receive the same value as other rows. Ranking functions are nondeterministic.






Rowset SQL Functions:

Rowset functions Return an object that can be used like table references in an SQL statement.



Scalar SQL Functions:

Operate on a single value and then return a single value. Scalar functions can be used wherever an expression is valid. There are different types of scalar functions in SQL:

SQL String Functions: CHARINDEX, CONCAT, DATALENGTH, LEN, LEFT, RIGHT, FORMAT, LTRIM, RTRIM, TRIM, LOWER, UPPER, QUOTENAME, REPLACE, REPLICATE, STUFF, SUBSTRING, etc.

SQL Date Functions: CURRENT_TIMESTAMP, DATEADD, DATEDIFF, DATEFROMPARTS, GETDATE, GETUTCDATE, ISDATE, etc.

SQL Advanced Functions: CAST, COALESCE, CONVERT, IIF, ISNULL, NULLIF, ISNUMERIC, USER_NAME, SYSTEM_USER etc.

Mathematical SQL Functions: ABS, EXP, FLOOR, POWER, RAND, ROUND, SQRT, etc.



Q114. What is User Defined Function (UDF) in SQL? What is the advantage of UDF in SQL?

SQL Server user-defined functions are routines that accept parameters, perform an action, such as a complex calculation, and return the result of that action as a value. The return value can either be a single scalar value or a result set.

It requires CREATE FUNCTION permission in the database and ALTER permission on the schema in which the function is being created. If the function specifies a user-defined type, it requires EXECUTE permission on the type.

A user-defined function can take zero or more input parameters and returns either a scalar value or a table. Parentheses are required after the function name even if a parameter is not specified. A function can have a maximum of 2,100 parameters. But a User-Defined Functions do not support output parameters.



When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value.  The value of each declared parameter must be supplied by the user when the function is executed unless a default for the parameter is defined.

You should have to specify a parameter name by using an at-sign (@) as the first character. The parameter name must comply with the rules for identifiers. 

Parameters are local to the function; the same parameter names can be used in other functions. Parameters can take the place only of constants; they cannot be used instead of table names, column names, or the names of other database objects.

ANSI_WARNINGS is not honored when you pass parameters in a stored procedure, user-defined function, or when you declare and set variables in a batch statement.

If a CREATE FUNCTION statement produces side effects against resources that do not exist when the CREATE FUNCTION statement is issued, SQL Server executes the statement. However, SQL Server does not execute the function when it is invoked.








Advantages of UDF:

UDF allows modular programming. You can create the function once, store it in the database, and call it any number of times in your program. User-defined functions can be modified independently of the program source code.

UDF allows faster execution. Similar to stored procedures, Transact-SQL user-defined functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions

This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times.

UDF can reduce network traffic. An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. The function can then be invoked in the WHERE clause to reduce the number of rows sent to the client.



Q115. What are the features and limitations of User-Defined SQL Functions (UDF)?


  1. A UDF function can have a maximum of 2100 input parameters.
  2. When a parameter of the function has a default value, the keyword DEFAULT must be specified when calling the function to get the default value.
  3. User-defined functions can be nested also. This means one user-defined function can call another.  User-defined functions can be nested up to 32 levels. Exceeding the maximum levels of nesting causes the whole calling function chain to fail.
  4. User-defined functions do not support OUT parameters.
  5. User-defined functions cannot be used to perform actions that modify the database state. You can use UPDATE, INSERT, and DELETE statements to modify table variables that are local to the function.
  6. User-defined functions cannot contain an OUTPUT INTO clause that has a table as its target.
  7. User-defined functions can not return multiple result sets. Use a stored procedure if you need to return multiple result sets.


  8. Error handling is restricted in a user-defined function. A UDF does not support TRY...CATCH, @ERROR, or RAISERROR.
  9. User-Defined functions cannot call a stored procedure but can call an extended stored procedure.
  10. User-defined functions cannot make use of Dynamic SQL or Temp Tables. But Table variables are allowed.
  11. SET Statements are not allowed in a user-defined function.
  12. The FOR XML clause is not allowed.
  13. The following Service Broker statements cannot be included in the definition of a Transact-SQL user-defined function: SEND, RECEIVE, GET CONVERSATION GROUP, MOVE CONVERSATION, BEGIN DIALOG CONVERSATION, END CONVERSATION.
  14. You can not use the INTO clause in the function body. 
  15. FETCH statements that return data to the client are not allowed. Only FETCH statements that assign values to local variables using the INTO clause are allowed.

The only changes that can be made by the TSQL statements in the function body are changes to objects local to the function, such as local cursors or variables. 

Modifications to database tables, operations on cursors that are not local to the function, sending e-mail, attempting a catalog modification, and generating a result set that is returned to the user are examples of actions that cannot be performed in a function.



Q116. What are the different Scalar User-Defined SQL Functions?

T-SQL UDFs are an elegant way to achieve code reuse and modularity across Transact-SQL queries. Some computations (such as complex business rules) are easier to express in imperative UDF form. 

Scalar User-Defined Function:

A Scalar UDF accepts zero or more parameters and returns a single value. The return type of a scalar function is any data type except text, ntext, image, cursor, and timestampScalar functions can be used in a WHERE clause of the SQL Query. 

For the inline scalar function, returned scalar value is the result of a single statement. On the other hand, for a multi-statement scalar function, the function body can contain a series of Transact-SQL statements that return a single value. A Multi-Statement contains multiple SQL statements enclosed in BEGIN-END blocks.

CREATE FUNCTION GetEmployeeName(@Id INT)  
 RETURNS VARCHAR(50)  
 AS  
 BEGIN
  RETURN ( SELECT Name FROM Employee WHERE Id = @Id )
 END



Limitations of Scalar UDF:

Scalar UDFs typically end up performing poorly due to the following reasons:

Iterative invocation: UDFs are invoked in an iterative manner, once per qualifying tuple. This incurs additional costs of repeated context switching due to function invocation.

Lack of costing: During optimization, only relational operators are costed, while scalar operators are not. 

Interpreted execution: UDFs are evaluated as a batch of statements, executed statement-by-statement. Each statement itself is compiled, and the compiled plan is cached. 



Serial execution: SQL Server does not allow intra-query parallelism in queries that invoke UDFs. Scalar UDF prevents the creation of parallel execution plans.

Inaccurate I/O statistics: The SET STATISTICS IO setting offers an I/O statistics report that helps to monitor the disk activity of the executed query and this report helps us to observe disk activities of the executed query. However, the scalar functions I/O statistics can not be measured by this report, and this case cause to observe misleading I/O information.

To overcome the above limitations of scalar UDF, SQL Server 2019 introduced a new feature called inlining. The goal of the scalar UDF inlining feature is to improve the performance of queries that invoke T-SQL scalar UDFs, where UDF execution is the main bottleneck.

Please read more here about the scalar UDF.



Q117. What are the different Table-Valued UDF in SQL?

User-defined table-valued functions return a table data type. A Table-Valued UDF accepts zero or more parameters and returns a table variable. This type of function is special because it returns a table where you can query the results of a join with other tables. 

A Table Valued function is further categorized into an “Inline Table-Valued Function” and a “Multi-Statement Table Valued Function”. Please watch the complete video of Table-Valued Type or Table-Valued Parameter here for more details.






Inline TVF:

An Inline Table-Valued Function contains a single statement that must be a SELECT statement. The result of the query becomes the return value of the function. There is no need for a BEGIN-END block in an Inline function.

CREATE FUNCTION GetEmployeeDetails(@Id INT)  
RETURNS TABLE  
AS  
RETURN  
    SELECT * FROM Employee WHERE Id = @Id



 Multi Statement TVF:

A Multi-Statement TVF contains multiple SQL statements enclosed in BEGIN-END blocks. In the function body, you can read data from databases and do some operations. In a Multi-Statement Table-valued function the return value is declared as a table variable and includes the full.

In a Multi-Statement Table-valued function the return value is declared as a table variable and includes the full structure of the table to be returned. The RETURN statement is without a value and the declared table variable is returned automatically. Please watch the Table Variable video here for more details.



CREATE FUNCTION GetRoleWiseSalary()  
RETURNS @EmployeeSalary TABLE  
(
RoleName VARCHAR(50),
MaxSalary Numeric(18,2),
MinSalary Numeric(18,2),
AvgSalary Numeric(18,2)
)
AS  
BEGIN
INSERT INTO @EmployeeSalary
SELECT RoleName, MAX(Salary) AS MaxSalary,  Min(Salary) AS MinSalary, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY RoleName
ORDER BY RoleName DESC;
RETURN
END

Joining a TVF in a FROM clause is possible, but can result in poor performance. SQL Server is unable to use all the optimized techniques on some statements that can be included in a TVF, resulting in a suboptimal query plan. To obtain the best possible performance, whenever possible use joins between base tables instead of functions.



Q118. What is Schema Bound function in SQL? What is the advantage of schema-bound functions?

CREATE FUNCTION supports a SCHEMABINDING clause that binds the function to the schema of any objects it references, such as tables, views, and other user-defined functions. 
An attempt to alter or drop any object referenced by a schema-bound function fails.

You can use ALTER FUNCTION to remove the schema binding. The ALTER FUNCTION statement should redefine the function without specifying WITH SCHEMABINDING.



Requirement of Schema Bound Function:

These conditions must be met before you can specify SCHEMABINDING in CREATE FUNCTION:

  1. All views and user-defined functions referenced by the function must be schema-bound.
  2. All objects referenced by the function must be in the same database as the function. The objects must be referenced using either one-part or two-part names.
  3. You must have REFERENCES permission on all objects (tables, views, and user-defined functions) referenced in the function.

CREATE FUNCTION dbo.GetEmployeeInfo(@Id INT)
RETURNS VARCHAR(50)
WITH SCHEMABINDING
AS
BEGIN
  RETURN ( SELECT Name FROM dbo.Employee WHERE Id = @Id )
END
GO

In order to change the table (Employee), you'd have to modify the function to not use WITH SCHEMABINDING, then change the table, then modify the function again, to both reflect the new data types and to once again use WITH SCHEMABINGINDING.



Advantage of Schema Bounded SQL Function:

SCHEMA BINDING is commonly used with SQL Server objects like views and UDF. 
The main benefit of SCHEMA BINDING is to avoid any accidental drop or change of an object that is referenced by other objects. 

If a UDF is not created with the SCHEMABINDING clause, changes that are made to underlying objects can affect the definition of the function and produce unexpected results when it is invoked. 

Microsoft recommends that you should implement one of the following methods to ensure that the function does not become outdated because of changes to its underlying objects:

Specify the WITH SCHEMABINDING clause when you are creating the UDF. This ensures that the objects referenced in the function definition cannot be modified unless the function is also modified.

Execute the sp_refreshsqlmodule stored procedure after modifying any object that is specified in the definition of the UDF.

If creating a UDF that does not access data, specify the SCHEMABINDING option. This will prevent the query optimizer from generating unnecessary spool operators for query plans involving these UDFs.




Q119. What is the difference between Stored Procedure and User-Defined Function in SQL?

Stored Procedures vs SQL Functions:

  1. The function must return a value explicitly while in Stored Procedure it is optional as it returns status value by default. 
  2. Functions can not have any OUT parameters whereas Procedures can have both input and output parameters.
  3. Functions can be called from Procedure whereas Procedures cannot be called from a Function.
  4. A function can not use Dynamic SQL Query but a stored procedure can use DSQL.
  5. We can use Transactions in Procedure whereas we can't use Transactions in Function.
  6. We can not use the TRY-CATCH block in function but a procedure can use exception handling TRY CATCH block.
  7. SQL Function cannot modify the database so it can not use INSERT, UPDATE, and DELETE DML commands while they are allowed in the stored procedure.
  8. The Temp Tables cannot be used within a function. Only table variables can be used. But a stored procedure can use both temp table and table variables.
  9. Functions can be used in JOIN clauses but stored procedures cannot be used in JOIN clauses.
  10. A function can be used directly in the SELECT clause but a procedure cannot be utilized in a SELECT statement.
  11. A function can be used anywhere in the WHERE/HAVING/SELECT section whereas stored procedures can not be.








Q120. What is the difference between Table-Valued Functions and View in SQL? 

Views are virtual database objects that retrieve data from one or more tables. A view in SQL Server is just like a virtual table created from a result set of a SQL query. 

The main difference between them is that views do not allow parameterized usage while  TVF allows parameters. 

The second difference between them is that we can update the data from view but you can't do using the TVF.

However, both view and TVF can be used for Joining with other tables and both can be schema bounded as well.



To Be Continued Part-14...


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.