Suppose we have a WHILE loop for deleting specified amount of data batches in each loop. Simple solution is to use TOP operator in loop for specifying the number of rows to be deleted in each batch. But it generates error while trying to use TOP operator in delete operation. Let us have a look at the problem.
-- Create a table with only one column in it CREATE TABLE DemoTable (id SMALLINT identity(1, 1)) GO -- Insert 100 values in identity column INSERT INTO DemoTable DEFAULT VALUES GO 100 -- Verify the values SELECT * FROM DemoTable GO -- Try to delete statement for top 10 rows DELETE TOP 10 FROM DemoTable
but following error message is generated
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ’10’.
It looks that TOP operator is not working in the DELETE statement. So can we use the TOP operator in DELETE operations or not?
Answer is YES, we may use TOP operator in DELETE operations but with little modification. Correct statement is as under with brackets around the number.
-- Using TOP in delete statement DELETE TOP (10) FROM DemoTable
So we have deleted the 10 records from demo table by using TOP operator in DELETE statement. Similarly instead of constant number any variable may be used in brackets with TOP.
-- Drop DemoTable DROP TABLE DemoTable
However it is notable that according to BOL
“TOP cannot be used together with UPDATE and DELETE statements on partitioned views.”.