Cannot use Temp Table in SQL server function, why?

Question

Can we use table variable in SQL server function, why?

Mayank · Answer

Hi Pravat, Sanwar Yes we are not allowed to do DML in temp table. But we are allowed to do the DML in temp variable. As per my knowledge temp table and temp variable both have columns and rows. Why DML is allowed in temp variable then?

Jignesh Kumar · Answer

Hi Mayank, I want to point out one thing here, We all know DML we can not perform inside function which is limitation of function. When we try to create #tempTable and try to insert records in temp table it means it is DML operation.

Pravat Kumar · Answer

Hello Mayank, This is the limitation of the user-defined function. the objects can't be created inside the user-defined function. the user-defined function can't execute the dynamic query or temporary tables.

Mayank · Answer

Hi Pravat, Sanwar I know I can't use temp table in udf in sql server.I want to know the reason. Why we are able to use table variable?

Pravat Kumar · Answer

Hello Mayank, you can't use the temp table in a user-defined function. But you can use the table variables or CTE in the user-defined function.

Sanwar Ranwa · Answer

Hello You can use user defined table type to solve your problem.

Cannot use Temp Table in SQL server function, why?

Answers (6)