A late response to the original post,

Assigning a Primary Key to a table variable can be beneficial and harmful. How it is use, the results may vary.

I have previous experience where Primary Keys on a column for table variables can be beneficial. This was used for counting orders activated/assigned/created/completed/cancelled between time intervals.

Brief Summary:

I had used a "Tally" or "Numbers" table variable. (from a SQL CookBook) I also created a "Temp" table variable to stored the calculated time intervals, example every 5 minutes for the last 7 days. Updating the "Temp" table variable with data gathered from our "Orders" table. In my case, I created an index on the "Temp" table variable by using Primary Key on "StartTime" and "EndTime." For a result-set of 2880 rows, it took less than 1 second. For testing purposes, I removed the primary key declarations and I basically had to cancel the query after 5 seconds. Unfortunately, in my case, without the use of primary keys, my code took roughly 7-10 seconds to return the 2880 rows.

My Sample Code:

DECLARE @EndDate DateTime

DECLARE @NumDaysAgo INT

DECLARE @MinsApart INT

SET @EndDate = '09/01/2007'

SET @NumDaysAgo=10

SET @MinsApart=5

declare @Temp table

(

ID INT IDENTITY,

StartTime Datetime,

EndTime DateTime,

OrdersCreated INT,

OrdersActivated INT,

OrdersAssigned INT,

OrdersCancelled INT,

OrdersCompleted INT

,

PRIMARY KEY

(

StartTime ASC,

EndTime ASC

)

)

DECLARE @Numbers TABLE

(

ID INT PRIMARY KEY

)

DECLARE @Helper TABLE

(

ID INT

)

INSERT INTO @Helper

SELECT ID FROM

((SELECT 1 AS ID)

UNION

(SELECT 2 AS ID)

UNION

(SELECT 3 AS ID)

UNION

(SELECT 4 AS ID)

UNION

(SELECT 5 AS ID)

UNION

(SELECT 6 AS ID)

UNION

(SELECT 7 AS ID)

UNION

(SELECT 8 AS ID)

UNION

(SELECT 9 AS ID)

UNION

(SELECT 10 AS ID)

) AS A

INSERT INTO @Numbers

SELECT (1000*(Thousands.ID-1)) + (100*(Hundreds.ID-1)) + (10*(Tens.ID-1)) + (Ones.ID)

From @Helper Thousands,@Helper Hundreds,@Helper Tens, @Helper Ones

Order by Thousands.ID ASC, Hundreds.ID ASC, Tens.ID ASC, Ones.ID

SET @EndDate = datediff(d,@NumDaysAgo-1,@EndDate)

INSERT INTO @Temp(StartTime, EndTime)

SELECT DATEADD(mi,@MinsApart*(n.ID-1),@EndDate),DATEADD(mi,@MinsApart*n.ID,@EndDate)

FROM @Numbers n

WHERE n.ID<CONVERT(INT,(@NumDaysAgo*24.0*60/@MinsApart) + 1)

-- Note: For the purpose of this posting, the "modified" code below is not the original.

-- The original contains 4 individual/distinct update sets for each type of count.

UPDATE t

SET OrdersCreated = Helper.HelpCount

FROM @Temp t

INNER JOIN

(SELECT t2.ID, COUNT(o.OrderID) AS helpCount

FROM @Temp t2, dbo.Order o

WHERE o.Created BETWEEN t2.StartTime AND t2.EndTime

GROUP BY t2.ID

) AS Helper

ON t.ID = Helper.ID

-- ....

-- Update other counts

--...

SELECT StartTime, EndTime

, ISNULL(OrdersCreated,0) AS Created

, ISNULL(OrdersActivated,0) AS Activated

, ISNULL(OrdersAssigned,0) AS Assigned

, ISNULL(OrdersCancelled,0) AS Cancelled

, ISNULL(OrdersCompleted,0) AS Completed

FROM @Temp

For case of the original post, since each row from the table variable is being processed one at a time, the Primary Key will not be beneficial in improving performance.

However, I would suggest checking the size of the tempdb(s) in for the Production environment to see if there was enough space allocated at the time when the Primary Key was present in the table variable. Just my assumptions, but the tempdb(s) may not have been configured with the appropriate space allocation. Usually, Production environments have larger tempdb(s) primarily because there are more activities than Development and/or Beta environments.

Regarding performance of the stored procedure, you may need to add additional logic to "the WHILE Loop" such as find a way to do it "SET-based" oriented which will be beneficial.

Regards,

Wameng Vang