creating schema binding 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

General SQL Server Forums

New to SQL Server Programming

creating schema binding view

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-18 : 00:29:31

Dear All,
we are using one view which is having around 30000 rows.
it is taking too much time to retrieve data. that's why i've decided to create an indexed view.

now i'm getting this error.
Cannot schema bind view 'view1' because name 'Table19' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.

what is the solution for this error?

Vinod
Even you learn 1%, Learn it with 100% confidence.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2008-01-18 : 00:46:09

two part naming scheme means owner.table

so if owner is dbo, change 'Table19' to 'dbo.Table19'

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-18 : 00:59:43

thank you russel...
now i'm getting another error. inside the view another view is there in join statement. is there any need to create that view with schemabinding?
error is :
Cannot schema bind view 'view1'. 'dbo.view2' is not schema bound.

Vinod
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-18 : 01:02:54

yes....
i got it....
thank you russel

Vinod
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-18 : 01:12:32

Now i'm trying to create a unique clustered index. i'm getting error.

Cannot index the view 'mydb.dbo.view1'. It contains one or more disallowed constructs.
what is meant by disallowed constructs?

Vinod
Even you learn 1%, Learn it with 100% confidence.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-18 : 01:31:29

i found the error root cause it is because of outer join. is it not possible to create index on a view which is having outer join?

Vinod
Even you learn 1%, Learn it with 100% confidence.

sunil
Constraint Violating Yak Guru

282 Posts

Posted - 2008-01-18 : 01:50:08

No, you cannot create index on view having outer join. read the following article.
http://www.microsoft.com/technet/prodtechnol/sql/2005/impprfiv.mspx?pf=true

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-01-18 : 01:54:20

i've read the article just before. now how can i replace the outer join part with another?
the code is like this
select *
FROM dbo.Table19 i LEFT OUTER JOIN dbo.Table11 t ON i.COLUMN16 = t.COLUMN1
LEFT OUTER JOIN dbo.Table13 c ON i.COLUMN17 = c.COLUMN1
LEFT OUTER JOIN dbo.TABLE20 iu ON i.COLUMN1 = iu.COLUMN1
LEFT OUTER JOIN dbo.view2 u ON iu.COLUMN4 = u.Uom_Id
LEFT OUTER JOIN dbo.Table25 mm ON i.Column1=mm.Column1
LEFT OUTER JOIN dbo.Table23 mod ON i.Column1=mod.Column1
LEFT OUTER JOIN dbo.Table117 ig ON i.Column20=ig.Column1
LEFT OUTER JOIN dbo.table22 pd on i.column1=pd.column1
WHERE i.Column16 <> '11'

Vinod
Even you learn 1%, Learn it with 100% confidence.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources