Indexed view vs. Outer join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Indexed view vs. Outer join

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-05-25 : 13:56:24

...Ok, I know you can't create an indexed view that includes an outer join (which stinks -- does anyone know if that will be fixed in a later release?).

I've got a snitz forum running, which I've heavily integrated into my user database, using a view to replace the SNITZ_MEMBERS table. The view combines columns from my existing users table with a SNITZ_MEMBERS_SUPP table which contains the snitz-specific columns.


Users
-----
user_id
login_name
password
etc...

User_Info
---------
i_users
i_countries
etc...

Countries
---------
i
name

Now, the problem is that not every user has entered their country data. So the join table user_info doesn't have entries for some users.

My view is created something like this (obviously this is only a small part, but it's the part that's giving me trouble):


select dbo.users.user_id AS MEMBER_ID,dbo.countries.name AS M_COUNTRY
  from users
    left outer join user_data on
      user_data.i_users=users.user_id
    left outer join countries on user_data.i_countries=countries.i

...which works, but it's got that blasted outer join. Can anyone see a way to get rid of the outer join and convert this to an indexed view? My only thought it so leave M_COUNTRY out of it, and find the places in snitz that use that, and have those bits of code join my indexed view against the user_data and countries table. Doable, but I'm hoping for a magic wand solution.

Thanks
-b

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-05-25 : 15:16:53

Ok, I bit the bullet and just moved my outer joins into the queries and out of the views.

However, I've got a really nasty problem now.

As soon as I create any index on the view, all of my snitz ASP pages throw the error:


Microsoft OLE DB Provider for SQL Server (0x80040E14)
UPDATE failed because the following SET options have incorrect settings: 'ARITHABORT'.

...Simply removing the index fixes the view. I imagine that the update in question is the "update [view] set M_LASTHEREDATE='20020524XXYYXX'" query.

Any ideas? It would be nice to get indexed views going here, but I always hate it when the arithabort / ansi_nulls quagmire pops up.

Thanks
-b

Edited by - aiken on 05/25/2002 15:18:01

jasper_smith
SQL Server MVP & SQLTeam MVY

846 Posts

Posted - 2002-05-25 : 17:33:48

Have a look in BOL for the SET options required for Indexed Views. All of them except ARITHABORT are set correctly by default by either ODBC or OLEDB connections. In order to be able to do anything to the view then SET ARITHABORT must be set to ON . It can also cause things like maintenace plans to fail depending on what settings you have (rebuild indexes,check integrity etc).From BOL

When creating and manipulating indexes on computed columns or indexed views, the SET options ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER, ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS must be set to ON. The option NUMERIC_ROUNDABORT must be set to OFF.
If any of these options are not set to the required values, INSERT, UPDATE, and DELETE actions on indexed views or tables with indexes on computed columns will fail. SQL Server will raise an error listing all the options that are incorrectly set. Also, SQL Server will process SELECT statements on these tables or indexed views as though the indexes on computed columns or on the views do not exist.

HTH
Jasper Smith

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-05-26 : 13:25:37

Thanks... I had run across the ARITHABORT thing at least and used sp_configure to set the default ARITHABORT to on. However, I'm still experiencing the same problem.

I'm suspecting that somewhere, the ASP code is explicitly setting ARITHABORT off. Can anyone think of a reason for that, and is it likely that I'll break other things if I explicitly set it to on? In particular, I'm confused about the whole user options associated with stored procedures (and tables?)... do you always need to run a procedure with the user options set to the way they were when the procedure was created, or does the procedure take care of that itself?

I had a nasty run-in with user options before, so I'm kind of scared of them now. I've done some searching but can't seem to find a good article discussing them and their (ab)use.

Thanks
-b

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-05-28 : 15:32:54

quote:

...Ok, I know you can't create an indexed view that includes an outer join (which stinks -- does anyone know if that will be fixed in a later release?).

It's always painful the first time you decide to implement materialized ( indexed ) views. Most developers have never had occasion to care about SET options and wonder why in the world MSFT requires them set in a certain fashion, and how they get set in the first place. Most developers also wonder why the rules on creating materialized views are so restrictive.

Both questions are answered by considering what a materialized view is - the results of a SELECT statement ( the view definition ) saved to data pages and automatically maintained in those data pages as the underlying base tables change. That creates a host of problems. With regard to SET options the same DML may change data differently with different settings. if ANSI NULLs are off, for example, running:

update {table}
set col1 = col1 + col2

where col2 is null would not set col1 to null. But it would if ANSI NULLS were on.

The upshot is, since SQL Server is persisting the view results to disk, it must choose one set of SET options and enforce their usage everywhere. Thus the onerous requirements.

setBasedIsTheTruepath
<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources