>>>Hi Naomi,
>>>I wrote SP like this
>>>

>>>ALTER PROCEDURE [dbo].[sp_deleteartikelbymanufacturer] @tnManufacturerId int
>>>AS
>>>BEGIN
>>>WHILE (SELECT COUNT(*) FROM ERPBESTBUY.dbo.vbestbuy WHERE ERPBESTBUY.dbo.vbestbuy.manufacturer_pk_id=@tnManufacturerId) > 0
>>>BEGIN
>>>	DELETE TOP (1000) 
>>>	FROM ERPBESTBUY.dbo.vbestbuy WITH (TABLOCK)
>>>	WHERE ERPBESTBUY.dbo.vbestbuy.manufacturer_pk_id=@tnManufacturerId 
>>>      CONTINUE
>>>END
>>>END
>>>

>>>
>>>it does not increase performance.
>>
>>BTW, a slightly simpler version will be
>>

>>declare @Cnt int
>>
>>set @Cnt = 1
>>
>>while @Cnt > 0
>>    begin
>>       delete top (10000) -- I suggest 10K at once if you need to delete 200K
>>        ...
>>     set @cnt = @@ROWCOUNT
>>  end
>>

>>Also, no need for TABLOCK hint.
>>
>>Finally, what is the recovery model for the DB? If you need to perform a mass update, then it can make sense to set it to SIMPLE.
>
>By the way, Naomi, is there something in SQL 2008 like a non transactional table type? In MySQL, you have InnoDB and MyIsam tables. The MyIsam tables are non-transactional and therefore insert's and delete's are much faster than in InnoDB. You loose the possibility of Rollback, but for these type of tables speed is the only thing you need at that moment. I am wondering if in SQL 2008 there is also something like a Table engine type, that could be a way to speed the process up.

Table variables in SQL Server don't participate in transactions.

declare @t table (...)

However, I don't see them applicable for the problem we're discussing as we need to delete many records from the real table.