Compatibility of queries written in SQL Server 2012 with SQL Server 2016 Can queries designed and written for SQL Server 2012 be used as is on SQL Server 2016. We are planning an upgrade of SQL Server and want minimal manual intervention in order to transition our work
SQL Server is always backwards compatible, with the exception of items listed on the deprecated features list.

The query optimizer has changes that may result in performance increases, and in some circumstances, performance decreases.  

Microsoft's Data Migration Assistant, provides a way to find deprecated functionality in use by your database.


  The Data Migration Assistant helps discover issues that can affect an upgrade to an on-premises SQL Server. These are described as compatibility issues and are organized in the following categories:
  
  
  Breaking changes
  Behavior changes
  Deprecated features
  


Microsoft also provides this list of breaking changes to database engine features.

Ensure you test the upgrade first in a non-production environment. 

The following script can be used to create two Extended Events sessions that log the use of deprecated features and final-support features:

IF EXISTS (
    SELECT 1 
    FROM sys.dm_xe_sessions xes 
    WHERE xes.name = 'DeprecationFinalSupportEvents'
    )
BEGIN
    ALTER EVENT SESSION DeprecationFinalSupportEvents ON SERVER STATE = STOP;
    DROP EVENT SESSION DeprecationFinalSupportEvents ON SERVER;
END
IF EXISTS (
    SELECT 1 
    FROM sys.dm_xe_sessions xes 
    WHERE xes.name = 'DeprecationAnnouncmentEvents'
    )
BEGIN
    ALTER EVENT SESSION DeprecationAnnouncmentEvents ON SERVER STATE = STOP;
    DROP EVENT SESSION DeprecationAnnouncmentEvents ON SERVER;
END
GO

DECLARE @LogPath VARCHAR(260);
DECLARE @FinalSupportPathETL VARCHAR(260);
DECLARE @FinalSupportPathMTA VARCHAR(260);
DECLARE @DepPathETL VARCHAR(260);
DECLARE @DepPathMTA VARCHAR(260);

SELECT @LogPath = REVERSE(SUBSTRING(REVERSE(path)
    , CHARINDEX('\', REVERSE(path)) + 1, LEN(path) - CHARINDEX('\', REVERSE(path)) + 1))
FROM sys.traces;

SET @FinalSupportPathETL = @LogPath + '\DeprecationFinalSupportEvents.etl';
SET @FinalSupportPathMTA = @LogPath + '\DeprecationFinalSupportEvents.mta';
SET @DepPathETL = @LogPath + 'DeprecationAnnouncmentEvents.etl';
SET @DepPathMTA = @LogPath + 'DeprecationAnnouncmentEvents.mta';

DECLARE @cmd NVARCHAR(MAX);

SET @cmd = '
CREATE EVENT SESSION DeprecationFinalSupportEvents ON SERVER
ADD EVENT sqlserver.deprecation_final_support (
    ACTION  (
        sqlserver.database_id
        , sqlserver.sql_text
        , sqlserver.session_id
        , sqlserver.tsql_stack
    )
)
ADD TARGET package0.asynchronous_file_target (
    SET FILENAME = ''' + @FinalSupportPathETL + '''
        , METADATAFILE = ''' + @FinalSupportPathMTA + '''
);

ALTER EVENT SESSION DeprecationFinalSupportEvents ON SERVER
STATE = START;
';
PRINT @cmd;
EXEC (@cmd);

SET @cmd = '
CREATE EVENT SESSION DeprecationAnnouncmentEvents ON SERVER
ADD EVENT sqlserver.deprecation_announcement
(
    ACTION (
        sqlserver.database_id
        , sqlserver.sql_text
        , sqlserver.session_id
        , sqlserver.tsql_stack
        , sqlserver.username
        , sqlserver.client_hostname
        , sqlserver.database_name
        )
)
ADD TARGET package0.asynchronous_file_target (
    SET FILENAME = ''' + @DepPathETL + '''
        , METADATAFILE = ''' + @DepPathMTA + '''
);

ALTER EVENT SESSION DeprecationAnnouncmentEvents ON SERVER
STATE = START;
';
PRINT @cmd;
EXEC (@cmd);
GO