Earlier I posted article SQL SERVER – Denali – Executing Stored Procedure with Result Sets. After reading this SQL Expert Ramdas asked following and very interesting question:
This is a nice feature and i am sure would be used a lot. How is the performance of this as compared with using temp tables?
I really loved this question, I ran the following code and measured the performance difference using execution plans.
CREATE PROCEDURE mySP (@ShiftID INT)
SELECT [ShiftID] ,[Name] ,[StartTime] ,[EndTime] ,[ModifiedDate] FROM [HumanResources].[Shift] WHERE [ShiftID] = @ShiftID
-- Executing Stored Procedure
EXEC mySP @ShiftID = 2
WITH RESULT SETS
,[UpdateDate] DATETIME -- Notice Name Change
-- Create TempTable and Inserting into it
CREATE TABLE #mySP
INSERT INTO #mySP
EXEC mySP @ShiftID = 2;
-- Clean up
DROP TABLE #mySP;
DROP PROCEDURE mySP;
Let us examine the results.
From the resultset it is very clear that new method takes only 14% where as compared to it the old method which has cost of 84%. I love the new method in ‘Denali’. Thanks Ramdas for interesting question.
Reference: Pinal Dave (http://blog.SQLAuthority.com)