NON-DETERMINISTIC?

I am trying to create a unique constraint on a computed column. I've tried
unique index, too, but they both fail w/this error:
Server: Msg 1933, Level 16, State 1, Line 2
Cannot create index because the key column 'msgID' is non-deterministic or
imprecise.
ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had to
use REPLACE in order to get rid of the date characters like MM/DD/YY. the
formula for MsgID is: (rtrim([endpoint]) +
replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
BOL says 1) all functions referenced by the expression are deterministic and
precise. 2) all columns referenced in the expression come from the table
containing the computed column and 3) no column reference pulls data from
multiple rows.
All of which I believe I'm good on. Each of these are SET ON:
ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
and NUMERIC_ROUNDABORT is SET OFF.
can somebody help me find what I'm missing?
-- LynnCan you give your table structure and some sample data?
http://www.aspfaq.com/5006
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>I am trying to create a unique constraint on a computed column. I've tried
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had
> to
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic
> and
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Also, if you create a primary key or unique constraint on the three base
columns, do you really need the computed column to be explicitly unique
(since it should be unique by definition anyway)? I am often amazed at this
desire to store computed values when you don't have to; views and queries
could easily construct this value on select instead of storing redundant
data...
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>I am trying to create a unique constraint on a computed column. I've tried
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had
> to
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic
> and
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Lynn,
The dateformat "1" uses a cutoff year which makes it imprecise.
The following example shows the problem and the solution.
create table #t(mydate datetime not null
,displaydate as convert(varchar(8),mydate,1)
)
create unique index someindex on #t(displaydate)
create table #t2(mydate datetime not null
,displaydate as
substring(convert(varchar(10),mydate,101
),1,6)+substring(convert(varchar(10)
,mydate,101),9,2)
)
create unique index someindex2 on #t2(displaydate)
drop table #t
drop table #t2
Gert-Jan
Lynn wrote:
> I am trying to create a unique constraint on a computed column. I've trie
d
> unique index, too, but they both fail w/this error:
> Server: Msg 1933, Level 16, State 1, Line 2
> Cannot create index because the key column 'msgID' is non-deterministic or
> imprecise.
> ideally, i just want endpoint+MMDDYY+orderno in the column, but I've had t
o
> use REPLACE in order to get rid of the date characters like MM/DD/YY. the
> formula for MsgID is: (rtrim([endpoint]) +
> replace(convert(varchar(8),[exectime],1)
,'/','') + rtrim([orderno]))
> BOL says 1) all functions referenced by the expression are deterministic a
nd
> precise. 2) all columns referenced in the expression come from the table
> containing the computed column and 3) no column reference pulls data from
> multiple rows.
> All of which I believe I'm good on. Each of these are SET ON:
> ANSI_NULLS,ANSI_PADDING,ANSI_WARNINGS,AR
ITHABORT,
> CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIE
R
> and NUMERIC_ROUNDABORT is SET OFF.
> can somebody help me find what I'm missing?
> -- Lynn|||Gert-Jan, doing that, my value becomes this: 08/26/05
I need this MMDDYY, w/out the forward slashes in there.
is there no way to do that w/out becoming imprecise?
-- Lynn
"Gert-Jan Strik" wrote:

> Lynn,
> The dateformat "1" uses a cutoff year which makes it imprecise.
> The following example shows the problem and the solution.
> create table #t(mydate datetime not null
> ,displaydate as convert(varchar(8),mydate,1)
> )
> create unique index someindex on #t(displaydate)
> create table #t2(mydate datetime not null
> ,displaydate as
> substring(convert(varchar(10),mydate,101
),1,6)+substring(convert(varchar(1
0),mydate,101),9,2)
> )
> create unique index someindex2 on #t2(displaydate)
> drop table #t
> drop table #t2
>
> Gert-Jan
>
> Lynn wrote:
>|||Yes, Aaron, unfortunately I do need it, as the composite PK of the three
columns invites duplicates. Meaning, it's endpoint+YYYY-MM-DD
HH:MM:MS:000+orderno. As weird as it may sound, the presence of the time
along w/the date is not desirable because like i said, it invites dupes.
Yes, I know the date w/out the time would seem as though it would do the sam
e
-- but it's something a little native to us, I suppose. So anyway, the
composite pk/constraint won't do, unless there's some way that I am unaware
of that will allow me to strip the time from the datestamp in the
constraint/pk.
can I do that? create a uniqe constraint and/or pk and/or unique index
(ideally, the constraint) on the three columns, but strip the time from the
exectime column?
exectime+endpoint+orderno
-- Lynn
"Aaron Bertrand [SQL Server MVP]" wrote:

> Also, if you create a primary key or unique constraint on the three base
> columns, do you really need the computed column to be explicitly unique
> (since it should be unique by definition anyway)? I am often amazed at th
is
> desire to store computed values when you don't have to; views and queries
> could easily construct this value on select instead of storing redundant
> data...
>
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:90919619-0625-4139-81D1-796DE8530503@.microsoft.com...
>
>|||What do you hope to gain from this particular format that cannot also be
accomplished using one of the standard ones. For constraint purposes, the
format of the date is not important. Does the computed column need to be in
this format for visual purposes? If so, why not create a 2nd column for
constraint purposes only.
"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:20149027-D3A8-40F8-A7E9-9474C6E4D30A@.microsoft.com...
> Gert-Jan, doing that, my value becomes this: 08/26/05
> I need this MMDDYY, w/out the forward slashes in there.
> is there no way to do that w/out becoming imprecise?
>
> -- Lynn
>
> "Gert-Jan Strik" wrote:
>|||This particular format is our uniqueID. W/the time, however, it is invalid
for business/application reasons. I started down this path hoping to do a
PK, but learned I could not do a PK on a computed column. Hence, I am tryin
g
both the unique constraint or the unique index, both of which fail w/the
non-deterministic problem. So, possibly for constraint purposes the format
of the date is not important. But it is for our purposes. I am hopeful
that I am in error or possibly missing something quite obvious, but I need
the time stripped from the datetime stamp in the value, whether constraint,
computed or otherwise.
-- Lynn
"Scott Morris" wrote:

> What do you hope to gain from this particular format that cannot also be
> accomplished using one of the standard ones. For constraint purposes, the
> format of the date is not important. Does the computed column need to be
in
> this format for visual purposes? If so, why not create a 2nd column for
> constraint purposes only.
> "Lynn" <Lynn@.discussions.microsoft.com> wrote in message
> news:20149027-D3A8-40F8-A7E9-9474C6E4D30A@.microsoft.com...
>
>|||On Fri, 26 Aug 2005 12:49:04 -0400, "Scott Morris" <bogus@.bogus.com>
wrote:
>What do you hope to gain from this particular format that cannot also be
>accomplished using one of the standard ones. For constraint purposes, the
>format of the date is not important. Does the computed column need to be i
n
>this format for visual purposes? If so, why not create a 2nd column for
>constraint purposes only.
What he said.
J.