Indexed View Error - select list does not include a proper use of COUNT_BIG

Problem:

I Tried to Create a Index on the Existing View, then i got the following Error:

Msg 10138, Level 16, State 1, Line 1
Cannot create index on view 'EISA.dbo.VIEW_CLIENT_GLOBAL' because its select list does not include a proper use of COUNT_BIG. Consider adding COUNT_BIG(*) to select list.

Code1:

I Created View By using following Query:

Code2:

I Used the Following Query to Create an Index on the Created View "VIEW_CLIENT_GLOBAL" By using following Query:

Solution:

If you want to create a Index on the view, we need follow the rules given in the following link,

http://technet.microsoft.com/en-us/library/ms191432.aspx

If GROUP BY is specified, the view select list must contain a COUNT_BIG(*) expression, and the view definition cannot specify HAVING, CUBE, or ROLLUP.

I Altered My View with COUNT_BIG(*) AS COUNT as shown below:

Then i created the index on the view by Query which is available in "Code2" Section, it works fine.

WITH SCHEMABINDING Clause in SQL Server

In My Previous post (View in SQL Server), I’ve mentioned that a table can be deleted even though it is associated with normal view. Also we can modify that table design or drop that table without any notification or warning message.

Have a look at this simple scenario,

Here I’ve a table, Named as Employee with 3 columns Empno, Empname, Test.

I created a view with following definition,

CREATE VIEW ViewTest
AS
SELECT Empno,EmpName FROM dbo.Employee
GO
 

After created the view "ViewTest", I renaming the column EmpName to Empname2

Now If we access the views, after modified the schema of the base table, it will throw the error like

Msg 207, Level 16, State 1, Procedure ViewTest, Line 3
Invalid column name 'EmpName'.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'ViewTest' because of binding errors.
 

Also it allows me the drop the base table.

DROP TABLE Employee
GO
 

If we access the views, after drop the base table, it will throw the error like
Msg 208, Level 16, State 1, Procedure ViewTest, Line 3
Invalid object name 'dbo.Employee'.
Msg 4413, Level 16, State 1, Line 1
Could not use view or function 'ViewTest' because of binding errors.

To avoid this sort of issues, SQL Server provided a option called SCHEMABINDING. This option will bind the table and schema together, also it is restricting the schema changes against on the base table. So we can avoid the error.

The following snippet helps, how to make a view with SCHEMABINDING

CREATE VIEW ViewTest WITH SCHEMABINDING
AS
SELECT Empno,EmpName FROM dbo.Employee
GO

Now if you try to change the column name of base table, then it will throw the following error

'Employee' table
- Warning: The following schema-bound objects will be modified:
  - View 'dbo.ViewTest': schema binding will be removed.

If you want to enforce the SCHEMABINDING  to existing view, you can follow the snippet given below

ALTER VIEW ViewTest WITH SCHEMABINDING
AS
SELECT Empno,EmpName FROM dbo.Employee
GO

Hope it helps you!

View in SQL Server

What is View in SQL?

• View is a virtual table.
• it won’t occupies any memory space for the data.
• it is referencing to the other table like pointer concept in programming language.
• View definition contains only SELECT statement.
• Views simplify the user perception of the database
• View improves the data security by preventing undesired access
• View faciliates the provision of additional data independence
• View doesn’t allow ORDER BY clause in view definition.

Can You Drop a table, If it has a view?

If the view is created with SCHEMABINDING clause from Table or views then base object can’t delete otherwise you can.

Why SQL Server Doesn’t allow ORDER BY Clause in View definition?
SQL Server excludes an ORDER BY clause from a view to comply with the ANSI SQL-92 standard. Because analyzing the rationale for this standard requires a discussion of the underlying structure of the structured query language (SQL) and the mathematics upon which it is based, also if you use the ORDER BY Clause then it will lead to major performance issue, sothat by default SQL server doesn’t allow this clause in view definition.

If you try to create view with ORDER BY clause it will throw the following error.
Msg 1033, Level 15, State 1, Procedure , Line 2
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.