In order to support table stretching, the CREATE and ALTER TABLE statements have been extended in SQL Server 2016. Here is the syntax extension for the ALTER TABLE statement that supports the Stretch DB feature:

<stretch_configuration> ::=   
    {   
      SET (   
        REMOTE_DATA_ARCHIVE    
        {   
            = ON (  <table_stretch_options>  )   
          | = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED )   
          | ( <table_stretch_options> [, ...n] )   
        }   
            )   
    }   
   
<table_stretch_options> ::=   
    {   
     [ FILTER_PREDICATE = { null | table_predicate_function } , ]   
       MIGRATION_STATE = { OUTBOUND | INBOUND | PAUSED }   
    }   
 

You can specify the following options to enable Stretch DB:

• REMOTE_DATA_ARCHIVE is required and can have these values: ON, OFF_WITHOUT_DATA_RECOVERY or no value.
• MIGRATION_STATE is also mandatory and can have one of the following values: OUTBOUND, INBOUND, or PAUSED.
• FILTER_PREDICATE is optional and is used to define the part of the data that needs to be migrated. If it's not specified, the entire table will be moved.

If your table contains both hot and cold data, you can specify a filter predicate to select the rows that should be migrated. The filter predicate is an inline table-valued function. Its parameters are identifiers for stretch table columns. At least one parameter is required. Here is the function syntax:

CREATE FUNCTION dbo.fn_stretchpredicate(@column1 datatype1, @column2 datatype2 [, ...n])   
RETURNS TABLE   
WITH SCHEMABINDING    
AS    
RETURN  SELECT 1 AS is_eligible   
        WHERE <predicate>   

The function returns either a non-empty result or no result set. In the first case, the row is eligible to be migrated, otherwise it remains in the local system.

The <predicate> can consist of one condition, or of multiple conditions joined with the AND logical operator:

<predicate> ::= <condition> [ AND <condition> ] [ ...n ]

Each condition in turn can consist of one primitive condition, or of multiple primitive conditions joined with the OR logical operator. You cannot use subqueries or non-deterministic functions. For a detailed list of limitations, please visit this page in the SQL Server Books Online: https://msdn.microsoft.com/en-us/library/mt613432.aspx.

The following code example shows how to enable the Stretch DB feature for the T1 table in the database Mila:

CREATE FUNCTION dbo.StretchFilter(@col DATETIME)   
RETURNS TABLE   
WITH SCHEMABINDING    
AS    
       RETURN SELECT 1 AS is_eligible WHERE @col < CONVERT(DATETIME, '01.02.2018', 104); 
GO 
ALTER TABLE dbo.T1  
  SET (  
          REMOTE_DATA_ARCHIVE = ON (   
                                      FILTER_PREDICATE = dbo.StretchFilter(c2),   
                                      MIGRATION_STATE = OUTBOUND 
                                    )  
       );    

After executing the preceding commands, Stretch DB is enabled for T1 table. The following screenshot shows the SSMS screen immediately after the execution:

The Stretch DB feature is enabled, but you can also see a warning message that informs you that although your T1 table has a primary key constraint, it will not be enforced! Thus, you can have multiple rows in your table with the same ID, just because you have enabled the Stretch DB. This schema and integrity change silently implemented as part of Stretch DB enabling can be dangerous; some developers will not be aware of it, since the information is delivered through a message warning.

When you ignore this issue, the rest of the action looks correct. After the table is enabled for stretching, you can expect three rows to remain in the local database (they have a value in the c2 column later than 1st February 2018). Two rows should be moved to the Azure SQL database. You will confirm this by querying stretch tables, but before that you will learn a tip about the creation of a filter predicate with sliding window.