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...
>
Showing posts with label property. Show all posts
Showing posts with label property. Show all posts
Wednesday, March 21, 2012
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!
>
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!
>
Subscribe to:
Posts (Atom)