June 9, 2009 at 12:28 am
HI,
I'm creating indexed view:
create view vw_userdata
WITH SCHEMABINDING
AS
SELECT login_ID
, dbo.fxn_get_userfullname(login_id) as username
FROM tblUsersdata
it's giving the error like
Cannot schema bind view 'vw_userdata'. 'dbo.fxn_get_userfullname' is not schema bound.
can any one help me out this.
in BOL:
What's new for Indexed Views in SQL Server 2005?
AS:
Scalar expressions and user-defined functions (UDFs).
Scalar expressions and user-defined functions (UDFs). For example, given a table T(a int, b int, c int) and a scalar UDF dbo.MyUDF(@x int), an indexed view defined on T can contain a computed column such as a+b or dbo.MyUDF(a).
🙂
June 9, 2009 at 1:58 am
vrabhadram (6/9/2009)
HI,I'm creating indexed view:
create view vw_userdata
WITH SCHEMABINDING
AS
SELECT login_ID
, dbo.fxn_get_userfullname(login_id) as username
FROM tblUsersdata
it's giving the error like
Cannot schema bind view 'vw_userdata'. 'dbo.fxn_get_userfullname' is not schema bound.
The error should tell you what is the problem. While you use the WITH SCHEMABINDING in the CREATE VIEW statement, it's obviously missing in your CREATE FUNCTION.
[font="Verdana"]Markus Bohse[/font]
June 9, 2009 at 11:43 pm
Thanks for you reply,
i have modified the function with the schema binding, it is useful for me.
but when i'm creating index getting this error
Cannot create index on view "vw_userdata" because function "fxn_get_userfullname" referenced by the view performs user or system data access.
🙂
June 12, 2009 at 1:03 pm
Any functions referenced in an indexed view must be deterministic; deterministic functions return the same value each time they’re invoked with the same arguments.
I think your function is not deterministic with respect to its parameters - which means the results may change as the data changes - so any view using it cannot be indexed.
MJ
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply