Home Microsoft SQL Server DigiMailing Contact

Replicating MSSQL Server views


Since Microsoft SQL Server 2005 there is an option to replicate views to a table on a subscriber database. This is very usefull when you want to replicate data with an advanced filter. To replicate those views you have to make sure that the views commited to a few demands:

  1. Verify the setting of ANSI_NULLS and QUOTED_IDENTIFIER  is correct.
  2. Verify the view definition is deterministic.
  3. Create the view using the WITH SCHEMABINDING option.
  4. Verify your session's SET options are set correctly as shown in the table below before creating the unique clustered index on the view.
  5. Create the unique clustered index on the view.

The COLUMNPROPERTY function can be used to check the value of IsPrecise and IsDeterministic on an existing table or view with the sample code: 

SELECT object_id('<viewname>'), COLUMNPROPERTY(object_id('<viewname>'),'<columnname>','IsPrecise')
SELECT object_id('<viewname>'), COLUMNPROPERTY(object_id('<viewname>'),'<columnname>','IsDeterministic')

Than you can alter your views to make the bound to the schema with

SELECT [column 1], [column 2] FROM dbo.tablename

 After that you can place your index on the query with

CREATE UNIQUE CLUSTERED INDEX viewname_pk ON viewname ([Column 1])

With those things done you are ready to setup replication. The most easy way is to follow the wizard but do not execute the script. Just save the script to execute is manually because you have to make some changes in it. You have to change "indexed view schema only" to "Indexed View logbased" as in the sample below. Than your all done!

use [to_repl_db]
exec sp_addarticle @publication = N'Sample_publ', @article = N'viewname', @source_owner = N'dbo',
@source_object = N'v_rvdg', @type = N'indexed view schema only', @description = null, @creation_script = null,
@pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'viewname', @destination_owner = N'dbo'

exec sp_addarticle @publication = N'Sample_publ', @article = N'viewname', @source_owner = N'dbo',
@source_object = N'viewname', @type = N'Indexed View logbased', @description = null, @creation_script = null,
@pre_creation_cmd = N'drop', @schema_option = 0x0000000008000001, @destination_table = N'view_name', @destination_owner = N'dbo'


Last revision

  Query a named instance
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash