Delete query not deleting data

I am running a web service which connects to a SQL Server 2012 database. When executing multiple delete queries within a short space of time (around 5 in a second), it appears random which ones are actually executed. When ran one at a time or putting a 0.5 second delay between each execution, they run perfectly.

Looking at the SQL profiler, all the query are showing as RPC:Completed even if they haven't actually deleted the row in the table. I checked the table and the data was still there, then copied and pasted the query from the Profiler into SSMS and that affected one row and delete it.

So I assume the web service is working fine and the problem is on the database side of it. Is there a way in the profiler to view if the query was a success? And what could be causing this to actually not affect the row?

No triggers. Running the same query just with different parameters. Only the data is changing usually sequentially. No other queries are running on the database just now.

I've added client site logging, it does actually return 1 if it deletes the row and 0 if it doesn't delete the row. However even when it comes up as 0, the Profiler shows it has ran the query but doesn't seem to have affected it. And when I run the query through SSMS, it does affect the row.

Not receiving any errors from the web service and the query runs fine through SSMS. Only seems to not delete when it is ran multiple times in quick succession. I do agree it is most likely targeting different rows but can't see how that happens when it runs okay in SSMS.

table structure and query

```
CREATE TABLE dbo.ContractDates2HumanAssets
(
iContractDate2HumanAssetID int IDENTITY(1,1) NOT NULL,
iContractDateID int NOT NULL,
iHumanAssetID int NOT NULL,
cCategory nvarchar(256) NOT NULL,
cHR_x0020_ID nvarchar(256) NOT NULL,
cCD_x0020_ID nvarchar(256) NOT NULL,
CONSTRAINT PK_ContractDates2HumanAssets PRIMARY KEY CLUSTERED
(
iContractDate2HumanAssetID ASC

SQL Server never randomly chooses to delete or not delete a row. When you send a valid DELETE statement to SQL Server, it executes it. Guaranteed. Period. If there is an error in the statement, SQL Server will return an error. Are you seeing errors?

The far more likely problem is the query itself is not targeting the rows you think it is, or those rows don't exist. Show your table structure, along with the actual delete statements.

Robert Rodriguez' answer below, which I upvoted back when the answer was posted, talks about the impact of implicit transactions, which may well be the cause of your issue. SQL Server most certainly does delete rows, even when SET IMPLICIT_TRANSACTIONS is ON. To prove this, run the following statements that create and populate a table in a query window in SSMS:

USE tempdb;

DROP TABLE IF EXISTS dbo.t;
CREATE TABLE dbo.t
(
    i int NOT NULL
        CONSTRAINT t_pk
        PRIMARY KEY
        CLUSTERED
);
GO

INSERT INTO dbo.t (i)
VALUES (1);

SELECT *
FROM dbo.t;
GO

Running the above code, you'll see this output:

╔═══╗
║ i ║
╠═══╣
║ 1 ║
╚═══╝

Now, in a second SSMS query window, run the following code:

USE tempdb;
/*********************************
* TURN ON IMPLICIT TRANSACTIONS  *
*********************************/
SET IMPLICIT_TRANSACTIONS ON;

SELECT [State] = 'PRE DELETE'
    , [@@TRANCOUNT] = @@TRANCOUNT
    , [IMPLICIT_TRANSACTIONS] = CASE WHEN @@OPTIONS & 2 = 2 THEN 'ON' ELSE 'OFF' END
    , [Count of Rows in dbo.t] = (SELECT COUNT(1) FROM dbo.t)

DELETE 
FROM dbo.t
WHERE dbo.t.i = 1;

SELECT [State] = 'POST DELETE'
    , [@@TRANCOUNT] = @@TRANCOUNT
    , [IMPLICIT_TRANSACTIONS] = CASE WHEN @@OPTIONS & 2 = 2 THEN 'ON' ELSE 'OFF' END
    , [Count of Rows in dbo.t] = (SELECT COUNT(1) FROM dbo.t)
GO

--DISCONNECT the session after you run the above code

Running the above code, you'll see two sets of results:

╔════════════╦═════════════╦═══════════════════════╦════════════════════════╗
║ State ║ @@TRANCOUNT ║ IMPLICIT_TRANSACTIONS ║ Count of Rows in dbo.t ║
╠════════════╬═════════════╬═══════════════════════╬════════════════════════╣
║ PRE DELETE ║ 1 ║ ON ║ 1 ║
╚════════════╩═════════════╩═══════════════════════╩════════════════════════╝

╔═════════════╦═════════════╦═══════════════════════╦════════════════════════╗
║ State ║ @@TRANCOUNT ║ IMPLICIT_TRANSACTIONS ║ Count of Rows in dbo.t ║
╠═════════════╬═════════════╬═══════════════════════╬════════════════════════╣
║ POST DELETE ║ 1 ║ ON ║ 0 ║
╚═════════════╩═════════════╩═══════════════════════╩════════════════════════╝

In the above output, you can see the first resultset indicates the row does exist in dbo.t. Prior to the second resultset, the DELETE FROM dbo.t statement executes. In the second resultset you can see SQL Server has in fact deleted the row. Ensure you now disconnect the session above, which will initiate an automatic ROLLBACK TRANSACTION due to the lack of an explicit COMMIT TRANSACTION statement, in combination with SET IMPLICIT_TRANSACTIONS ON;.

Now open a third query window in SSMS, and run the following code:

SELECT [State] = 'POST ROLLBACK'
    , [@@TRANCOUNT] = @@TRANCOUNT
    , [IMPLICIT_TRANSACTIONS] = CASE WHEN @@OPTIONS & 2 = 2 THEN 'ON' ELSE 'OFF' END
    , [Count of Rows in dbo.t] = (SELECT COUNT(1) FROM dbo.t);

You'll see the following output indicating the row still exists in dbo.t.

╔═══════════════╦═════════════╦═══════════════════════╦════════════════════════╗
║ State ║ @@TRANCOUNT ║ IMPLICIT_TRANSACTIONS ║ Count of Rows in dbo.t ║
╠═══════════════╬═════════════╬═══════════════════════╬════════════════════════╣
║ POST ROLLBACK ║ 0 ║ OFF ║ 1 ║
╚═══════════════╩═════════════╩═══════════════════════╩════════════════════════╝

Of course, the row exists in the table, due to the implicit transaction being rolled back, exactly as if you'd issued a ROLLBACK TRANSACTION directly after the DELETE FROM.

Assuming you still don't believe the row is actually deleted by the DELETE FROM dbo.t statement, lets add the output from DBCC PAGE for the table in question to the second set of code, and re-run it. The code below is taken from my blog post on using DBCC PAGE to view row details

```
USE tempdb;
SET IMPLICIT_TRANSACTIONS ON;

SELECT [State] = 'PRE DELETE'
, [@@TRANCOUNT] = @@TRANCOUNT
, [IMPLICIT_TRANSACTIONS] = CASE WHEN @@OPTIONS & 2 = 2 THEN 'ON' ELSE 'OFF' END
, [Count of Rows in dbo.t] = (SELECT COUNT(1) FROM dbo.t)

DBCC TRACEON(3604) WITH NO_INFOMSGS;
DECLARE @dbid int = DB_ID();
DECLARE @fileid int;
DECLARE @pageid int;
DECLARE cur CURSOR LOCAL FORWARD_ONLY STATIC READ_ONLY
FOR
SELECT dpa.allocated_page_file_id
, dpa.allocated_page_page_id
FROM sys.schemas s
INNER JOIN sys.objects o ON o.schema_id = s.schema_id
CROSS APPLY sys.dm_db_

StackExchange Database Administrators Q#198105, answer score: 12