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.

How do I set the size of SqlParameter of SqlCommand for varchar(max)?

If you need to execute a Stored Procedure that implemented with varchar(max) parameter, using .Net Application, how do you set the size of the parameter?

Since the constructor of SqlParameter does not accept string value for size property, we have no way of passing max as the size;

// This will not compile
SqlParameter parameterNote = new SqlParameter("Note", SqlDbType.NVarChar, max);

But we can pass -1 as the size that indicates it is max;

SqlParameter parameterNote = new SqlParameter("Note", SqlDbType.NVarChar, -1);

Finding SQL Server Startup Stored Procedures

When I was discussing about getting Stored Procedures automatically executed at service start, a question raised; How can we find Stored Procedure added as Startup Procedures. This is simple, all we have to do is, query the sys.procedure system view and filter the result for ExecIsStartup Property.

Here is the code;

USE master;
GO

SELECT *
FROM sys.procedures
WHERE OBJECTPROPERTY(OBJECT_ID, 'ExecIsStartup') = 1;

For more info on how to add procedures as startup procedures using sp_procoption, read this: https://msdn.microsoft.com/en-us/library/ms181720.aspx

How to find all tables and columns used in the Stored Procedures?

As a part of modulerizing business logic, we create stored procedures and functions with SQL Server databases and in most cases, stored procedures are used. A stored procedure is named collection of TSQL statements that is created for implementing a business logic. 

When implementing a stored procedure, you will use one or more tables within it and later you may need to find out all tables along with columns that have been used with your specific stored procedures. Yes, you can open the stored procedure and see the code for finding them but what if you need to do it programmitically and not only one, for few stored procedures? Or what if you need to check and see a particular table has been used with your specific stored procedures? How can we find out these info, or in other words, how can we find out dependencies?

There are few ways of finding them using system views and functions given. We can use  sys.sql_expression_dependencies  view for finding objects used with a user-defined object and it can be used with stored procedures. However, since it returns one-row-per-one-object, you will not be able to get the column list of a table if you use this with a stored procedure. The dynamic management function that is sys.dm_sql_referenced_entities can be used as well. This gives more details on referenced objects. For an example, if you search for a stored procedure, it returns all tables along with column names used with the stored procedure.

Following code shows how to get the tables and columns used in dbo.uspGetBillOfMaterials stored procedure. As you see, sys.sql_expression_dependencies does not provide column details but sys.dm_sql_referenced_entities dynamic management function provides all the details we need.

USE AdventureWorks2014;
GO

SELECT * FROM sys.sql_expression_dependencies
WHERE referencing_id = OBJECT_ID(N'dbo.uspGetBillOfMaterials');  

SELECT * 
FROM sys.dm_sql_referenced_entities(N'dbo.uspGetBillOfMaterials', N'OBJECT');

In case of you need to find out whether the object is being used other objects, we have another dynamic management function named sys.dm_sql_referencing_entities. This returns details of all referencing objects.

SELECT * 
FROM sys.dm_sql_referencing_entities(N'Production.Product', N'OBJECT');

In case of you need to know how your object has been used by others, here is the way. This shows how to use sys.dm_sql_referencing_entities dynamic management function to see how the Production.Product table is used by other objects.

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!

"Failed to retrieve data for this request" "unknown property". (Microsoft.SqlServer.Management.Sdk.Sfc) : Microsoft SQL Server Management Studio

Are you getting this error when trying to retrieve stored procedures added to Azure V12 SQL database through Management Studio November 2015 Preview?

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

------------------------------

ADDITIONAL INFORMATION:

unknown property IsNativelyCompiled (Microsoft.SqlServer.Management.Sdk.Sfc)

I think that this is only related to SSMS November 2015 Preview because there is no issue with SQL Server 2012 Management Studio. However, if you want to use the latest, install December Preview 2015, it sorts out the issue.

With SSMS November Preview

With SQL Server 2012

With SSMS December 2015 Preview

Stored procedure parameters: Setting default values with functions

