computed value

I've added a column to my table as a computed value, pretty much only to
populate it for the existing data. I want to do this, then drop the compute
d
value, make it's new column NOT NULL and lay over it a unique constraint.
Can I drop the computed value w/out dropping the column?
-- Lynn"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:4052E967-8301-4F0D-A2FF-C721068FC0B3@.microsoft.com...
> I've added a column to my table as a computed value, pretty much only to
> populate it for the existing data. I want to do this, then drop the
> computed
> value, make it's new column NOT NULL and lay over it a unique constraint.
> Can I drop the computed value w/out dropping the column?
> -- Lynn
Why not add it as NOT NULL and default it to zero, or x or whatever.
Then run and update on the column and compute your new values.
Better yet, why are you storing a computed value anyhow? Why not just use a
SELECT statement or a view and compute the value on the fly?
Rick Sawtell|||A computed column or one being used in a computed column cannot be altered,
nor can a computed column be updated.
What are you traing to do? If you need to change the behaviour of a computed
column, you need to drop it first. You cannot add a non-nullable column
without specifying a default value.
However, you can create a temporary table to store the values of the
computed column, drop the column, add a new column (it needs to be nullable)
,
then fill it with previous values, and alter it to make it non-nullable.
And if you post DDL and sample data, we can help you build a script to
achieve all this.
ML|||Thank you both. I think I realized I was overthinking this one a bit. It
doesn't have to be computed. I put MsgID on as varchar(64) NOT NULL, update
d
it for existing data w/this:
UPDATE tableA...
SET MsgID = endpoint+(convert(varchar(8),[exectime],
112) + [ordernumber])
GO
Then I changed it to NOT NULL and created the unique constraint. All
w/existing data in the table. What do you guys think?
-- Lynn
"ML" wrote:

> A computed column or one being used in a computed column cannot be altered
,
> nor can a computed column be updated.
> What are you traing to do? If you need to change the behaviour of a comput
ed
> column, you need to drop it first. You cannot add a non-nullable column
> without specifying a default value.
> However, you can create a temporary table to store the values of the
> computed column, drop the column, add a new column (it needs to be nullabl
e),
> then fill it with previous values, and alter it to make it non-nullable.
> And if you post DDL and sample data, we can help you build a script to
> achieve all this.
>
> ML|||Does it work as it is supposed to work? :)
Looks like you've nailed it.
ML|||I don't know yet, it's still running now. I am sure hoping we're good on
this one...
-- Lynn
"ML" wrote:

> Does it work as it is supposed to work? :)
> Looks like you've nailed it.
>
> ML|||Worked beautifully. Thank you guys for looking into this w/me.
-- Lynn
"ML" wrote:

> Does it work as it is supposed to work? :)
> Looks like you've nailed it.
>
> ML|||On Wed, 7 Sep 2005 12:01:02 -0700, Lynn wrote:

>Thank you both. I think I realized I was overthinking this one a bit. It
>doesn't have to be computed. I put MsgID on as varchar(64) NOT NULL, updat
ed
>it for existing data w/this:
>UPDATE tableA...
>SET MsgID = endpoint+(convert(varchar(8),[exectime],
112) + [ordernumber])
>GO
>Then I changed it to NOT NULL and created the unique constraint. All
>w/existing data in the table. What do you guys think?
Hi Lynn,
If the MsgID column will always be the concatenation of these three
other columns, than I wouldn't store it like this. Create a view that
does the concatenation if you prefer the ease of use.
For enforcing uniqueness, just do
ALTER TABLE tableA
ADD CONSTRAINT MyUniq UNIQUE (endpoint, exectime, ordernumber)
No need to add an extra column for this.
Of course, if the MsgID for NEW rows in the database will be filled with
other values, and this concatenation is just the "starting" value for
existing rows, then the above doesn't apply.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Indexing that view might also be of help - that way the concatenated values
are stored as if the view were a table. Views aren't cached permanently.
However, as Hugo already stated, this only applies if the values need to be
generated by concatenation *every time*.
MLsqlsql