SQL Server Indexed Views in ApexSQL Data Diff

In this article, we’ll introduce the use of Indexed views in ApexSQL Data Diff.

ApexSQL Data Diff is a database development tool for comparing and synchronizing data between two databases or their backups, and for simplifying the data migrations between them.

SQL Server indexed views are views that have a unique clustered index, and they contain data that can be compared. Therefore, they are physically stored and require memory for processing. The data which is available through the standard views is not physically stored as a distinct object, so we can think of the views as the virtual tables, which contain information about certain data.

To compare data in views, by using ApexSQL Data Diff, SQL Server indexed views will need to be created.

First, we’ll create standard views, and see what result we’ll get in ApexSQL Data Diff after the comparing process.

Let’s start with creating two databases in SSMS, FruitImport2014 and FruitImport2015. In the new query window, we’ll create two tables, and execute them for both databases, using the following code:

CREATE TABLE [dbo].[Quarter1](
	[SysInfoID] [int] NOT NULL,
	[Apple] [varchar](255) NULL,
	[Orange] [varchar](255) NULL,
	[Banana] [varchar](255) NULL,
	PRIMARY KEY (SysInfoID)
	)

CREATE TABLE [dbo].[Quarter2](
	[SysInfoID] [int] NOT NULL,
	[Apple] [varchar](255) NULL,
	[Orange] [varchar](255) NULL,
	[Banana] [varchar](255) NULL,
	PRIMARY KEY (SysInfoID)
	)

Also, we’ll create standard views for created tables, and execute them for both databases, using the following code:

CREATE VIEW [dbo].[ViewQuarter1]
AS
SELECT * FROM [dbo].[Quarter1]
GO

CREATE VIEW [dbo].[ViewQuarter2]
AS
SELECT * FROM [dbo].[Quarter2]
GO

Now, when we created the tables and the views, we’ll enter some values for the columns in the tables, so that there is a data for comparison. After the data is entered for all of the tables, we can run ApexSQL Data Diff. Select the FruitImport2014 database as source, and the FruitImport2015 as destination database:

First, we’ll compare tables, and see what results they provide. Click the Options tab in the top left corner, and select the Tables option, as an object type for comparison.

Click the Compare button in the bottom right corner, and the following results will be provided. As shown in the picture below, there are two different records, Table1 and Table2. In the Data difference pane, we can find out which data is missing, and which is additional, for the chosen table.

To compare views, we’ll repeat the same procedure for set up, but instead of Tables, we’ll now select Views in the Options tab as an object for comparison, and click on the Compare button. From the provided results list, we can see that there are two identical records, ViewTable1 and ViewTable2. There are no rows, missing, or additional, as we had when we compared tables.

The results are telling us that these two views are the same, and that there is nothing to synchronize.

When there is a need to compare data inside views, indexed views in SQL Server need to be created. We’ll delete views that we created and we’ll create indexed views with schema binding like in the following code:

CREATE VIEW [dbo].[ViewQuarter1]
WITH SCHEMABINDING
AS
SELECT Apple, Orange, Banana FROM dbo.Quarter1
GO
CREATE UNIQUE CLUSTERED INDEX ID_Quarter1 ON dbo.ViewQuarter1 (Apple, Orange, Banana)

CREATE VIEW [dbo].[ViewQuarter2]
WITH SCHEMABINDING
AS
SELECT Apple, Orange, Banana FROM dbo.Quarter2 
GO
CREATE UNIQUE CLUSTERED INDEX ID_Quarter2 ON dbo.ViewQuarter2 (Apple, Orange, Banana)

When this code is executed for both databases, it will create two SQL Server indexed views. Since the data for tables is already entered, we can run ApexSQL Data Diff. The setup options are the same as previous for views, and we’ll click on the Compare button. The following results are provided:

From the comparison results we can see that there are two different records, ViewQuarter1 and ViewQuarter2. If we select ViewQuarter1, in the Results grid, missing and additional data will be shown in the Data difference pane. When indexed views are created, they could be compared, and data inside views can be synchronized in ApexSQL Data Diff, like it can be done for tables.

Before creating Indexed views, a couple of things need to be considered. The following TSQL elements can’t be used by the SELECT statement in the view definition: COUNT, OUTER joins (LEFT, RIGHT, or FULL), float *, text, ntext, image, XML, or filestream columns, ORDER BY, SUM function that references a nullable expression, etc. We mentioned a few, but the full list of recommendations is provided on this link

When creating indexed views a need to know is that contained tables can be changed only if the views are first altered or dropped. As mentioned before, they are physically stored and they require a memory for processing, so there needs to be a plan which views are the most suitable to become indexed views.

Useful resources:
Create Indexed Views
SQL Server Indexed Views – Speed Up Your Select Queries: Part 1
SQL Server Indexed Views