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 

Nato
Starting Member

30 Posts
Posted - 2003-08-05 : 08:01:06
I want to create an indexed view. I am trying to create the following view:

create  View Web_Products_Date_Temp
WITH SCHEMABINDING
AS
SELECT Datepart(year,Date) AS Stat_Date, Web_Product, SUM(Web_Product) AS Sum_Web_Product 
FROM Stats_Web_Product
WHERE Web_Product <> 0 
AND IP_Address <> '62.177.158.114' 
AND Date >= '02/01/2003' 
AND Date <= '08/01/2003' 
AND Web_Platform = 2 
GROUP BY Datepart(year,Date), Web_Product


But SQL Analyser keeps telling me that:

"Cannot schema bind view 'Web_Products_Date_Temp' because name 'Stats_Web_Product' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself."

I dont fully understand this, can anyone help me.

Thanks

robvolk
Most Valuable Yak

15732 Posts
Posted - 2003-08-05 : 08:32:22
You can't have GROUP BY in an indexed view. Also, you've already indexed Stats_Web_Product with a clustered index. An indexed view is used to order data in tables that are unordered/nonclustered.
Go to Top of Page

SamC
White Water Yakist

3467 Posts
Posted - 2003-08-05 : 08:49:25
How about using an inline function? Inline functions support GROUP BY.

Sam
Go to Top of Page

Nato
Starting Member

30 Posts
Posted - 2003-08-05 : 09:01:26
quote:
Originally posted by robvolk

You can't have GROUP BY in an indexed view. Also, you've already indexed Stats_Web_Product with a clustered index. An indexed view is used to order data in tables that are unordered/nonclustered.


robvolk, I was looking for an Indexed View becuase the result set I am looking for, has a lot of data that will never change. Old data will always remain the same. So my research tells me that an Indexed view is perfect for this.

So I can use Group By, in a view, but not in an Indexed View. That sucks.
Go to Top of Page

mohdowais
Sheikh of Yak Knowledge

1456 Posts
Posted - 2003-08-05 : 09:36:58
Why dont you put aggregated data into an archive table? You could run a job every night or once a week during non-peak hours to do that for you. And if necesary, create a view to filter results from that table. You could extend this concept a lot further based on your requirements, for example, you could add records into the table for each day, and then be able to present a report on a weekly, monthly or yearly basis.

Owais
Go to Top of Page

Nato
Starting Member

30 Posts
Posted - 2003-08-05 : 09:58:56
Owais

Thats an interesting suggestion, is that best way to deal with Large tables. I pressume that 5 million rows in a table is not that biggest table SQL Server has ever known.

It just seems, from my understanding of things, that Indexed Views, are perfect for what I am trying to do.

But I get a SCHEMABINDING error, and I cant find any good information on that.

Thanks

Philip
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-08-05 : 11:03:36
Hold on, there....

Don't forget that if you have proper indexes on the underlying TABLES in a view, querying the View will make use of those indexes.. It doesn't execute the entire view and then filter later -- SQL is pretty smart about that.

I haven't really seen a big need for indexed views yet -- I'm sure they exist, but i haven't seen the need. If the underlying tables are properly indexed, and the Views are written correctly and efficeintly (i.e., no FULL OUTER JOINS ), you should have no trouble.

Long story short: I would focus on making sure your tables are indexed properly and your Views are efficiently written. Feel free to post from SQL, we can take a look.

By the way, in your case you may benefit from creating a computed column called "Year" in your logs table, which can then be indexed.

Also ( i just noticed this):

quote:

SELECT Datepart(year,Date) AS Stat_Date, Web_Product, SUM(Web_Product) AS Sum_Web_Product
FROM Stats_Web_Product
WHERE Web_Product <> 0
AND IP_Address <> '62.177.158.114'
AND Date >= '02/01/2003'
AND Date <= '08/01/2003'
AND Web_Platform = 2
GROUP BY Datepart(year,Date), Web_Product


Why are you GROUPING by and SUMMING the same column? what results are you trying to return? If you just want total numbers per year you shouldn't include Web_Product in your SELECT or in your GROUP BY.


- Jeff
Go to Top of Page

robvolk
Most Valuable Yak

15732 Posts
Posted - 2003-08-05 : 19:29:03
Hmmmmm, it seems I'm utterly WRONG, you CAN have GROUP BY in an indexed view. BUT...all members of the SELECT list must be deterministic. I have a feeling that one or more of the functions in the query are non-deterministic and will not let the view be created. If you look in Books Online under "Indexed views" and "CREATE VIEW" you'll get a list of things that will disallow an indexed view from being created. You may be able to write it in such a way so that you can have an indexed view, but you really don't need it.

As Jeff already mentioned, proper indexes should go a long way towards making the query fast. Clustering on the date column like I suggested here:

Anyway:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=28170

...will allow the optimizer to access those rows very quickly. If you haven't tested it yet I suggest you try it first.

Owais's suggestion is also a good one; if the regular query still doesn't perform like you want it to, set up a job to refresh the summary table every day, and base your queries on the summary table instead. I did this a number of times in some old apps, and it worked really well (because the query to generate the summary took 20 minutes to run)
Go to Top of Page

byrmol
Shed Building SQL Farmer

1591 Posts
Posted - 2003-08-05 : 20:15:33
Nato,

For a start the error message you are getting tells you exactly what the problem is... Names must be in two-part format

That is owner.object or in your case dbo.Stats_Web_Product

DavidM

"SQL-3 is an abomination.."
Go to Top of Page

shsmonteiro
Constraint Violating Yak Guru

290 Posts
Posted - 2003-08-05 : 20:16:12
Well, not entering in the what is best discussion, lets point something the cause for your error:

Base table names in indexed views MUST USE TWO-PART naming, and I notice that yours is not using it.

Also remember to set ANSI_NULLS and QUOTED_IDENTIFIER to ON when creating the view. Also, the table you're referencing to in the view, should be create with ANSI_NULLS ON.

As I understand, you're not using any non-deterministic function. (Except Datepart is non-deterministic, what I do not believe.)

regards,




Sérgio Monteiro
Trust in no Oracle
Go to Top of Page

Nato
Starting Member

30 Posts
Posted - 2003-08-06 : 06:25:56
OK, thanks for all the feedback guys/gals. I am still sticking with a Indexed View, primarly because of this article:

[url]http://www.sqlteam.com/item.asp?ItemID=1015[/url]

I have made the changes outlined above, and am not getting this error:

Index on view 'Web_Stats.dbo.Web_Products_Date' cannot be
created because the view requires a conversion involving dates or variants.


Which is frustration because there is nothing in books online which says that Dates are an issue. any ideas?
Go to Top of Page

shsmonteiro
Constraint Violating Yak Guru

290 Posts
Posted - 2003-08-07 : 19:32:54
I think that the where clause is the problem: SQL Server is performing a cast of your string values to datetime value. And as the erro messages points it is a not allowed operation. Could you simply drop it and use when selecting from the view.

regards,

Sérgio Monteiro
Trust in no Oracle
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site