Sql-server – SQL Server 2008 Indexed View

schemasql serversql-server-2008t-sql

I am attempting to add schemabinding to a view in SQL Server 2008, but I am getting an error of

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

I am using Openquery because I am querying a linked server and this is the way I must query it. Is there a workaround for this?

This was the syntax I tried with my view

Create View dbo.Test
With Schemabinding
As

  Select * FROM OPENQUERY(191.183.18.101, 'Select * from ls1prod.hrinfo')

Go

Best Answer

The documentation for Indexed Views is pretty clear about what's allowed, and what's not allowed.

Specifically:

ROWSET functions (OPENDATASOURCE, OPENQUERY, OPENROWSET, AND OPENXML)

There's no 'workaround' because it's not allowed.

Related Solutions

Sql-server – How to help SQL Server recognize the indexed view column is NOT NULL-able

user_id, currency_id, and transaction_amount are all defined as NOT NULL columns in dbo.transactions

It looks to me that SQL Server has a blanket assumption that an aggregate can produce a null even if the field(s) it operates on are not null. This is obviously true in certain cases:

create table foo(bar integer not null);
select sum(bar) from foo
-- returns 1 row with `null` field

And is also true in the generalized versions of group by like cube

This simpler test case illustrates the point that any aggregate is interpreted as being nullable:

CREATE VIEW dbo.balances
with schemabinding
AS
SELECT
      user_id
    , sum(1)   AS balance_amount
FROM dbo.transactions
GROUP BY
      user_id
;
GO

IMO this is a limitation (albeit a minor one) of SQL Server - some other RDBMSs allow the creation of certain constraints on views that are not enforced and exist only to give clues to the optimizer, though I think 'uniqueness' is more likely to help in generating a good query plan than 'nullability'

If the nullability of the column is important, perhaps for use with an ORM, consider wrapping the indexed view in another view that simply guarantees the non-nullability using ISNULL:

CREATE VIEW dbo.balancesORM
WITH SCHEMABINDING
AS
SELECT 
    B.[user_id],
    B.currency_id,
    balance_amount = ISNULL(B.balance_amount, 0),
    transaction_count = ISNULL(B.transaction_count, 0)
FROM dbo.balances AS B;

Sql-server – Performance implications of using OPENQUERY in a view

Summary

Let the linked server do as much as possible.
It is impossible for SQL Server to optimise a query on a linked server, even another SQL Server

Long

The key factor is where the query runs.

In this case, it is a trivial SELECT so all rows from a table will be sent down the wire. It doesn't matter.

When you add JOINs and WHEREs then it can matter. You want SQL Server to allow the linked server to do as much filtering as possible to reduce the size of the data coming over the network.

For example, the 2nd case here should be more efficient.

SELECT *
FROM OPENQUERY(<linked server>, 
            'SELECT <column list> FROM MyTable') T1
     JOIN
     SomeLocalTable T2 ON ...
WHERE T1.foo = 'bar'

SELECT *
FROM OPENQUERY(<linked server>, 
           'SELECT <column list> FROM MyTable WHERE foo = ''bar''')
     JOIN
     SomeLocalTable T2 ON ...

A limitation of OPENQUERY is that you can't parametrise: so you need dynamic SQL to add WHERE clauses etc.

Linked servers performance can be affected by sp_serveroption. The setting collation compatible says it all

If this option is set to true, SQL Server assumes that all characters in the linked server are compatible with the local server, with regard to character set and collation sequence (or sort order). This enables SQL Server to send comparisons on character columns to the provider. If this option is not set, SQL Server always evaluates comparisons on character columns locally.

That is, try not to let SQL Server process the data locally.

Note: On my foo = 'bar' 2nd example above, the filter is sent to the linked server because it just a string constant to SQL Server. The real WHERE clause in the first example may or not be sent remotely.

Finally, I've also found that staging the data into a temp table and joining that onto local tables is often better then joining directly onto the OPENQUERY.