C# & SQL Server - best way to delete multiple Rows in "one go" using a stored procedure

c#, performance, sql-server-2012, sql-server-2014

Solution

You could use table valued parameters to hand this. The application layer would look something like

C#

var tvp = new DataTable();
tvp.Columns.Add("Id", typeof(int));

foreach(var id in RecIdsToDelete)
    tvp.Rows.Add(new {id});

var connection = new SqlConnection("your connection string");

var delete = new SqlCommand("your stored procedure name", connection)
{
  CommandType = CommandType.StoredProcedure
};

delete
  .Parameters
  .AddWithValue("@ids", tvp)
  .SqlDbType = SqlDbType.Structured;

delete.ExecuteNonQuery();

SQL

IF NOT EXISTS(SELECT * FROM sys.table_types WHERE name = 'IDList')
BEGIN
    CREATE TYPE IDList AS TABLE(ID INTEGER)
END


CREATE PROCEDURE School.GroupStudentDelete
(                                         
        @IDS IDLIST READONLY      
)                                         
AS

SET NOCOUNT ON;

BEGIN TRY
        BEGIN TRANSACTION

        DECLARE @Results TABLE(id INTEGER)

        DELETE 
        FROM TblName 
        WHERE Id IN (SELECT ID FROM @IDS)        

        COMMIT TRANSACTION
END TRY
BEGIN CATCH
        PRINT ERROR_MESSAGE();

        ROLLBACK TRANSACTION
        THROW; -- Rethrow exception
END CATCH
GO

There are a number of advantages to this approach over building strings

- You avoid creating queries in the application layer, creating a seperation of concerns

- You can more easily test execution plans and optimize queries

- You are less vulnerable to SQL injection attacks, since your given approach would not be able to use a paramaterized query to build the IN clause

- The code is more readable and illustrative

- You do not end up building excessively long strings

Performance

There are some considerations about the performance of TVPs on large datasets.

Because TVPs are variables, they do not compile statistics. This means the query optimizer can fudge the execution plan sometimes. If this happens there a couple options :

- set `OPTION (RECOMPILE)` on any TVP statements where indexing is an issue

- write the TVP into a local temp and setup the indexing there

Here is a great article on TVP's with a good section on performance considerations, and what to expect when.

So if you are worried about hitting limits on string parameters, the table valued parameters might be the way to go. But in the end, it is hard to say without knowing more about the data set you are working with.

Problem

Original source