Is tables created in UDF thread safe?

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
GOHi
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/
"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
>|||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/
---
"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
>|||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.|||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

Is My Backup Plan Safe?

I need to setup an online backup mechanism which allows restoration of data
up to point of failure in the event of DB file corruption or hard disk drive
failure. The current DB size is ~ 100M with a growth rate of ~15M/month for
the last 6 months. I'm thinking of :
1. Create a scheduled daily full backup from c drive to d drive
2. Create a scheduled hourly transaction log backup from c drive to d drive
3. Create a scheduled weekly full backup of backup folder in d drive to tape
drive with Windows backup utility.
4. Create scheduled daily differential backup of backup folder in d drive to
tape drive with Windows backup utility.
5. Two tapes will be rotated every alternate week for backup to tapes. Tape
not in used will be taken off-site.
Is the above plan good enough for the recovery scenario I want to address?Ong
Yep it looks pretty good. But remember that everything you did so far has
no value if you cannot restore the database. Verify that backups are not
corrupted and you can easily restore the database
"Ong" <Ong@.discussions.microsoft.com> wrote in message
news:68837F91-E41F-4487-B673-9316EE9D9037@.microsoft.com...
>I need to setup an online backup mechanism which allows restoration of data
> up to point of failure in the event of DB file corruption or hard disk
> drive
> failure. The current DB size is ~ 100M with a growth rate of ~15M/month
> for
> the last 6 months. I'm thinking of :
> 1. Create a scheduled daily full backup from c drive to d drive
> 2. Create a scheduled hourly transaction log backup from c drive to d
> drive
> 3. Create a scheduled weekly full backup of backup folder in d drive to
> tape
> drive with Windows backup utility.
> 4. Create scheduled daily differential backup of backup folder in d drive
> to
> tape drive with Windows backup utility.
> 5. Two tapes will be rotated every alternate week for backup to tapes.
> Tape
> not in used will be taken off-site.
> Is the above plan good enough for the recovery scenario I want to address?|||Thank you for your comment & precaution on backup verification.
"Uri Dimant" wrote:
> Ong
> Yep it looks pretty good. But remember that everything you did so far has
> no value if you cannot restore the database. Verify that backups are not
> corrupted and you can easily restore the database
> "Ong" <Ong@.discussions.microsoft.com> wrote in message
> news:68837F91-E41F-4487-B673-9316EE9D9037@.microsoft.com...
> >I need to setup an online backup mechanism which allows restoration of data
> > up to point of failure in the event of DB file corruption or hard disk
> > drive
> > failure. The current DB size is ~ 100M with a growth rate of ~15M/month
> > for
> > the last 6 months. I'm thinking of :
> > 1. Create a scheduled daily full backup from c drive to d drive
> > 2. Create a scheduled hourly transaction log backup from c drive to d
> > drive
> > 3. Create a scheduled weekly full backup of backup folder in d drive to
> > tape
> > drive with Windows backup utility.
> > 4. Create scheduled daily differential backup of backup folder in d drive
> > to
> > tape drive with Windows backup utility.
> > 5. Two tapes will be rotated every alternate week for backup to tapes.
> > Tape
> > not in used will be taken off-site.
> >
> > Is the above plan good enough for the recovery scenario I want to address?
>
>|||Is the C and D drive the same physical drive? Then you could lose you
database drive and your backup drive.
If C and D are separate physical drives are they on the same disk
controller? If the controller fails then you won't be able to access your
database and backups until you replace the controller or put the drive in
another box..
Imagine your server and tape drive are stolen. It sounds like you would have
to go to tape and possibly lose a week of transactions.
I would backup across the network or directly to the tape drive or get a
separate external hard drive. If your only moving data offsite once a week
get a fireproof safe.
I don't think your backup plan is safe and I don't believe you will be able
to restore to the point of failure if say your building burns down.
"Ong" <Ong@.discussions.microsoft.com> wrote in message
news:68837F91-E41F-4487-B673-9316EE9D9037@.microsoft.com...
> I need to setup an online backup mechanism which allows restoration of
data
> up to point of failure in the event of DB file corruption or hard disk
drive
> failure. The current DB size is ~ 100M with a growth rate of ~15M/month
for
> the last 6 months. I'm thinking of :
> 1. Create a scheduled daily full backup from c drive to d drive
> 2. Create a scheduled hourly transaction log backup from c drive to d
drive
> 3. Create a scheduled weekly full backup of backup folder in d drive to
tape
> drive with Windows backup utility.
> 4. Create scheduled daily differential backup of backup folder in d drive
to
> tape drive with Windows backup utility.
> 5. Two tapes will be rotated every alternate week for backup to tapes.
Tape
> not in used will be taken off-site.
> Is the above plan good enough for the recovery scenario I want to address?|||Terri ,
Thanks for your important input.
I didn't check this thread for a few days & only saw your reply today.
I hope you can clarify my questions below:
"Terri" wrote:
> Is the C and D drive the same physical drive? Then you could lose you
> database drive and your backup drive.
> If C and D are separate physical drives are they on the same disk
> controller? If the controller fails then you won't be able to access your
> database and backups until you replace the controller or put the drive in
> another box..
FYI, D is an external usb drive
> Imagine your server and tape drive are stolen. It sounds like you would have
> to go to tape and possibly lose a week of transactions.
> I would backup across the network or directly to the tape drive or get a
> separate external hard drive.
Will backup across the network be slow and cause network jam?
>If your only moving data offsite once a week
> get a fireproof safe.
Do you mean to put the tape in a fireproof safe in the office or offsite?
> I don't think your backup plan is safe and I don't believe you will be able
> to restore to the point of failure if say your building burns down.
Sounds like for solution with tape, the only safe way is to use 7 (or 5
excluding weekends) backup tapes to do full db backup daily and transaction
logs backup hourly and keep all the unused tapes offsite.