CODEWITHSUNDEEP
sql-server
naz
naz

Reputation: 2072

Table valued function in SQL Server; the table in the function is temporary?

For the table valued functions in SQL Server, when one executes something like the following, it only creates the temporary table @Table, just like with the variables (in scalar-valued functions), correct? Would any issues arise if many such statements were to be executed?

CREATE FUNCTION some_function
     (@some_var varchar(20), @someDate date)
RETURNS @Table Table (id int, name varchar(50), DoB date)
AS
BEGIN
    INSERT INTO @Table
        SELECT id, Name, CAST(DoB as date) 
        FROM tblEmployees

    RETURN
END

Upvotes: 1

Views: 114

Answers (1)

IVNSTN
IVNSTN

Reputation: 9325

Would any issues arise if many such statements were to be executed?

No, that is not possible:

The scope of a local variable is the batch in which it is declared.

Table variable's scope is same as scalar variable's.

About table variable and temp table, briefly:

The scope of a table variable is limited to the specific batch, while a local temporary table is limited to the specific spid.

Which means even if you are running same code from several threads - they will not conflict on # table names, since each thread will have own spid. Different users (different connections) cannot conflict nor on table variable names (not possible at all) neither on temp table names (not possible against different connections).

Only conflicts on ## (global temp tables) and # within same connection (e.g. nested procedures trying to create temp table with same name) are possible.

Upvotes: 1

Related Questions