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.
USE AdventureWorks2008R2
GO
CREATE PROCEDURE mySP (@ShiftID INT)
AS
SELECT [ShiftID] ,[Name] ,[StartTime] ,[EndTime] ,[ModifiedDate] FROM [HumanResources].[Shift] WHERE [ShiftID] = @ShiftID
GO
-- Executing Stored Procedure
EXEC mySP @ShiftID = 2
WITH RESULT SETS
(
(
[ShiftID] TINYINT
,[Name] NVARCHAR(50)
,[StartTime] DATETIME
,[EndTime] DATETIME
,[UpdateDate] DATETIME -- Notice Name Change
)
);
GO
-- Create TempTable and Inserting into it
CREATE TABLE #mySP
([ShiftID] TINYINT
,[Name] NVARCHAR(50)
,[StartTime] DATETIME
,[EndTime] DATETIME
,[UpdateDate] DATETIME)
INSERT INTO #mySP
EXEC mySP @ShiftID = 2;
SELECT *
FROM #mySP
GO
-- 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 (https://blog.sqlauthority.com)
4 Comments. Leave new
hi Pinal,
Thank you for providing the answer for my question by providing a detailed article. Thanks Pinal.
Is it possible (and how) to consume result table from sp (eg. JOIN)?
Refer this
hi,
how can i use with result set in c#.net ?