March 21, 2012 at 9:22 am
Hi ,
I have products databsae with was exists in sql 2000 server
now i have upgraded this databse to sql server 2008.But still i am using the compatability level for products databse as 80.
Now i have developped some queries with 2008 feature like CROSS APPLY & MATCHED etc .It working fine.
My question is in my sql 2008 server the master and temp database are having the compatability level set tp 100. Is because of that i can able to access the new feaures in the products database having the compatability level 80.
March 21, 2012 at 9:29 am
the compatiability level only affects certain things like DBCC CHECKDB etc which behave differently between 2000 and 2008
take a look here for the difference which are affected with the compatability level
March 21, 2012 at 9:35 am
Thanks anthony .
So is my assumption is correct beacuse of the master compatabality set 100 the user specific databases are set to 80 still we have access to new feature in sql 208
March 21, 2012 at 9:36 am
as long as it doesnt use anything which is in the Differences Between Lower Compatibility Levels and Level 100 section of that article then yes
March 21, 2012 at 9:42 am
Anthony is correct.
It isn't master, but the way compat level works. It doens't make it a 2000 database. It's still a 2008 database, so all new features work. Backups, restores, query processor, all the 2008 stuff is functioning. The parser, however, treats some commands the way they would be treated in 2000. It provides backwards compatability, not limitations.
March 21, 2012 at 9:48 am
The compat mode just affects how the query processor processes and checks certain T-SQL constructs. It's not about different behaviour (much), CheckDB does the same in a DB under compat mode 100 as it does one at 80. It doesn't affect the database structure. It's mostly about changed syntax and what's allowed and what isn't (*= is not allowed under compat mode 100, only under 80), and it only affects queries run in the context of the particular database.
eg, if a database is in compat mode 80, CROSS APPLY with a UDF won't work (because the column as a parameter is syntatically invalid), if the query is run from a DB in compat mode 100 and just references table in the compat mode 80 DB, the query will work fine.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 21, 2012 at 9:49 am
Also, in comapability level 80 (SQL Server 2000) you don't have access to new system tables and dmvs introduced in SQL Server 2005 and expanded in SQL Server 2008. Or at least that has been my experience.
March 21, 2012 at 9:55 am
Lynn Pettis (3/21/2012)
Also, in comapability level 80 (SQL Server 2000) you don't have access to new system tables and dmvs introduced in SQL Server 2005 and expanded in SQL Server 2008. Or at least that has been my experience.
You do, there's no limitation around objects available. What you may have seen is queries like this failing in compat mode 80:
SELECT <columns>
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle)
That will fail in a compat mode 80 DB because the passing of a column to a function is syntatically invalid in SQL 2000.
Querying just sys.dm_exec_query_stats is fine, querying just sys.dm_exec_query_plan and passing a variable, parameter or constant is fine too.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 21, 2012 at 10:11 am
Hmm, just ran a test db, in compat mode 80, and CROSS aPPLY works, as does a select from missing_indexes.
SQL2K8, SP1
CREATE DATABASE sql2kCompat
;
go
ALTER DATABASE SQL2KCompat
SET COMPATIBILITY_LEVEL = 80
;
go
USE SQL2KCompat
;
go
SELECT
*
FROM sys.dm_database_encryption_keys
;
go
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
SELECT * FROM Department D
CROSS APPLY
(
SELECT * FROM Employee E
WHERE E.DepartmentID = D.DepartmentID
) A
;
GO
SELECT *
FROM sys.dm_db_missing_index_details
;
GO
USE MASTER
;
GO
DROP DATABASE SQL2KCompat
;
GO
March 21, 2012 at 10:16 am
Yes, Cross Apply works, so does any DMV. What doesn't work is this:
SELECT sql_handle ,
text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '.'.
because passing a column as a parameter to a function is syntatically invalid on SQL 2000. It's not the CROSS APPLY that breaks, it's not the DMVs, it's this: sys.dm_exec_sql_text(qs.sql_handle)
Querying sys.dm_exec_sql_text and passing a constant, variable or parameter will work fine. It is just being called with a column passed to the function that is invalid below compat mode 90 and the only thing that can be in brackets after a table is a hint (or a variable/constant for a function).
p.s. taking out the qs. doesn't mean it works, just changes the error to something a lot more sensible:
SELECT sql_handle ,
text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS st
Msg 321, Level 15, State 1, Line 4
"sql_handle" is not a recognized table hints option. If it is intended as a parameter to a table-valued function or to the CHANGETABLE function, ensure that your database compatibility mode is set to 90.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply