SA0209 : SET OFFSETS is deprecated

The problem with using the OFFSETS option in T-SQL scripts is that it is a feature in maintenance mode and may be removed in future releases of Microsoft SQL Server. Database administrators and developers who rely on this feature in their SQL queries might face unexpected issues or need to perform significant rewrites of their codebase if they do not address this early.

For example:

1-- Example of setting OFFSETS option
2SET OFFSETS 10;

In this example, the use of SET OFFSETS is problematic because it could lead to future compatibility issues once the feature is deprecated. Adopting currently supported and future-proof alternatives, like utilizing ORDER BY with OFFSET-FETCH, is recommended.

• Continued use of OFFSETS could result in technical debt and maintainability problems.

• Preparing for deprecation helps ensure smoother transitions and reduces the risk of performance and functionality disruptions.

Avoid using the OFFSETS option in T-SQL code as it is potentially deprecated. Modify your queries to use alternatives such as ORDER BY with OFFSET-FETCH.

Follow these steps to address the issue:

1. Review your existing SQL queries to identify any use of the SET OFFSETS option.

2. Replace the SET OFFSETS usage with the ORDER BY … OFFSET … FETCH clause to achieve similar functionality.

3. Test the modified queries to ensure they return the expected results and perform efficiently.

For example:

1-- Deprecation-prone usage
2SET OFFSETS 10;
3
4-- Recommended alternative
5SELECT Column1, Column2 
6FROM TableName 
7ORDER BY Column1 
8OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Deprecated Features, Bugs

Deprecated Database Engine Features in SQL Server 2017

Other Resources