Improve performance of View created referencing table on a different server using OPENROWSET

• sql server developer

  SSCarpal Tunnel

  Points: 4746

  More actions

  August 23, 2012 at 5:45 pm

  #147301

  Hi All,

  I would like to improve the performance of view created referencing table on a different server using OPENROWSET

  I’ve created a view say, ViewA in ServerA that referances TableB in ServerB.

  CREATE VIEW [dbo].[ViewA]

  AS

  SELECT Col1,Col2,Col3 FROM

  OPENROWSET('SQLOLEDB','ServerB';'Login';'Pass',

  'SELECT Col1,Col2,Col3 FROM DB.dbo.TableA

  ')

  The view is running slow so inorder to improve performance I tried to create an index and ended up with the below error, saying cannot create index without SCHEMABINDING

  CREATE UNIQUE CLUSTERED INDEX IX_ViewA ON [dbo].[ViewA] (Col1)

  Error:

  Msg 1939, Level 16, State 1, Line 1

  Cannot create index on view 'ViewA' because the view is not schema bound.

  When I try to create schema binding using ‘WITH SCHEMABINDING’ getting below error

  Error:

  Msg 1054, Level 15, State 3, Procedure vPRSummaryWBSList, Line 23

  Syntax 'Openrowset/Openquery/Opendatasource' is not allowed in schema-bound objects.

  Now my questions how can I improve the performance of the view created referencing table on a different server using OPENROWSET if I cannot have SCHEMABINDING which is required for INDEX?

  I really appreciate if someone could assist in improving the performance of this view

  Thanks in advance!