Cannot create an index on a view or computed column because the compatibility level of this database is less than 80. Use sp_dbcmptlevel to raise the compatibility level of the database.

Error Message:
Msg 1959, Level 16, State 1, Line 2
Cannot create an index on a view or computed column because the compatibility level of this database is less than 80. Use sp_dbcmptlevel to raise the compatibility level of the database.

Severity level:
16.

Description:
This error message appears when you try to create an indexed view that is contained in a database with a compatibility level less than 80.

Consequences:
The T-SQL statement can be parsed, but causes the error at runtime.

Resolution:
Error of the Severity Level 16 are generated by the user and can be fixed by the SQL Server user. The statement cannot be executed this way. To index a view the compatibility level of the database must be at least 80 (or higher).

Versions:
All versions of SQL Server /(beginning with SQL Server 2000).

Example(s):
USE Northwind;
GO
EXEC sp_dbcmptlevel ‘Northwind’, ’70′
GO

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING,
    ANSI_WARNINGS,
    CONCAT_NULL_YIELDS_NULL,
    ARITHABORT,
    QUOTED_IDENTIFIER,
    ANSI_NULLS ON;
GO

IF OBJECT_ID (‘dbo.MyOrderView’, ‘View’) > 0
   DROP VIEW dbo.MyOrderView;
GO
CREATE VIEW dbo.MyOrderView
WITH SCHEMABINDING
AS
SELECT SUM(UnitPrice*Quantity*(1.00-Discount)) AS Revenue,
    OrderDate, ProductID, COUNT_BIG(*) AS MyCount
  FROM dbo.[Order Details] AS od
  JOIN dbo.Orders AS o
    ON od.OrderID = o.OrderID
 GROUP BY OrderDate, ProductID;
GO

CREATE CLUSTERED INDEX cix_MyOrderView
    ON dbo.MyOrderView (OrderDate, ProductID);
GO
EXEC sp_dbcmptlevel ‘Northwind’, ’70′
GO

Remarks:
In the above example we lower than compatibility level of the Northwind database to 70. When trying now to create an indexed view, the error is raised.




Array

No comments yet... Be the first to leave a reply!

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |