{"id":10884,"date":"2019-03-24T09:19:46","date_gmt":"2019-03-24T09:19:46","guid":{"rendered":"https:\/\/dbtut.com\/?p=10884"},"modified":"2019-03-24T19:20:05","modified_gmt":"2019-03-24T19:20:05","slug":"what-is-indexed-view-in-sql-server-and-how-to-create-an-indexed-view","status":"publish","type":"post","link":"https:\/\/dbtut.com\/index.php\/2019\/03\/24\/what-is-indexed-view-in-sql-server-and-how-to-create-an-indexed-view\/","title":{"rendered":"What is Indexed View in SQL Server and How To Create an Indexed View"},"content":{"rendered":"

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<\/a>“.<\/p>\n

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.<\/p>\n

Condition 1: WITH SHEMABINDING<\/h2>\n

You must use WITH SHEMABINDING Clause when creating view.<\/p>\n

What is Schemabinding?<\/h3>\n

When you set View as schemabinding, DML operations cannot be performed in the tables that View selects.<\/p>\n

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.<\/p>\n

Create a SCHEMABINDING VIEW<\/h3>\n
USE [TestDB]\nGO\nCREATE VIEW [dbo].[IndexedViewExample]\nWITH SCHEMABINDING AS\nSELECT [ID],[Name] FROM [dbo].[MyTable]\nGO<\/pre>\n
Convert a VIEW To SCHEMABINDING VIEW<\/h3>\n
USE [TestDB]\nGO\nALTER VIEW [dbo].[IndexedViewExample]\nWITH SCHEMABINDING AS\nSELECT [ID],[Name] FROM [dbo].[MyTable]\nGO<\/pre>\n
ALTER the column that the SCHEMABINDING VIEW Selects<\/h3>\n
USE [TestDB]\nGO\nALTER TABLE [dbo].[MyTable]\nALTER COLUMN ID bigint;\n<\/pre>\n
Msg 5074, Level 16, State 1, Line 3<\/em><\/span><\/p>\n
The object ‘IndexedViewExample’ is dependent on column ‘ID’.<\/em><\/span><\/p>\n
Msg 4922, Level 16, State 9, Line 3<\/em><\/span><\/p>\n
ALTER TABLE ALTER COLUMN ID failed because one or more objects access this column.<\/em><\/span><\/p>\n
\"\"<\/p>\n
Condition 2: * FROM<\/h2>\n
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.<\/p>\n
Msg 1054, Level 15, State 6, Procedure IndexedViewExample, Line 4<\/em><\/span><\/p>\n
Syntax ‘*’ is not allowed in schema-bound objects.<\/em><\/span><\/p>\n
Condition 3: Create a Unique Clustered Index<\/h2>\n
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.<\/p>\n
CREATE UNIQUE CLUSTERED INDEX UIX_IndexedViewExample \n    ON dbo.IndexedViewExample(ID);   \nGO  \n<\/pre>\n
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.<\/p>\n
Msg 1505, Level 16, State 1, Line 1<\/em><\/span><\/p>\n
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).<\/em><\/span><\/p>\n
The statement has been terminated.<\/em><\/span><\/p>\n
Condition 4: Unavailable TSQLs<\/h2>\n
You cannot use the expressions specified below in your select query when creating View.<\/p>\n