I saw a note recently from someone asking if they could use CROSS APPLY on a SQL Server 2008 instance with an older database in SQL 2000 compatibility mode. You can.

CREATE DATABASE sql2kCompat
;
go
ALTER DATABASE SQL2KCompat 
  SET COMPATIBILITY_LEVEL = 80
;
go

Once I have a database, I can access any of the newer views and DMVs. For example:

USE SQL2KCompat
;
go
SELECT 
 * 
  FROM sys.dm_database_encryption_keys
;
go

This doesn’t return anything because I don’t have keys, but I do get the headers. Now let’s add some data.

CREATE TABLE [Department](
   [DepartmentID] [int] NOT NULL PRIMARY KEY,
   [Name] VARCHAR(250) NOT NULL,
)
;
GO
INSERT [Department] ([DepartmentID], [Name]) 
 VALUES (1, N'Engineering')
;
INSERT [Department] ([DepartmentID], [Name]) 
 VALUES (2, N'Administration')
;
INSERT [Department] ([DepartmentID], [Name]) 
 VALUES (3, N'Sales')
, (4, N'Marketing')
, (5, N'Finance')
;
GO
CREATE TABLE [Employee](
   [EmployeeID] [int] NOT NULL PRIMARY KEY,
   [FirstName] VARCHAR(250) NOT NULL,
   [LastName] VARCHAR(250) NOT NULL,
   [DepartmentID] [int] NOT NULL REFERENCES [Department](DepartmentID),
)
;
GO
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
 VALUES (1, N'Orlando', N'Gee', 1 )
;
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
 VALUES (2, N'Keith', N'Harris', 2 )
;
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
 VALUES (3, N'Donna', N'Carreras', 3 )
;
INSERT [Employee] ([EmployeeID], [FirstName], [LastName], [DepartmentID])
 VALUES (4, N'Janet', N'Gates', 3 ) 
;
go

I create a few objects, which are standard, but notice the third insert statement for the Department table. It uses the new insert syntax for multiple rows in one statement. That’s not legal in SQL Server 2000, but it works here.

Now I can use CROSS APPLY

SELECT * FROM Department D
 CROSS APPLY
   (
   SELECT * FROM Employee E
   WHERE E.DepartmentID = D.DepartmentID
   ) A
;
GO

This returns me results, just as it does in a SQL Server 2008 database.

It appears that SQL 2008 functions work, which is what I’d hope would happen. However for the purposes of backwards compatibility, the functions that are from SQL Server 2000, should work as expected in SQL Server 2000.

The thing to be aware of is something that wasn’t legal in SQL Server 2000

SELECT
  q.sql_handle 
, t.text 
 FROM sys.dm_exec_query_stats AS q
  CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t

you get an error. Passing a column into a function wasn’t allowed in SQL Server 2000, so this is a problem.

And a little cleanup

USE MASTER
;
GO
DROP DATABASE SQL2KCompat
;
go

Filed under: Blog Tagged: sql server, syndicated, T-SQL