Computed columns in temp tables

I am having a problem with using UDF as part of a temp table computed column. Here's the sample code:
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name = 'fn_test')
DROP FUNCTION dbo.fn_testGO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO

CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
I receive the following error:

Server: Msg 208, Level 16, State 1, Line 2
Invalid object name 'dbo.fn_test'.

I do not get this error if I use a regular table.
HELP!The function you are creating resides in your user database, where as the temporay table is actually created in tempdb database, where your function does not exist.

To test this out I created the function in tempdb and then went into my user database (ex. testdb) and created the temporay table using your script and it worked fine.

The problem with creating a function in tempdb is that the next time SQL Server stops and then restarts the function will be gone. The way around this is to also create the function in model database, since each time SQL Server starts up it takes a copy of model to create tempdb.|||Can you post your script please. I have tried creating the function in the master database and then use it in the temp table creation like this:

z AS (master.dbo.fn_test(x,y))

and still got the error. :confused:|||Let's assume that your user database is named foo for the purpose of this posting. When you create a function named fn_test in that database, the three part name for that function is foo.dbo.fn_test. Within foo, you can refer to it as dbo.fn_test, but in other databases on the server you'll need to use the three part name, or foo.dbo.fn_test.

Since a temp table resides in tempdb, it is in another database (not really in foo at all). Because of that in order for your computed column to work properly, I think that you'll have to use:CREATE TABLE #X
(
x INT,
y INT,
z AS (foo.dbo.fn_test(x,y))
)-PatP|||I did just that and still got the same error. Previous poster mentioned that he was able to do it but haven't posted his version of the script.

Steve

Let's assume that your user database is named foo for the purpose of this posting. When you create a function named fn_test in that database, the three part name for that function is foo.dbo.fn_test. Within foo, you can refer to it as dbo.fn_test, but in other databases on the server you'll need to use the three part name, or foo.dbo.fn_test.

Since a temp table resides in tempdb, it is in another database (not really in foo at all). Because of that in order for your computed column to work properly, I think that you'll have to use:CREATE TABLE #X
(
x INT,
y INT,
z AS (foo.dbo.fn_test(x,y))
)-PatP|||I have a database called Test, where I'm creating the temporay table.

set nocount on
go
use tempdb
go
IF EXISTS( SELECT 1 FROM information_schema.routines WHERE routine_name = 'fn_test')
DROP FUNCTION dbo.fn_test
GO
CREATE FUNCTION dbo.fn_test( @.x int, @.y int)
RETURNS INT AS
BEGIN
DECLARE @.z INT
SET @.z = @.x + @.y
RETURN @.z
END
GO

use test
go
CREATE TABLE #X
(
x INT,
y INT,
z AS (dbo.fn_test(x,y))
)
go
insert #x values (5,6)
go
select * from #x
go
drop table #xsqlsql