Indexed View Creation Problem:

Question

Post reply

Indexed View Creation Problem:

TK SAHU

SSC Veteran

Points: 272
More actions
May 24, 2006 at 3:36 am

#113809

Problem-1
I want to create an indexed view by using the following query :
Create view View1
with SCHEMABINDING as
SELECT cnt = count_big(*), repair_cost = sum(isnull(trbic.repair_cost,0)), cost_value = sum(isnull(trboc.cost_value,0)), trbi.referral_id
FROM dbo.tbl_repair_bid_info trbi WITH (NOLOCK)
LEFT JOIN dbo.tbl_repair_bid_vendor trbv WITH (NOLOCK)
ON trbi.repair_bid_id = trbv.repair_bid_id
LEFT JOIN dbo.tbl_repair_bid_itemcost trbic WITH (NOLOCK)
ON trbv.repair_vendor_id = trbic.repair_vendor_id
LEFT JOIN dbo.tbl_repairbid_others_cost trboc WITH (NOLOCK)
ON trbi.referral_id = trboc.referral_id
GROUP BY trbi.referral_id
go
CREATE UNIQUE CLUSTERED INDEX Idx_View1 ON View1(referral_id)
I found that repair_cost, cost_value columns numeric columns which may null values. So I have used ISNULL function. But still then it throw the following error:
Cannot index the view 'pubs.dbo.Tap1'. It contains one or more disallowed constructs.
Is ther any alternative solution to create the index view ?
Problem-2
I am trying to create a index view on a table, which was created when 'SET ANSI_NULLS' was OFF. Hence after creating the view, while trying to create the Unique Clustured Index it throws the following error :
Cannot create index. Object 'tbl_analysis_loan_info' was created with the following SET options off: 'ANSI_NULLS.'.
Is ther any solution to the same witout recreating the table ?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Ajit-297150 Mr or Mrs. 500 Points: 593 More actions · Answer 1

Hope this help.

Creating An Indexed View:

----------------------------------------------

The CREATE INDEX statement must meet these requirements in addition to the

normal CREATE INDEX requirements:

The user executing the CREATE INDEX statement must be the view owner.

These SET options must be set to ON when the CREATE INDEX statement is

executed:

ANSI_NULLS

ANSI_PADDING

ANSI_WARNINGS

ARITHABORT

CONCAT_NULL_YIELDS_NULL

QUOTED_IDENTIFIERS

The NUMERIC_ROUNDABORT option must be set to OFF.

The view cannot include text, ntext, or image columns, even if they are not

referenced in the CREATE INDEX statement.

If the SELECT statement in the view definition specifies a GROUP BY clause,

the key of the unique clustered index can reference only columns specified

in the GROUP BY clause.

TK SAHU SSC Veteran Points: 272 More actions · Answer 2

Mr Ajit,

Thanks for the reply. I have tried before whatever you have mantioned. It was not working. Any other solution to resolve the above two problems.

stax68 SSChampion Points: 11711 More actions · Answer 3

I can't see a reason. I assume none of the columns referenced are indeterministic computed or view columns? Any float (imprecise) data? - I'm clutching at straws now...

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

blakeyu SSC Enthusiast Points: 120 More actions · Answer 4

I got same problem, any reseach result now? how to solve.

blakeyu SSC Enthusiast Points: 120 More actions · Answer 5

Very appriciate if you can help. my script as below.

SET SHOWPLAN_ALL OFF

GO

SET NUMERIC_ROUNDABORT OFF

GO

SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL ON

GO

SET ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON

GO

DROP VIEW VW_wanted

GO

CREATE VIEW VW_wanted

WITH SCHEMABINDING

AS

select t1.wanted_id ,

t1.local_warrant_arrest_no ,

t4.offence_id ,

t4.police_report_number,

t1.issuing_date,

t1.warrant_status,

t5.name as offence_classification,

v1.name as person_name,

v1.pid_no as person_pid,

t2.person_id

from dbo.eads_person_wanted t1

left join dbo.eads_relation_person_wanted t2 on t2.wanted_id = t1.wanted_id

left join dbo.eads_person v1 on v1.person_id = t2.person_id

left join dbo.eads_relation_offence_wanted t3 on t3.wanted_id = t1.wanted_id

left join dbo.eads_offence t4 on t4.offence_id = t3.offence_id

left join dbo.eads_code_offence t5 on t5.code = t4.offence_classification

-- Create UNIQUE CLUSTERED index on the view

CREATE UNIQUE CLUSTERED INDEX IVW_wanted

ON VW_wanted(wanted_id, local_warrant_arrest_no, offence_id,police_report_number,

issuing_date, warrant_status, offence_classification, person_name,person_pid,person_id)

GO

Jeff Moden SSC Guru Points: 1004354 More actions · Answer 6

Unless it is a homework problem, I see no practical reason to index the view... the underlying indexes of the tables will inherently be used during normal view resolution.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gift Peddie SSC Guru Points: 73570 More actions · Answer 7

Jeff is right and ANSI SQL limit for OUTER JOIN is four you have five and Sums in an indexed view cannot be nullable. Try the link below for more details. Hope this helps.

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

Kind regards,

Gift Peddie

Kind regards,
Gift Peddie