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)  Index view

Author  Topic  Murali Starting Member 4 Posts Posted - 2001-09-04 : 01:52:58 I have created following view with schema binding as CREATE VIEW SALES_ANALYSIS_BYM WITH SCHEMABINDING AS select s.period_id, s.location_id, s.customer_id, s.rep_id, s.products_id, s.a_sale_amount, s.sale_amount, dbo.getprevmonthdata( s.period_id, s.location_id, s.products_id, s.rep_id, s.customer_id ) as prev_month_actual, dbo.getprev_year_same_month_actual( s.period_id, s.products_id, s.customer_id, s.rep_id, p.period_year ) as perv_year_same_month_actual, dbo.getactual_ytd( s.period_id, s.products_id, s.customer_id, s.rep_id, p.period_year ) as actual_ytd, dbo.getbgt_ytd( s.period_id, s.products_id, s.customer_id, s.rep_id, p.period_year ) as budgeted_ytd, dbo.getprev_year_ytd( s.period_id, s.products_id, s.customer_id, s.rep_id, p.period_year ) as prev_year_actual_ytd, c.customer_name, l.location_name, prod.product_name, p.period_name, sr.first_name+', '+sr.last_name as srep_name, pline.product_line_name, pline.product_line_id, b.brand_id, b.brand_name from dbo.sales_fact s, dbo.period p, dbo.products prod, dbo.sales_rep sr, dbo.customer c, dbo.location l, dbo.product_line pline, dbo.brands b where p.period_id = s.period_id and l.location_id = s.location_id and c.customer_id = s.customer_id and sr.rep_id = s.rep_id and prod.product_id = s.products_id and pline.product_line_id = b.product_line_id and b.brand_id = prod.brand_id View has been created successfully with out any problem then I have tried to create index on above with following syntax GO SET ARITHABORT ON CREATE UNIQUE CLUSTERED INDEX ivSALES_ANALYSIS_BYM ON SALES_ANALYSIS_BYM (period_id,location_id,customer_id,rep_id,products_id) I have received following error when i treid to create index on SALES_ANALYSIS_BYM view per return following message has come Server: Msg 1956, Level 16, State 1, Line 1 Index on view 'sqlchartapp.dbo.SALES_ANALYSIS_BYM' cannot be created because the view uses a nondeterministic user-defined function. i have used functions to create view which mandatoy in my case syntax of one of the function used in above view ------------------------------- CREATE function GETPREVMONTHDATA( @a_period_id int, @A_location_id int, @A_product_id int, @a_rep_id int, @a_customer_id int ) returns float(2) with schemabinding AS BEGIN return( select sum(a_sale_amount)from dbo.sales_fact where period_id=@A_period_id-1 and location_id=@A_location_id and products_id=@A_Product_id and Rep_id=@A_rep_id and customer_id=@a_customer_id ) END ---------------------------------------- Is there any way to create index on above view? Murali