Delayed Durability - Getting Started with SQL Server 2014 Administration - page 49

Delayed Durability and Natively Compiled
Stored Procedures
When it comes to natively compiled stored procedures, the Delayed Durability
switch will be much the same as the Transact-SQL equivalent. The official name for
the Delayed Durability level is atomic block level control. The Transact-SQL syntax
is the same:
DELAYED_DURABILITY = { OFF | ON }
The OFF switch means that the transaction will be fully durable unless the database
Delayed Durability setting has been set to Forced . If it is set to Forced , the commit
will be asynchronous and thus delayed durable and thus there is a risk of data loss.
The ON switch means that the transaction will use Delayed Durability unless the
database option for Delayed Durability has been set to Disabled . If it is set to
Disable , then the transaction will be set to fully durable regardless of the settings
at the atomic block level in natively compiled stored procedures.
Much like the Transact-SQL equivalent, the setting used at the atomic block level will
be determined by the setting at the database level. If you want specific transactions
to be able to determine if they delayed durability, then you need to set the database
option to Allowed for that granular control of the Delayed Durability setting.
The following is an example of the syntax needed at the atomic level to make a
transaction using Delayed Durability:
CREATE PROCEDURE <procedureName> …
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
DELAYED_DURABILITY = ON,
TRANSACTION ISOLATION LEVEL = SNAPSHOT,
LANGUAGE = N'English'
…
)
END