Stored procedures are common objects in SQL Server databases as it gives many benefits for us. One important thing with Stored Procedure is, allowing us to pass parameters for executing the procedure as we want. Parameters can be set as either input or output parameters and input parameters can be set with default values allowing users to execute the procedure either with value they need or without passing a value, letting procedure to use the default value. However, can we set up a function for the default value? That is the question I was asked.

SQL Server allows us to pass 2,100 parameters per Stored Procedure. Parameters can be set only with constant expressions, hence functions CANNOT be used when setting default values to parameters. But we can workaround it, though it is not the exact solution;

USE AdventureWorks2014;
GO

IF OBJECT_ID('dbo.GetOrders', 'P') IS NOT NULL
 DROP PROCEDURE dbo.GetOrders;
GO

-- creating the procedure
-- note that default value is null
-- and set the default value if no value found
CREATE PROCEDURE dbo.GetOrders @Year int = NULL
AS
BEGIN

 SET @Year = ISNULL(@Year, YEAR(getdate()));

 print @Year
 SELECT * FROM Sales.SalesOrderHeader
 WHERE YEAR(OrderDate) = @Year;
END
GO

-- EXEC dbo.GetOrders

I want an execution plan optimized for my value, not for the value passed for parameters with first execution.

Since I got few questions on the posts written on stored procedure in last two weeks, thought to make few more posts on the same based on questions asked. One question asked was Can we make sure that the stored procedure is optimized for one range of values, not for exceptions. If we need to get the stored procedure optimized for one set of values, then instruction has to be passed during the creation. We should only do this if we know that there are different best plans for different values.

I have written a post called How to determine whether RECOMPILE is required? If so, at the creation or execution? and it shows the way of seeing whether different best plans are exist for different values. Have a look on it if you want to know the way.

Let's see how we can optimize a procedure for our values, not for values passed with first execution. Below code creates a stored procedure, an index on OrderDate. 

-- Clearing the cache  
DBCC FREEPROCCACHE  
GO  
  
USE AdventureWorks2014;  
GO  
  
-- Drop if it is exist  
IF OBJECT_ID('dbo.GetOrderDetails', 'P') IS NOT NULL  
 DROP PROC dbo.GetOrderDetails;  
GO  
  
-- Create the procedure  
CREATE PROC dbo.GetOrderDetails @FromDate datetime, @ToDate datetime  
AS  
    SELECT h.* 
 FROM Sales.SalesOrderHeader h
 WHERE h.OrderDate BETWEEN @FromDate AND @ToDate
 ORDER BY h.OrderDate
GO  

-- Create an index on OrderDate
CREATE INDEX IX_SalesOrderHeader_OrderDate ON Sales.SalesOrderHeader (OrderDate DESC)
GO

Nest code executes the stored procedure with three different values. As you see, WITH RECOMPILE has been added to see whether same plan or different plans are used for the execution.

EXEC dbo.GetOrderDetails '2014-06-29T00:00:00', '2014-06-30T00:00:00' WITH RECOMPILE;  
EXEC dbo.GetOrderDetails '2014-06-26T00:00:00', '2014-06-30T00:00:00' WITH RECOMPILE;  
EXEC dbo.GetOrderDetails '2014-06-01T00:00:00', '2014-06-30T00:00:00' WITH RECOMPILE;  
GO

Here are the plans generated.

As you see, plan is same for first two executions, and a different plan is for third execution. The reason for this is, first and second one try to get orders within few number of days and third one is for many days, hence newly added index is used for first and second one with seek operation and clustered index is used for third one with scanning. 

Assume that most requests made on this procedure are based on small number of days and we need to optimize it for small number of days. If so, we need to alter the procedure instructing that create a plan optimized for a sample range that contains small number of days. If we do not do it and the first execution is based on large number of days, plan will be created for it and the consecutive executions will use the plan created even for small number of days which is not the optimized plan for them.

Okay, how can we instruct then? Here is the way.

-- Alter the procedure  
ALTER PROC dbo.GetOrderDetails @FromDate datetime, @ToDate datetime  
AS  
    SELECT h.* 
 FROM Sales.SalesOrderHeader h
 WHERE h.OrderDate BETWEEN @FromDate AND @ToDate
 ORDER BY h.OrderDate
 OPTION (OPTIMIZE FOR (@FromDate = '2014-06-29T00:00:00', @ToDate = '2014-06-30T00:00:00'))
