How to Alter This Computed Column in SQL Server 2008

How can I alter this computed column in SQL Server 2008?

If you're trying to change an existing column, you can't use ADD. Instead, try this:

alter table tbPedidos
alter column restricoes as
(cast(case when restricaoLicenca = 1 or restricaoLote = 1 or restricaoValor = 1
then 1 else 0 end as bit))

EDIT: The above is incorrect. When altering a computed column the only thing you can do is drop it and re-add it.

How to create a calculated column in a SQL Server 2008 table

You can wrap your query into the function like this (it HAS to return one value):

CREATE FUNCTION dbo.SumIt(@Key1 varchar(max))
returns float
as
begin
  return (select sum(UTOTALWBUD) from
   CONTACT1 inner join
   CONTACT2 on
   CONTACT1.ACCOUNTNO=CONTACT2.ACCOUNTNO
   where KEY1=@key1
   group by KEY3)
END

And use this function instead with calc field - something like this:

alter table ManagerTaLog add WeeklyBudget as dbo.SumIt(Key1)

NOTE

that it will be the performance killer for queries like that:

select * from ManagerTaLog 

You should change your function in such a way, that is accept NOT varchar value, but NVARCHAR(255) - the same type as Manager column. Try it.

SQL Server Alter Computed Column

Not that I know of but here is something you can do

add another column to the table
update that column with the values of the computed column then drop the computed column

Creating a computed column in SQL Server 2008

You can define the column in your CREATE TABLE as:

AgeInMinutes as (DATEDIFF(minute, PublishDate, GETDATE())

Alternatively, just do

ALTER TABLE Book
ADD AgeInMinutes as (DATEDIFF(minute, PublishDate, GETDATE())

Alter a column to be calculated SQL SERVER

You can't alter it.

ALTER COLUMN

Specifies that the named column is to be changed or altered.

The modified column cannot be any one of the following:

A column with a timestamp data type.

The ROWGUIDCOL for the table.

A computed column or used in a computed column.

You need to drop and recreate:

ALTER TABLE [dbo].[test]
DROP COLUMN [name];

ALTER TABLE [dbo].[test]
ADD [name] AS ([dbo].[f_get_name]([id_source],[id_dest],[action]));

How can I alter this computed column in SQL Server 2008?

If you're trying to change an existing column, you can't use ADD. Instead, try this:

alter table tbPedidos
alter column restricoes as
(cast(case when restricaoLicenca = 1 or restricaoLote = 1 or restricaoValor = 1
then 1 else 0 end as bit))

EDIT: The above is incorrect. When altering a computed column the only thing you can do is drop it and re-add it.

How to alter a column and a computed column

I suggest you drop the index, then drop the computed column. Alter the size, then re-add the computed column and the index. Using your example....

create table testTable (baseColumn varchar(10), upperBaseColumn AS (upper(baseColumn)))
create index idxUpperBaseColumn ON testTable (upperBaseColumn)

Drop Index TestTable.idxUpperBaseColumn

Alter Table testTable Drop Column upperBaseColumn

Alter Table testTable Alter Column baseColumn VarChar(20)

Alter Table testTable Add upperBaseColumn As Upper(BaseColumn)

create index idxUpperBaseColumn ON testTable (upperBaseColumn)

Computed column in SQL

To change a calculated column, you must first DROP column and then redefine the column as calculated.

ALTER TABLE MyTable DROP COLUMN MyComputedColumn 
ALTER TABLE MyTable ADD MyComputedColumn AS DATEDIFF(DD, GETDATE(), MyDateColumn)

---

Related Topics