Each SQL Server database has a setting called Compatibility Level that determines how T-SQL commands are interpreted and what features are supported for that database. A single SQL instance can host multiple databases with different compatibility levels, so SQL statements that work on one database might not work on the other. Each version of SQL Server introduces a new compatibility level and supports two previous levels for backward compatibility:

There is no full backward compatibility! Some features get deprecated and no longer supported over time so, for example, code written for SQL Server 2000 might not work on SQL 2012 and later. A SQL 2000 database with compatibility level 80 can only be migrated to SQL Server 2005 or 2008/2008R2 without any code changes. If we want to migrate it to SQL 2012, we need to increase the compatibility level for that database to at least 90.

Performing a migration using either backup/restore method or detach/attach method will not automatically change the database compatibility level, so that must be done manually prior to the migration.

The difference between compatibility levels can be significant and sometimes affect frequently used coding techniques. Here are some of the changes introduced in SQL 2005:

Joins
The *= and =* operators for outer joins are no longer supported, the OUTER JOIN keyword should be used instead. For example:

SELECT *
FROM t1, t2
WHERE t1.id *= t2.id;

Should be changed to:

SELECT *
FROM t1 LEFT OUTER JOIN t2
ON t1.id = t2.id;

Common table expressions
The statement before WITH must end with a semicolon otherwise the code will fail:

SELECT ... FROM ... WHERE ... ;
WITH myCTE AS ( 
  SELECT ...
  FROM t1 
 ) 
SELECT .... FROM myCTE;

Keywords as column names
In SQL 2005 the number of keywords was increased significantly so that the chance of a keyword matching a name of a column also increased. This is not forbidden, but it requires enclosing column names in square brackets []:

SELECT t1.External FROM t1;

Should be changed to:

SELECT t1.[External] FROM t1;

The full list of keywords is available on MSDN.

Table hints
Some developers love messing with the SQL optimizer by using index hints like this:

SELECT ...
FROM t1 (INDEX=IX_t1_indexname);

If you do this then keep in mind that the syntax has changed and therefore such code must be edited to look like this:

SELECT ...
FROM t1 WITH (INDEX (IX_t1_indexname));

A full list of differences between various compatibility levels is available on MSDN pages for SQL 2014, 2012, 2008, etc.