GO 

-- Execute with a large range and see
EXEC dbo.GetOrderDetails '2014-01-01T00:00:00', '2014-06-30T00:00:00';  
GO

Here is the plan;

I have used OPTIMIZE FOR hint by passing two values for parameters. Note that values are set for a small range and this instructs engine to create the plan based on these values without considering the values submitted with executions. As you see, when the procedure is executed even with a wide range, still engine uses the plan optimized for small range.

Read more on this hint at: https://msdn.microsoft.com/en-us/library/ms181714.aspx.

How to determine whether RECOMPILE is required? If so, at the creation or execution?

When we know that the parameters we pass for stored procedures are widely vary, we insist engine to recompile the procedure. But it does not mean that we have to recompile just because of the values are vary, we need make sure that there are different best plans for these values because there can be an instance where we see just a single plan best for all values. Therefore we need a way of seeing that there are different best plans for different values.

What is the simplest way? Easiest way is, execute the stored procedure with multiple different values with RECOMPILE option, enabling Actual Execution Plan. This tells us whether engines makes different plans for different execution indicating whether we need to have RECOMPILE option.

Here is the way;

-- Clearing the cache
DBCC FREEPROCCACHE
GO

USE AdventureWorks2014;
GO

-- Drop if it is exist
IF OBJECT_ID('dbo.GetOrderDetails', 'P') IS NOT NULL
 DROP PROC dbo.GetOrderDetails;
GO

-- Create the procedure
CREATE PROC dbo.GetOrderDetails @Number int
AS
    SELECT * FROM Sales.SalesOrderDetail 
    WHERE SalesOrderDetailID <= @Number
GO

-- Enable "Include Actual Execution Plan"
-- and execute all together
EXEC dbo.GetOrderDetails 1 WITH RECOMPILE;
EXEC dbo.GetOrderDetails 10000 WITH RECOMPILE;
EXEC dbo.GetOrderDetails 10 WITH RECOMPILE;
EXEC dbo.GetOrderDetails 1000 WITH RECOMPILE;
EXEC dbo.GetOrderDetails 100 WITH RECOMPILE;
GO

Let's have a look on plans generated;

Since this is a simple stored procedure, we do not see many different plans for values passed. But if you see many different plans and procedure is always executed with values that are vary, it indicates that we need to add RECOMPILE at the creation.

If you see a common plan with 80-90% of sample executions and see one or two different plans only few few executions, it indicates that RECOMPILE is required at the execution only when passing values similar to those executions.

EXECUTE....... WITH RECOMPILE: Does it discard the previously cached plan?

Not an advance theory, everyone knows that first execution of the stored procedure creates the best plan for the execution and keeps it in the memory for the next execution. The plan is created based on the values passed to parameters with its first execution. This behavior is called as Parameter Sniffing and it is considered as a good thing as long as the cached plan is still good for parameter values passed with the consecutive execution. This behavior becomes bad when a completely odd value is passed for the parameter or the value of the parameter is vary with initial value. In a situation like this, we consider that the cached plan is not the best for this execution, hence we force SQL Server engine to recompile the plan with new values.

Forcing is done with RECOMPILE option. This option can be added to the stored procedure at its creation, resulting recompilation with all executions or can be added to the execute statement, resulting recompilation only with the current execution. Now the question is, when we force to recompile the stored procedure and generate a new plan by adding RECOMPILE option, does it discard the previously cached plan? Does it keep the new plan for next execution?

Let's test this. Following code adds a stored procedure and executes it three times. Then it checks cache.

-- Clearing the cache
DBCC FREEPROCCACHE
GO

USE AdventureWorks2014;
GO

-- Drop if it is exist
IF OBJECT_ID('dbo.GetOrderDetails', 'P') IS NOT NULL
 DROP PROC dbo.GetOrderDetails;
GO

-- Create the procedure
CREATE PROC dbo.GetOrderDetails @Number int
AS
    SELECT * FROM Sales.SalesOrderDetail 
    WHERE SalesOrderDetailID <= @Number
GO

-- Execute three times
EXEC dbo.GetOrderDetails 1;
GO 3

