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.