From the docs:
Sets certain database behaviors to be compatible with the specified version of SQL Server.
...
Compatibility level provides only partial backward compatibility with earlier versions of SQL Server. Use compatibility level as an interim migration aid to work around version differences in the behaviors that are controlled by the relevant compatibility-level setting.
In my interpretation, compatibility mode is about behavior and parsing of syntax, not for things like the parser saying, "Hey, you can't use ROW_NUMBER()!" Sometimes the lower compatibility level allows you to continue getting away with syntax no longer supported, and sometimes it prevents you from using new syntax constructs. The documentation lists several explicit examples, but here are a few demonstrations:
Passing built-in functions as function arguments
This code works in compatibility level 90+:
SELECT *
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, NULL);
But in 80 it yields:
Msg 102, Level 15, State 1
Incorrect syntax near '('.
The specific problem here is that in 80 you aren't allowed to pass a built-in function into a function. If you want to stay in 80 compatibility mode, you can work around this by saying:
DECLARE @db_id INT = DB_ID();
SELECT *
FROM sys.dm_db_index_physical_stats(@db_id, NULL, NULL, NULL, NULL);
Passing a table type to a table-valued function
Similar to the above, you can get a syntax error when using a TVP and trying to pass it to a table-valued function. This works in modern compat levels:
CREATE TYPE dbo.foo AS TABLE(bar INT);
GO
CREATE FUNCTION dbo.whatever
(
@foo dbo.foo READONLY
)
RETURNS TABLE
AS
RETURN (SELECT bar FROM @foo);
GO
DECLARE @foo dbo.foo;
INSERT @foo(bar) SELECT 1;
SELECT * FROM dbo.whatever(@foo);
However, change the compatibility level to 80, and run the last three lines again; you get this error message:
Msg 137, Level 16, State 1, Line 19
Must declare the scalar variable "@foo".
Not really any good workaround off the top of my head, other than upgrading the compat level or getting the results a different way.
Using qualified column names in APPLY
In 90 compatibility mode and up, you can do this without problem:
SELECT * FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) AS t;
However, in 80 compatibility mode, the qualified column handed to the function raises a generic syntax error:
Msg 102, Level 15, State 1
Incorrect syntax near '.'.
ORDER BY an alias that happens to match a column name
Consider this query:
SELECT name = REVERSE(name), realname = name
FROM sys.all_objects AS o
ORDER BY o.name;
In 80 compatibility mode, the results are as follows:
001_ofni_epytatad_ps sp_datatype_info_100
001_scitsitats_ps sp_statistics_100
001_snmuloc_corps_ps sp_sproc_columns_100
...
In 90 compatibility mode, the results are quite different:
snmuloc_lla all_columns
stcejbo_lla all_objects
sretemarap_lla all_parameters
...
The reason? In 80 compatibility mode, the table prefix is ignored entirely, so it is ordering by the expression defined by the alias in the SELECT list. In newer compatibility levels, the table prefix is considered, so SQL Server will actually use that column in the table (if it is found). If the ORDER BY alias is not found in the table, the newer compatibility levels are not so forgiving about ambiguity. Consider this example:
SELECT myname = REVERSE(name), realname = name
FROM sys.all_objects AS o
ORDER BY o.myname;
The result is ordered by the myname expression in 80, because again the table prefix is ignored, but in 90 it generates this error message:
Msg 207, Level 16, State 1, Line 3
Invalid column name 'myname'.
This is all explained as well in the documentation:
When binding the column references in the ORDER BY list to the columns defined in the SELECT list, column ambiguities are ignored and column prefixes are sometimes ignored. This can cause the result set to return in an unexpected order.
For example, an ORDER BY clause with a single two-part column (<table_alias>.<column>) that is used as a reference to a column in a SELECT list is accepted, but the table alias is ignored. Consider the following query.
SELECT c1 = -c1 FROM t_table AS x ORDER BY x.c1
When executed, the column prefix is ignored in the ORDER BY. The sort operation does not occur on the specified source column (x.c1) as expected; instead it occurs on the derived c1 column that is defined in the query. The execution plan for this query shows that the values for the derived column are computed first and then the computed values are sorted.
ORDER BY something not in the SELECT list
In 90 compatibility mode you can't do this:
SELECT name = COALESCE(a.name, '') FROM sys.objects AS a
UNION ALL
SELECT name = COALESCE(a.name, '') FROM sys.objects AS a
ORDER BY a.name;
Result:
Msg 104, Level 16, State 1
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
In 80, though, you can still use this syntax.
Old, icky outer joins
80 mode also allows you to use the old, deprecated outer join syntax (*=/=*):
SELECT o.name, c.name
FROM sys.objects AS o, sys.columns AS c
WHERE o.[object_id] *= c.[object_id];
In SQL Server 2008 / 2008 R2, if you're in 90 or greater, you get this verbose message:
Msg 4147, Level 15, State 1
The query uses non-ANSI outer join operators ("*=" or "=*"). To run this query without modification, please set the compatibility level for current database to 80, using the SET COMPATIBILITY_LEVEL option of ALTER DATABASE. It is strongly recommended to rewrite the query using ANSI outer join operators (LEFT OUTER JOIN, RIGHT OUTER JOIN). In the future versions of SQL Server, non-ANSI join operators will not be supported even in backward-compatibility modes.
In SQL Server 2012, this is no longer valid syntax at all, and yields the following:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near '*='.
Of course in SQL Server 2012 you can no longer work around this problem using compatibility level, since 80 is no longer supported. If you upgrade a database in 80 compat mode (by in-place upgrade, detach/attach, backup/restore, log shipping, mirroring, etc.) it will automatically be upgraded to 90 for you.
Table hints without WITH
In 80 compat mode, you can use the following and the table hint will be observed:
SELECT * FROM dbo.whatever NOLOCK;
In 90+, that NOLOCK is no longer a table hint, it's an alias. Otherwise, this would work:
SELECT * FROM dbo.whatever AS w NOLOCK;
But it doesn't:
Msg 1018, Level 15, State 1
Incorrect syntax near 'NOLOCK'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
Now, to prove that the behavior is not observed in the first example when in 90 compat mode, use AdventureWorks (making sure it's in a higher compat level) and run the following:
BEGIN TRANSACTION;
SELECT TOP (1) * FROM Sales.SalesOrderHeader UPDLOCK;
SELECT * FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
AND resource_type IN ('KEY', 'OBJECT'); -- how many rows here? 0
COMMIT TRANSACTION;
BEGIN TRANSACTION;
SELECT TOP (1) * FROM Sales.SalesOrderHeader WITH (UPDLOCK);
SELECT * FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID
AND resource_type IN ('KEY', 'OBJECT'); -- how many rows here? 2
COMMIT TRANSACTION;
This one is particularly problematic because the behavior changes without an error message or even an error. And it's also something that the upgrade advisor and other tools might not even spot, since for all it knows, that's a table alias.
Conversions involving new date/time types
The new date/time types introduced in SQL Server 2008 (e.g. date and datetime2) support a much larger range than the original datetime and smalldatetime). Explicit conversions of values outside the supported range will fail no matter what the compatibility level, for example:
SELECT CONVERT(SMALLDATETIME, '00010101');
Yields:
Msg 242, Level 16, State 3
The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value.
However, implicit conversions will work themselves out in the newer compatibility levels. For example this will work in 100+:
SELECT DATEDIFF(DAY, CONVERT(SMALLDATETIME, SYSDATETIME()), '00010101');
But in 80 (and also in 90), it yields a similar error as above:
Msg 242, Level 16, State 3
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
Redundant FOR clauses in triggers
This is an obscure scenario that came up here. In 80 compatibility mode, this will succeed:
CREATE TABLE dbo.x(y INT);
GO
CREATE TRIGGER tx ON dbo.x
FOR UPDATE, UPDATE
------------^^^^^^ notice the redundant UPDATE
AS PRINT 1;
In 90 compatibility and higher, this no longer parses, and instead you get the following error message:
Msg 1034, Level 15, State 1, Procedure tx
Syntax error: Duplicate specification of the action "UPDATE" in the trigger declaration.
PIVOT/UNPIVOT
Some forms of syntax won't work under 80 (but work just fine in 90+):
SELECT col1, col2
FROM dbo.t1
UNPIVOT (value FOR col3 IN ([x],[y])) AS p;
This yields:
Msg 156, Level 15, State 1
Incorrect syntax near the keyword 'for'.
For some workarounds, including CROSS APPLY, see these answers.
New built-in functions
Try using new functions like TRY_CONVERT() in a database with compatibility level < 110. They are simply not recognized there at all.
SELECT TRY_CONVERT(INT, 1);
Result:
Msg 195, Level 15, State 10
'TRY_CONVERT' is not a recognized built-in function name.
Recommendation
Only use 80 compatibility mode if you actually need it. Since it will no longer be available in the next version after 2008 R2, the last thing you want to do is write code in this compat level, rely on the behaviors you see, and then have a whole bunch of breakage when you can no longer use that compat level. Be forward thinking and don't try to paint yourself into a corner by buying time to continue using old, deprecated syntax.
Best Answer
There is not a simple tool or existing script for this that I am aware of. In terms of scanning for the use of functions added in a specific version (or enabled by a specific compatibility level) something could be written using the documentation from MS though that might not catch calls via ad-hoc SQL if your stored procedure and other use that pattern for anything, and you'll need to do further research to cover changes to undocumented functions.
Would it not be acceptable to test against the minimum version you need to support, rather than testing against every version from that onwards? That would only be one extra instance you need to add to your CI/Test/other processes. If 2016sp1 is the earliest version you need to support then almost everything is available in all editions so you can use Express and not even have any licensing costs for that one instance. You might get away with that with prior versions too, if you don't use any features that were standard-/enterprise- only prior to 2016sp1.