Indexed View - 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

Author

Topic

eisenpony
Starting Member

3 Posts

Posted - 2009-01-29 : 16:55:51

Hi, I'm trying to create an indexed view but having trouble forming my query.

I have two tables
Events
IpAddresses

Events contains information about web service uses. It has the following schema

PK [ID] int
[IP] varchar
[TimeStamp] datetime
[Name] varchar
[Description] varchar

IPAddresses contains a list of IP addresses with mappings to names. It also has an ignore flag so certain IP addresses can be ignored. It has the following schema

PK [ID] int
[IP] varchar
[Name] varchar
[Ignore] bool

My view should include all events with IP addresses that do not appear in the IPAddresses table, and all IP addresses that do not have the Ignore flag set to true. In addition, it would be nice if the view used the Name from the IPAddresses table if there is an entry, but I'm not concerned about that functionality.

I've got a simple SQL Query for this right now, but I had to use a subquery to determine if the IP should be ignored. Since I want this view indexed, the sub query isn't allowed. Any ideas?

CREATE VIEW dbo.RealEvents WITH SCHEMABINDING
AS
SELECT
a.[ID],
a.[Time_Stamp],
a.[IP], 
a.[Name],
a.[Description]

FROM	[dbo].[Events] a 

WHERE	a.[IP] NOT IN (SELECT [IP] FROM [dbo].[IPAddresses] WHERE [Ignore] = 1)

<--- Spider, for compatibility

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-29 : 17:10:10

You can't have Outer joins or Subquery in indexed view. Is there any issue with queries so you have to create indexed view?

eisenpony
Starting Member

3 Posts

Posted - 2009-01-29 : 17:15:31

I want to use this view in other SQL query to alleviate some of the challenges involved with an evolving schemata. (Adding additional fields to the [Events] table.) There is a lot of data which is used for a report. I want to glean as much performance as possible.

<--- Spider, for compatibility

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-29 : 17:29:31

Yes you can take advantages of it but read this:
http://www.sqlteam.com/article/indexed-views-in-sql-server-2000

eisenpony
Starting Member

3 Posts

Posted - 2009-01-29 : 17:33:16

Thanks sodeep, I will review this. I just finished reading a slightly more thorough discussion of indexed views here http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx

I was just looking for assistance writing this simple query as I can't seem to avoid violating one of the view restrictions.

<--- Spider, for compatibility

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources