Out of Memory Oddity

This function (well, a similar non obfuscated one) caused me issues today.

CREATE FUNCTION [dbo].[history] (@id INT) 
RETURNS @history TABLE  (row_num INT IDENTITY(0,1)
            ,history_id AS id + '|' + CONVERT(VarChar(20), row_num)
          ,history_title  VARCHAR(50))
AS 
BEGIN 

INSERT INTO @history(history_id,history_title)

SELECT a.value1 AS history_id
      ,a.title  AS history_title
FROM  mytable a
WHERE a.id = @id

RETURN

END

GO

It would throuw 'Out of memory' errors on SQL 2008. How could one function achieve this on a box with 8GB of memory? After a great deal of investigation, the below solution turned out to be the fix. The alculated column in table definition of the orignal was the issue so my reolution was to use 2 table variables. The fix had to work for clients running SQL versions 2000 - 2008.

CREATE FUNCTION [dbo].[history] (@id INT) 
RETURNS @history TABLE  (row_num  INT 
            ,history_id VARCHAR(50)
          ,history_title  VARCHAR(50))
AS 

DECLARE @histtemp TABLE (row_num  INT IDENTITY(0,1)
            ,history_id AS id + '|' + CONVERT(VarChar(20), row_num)
          ,history_title  VARCHAR(50))


INSERT INTO @histtemp (history_id,history_title)
SELECT a.value1 AS history_id
      ,a.title  AS history_title
FROM   mytable a
WHERE a.id = @id

INSERT INTO @history(row_num, History_id, history_id) 
SELECT row_num, History_id, history_id
FROM @histtemp 

RETURN

END

GO