Hi All, Recently I had been asked the **difference between temporary tables and table variable**. then one point I mentioned that only table variable can be used in the user defined functions but not the temporary tables. But interviewer cross questioned me that why it is so. Both the table variable and temporary tables are created in the temporary database and created as an object then why there is a partiality in user defined functions that one can be used in the same and not the other one? Can anyone have good explaination on the same. Thanks in advance Ammy
**from BOL : Table Valued Parameters** **Benefits** Table-valued parameters offer more flexibility and in some cases better performance than temporary tables or other ways to pass a list of parameters. Table-valued parameters offer the following benefits: - Do not acquire locks for the initial -population of data from a client. -Provide a simple programming model. - Enable you to include complex -business logic in a single routine. -Reduce round trips to the server. - Can have a table structure of different - cardinality. Are strongly typed. -Enable the client to specify sort - order and unique keys. **Restrictions** Table-valued parameters have the following restrictions: - SQL Server does not maintain statistics on columns of table-valued parameters. - Table-valued parameters must be passed as input READONLY parameters to Transact-SQL routines. - You cannot perform DML operations such as UPDATE, DELETE, or INSERT on a table-valued parameter in the body of a routine. - You cannot use a table-valued parameter as target of a SELECT INTO or INSERT EXEC statement. A table-valued parameter can be in the FROM clause of SELECT INTO or in the INSERT EXEC string or stored-procedure. **Scope** A table-valued parameter is scoped to the stored procedure, function or dynamic Transact-SQL text, exactly like other parameters. Similarly, a variable of table type has scope like any other local variable that is created by using a DECLARE statement. You can declare table-valued variables within dynamic Transact-SQL statements and pass these variables as table-valued parameters to stored procedures and functions I advise that you also take a look at table valued functions in BOL :ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/277f03c0-d27b-4654-b357-bdee1875d11c.htm
The two big points are that user defined functions don't allow data definition language, and if we're talking about multi-statement table valued user defined functions, they don't statistics. If you're talking about creating a table, even a temporary table, you're violating the first rule. If you're trying to create a temporary table in the multi-statement udf, then you're violating the second. That's why they won't be allowed within a function.