Wednesday, March 21, 2012

Index on UDT

Hi,
I wanted to create an index on a property of my UDT and used this DDL:
create table t2(
c1 int identity,
c2 point,
c3 as c2.X persisted,
c4 as c2.Y persisted)
go
But I get this error:
Msg 4936, Level 16, State 1, Line 1
Computed column 'c3' in table 't2' cannot be persisted because the column is
non-deterministic.
X and Y are properties of Point UDT, but I cannot use
SqlFunction(IsDeterministic:=True) for that.
Any help would be greatly appreciated.
LeilaLook up SqlMethodAttribute in MSDN.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23fifF85LGHA.2320@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I wanted to create an index on a property of my UDT and used this DDL:
> create table t2(
> c1 int identity,
> c2 point,
> c3 as c2.X persisted,
> c4 as c2.Y persisted)
> go
> But I get this error:
> Msg 4936, Level 16, State 1, Line 1
> Computed column 'c3' in table 't2' cannot be persisted because the column
> is non-deterministic.
> X and Y are properties of Point UDT, but I cannot use
> SqlFunction(IsDeterministic:=True) for that.
> Any help would be greatly appreciated.
> Leila
>|||I used this attribute for X property but generated error: This attribute is
not valid on this declaration type.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:%23MLKkb9LGHA.500@.TK2MSFTNGP15.phx.gbl...
> Look up SqlMethodAttribute in MSDN.
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23fifF85LGHA.2320@.TK2MSFTNGP11.phx.gbl...
>|||You need to set it on the get or set method of the property independently.
e.g.:
public int X
{
[SqlMethodAttribute(...)]
get
{
//...
}
set
{
//...
}
}
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23AgVJVBMGHA.964@.tk2msftngp13.phx.gbl...
>I used this attribute for X property but generated error: This attribute is
>not valid on this declaration type.
> "Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
> news:%23MLKkb9LGHA.500@.TK2MSFTNGP15.phx.gbl...
>|||Thanks indeed :-)
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OBV22nCMGHA.2628@.TK2MSFTNGP15.phx.gbl...
> You need to set it on the get or set method of the property independently.
> e.g.:
> public int X
> {
> [SqlMethodAttribute(...)]
> get
> {
> //...
> }
> set
> {
> //...
> }
> }
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23AgVJVBMGHA.964@.tk2msftngp13.phx.gbl...
>

No comments:

Post a Comment