Showing posts with label property. Show all posts
Showing posts with label property. Show all posts

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...
>

Sunday, February 19, 2012

Index and constraint on uniqueidentifier field

Hi,
I have made an index on a uniqueidentifier field.
Now I don't want duplicate values.
Should I set the "is unique" property to true or add an contraints on this
field?
Thanks!if you use a default of newid() you are guaranteed uniqueness, or the
chances of getting a duplicate is miniscule.
You can add a unique index to this column,
create unique index test on tablename(uniquecolumnname)
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Arjen" <boah123@.hotmail.com> wrote in message
news:dcq6a6$v4u$1@.news5.zwoll1.ov.home.nl...
> Hi,
> I have made an index on a uniqueidentifier field.
> Now I don't want duplicate values.
> Should I set the "is unique" property to true or add an contraints on this
> field?
> Thanks!
>|||Let's say that I don't generate an id with newid().
I create an unique index and I add one id two times. Do I receive an error
when adding it for the second time?
Thanks!
"Hilary Cotter" <hilary.cotter@.gmail.com> schreef in bericht
news:euqubdBmFHA.3304@.tk2msftngp13.phx.gbl...
> if you use a default of newid() you are guaranteed uniqueness, or the
> chances of getting a duplicate is miniscule.
> You can add a unique index to this column,
> create unique index test on tablename(uniquecolumnname)
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Arjen" <boah123@.hotmail.com> wrote in message
> news:dcq6a6$v4u$1@.news5.zwoll1.ov.home.nl...
>|||You bet!
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Arjen" <boah123@.hotmail.com> wrote in message
news:dcq9h6$tot$1@.news6.zwoll1.ov.home.nl...
> Let's say that I don't generate an id with newid().
> I create an unique index and I add one id two times. Do I receive an error
> when adding it for the second time?
> Thanks!
>
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> schreef in bericht
> news:euqubdBmFHA.3304@.tk2msftngp13.phx.gbl...
>|||If you don't mind letting the server assign new ids sequentially, then
re-define the column as an identity.
"Arjen" <boah123@.hotmail.com> wrote in message
news:dcq6a6$v4u$1@.news5.zwoll1.ov.home.nl...
> Hi,
> I have made an index on a uniqueidentifier field.
> Now I don't want duplicate values.
> Should I set the "is unique" property to true or add an contraints on this
> field?
> Thanks!
>