-- Seeing the cache
SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE p.usecounts > 0 
 AND TEXT LIKE '%Sales.SalesOrderDetail%'
 AND TEXT NOT LIKE '%dm_exec_cached_plans%'
ORDER BY p.usecounts DESC;

As you see, plan has been created with its first execution and it has been used three times. Now let's try to use a different plan for executing the procedure. Let's force engine to recompile and check the cache again.

-- Execute with RECOMPILE
EXEC dbo.GetOrderDetails 1000 WITH RECOMPILE;
GO 

-- Checking the cache
SELECT p.usecounts, p.cacheobjtype, p.objtype, t.text
FROM sys.dm_exec_cached_plans p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) t
WHERE p.usecounts > 0 
 AND TEXT LIKE '%Sales.SalesOrderDetail%'
 AND TEXT NOT LIKE '%dm_exec_cached_plans%'
ORDER BY p.usecounts DESC;

This shows the same plan and no new plan. What does it mean? If a new plan is created, why cannot we see it? If no new plan is created, has the old plan been used? If so, why we do not see an increase on usercounts for the plan? Answer is simple. RECOMPILE creates a new plan based on passed values but it does not keep it and it does not remove the old one from the cache. The newly created plan is only for this execution.

How to check parameters values used for compiling the stored procedure?

It is a known fact that the initial plan of the stored procedure is created with its first execution. SQL Server engines creates the plan based on the values passed for parameters with stored procedure's first execution and places the plan in the cache. This plan will be reused with consecutive executions regardless of the values passed to parameters. This behavior is called as Parameter Sniffing, and it helps to improve the overall performance of the stored procedure's execution as well as downgrade the performance of it. For more info on this, read one of my old post: Do we need to recompile Stored Procedures when an index is added or parameter value is vary greatly?

With consecutive executions, if you want to see the values that have been used for compiling the procedure, or in other words, parameters values used for the plan in the cache, what is the easiest way of seeing it? Simplest way is, get the Properties of last component in the plan and see Parameter List property. Here is the way of doing it;

First, let's create a procedure and execute once.

USE AdventureWorks2014;
GO

-- Drop if it is exist
IF OBJECT_ID('dbo.GetOrderDetails', 'P') IS NOT NULL
 DROP PROC dbo.GetOrderDetails;
GO

-- Create the procedure
CREATE PROC dbo.GetOrderDetails @Number int
AS
    SELECT * FROM Sales.SalesOrderDetail 
    WHERE SalesOrderDetailID <= @Number
GO

-- Execute once. This adds the plan which is optimized for 
-- parameter value 1.
EXEC dbo.GetOrderDetails 1;

Now let's execute it with a different value enabling Include Actual Execution Plan.

Open the Execution Plan tab and click on SELECT.

Properties window can be opened by either pressing F4 or Right-Clicking and Selecting Properties. Once it is opened, You will see property called Parameter List and it has the values used for compiling the stored procedure and values used for current execution.

Is this useful? Yes, if you are planning to recompile or want to know whether the plan is optimized for new executions, this holds some of required information you need on it.

Object Name Resolution - Stored Procedure

SQL Server does not resolve names of stored procedures just as it does on tables. This resolution is bit different. Here is the post I made on Object Name Resolution - Table: http://dinesql.blogspot.com/2015/04/object-name-resolution-table.html. Let;s see how this happens with Stored Procedure.

Have a look on the image below;

As per the example that shows in the image, Jane executes GetOrders stored procedure without adding its schema. In order to resolve this name, SQL Server initially adds sys schema. Since it does not success, it adds user's schema which is HR. As the stored procedure has been created under HR, Jane successfully executes the query.

Jack executes the same and SQL Server tries with sys.GetOrders. Since it is unsuccessful, SQL Server adds his schema which is FN. Again SQL Server cannot resolve an object called FN.GetOrders. SQL Server tries again with different schema which is dbo. That is the last try, and as it gives the same unsuccessful result, Jack gets an error.

Just like table names, makes sure you add schema to stored procedures when executing them. This makes sure that SQL Serve does not need to spend extra time for executing it and avoids overhead and errors.