|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
NON-DETERMINISTIC?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,ARITHABORT, CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER and NUMERIC_ROUNDABORT is SET OFF. can somebody help me find what I'm missing? -- Lynn Can you give your table structure and some sample data?
http://www.aspfaq.com/5006 Show quote "Lynn" <L***@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,ARITHABORT, > CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER > 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... Show quote "Lynn" <L***@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,ARITHABORT, > CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER > and NUMERIC_ROUNDABORT is SET OFF. > > can somebody help me find what I'm missing? > > -- Lynn 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 same --- 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 Show quote "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 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" <L***@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,ARITHABORT, > > CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER > > 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: Show quote > > 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,ARITHABORT, > CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER > 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 Show quote "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(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 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,ARITHABORT, > > CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER > > and NUMERIC_ROUNDABORT is SET OFF. > > > > can somebody help me find what I'm missing? > > > > -- Lynn > 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. Show quote "Lynn" <L***@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: > >> 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 >> > 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,ARITHABORT, >> > CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER >> > and NUMERIC_ROUNDABORT is SET OFF. >> > >> > can somebody help me find what I'm missing? >> > >> > -- Lynn >> 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 trying 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 Show quote "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" <L***@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: > > > >> 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 > >> > 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,ARITHABORT, > >> > CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER > >> > and NUMERIC_ROUNDABORT is SET OFF. > >> > > >> > can somebody help me find what I'm missing? > >> > > >> > -- Lynn > >> > > > On Fri, 26 Aug 2005 12:49:04 -0400, "Scott Morris" <bo***@bogus.com> What he said.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. J. Disregard. I believe I got it. I changed table creation - this is now the
MsgID column: [MsgID] AS endpoint+(convert(varchar(8),[exectime],112) + [orderno]) I've got a unique non-clustered constraint on MsgID. All other indices and pk are in place as they should be, and I've transferred a week of data there w/out error. I've been on this for a couple days, failing for various reasons throughout. apparently i've gotten beyond it, it's functioning just fine now. the only major change i've made is the computed value, so i'm a little puzzled, but i'm moving on. than you for having looked at it for me. -- Show quoteLynn "jxstern" wrote: > On Fri, 26 Aug 2005 12:49:04 -0400, "Scott Morris" <bo***@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 in > >this format for visual purposes? If so, why not create a 2nd column for > >constraint purposes only. > > What he said. > > J. > > Lynn,
I expected a little creativity on your part. What I posted was only a replacement for the "convert(varchar(8),[exectime],1)" part. The Replace doesn't cause any problems. The cause for the error was the converting format. create table #t (endpoint varchar(20) not null ,exectime datetime not null ,orderno varchar(20) not null ,uglydisplay as rtrim([endpoint]) + replace( substring(convert(varchar(10),[exectime],101),1,6)+substring(convert(varchar(10),[exectime],101),9,2) ,'/','') + rtrim([orderno]) ) insert into #t values ('bla---',getdate(),'---bla') select * from #t create unique index someindex2 on #t(uglydisplay) drop table #t If you do not want the strange info message when creating the index, the add "substring( ... ,1,100) to the computed column. Gert-Jan Lynn wrote: Show quote > > 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(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 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,ARITHABORT, > > > CONCAT_NULL_YIELDS_NULL,QUOTED_IDENTIFIER > > > and NUMERIC_ROUNDABORT is SET OFF. > > > > > > can somebody help me find what I'm missing? > > > > > > -- Lynn > >  |
|||||||||||||||||||||||
Other interesting topics