Re: Computed columns in temp tables

From: Jacco Schalkwijk (jacco.please.reply_at_to.newsgroups.mvps.org.invalid)
Date: 06/28/04 

Date: Mon, 28 Jun 2004 18:36:10 +0100

You can either add the function to the model database (on the basis of which
all databases are created) or you can create a start up stored procedure to
create the function in tempdb whenever SQL Server is started. The function
will not just disappear from tempdb once you have created it, only when SQL
Server is restarted, because tempdb gets recreated then. 

-- 
Jacco Schalkwijk
SQL Server MVP
"Steven Yampolsky" <syampolsky@eagleinvsys.com> wrote in message
news:ufdhcNTXEHA.1656@TK2MSFTNGP09.phx.gbl...
> Won't that function get dropped/cleaned up and will require constant
> checking for its existance?
>
> Steve
>
> "Roji. P. Thomas" <lazydragon@nowhere.com> wrote in message
> news:e5MzPCSXEHA.3016@tk2msftngp13.phx.gbl...
> > Create a permanent function in tempdb, like
> >
> > USE tempdb
> > GO
> >
> >
> > CREATE FUNCTION dbo.fnTest
> > (@x int, @y int)
> > RETURNS int
> > AS
> >  BEGIN
> >  RETURN (@x+@y)
> >  END
> > GO
> >
> > Now using another database u can call this function during temp table
> > creation
> >
> > eg.
> >
> >
> > USE Pubs
> >
> >
> > CREATE TABLE #temp(x int,y int, z as dbo.fnTest(x,y))
> >
> >
> > INSERT INTO #temp VALUES(1,2)
> > INSERT INTO #temp VALUES(3,2)
> >
> > SELECT * FROM #temp
> >
> >
> >
> > -- 
> > Roji. P. Thomas
> > SQL Server Programmer
> > "Steven Yampolsky" <syampolsky@eagleinvsys.com> wrote in message
> > news:eLwmCwRXEHA.2940@TK2MSFTNGP09.phx.gbl...
> > > How can I create a function in tempdb while inside a stored procedure?
I
> > > don't think I can do that.
> > >
> > > Lets assume I can use sp_executesql to create the function inside a
SP.
> > What
> > > will be a lifespan of it? Will it get dropped once the session is
> closed?
> > > will it be accessible from other sessions? Will there be a name
conflict
> > > when two sessions will try and execute the same SP?
> > >
> > > The more I get into it, the more questions I get. Hopefully I'll come
> out
> > > with good knowledge! :)
> > >
> > > Steve
> > >
> > > "Jacco Schalkwijk" <jacco.please.reply@to.newsgroups.mvps.org.invalid>
> > wrote
> > > in message news:uCXtqSrWEHA.2520@TK2MSFTNGP12.phx.gbl...
> > > > As Vyas pointed out, the issue is that you have to create the
function
> > in
> > > > tempdb. I think he is right in suggesting that you can't use UDFs
that
> > > > reside in a different database in the definition of a table.
> > > >
> > >
> > >
> >
> >
>
>

Relevant Pages

  • Re: Computed columns in temp tables
    ... create the function in tempdb whenever SQL Server is started. ... will not just disappear from tempdb once you have created it, ... >> Now using another database u can call this function during temp table ... >>> How can I create a function in tempdb while inside a stored procedure? ...
    (microsoft.public.sqlserver.datamining)
  • Re: Computed columns in temp tables
    ... create the function in tempdb whenever SQL Server is started. ... will not just disappear from tempdb once you have created it, ... >> Now using another database u can call this function during temp table ... >>> How can I create a function in tempdb while inside a stored procedure? ...
    (microsoft.public.sqlserver.programming)
  • Re: Can I move tempdb?
    ... Tempdb file name changes take affect after SQL Server is restarted and are ... reference posted by details procedures for moving other database files. ... Model isn't used to determine database file locations. ...
    (microsoft.public.sqlserver.server)
  • Re: using sp_ as a naming convention for stored procedures
    ... System stored procedures are created and stored in the master ... database and have the sp_ prefix. ... from any database without having to qualify the stored procedure name fully ... SQL Server always looks for a stored procedure ...
    (microsoft.public.sqlserver.programming)
  • Re: Checkpointing Not Happening in Simple Recovery Model
    ... You cannot set the recovery model in tempdb. ... Columnist, SQL Server Professional ... We would then have to issue an alter database ...
    (microsoft.public.sqlserver.server)