Couldn't get rows back in Azuer SQL Database

Question

Hi all,
I have one stored procedure in azure sql database, and there is one service principal created in the sql database. The service principal only be used in one application, and could not be used in SSMS.
When the stored procedure doesn't include the logic of temp table or table variable, it could get the rows back normally.
But when I define one table variable or temp table to store the rows of one physical table, then query the table variable/ temp table later for aggregation operation, it could not return the rows normally without any errors.
Anyone has any idea about it?

BTW, I tried to call the the SP with logic of temp table or table variable using SQL Server account in SSMS, it could get the rows back.

Answer 1

Use table variables only with small data sets or your stored procedure could get hanged during execution. Use temporary tables (CREATE TABLE #Table1) instead as they provide statistics to the database engine, they allow indexes that can support joining this temporary tables to other tables, they are more scalable.

You can also create temp tables as user tables in the database using a prefix tmp, instead of creating them on TempDB using table variables, but inserts/deletes/updates on this type of tables are logged on the database log and may execute slower.

Create appropriate indexes on your temporary tables and it should be fine.