[BP017] DELETE statement without WHERE clause

Sometimes people ask me: "What wrong with following code? Why issue BP017 is registered?"

delete d
from    dbo.Data d inner join #ids i on d.id = i.id

As you can see, rows to be deleted are limited by joining with temporary table named #ids which apparently contains a list of needed row identifiers.
So what is the problem? Why BP017 is registered?

The reason why is that description of issue must be read literally - "without WHERE clause".
Do you see any WHERE clause? No? Well, you got your issue.

Looks silly, I know :)

[nerd mode on]
To be precise, "WHERE Specifies the search condition for the rows returned by the query".
If there is no WHERE clause then all rows can be affected by statement. And if some rows where filtered out by join - we are simply lucky.
[nerd mode off]

So if it usual for you to delete rows without WHERE clause you can simply turn off this issue or add table name to BP017AllowedTables.
In some cases you can use TRUNCATE statement instead of DELETE without limitation. However you should take into account that TRUNCATE is not the same as DELETE (see Remarks and Permissions section in MSDN article - foreign keys, triggers and owner level permissions).
And yet some differences:
1. You cannot truncate view, derived table/CTE or object at linked server.
2. TRUNCATE resets IDENTITY (http://blog.sqlauthority.com/2012/08/24/sql-server-delete-truncate-and-reseed-identity/)

In nearest future I plan to add yet another issue, something like "DELETE statement without WHERE or JOIN clause".

Follow me at Twitter @SQLCodeGuard or subscribe to Blog to be the first one to know about it.