"WITH CHECK OPTION" and "TOP" causes error when upgrading to SQL Server 2016

Question

Whilst looking to upgrade a Microsoft SQL Server from 2014 to 2016, I ran Microsoft's Data Migration Assistant, and a breaking change was found with the WITH CHECK OPTION when using a view in a table.

Whilst looking through Microsoft's various pages for help, it seemed that they were dead-ends. Our current compatibility version is 120, and one option to fix was downgrading it to 90; however this isn't possible.

The code breaking is:

CREATE VIEW [table]
AS  
    SELECT TOP 1000000
        [Id],
        [UserName],
        [Firstname]
    FROM 
        [Users] WITH (NOLOCK)
    WITH CHECK OPTION 
GO

Does anyone have a fix for this? Otherwise it seems upgrading to 2016 version will not work.

Answer 1

So, if you follow the MSDN official documentation Create View (Transact-SQL) you will note the following in the UPDATABLE VIEWS section:

You can modify the data of an underlying base table through a view, as long as the following conditions are true:

...

TOP is not used anywhere in the select_statement of the view together with the WITH CHECK OPTION clause.

So it is highly possible that the SQL Server team have enforced this impossibility as the CHECK

Forces data modification statements executed against the view to follow the criteria set within select_statement.

Obviously, you will not be able to normally edit the code with the combined, so perhaps the answer is clear.