0

I am writing a stored procedure, main purpose of it will be the insertion into table
Procedure will be signed by certificate, and sending email notifications

Procedure will accept user-defined table-variable as a parameter, and insert it's contents into persistent table

Below couple of questions:

  1. Can insertion into persistent table cause Table lock escalation on that table?
    Does it make any sense to the limit number of rows allowed to be inserted per 1 procedure execution to certain limit? say 100,200,500 rows etc. - to avoid lock_escalation event ?

  2. Temp tables have some benefit comparing to table variables (like statistics).
    Inside stored procedure, does it make any sense of first inserting contents of user-defined table-variable into #temp table, and then into target table ?
    Or we can skip #temp table and insert into persistent table, from table-variable directly and there won't be any performance drawbacks ?

  3. What locks are taken during insert ? And are there any other performance considerations for inserts ?

update: added simplified procedure code:

create procedure InsertIntoTable (@_TableData TableData readonly)
as begin

-- lets use intermediate temp table
drop table if exists #InsertIntoTable

create table #InsertIntoTable (
    column1             bigint,
    column2             varchar(50),
    column3             smallint,
    column4             datetime,
    column5             tinyint,
    column6             datetime,
    column7             varchar(500)
)

-- insert from table-variable into temp table
insert into #InsertIntoTable (column1, column2, column3, column4, column5, column6, column7)
select 
    column1, 
    column2, 
    column3, 
    column4, 
    column5, 
    column6, 
    column7
from @_TableData

-- let's do the actual insert
insert into Database..Table (column1, column2, column3, column4, column5, column6, column7)
select 
    column1, 
    column2, 
    column3, 
    column4, 
    column5, 
    column6, 
    column7
from #InsertIntoTable 

end
2
  • Could you possibly generate some pseudo-code for the stored procedure? Or expand on your short explanation. e.g. will the users be assigning data to various/different tables? Is the procedure dynamic? Thanks.
    – John K. N.
    Commented Dec 17, 2021 at 9:13
  • 1
    @JohnK.N. added procedure's code to the question. Users will insert data into table variable first, and then supply that variable as parameter when executing stored procedure. Procedure will do insertion into just 1 table. Procedure will not use any dynamic sql if that is what you meant Commented Dec 17, 2021 at 14:26

1 Answer 1

0

Since something needs to first populate the table variable that is then passed to the stored procedure, unless the procedure is doing more than what you posted, why not populate the table directly? Skipping the use of the table variable and sproc?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.