How to delete data in a large table to minimize the impacts on transaction logging?

WHILE 1 = 1
    BEGIN
        DELETE TOP (1000)
        FROM    Sales.MyOrderDetails
        WHERE   productid = 12;
        IF @@rowcount < 1000
            BREAK;

    END

The code above uses an infinite loop (WHERE 1 = 1 is always true). In each iteration, a DELETE statement with a TOP option limits the number of affected rows to no more than 1,000 at a time. Then the IF statement checks if the number of affected rows is less than 1,000; in such a case, the last iteration deleted the last chunk of qualifying rows. After the last chunk of rows has been deleted, the code breaks from the loop.

Correctly understand the DML triggers and the DDL triggers (and the structure of the EVENT_INSTANCE)

For some reason, I have some misconceptions about triggers. Here is the correct understanding:

·       DML Triggers (or triggers for DML Events) can be AFTER (or FOR) or INSTEAD OF triggers. It is also true for the Azure off-premise database. See the Syntax below:

Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)

CREATE TRIGGER [ schema_name . ]trigger_name
ON { table | view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

<method_specifier> ::= 
    assembly_name.class_name.method_name

·       DDL Triggers (or triggers for DDL Events) can only be AFTER (or FOR) triggers. No INSTEAD OF triggers for DDL events. See the Syntax below:

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<ddl_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

·       LOGON Triggers (or triggers for LogOn Events), the same as DDL Triggers, can only be AFTER trigger, no INSTEAD OF trigger. See the Syntax below:

Trigger on a LOGON event (Logon Trigger)

CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR| AFTER } LOGON 
AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }

<logon_trigger_option> ::=
    [ ENCRYPTION ]
    [ EXECUTE AS Clause ]

DDL Triggers can be of two scopes.
·       Server Level Trigger – To capture events for all Databases on the Server,
·       Database Level Trigger – Limited to a single Database on which the Trigger is created.

DDL Trigger often captures the information about the EVENT that fired it by using EVENTDATA()function. EVENTDATA() returns an XML. The following example not only captures the DDL EVENT Type, but the entire structure of the Event Data as well:

--A trigger that works with DDL statements
CREATE TRIGGER Database_CreateTable_DropTable
ON DATABASE
AFTER CREATE_TABLE, DROP_TABLE
AS
    DECLARE @EventData XML;
    SELECT  @EventData = EVENTDATA();
    DECLARE @EventType VARCHAR(100);
    SET @EventType = @EventData.value('(/EVENT_INSTANCE/EventType)[1]',
                                      'varchar(100)');
    IF @EventType = 'CREATE_TABLE'
        PRINT 'A new table has been created.';
    ELSE
        PRINT 'A table has been dropped.';
    PRINT CONVERT(VARCHAR(MAX), @EventData);

--A CREATE TABLE statement that fires the trigger
CREATE TABLE VendorsTest
    (
        VendorID INT ,
        VendorName VARCHAR(50)
    );

/*
The response from the system:

A new table has been created.
*/

-- Below shows the structure of the Event Data or EVENT_INSTANCE:

<EVENT_INSTANCE>
    <EventType>CREATE_TABLE</EventType>
    <PostTime>2012-05-09T17:01:15.390</PostTime>
    <SPID>51</SPID>
    <ServerName>ANNE-PC\SQLEXPRESS</ServerName>
    <LoginName>murach\Anne</LoginName>
    <UserName>dbo</UserName>
    <DatabaseName>AP</DatabaseName>
    <SchemaName>dbo</SchemaName>
    <ObjectName>VendorsTest</ObjectName>
    <ObjectType>TABLE</ObjectType>
    <TSQLCommand>
        <SetOptions ANSI_NULLS="ON" ANSI_NULL_DEFAULT="ON"
        ANSI_PADDING="ON" QUOTED_IDENTIFIER="ON"
        ENCRYPTED="FALSE"/>
        <CommandText>
            CREATE TABLE VendorsTest (VendorID int,
            VendorName varchar(50));
        </CommandText>
    </TSQLCommand>

</EVENT_INSTANCE>