Is tables created in UDF thread safe?

29 Jul 2005 1:11 PM

Dave

1) Is the tables being created thread safe. If 2 users hit this function at
the same time, do they each get their own copy?
2) Is there a reason to use 2 tables - One temp and one to return?

Thanks,
Dave

Example from "CREATE FUNCTION" in Sql Server 2000 Help
==========================================================

CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
RETURNS @retFindReports TABLE (empid nchar(5) primary key,
   empname nvarchar(50) NOT NULL,
   mgrid nchar(5),
   title nvarchar(30))
/*Returns a result set that lists all the employees who report to given
employee directly or indirectly.*/
AS
BEGIN
   DECLARE @RowsAdded int
   -- table variable to hold accumulated results
   DECLARE @reports TABLE (empid nchar(5) primary key,
      empname nvarchar(50) NOT NULL,
      mgrid nchar(5),
      title nvarchar(30),
      processed tinyint default 0)
-- initialize @Reports with direct reports of the given employee
   INSERT @reports
   SELECT empid, empname, mgrid, title, 0
   FROM employees
   WHERE empid = @InEmpId
   SET @RowsAdded = @@rowcount
   -- While new employees were added in the previous iteration
   WHILE @RowsAdded > 0
   BEGIN
      /*Mark all employee records whose direct reports are going to be
   found in this iteration with processed=1.*/
      UPDATE @reports
      SET processed = 1
      WHERE processed = 0
      -- Insert employees who report to employees marked 1.
      INSERT @reports
      SELECT e.empid, e.empname, e.mgrid, e.title, 0
      FROM employees e, @reports r
      WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
      SET @RowsAdded = @@rowcount
      /*Mark all employee records whose direct reports have been found
   in this iteration.*/
      UPDATE @reports
      SET processed = 2
      WHERE processed = 1
   END

   -- copy to the result of the function the required columns
   INSERT @retFindReports
   SELECT empid, empname, mgrid, title
   FROM @reports
   RETURN
END
GO

29 Jul 2005 1:17 PM

Mike Epprecht (SQL MVP)

Hi

Yes, each Temporarty Table Variable as you defined it is only in the SPIDS
scope, other SPIDS will not see it.

Your UDF will not perform very well as UDF's are not designed for this. I
hope you are not using it on a table that is larger than a few rows. This
should be a SP, not a UDF.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quote

"Dave" wrote:

> 1) Is the tables being created thread safe. If 2 users hit this function at
> the same time, do they each get their own copy?
> 2) Is there a reason to use 2 tables - One temp and one to return?
>
> Thanks,
> Dave
>
>
> Example from "CREATE FUNCTION" in Sql Server 2000 Help
> ==========================================================
>
> CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
> RETURNS @retFindReports TABLE (empid nchar(5) primary key,
>    empname nvarchar(50) NOT NULL,
>    mgrid nchar(5),
>    title nvarchar(30))
> /*Returns a result set that lists all the employees who report to given
> employee directly or indirectly.*/
> AS
> BEGIN
>    DECLARE @RowsAdded int
>    -- table variable to hold accumulated results
>    DECLARE @reports TABLE (empid nchar(5) primary key,
>       empname nvarchar(50) NOT NULL,
>       mgrid nchar(5),
>       title nvarchar(30),
>       processed tinyint default 0)
> -- initialize @Reports with direct reports of the given employee
>    INSERT @reports
>    SELECT empid, empname, mgrid, title, 0
>    FROM employees
>    WHERE empid = @InEmpId
>    SET @RowsAdded = @@rowcount
>    -- While new employees were added in the previous iteration
>    WHILE @RowsAdded > 0
>    BEGIN
>       /*Mark all employee records whose direct reports are going to be
>    found in this iteration with processed=1.*/
>       UPDATE @reports
>       SET processed = 1
>       WHERE processed = 0
>       -- Insert employees who report to employees marked 1.
>       INSERT @reports
>       SELECT e.empid, e.empname, e.mgrid, e.title, 0
>       FROM employees e, @reports r
>       WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
>       SET @RowsAdded = @@rowcount
>       /*Mark all employee records whose direct reports have been found
>    in this iteration.*/
>       UPDATE @reports
>       SET processed = 2
>       WHERE processed = 1
>    END
>
>    -- copy to the result of the function the required columns
>    INSERT @retFindReports
>    SELECT empid, empname, mgrid, title
>    FROM @reports
>    RETURN
> END
> GO
>

