Table Value Function or Stored Procedure - SQL Server Brain Basher of the Week #067

Let's talk about a very common scenario that you do with all solutions. If you need to implement a business logic using a database object such as View, Stored Procedure, or Function, how do you decide the best for your requirements.

Here is the Interview Question of the week. Should you use a Stored Procedure or Table Value Function (TVF) for the given scenario?

You have a table called Customer that has a column called CreditLimit. You must write module for accepting the CustomerId and Credit Limit, and updating the CreditLimit column with submitted Credit Limit. You need to make sure that exceptions are handled as well.

In order to answer this question, you should certain set of factors that can be used for deciding whether the logic must be implemented using a Stored Procedure or Function. Let's see each an every factor.

First thing you should know is, the output of the Stored Procedure cannot be easily used with another output generated by SELECT though INSERT EXEC is possible. However this can be easily done with TVFs. In addition to that, Stored Procedures allow us to return multiple results but function can return a single result.

Other main thing is modification. You cannot modify records inside Functions but it is possible within Stored Procedures. And you can form a statement dynamically and execute inside the Stored Procedure but it is not possible with Functions.

Exception handling is not possible with functions but details exception handling is possible with Stored Procedures.

Considering these factors, the answer is Stored Procedure because data modification and exception handling are not possible with Functions.

SQL Server Brain Basher of the Week #045 - Stored Procedure or Function

A question related to Stored Procedure is very common during an interview and many get stuck with it when it has to be discussed in details. What everyone thinks is, Stored Procedure is a pre-compiled code just like and EXE (Of course it is not) and because of that it can execute the logic added fast. Yes, it is true that you get some performance benefits but it is not the right way to think about it. What if a scenario like below is given and a question is asked.......

Assume that you need to implement a business logic that suppose to do some calculation based on inputs given. The result is always a single integer value and the code of the implementation is very complex. Logic requires few tables to be read and do some complex calculations. Should I use a Stored Procedure for this or a Function?

Generally, If I am the interviewer, I do not expect just an answer saying "Stored Procedure" or "Function". I prefer a lengthy answer explaining why we should go for either Stored Procedure or Function.

Let's see some key points for making the decision. Considering the return value and inputs, it looks like it is better to use a function than a stored procedure. Because the implementation is similar to a scalar function. You need to get more details on the logic that needs to be implemented. If it needs loading data into temporary containers, remember, function does not support temporary tables but table variables. And we need to know how it going to be used. Is it going to be used with SELECT statements? If so, you know that stored procedure cannot be used with SELECT statement, specifically with columns or WHERE clause. As per the requirement, it looks like it does not require to do INSERT, UPDATE or DELETE. Do not forget, these statements are not allowed inside functions.

Not only that, though you do not need to consider with the given scenario, remember, function does not support transactions and TRY/CATCH blocks.

This is what we expect from the interviewee. If interviewee can explain the answer with details, it forced the interviewer to think as He is smart. So be smart!

Types of SQL Server Built-In Functions: Scalar, Grouped Aggregate, Window, Rowset

SQL Server offers many number of built-in functions for getting commonly required operations done, minimizing the time and resources need to implement them for handling business logics. These functions can be categorized with various classifications. One way of categorizing them is, understanding their scope of input and return type. Under this categorization, they can be organized into four groups: Scalar, Grouped Aggregate, Window, and Rowset. The organization of functions under this categorization makes some of functions member of more than one group.

Here is a small note on this categorization.

Scalar Functions
These functions accept zero or one or more than one value (or a row), process them as per its intended functionalities, and return a single value. These functions can be further categorized as string, conversion, mathematical, etc.. Some of these functions are deterministic  and some are nondeterministic. (Deterministic functions always return same result any time they are called with same input and same database state. Nondeterministic functions may return different result each time they are called with same input and same database state) Here are some examples for Scalar Functions;

1. -- accepts no arguments
2. SELECT GETDATE()
3.  
4. -- accepts one argument
5. SELECT LEN('Hello World')
6.  
7. -- accepts multiple argument
8. SELECT STUFF('accepts one argument', 9, 3, 'multiple')

Grouped Aggregate Functions
These functions perform their intended operations on a set of rows defined in a GROUP BY clause and return a single value. If GROUP BY is not provided, all rows are considered as one set and operation is performed on all rows. All aggregate functions are deterministic and ignore NULLs except the COUNT(*).

1. -- Aggregate functions without GROUP BY
2. SELECT COUNT(*), MIN(ListPrice), MAX(ListPrice)
3. FROM Production.Product
4.  
5. -- Aggregate functions with GROUP BY
6. SELECT YEAR(OrderDate) OrderYear, SUM(SubTotal) Total
7. FROM Sales.SalesOrderHeader
8. GROUP BY YEAR(OrderDate)

Window Functions
The operation of Window Functions are bit different from other functions. These functions produce a scalar value based on a calculation done on a subset of the main recordset. The subset used for producing the result is called as the Window. A different order can be defined for the window for performing the calculation without affecting the order of input rows or output rows. In addition to that, partitioning the window is also allowed.

Window is specified using OVER clause with its specification. SQL Server offers number of Window function for handling ranking, aggregation and offset comparisons between rows.

Here are few example on Window Functions;

1. -- This uses SUM over Territory subsets (window)
2. SELECT t.Territory, t.OrderYear, t.Total
3.     , SUM(t.Total) OVER (PARTITION BY MONTH(t.Territory)) TotalByTerritory
4. FROM (SELECT TerritoryId Territory, YEAR(OrderDate) OrderYear, SUM(SubTotal) Total
5.     FROM Sales.SalesOrderHeader
6.     GROUP BY TerritoryId, YEAR(OrderDate)) as t
7. ORDER BY 1, 2
8.  
9. -- This uses ROW_NUMBER over all records
10. SELECT SalesOrderID, SubTotal
11.     , ROW_NUMBER() OVER (ORDER BY SubTotal) AS OrderValuePosition
12. FROM Sales.SalesOrderHeader
13. WHERE YEAR(OrderDate) = 2008
14. ORDER BY SalesOrderID

Rowset Functions
Rowset functions accept input parameters and return objects that can be used as tables in TSQL statements. SQL Server offers four Rowset functions (OPENDATASOURCE, OPENQUERY, OPENROWSET, OPENXML) and they are nondeterministic functions.

Here is an example for it.

1. -- Loading data from a text file
2. -- as single BLOB
3. SELECT * FROM
4. OPENROWSET(BULK N'D:\TestData.txt', SINGLE_BLOB) AS Document