Some compatibility differences between versions of SQL Server

In our environment most of developers use SQL Server 2008,but in production we have SQL Server 2005, and some databases still have compatibility level 80(SQL server 2000)
It sometimes causes problems
E.g. the SQL wtitten on SQL Server 2008

INSERT INTO[dbo].[SiteIds]  ([SiteID], [SiteCode],[ParentID])

    VALUES  (11,’ChildOfXX’,(select [SiteID] from [SiteIds] where SiteCode=’XX’))

GO

on SQL Server 2005 caused
Msg 1046, Level 15, State 1, Line 5
Subqueries are not allowed in this context. Only scalar expressions are allowed.
Fortunately it’s easy to rewrite in SQL 2005 acceptable form
 

DECLARE @SiteId int

    set @siteID=(select [SiteID] from [SiteIds] where SiteCode=’XX’)

INSERT INTO[dbo].[SiteIds]  ([SiteID], [SiteCode],[ParentID])  VALUES  (11,’ChildOfXX’,@siteID)

Another statement that generated in SQL Server 2008 should be removed in SQL Server 2005
ALTER TABLE dbo.Event SET (LOCK_ESCALATION = TABLE)

Also we had a stored procedure, that worked in  compatibility level 80(SQL server 2000), but caused an error after changing to compatibility level 90

Msg 145, Level 15, State 1, Line 4
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
The explanation of the error can be found in SELECT DISTINCT and ORDER BY post.  In my case DISTINCT was completely redundant and I’ve removed it without any side effects.

Advertisements