30 Jul 2005 10:49 AM

Damien

Mike Epprecht (SQL MVP) wrote:
[snip]

>
> Your UDF will not perform very well as UDF's are not designed for this. I
> hope you are not using it on a table that is larger than a few rows. This
> should be a SP, not a UDF.
>

It's not really his fault though, since as he said, it's straight out
of BOL - I think he's just using it for the purposes of illustration.

Damien

29 Jul 2005 1:21 PM

Chandra

Hi

Here is the answer:

1:
as you have created table as a variable, the table will be thread safe.
Table will be safe and there is no collision when u create a table as a
variable or Temp Table

2:
U need not declare a return table, just specify RETURNS TABLE and
say
RETURN
SELECT empid, empname, mgrid, title
FROM @reports

please let me know if you have any questions

--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---------------------------------------

Show quote

"Dave" wrote:

> 1) Is the tables being created thread safe. If 2 users hit this function at
> the same time, do they each get their own copy?
> 2) Is there a reason to use 2 tables - One temp and one to return?
>
> Thanks,
> Dave
>
>
> Example from "CREATE FUNCTION" in Sql Server 2000 Help
> ==========================================================
>
> CREATE FUNCTION fn_FindReports (@InEmpId nchar(5))
> RETURNS @retFindReports TABLE (empid nchar(5) primary key,
>    empname nvarchar(50) NOT NULL,
>    mgrid nchar(5),
>    title nvarchar(30))
> /*Returns a result set that lists all the employees who report to given
> employee directly or indirectly.*/
> AS
> BEGIN
>    DECLARE @RowsAdded int
>    -- table variable to hold accumulated results
>    DECLARE @reports TABLE (empid nchar(5) primary key,
>       empname nvarchar(50) NOT NULL,
>       mgrid nchar(5),
>       title nvarchar(30),
>       processed tinyint default 0)
> -- initialize @Reports with direct reports of the given employee
>    INSERT @reports
>    SELECT empid, empname, mgrid, title, 0
>    FROM employees
>    WHERE empid = @InEmpId
>    SET @RowsAdded = @@rowcount
>    -- While new employees were added in the previous iteration
>    WHILE @RowsAdded > 0
>    BEGIN
>       /*Mark all employee records whose direct reports are going to be
>    found in this iteration with processed=1.*/
>       UPDATE @reports
>       SET processed = 1
>       WHERE processed = 0
>       -- Insert employees who report to employees marked 1.
>       INSERT @reports
>       SELECT e.empid, e.empname, e.mgrid, e.title, 0
>       FROM employees e, @reports r
>       WHERE e.mgrid=r.empid and e.mgrid <> e.empid and r.processed = 1
>       SET @RowsAdded = @@rowcount
>       /*Mark all employee records whose direct reports have been found
>    in this iteration.*/
>       UPDATE @reports
>       SET processed = 2
>       WHERE processed = 1
>    END
>
>    -- copy to the result of the function the required columns
>    INSERT @retFindReports
>    SELECT empid, empname, mgrid, title
>    FROM @reports
>    RETURN
> END
> GO
>

29 Jul 2005 1:36 PM

--CELKO--

You have mimiced 1950's style "scratch tapes" in temp tables, used
assembly language style bit flags and have a loop. That is about as
non-relational as you can get.

If I undrerstand this, you can replace the adjacency list model of the
organizational chart with a nested set model and avoid this kind of
horrible coding. Get a copy of TREES & HIERSARCHIES IN SQL if you need
details.