Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
We create Indexed Views by adding indexes to views. For detailed information about the View concept, I would recommend you read my article “What is View in SQL Server and How to Create a View“.
Views does not store data. When we convert the views to indexed view, they start to store the data. Therefore, the performance of the view is increased while the insert, update, and delete performances of the tables selected by the view decrease. Also indexed views begin to take up extra space in your database. There are some conditions-restrictions for converting views to indexed view. Let’s examine these conditions.
Condition 1: WITH SHEMABINDING
You must use WITH SHEMABINDING Clause when creating view.
What is Schemabinding?
When you set View as schemabinding, DML operations cannot be performed in the tables that View selects.
After you create the view in the schemabinding structure, if you perform a DML operation in the columns in the tables that the view selects, you will receive an error as follows.
Create a SCHEMABINDING VIEW
1 2 3 4 5 6 | USE [TestDB] GO CREATE VIEW [dbo].[IndexedViewExample] WITH SCHEMABINDING AS SELECT [ID],[Name] FROM [dbo].[MyTable] GO |
Convert a VIEW To SCHEMABINDING VIEW
1 2 3 4 5 6 | USE [TestDB] GO ALTER VIEW [dbo].[IndexedViewExample] WITH SCHEMABINDING AS SELECT [ID],[Name] FROM [dbo].[MyTable] GO |
ALTER the column that the SCHEMABINDING VIEW Selects
1 2 3 4 | USE [TestDB] GO ALTER TABLE [dbo].[MyTable] ALTER COLUMN ID bigint; |
Msg 5074, Level 16, State 1, Line 3
The object ‘IndexedViewExample’ is dependent on column ‘ID’.
Msg 4922, Level 16, State 9, Line 3
ALTER TABLE ALTER COLUMN ID failed because one or more objects access this column.
Condition 2: * FROM
You must write the columns individually instead of “* FROM” in the Select statement that will create the View. If you do not, you will receive an error as below.
Msg 1054, Level 15, State 6, Procedure IndexedViewExample, Line 4
Syntax ‘*’ is not allowed in schema-bound objects.
Condition 3: Create a Unique Clustered Index
After creating the view with schemabinding structure, you must first create a unique clustered index. After creating unique clustered index, you can create index in other columns. You can create a unique clustered index with the following script.
1 2 3 | CREATE UNIQUE CLUSTERED INDEX UIX_IndexedViewExample ON dbo.IndexedViewExample(ID); GO |
In our example, we chose to create unique clustered index on ID column. Make sure that the column you choose for Unique Clustered Index is unique in the table that View selects. Otherwise you will receive the below error.
Msg 1505, Level 16, State 1, Line 1
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘IndexedViewExample’ and the index name ‘UIX_IndexedViewExample’. The duplicate key value is (182861, C740D0288EA7C45FE0407C0A04162BDD, 12685525396).
The statement has been terminated.
Condition 4: Unavailable TSQLs
You cannot use the expressions specified below in your select query when creating View.
- COUNT
- Derived tables
- float, text, ntext, image, XML, or filestream columns
- CONTAINS and FREETEXT predicates
- CLR User-Defined Aggregate Functions
- MIN and MAX Functions
- Table variables
- Sparse columns
- ROWSET functions (OPENDATASOURCE, OPENQUERY, OPENROWSET, AND OPENXML)
- Self Joins
- Subqueries
- SUM function if it references a nullable expression
- TOP
- UNION, EXCEPT and INTERSECT Operators
- CROSS APPLY and OUTER APPLY Operators
- Table-valued functions(Inline or multi statement)
- OUTER Join Types
- Common table expression (CTE)
- OVER and ORDER BY
- CUBE, ROLLUP and GROUPING SETS Operators
- PIVOT and UNPIVOT
- OFFSET
For example, if you use “UNION, INTERSECT, or EXCEPT“, you will receive an error as follows when creating the unique clustered index.
Msg 10116, Level 16, State 1, Line 1
Cannot create index on view ‘dbo.IndexedViewExample’ because it contains one or more UNION, INTERSECT, or EXCEPT operators.
Consider creating a separate indexed view for each query that is an input to the UNION, INTERSECT, or EXCEPT operators of the original view.
If you use Common Table Expression, you will receive the error as below when creating the index.
Msg 10137, Level 16, State 1, Line 1
Cannot create index on view “dbo.IndexedViewExample” because it references common table expression “TBL_CTE”. Views referencing common table expressions cannot be indexed. Consider not indexing the view, or removing the common table expression from the view definition.
Condition 5: SET OPTIONS
You must pay attention to the “SET Options” for the views return the same result set as the table. Because different “SET Options” may have different results.
For example,
If you type SET CONCAT_NULL_YIELDS_NULL ON at the beginning of the view, the result will be null when you type ‘x’ + null in the select statement.
If you type SET CONCAT_NULL_YIELDS_NULL OFF at the beginning of the view, the result will be “x”.
You can see the “SET OPTIONS” that should be in the table in the below link. In fact, they are default configurations, but if you are using OLE DB or ODBC connection, you need to make some changes. You can see the values that must be and the default values in the following table.
Condition 6: Two-part Format
In the query in the contents of the View, you must write the Select Statements by specifying the schema name as follows. Otherwise, you will receive the following error.
1 | schemename.tablename |
Msg 4512, Level 16, State 3, Procedure IndexedViewExample, Line 4
Cannot schema bind view ‘dbo.IndexedViewExample’ because name ‘MyTable’ is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
If you use user defined functions within the view, you must also write this function by specifying the schema name.
Condition 7: GROUP BY Clause in Indexed View
If you use GROUP BY in the query, you must use COUNT_BIG (*) in the SELECT statement and not use HAVING.
For example;
1 2 3 4 5 6 7 | USE [TestDB] GO CREATE VIEW [dbo].[IndexedViewExample] WITH SCHEMABINDING AS SELECT SUM([ID]) SUM_ID,[Name] FROM [dbo].[MyTable] GROUP BY [Name] GO |
If you don’t use COUNT_BIG (*) when you use Group By when creating the view, you cannot create a unique clustered index. You will receive an error as follows.
Msg 10138, Level 16, State 1, Line 1
Cannot create index on view ‘TestDB.dbo.IndexedViewExample’ because its select list does not include a proper use of COUNT_BIG.
Consider adding COUNT_BIG(*) to select list.
Correct View Create Script While You are Using Group By Clause:
1 2 3 4 5 6 7 | USE [TestDB] GO CREATE VIEW [dbo].[IndexedViewExample] WITH SCHEMABINDING AS SELECT SUM([ID]) SUM_ID,[Name],COUNT_BIG(*) AS [Count] FROM [dbo].[MyTable] GROUP BY [Name] GO |
Msg 8661, Level 16, State 0, Line 1
Cannot create the clustered index “UIX_IndexedViewExample” on view “TestDB.dbo.IndexedViewExample” because the index key includes columns
that are not in the GROUP BY clause. Consider eliminating columns that are not in the GROUP BY clause from the index key.
Therefore, the clustered index column must be in the GROUP BY Clause.
Condition 8: Defining Alias
If you do not define the alias after using a function such as SUM in the select statement in the view, you will get an error as follows.
Msg 4511, Level 16, State 1, Procedure IndexedViewExample, Line 4 [Batch Start Line 0]
Create View or Function failed because no column name was specified for column 1.
Incorrect view creation script: The error in this script is; there is not an alias for SUM (ID).
It should be: SUM(ID) AS SUM_ID
1 2 3 4 5 | CREATE VIEW [dbo].[IndexedViewExample] WITH SCHEMABINDING AS SELECT SUM(ID),Name FROM [dbo].[MyTable] GROUP BY Name |
