How to Create a Calculated Column in a SQL Server 2008 Table

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.

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())

Creating calculated fields in sql

You are very close, it's called Computed Column

https://technet.microsoft.com/en-us/library/ms191250(v=sql.105).aspx

ALTER TABLE dbo.tablename ADD Employment AS ((m1+m2+m3)/3)

Update:

If you would like to force data type for a computed column, you could do the following

ALTER TABLE dbo.tablename ADD Employment AS CAST((m1+m2+m3)/3 AS Numeric (9,0))

SQL Server 2008 - A Calculated Column

You can use

-- be careful, I do asume lot of field names
create view withCount as
select s.*, c.cant 
from Streams s
left join 
   (select Streams_ID, count(*) as cant from Comments group by Streams_ID) as c
   on s.id = c.Streams_ID

And then

select * from withCount where ... order by ...  // or whatever you want

Do not use triggers to this task, they are more dificult to maintain and to understud

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.

define a computed column reference another table

Yes, you can do that - you need a function to count the players for the team, and use that in the computed column:

CREATE FUNCTION dbo.CountPlayers (@TeamID INT)
RETURNS INT 
AS BEGIN
    DECLARE @PlayerCount INT

    SELECT @PlayerCount = COUNT(*) FROM dbo.Player WHERE TeamID = @TeamID

    RETURN @PlayerCount
END

and then define your computed column:

ALTER TABLE dbo.Team
ADD TotalPlayers AS dbo.CountPlayers(ID) 

Now if you select, that function is being called every time, for each team being selected. The value is not persisted in the Team table - it's calculated on the fly each time you select from the Team table.

Since it's value isn't persisted, the question really is: does it need to be a computed column on the table, or could you just use the stored function to compute the number of players, if needed?

Creating a calculated field for my simple SQL 2008 table

CREATE TABLE [dbo].[Shipping]
(
    [ShippingId]        INT PRIMARY KEY IDENTITY(1,1),
    [ProductId]         INT FOREIGN KEY REFERENCES [dbo].[Product]([ProductId]),
    [LargoEnCm]         DECIMAL(16,2),
    [AltoEnCm]          DECIMAL(16,2),
    [AnchoEnCm]         DECIMAL(16,2),
    [PesoKg]            DECIMAL(16,2),
    [PesoFacturable]    AS CASE WHEN [PesoKg] > (([LargoEnCm] * [AltoEnCm] * [AnchoEnCm]) / 6000) THEN [PesoKg] ELSE (([LargoEnCm] * [AltoEnCm] * [AnchoEnCm]) / 6000) END,
    [A]                 DECIMAL(16,2),
    [B]                 DECIMAL(16,2),
    [C]                 DECIMAL(16,2),
    [D]                 DECIMAL(16,2),
    [E]                 DECIMAL(16,2),
    [F]                 DECIMAL(16,2),
    [G]                 DECIMAL(16,2),
    [PesoVolumen]       AS (([LargoEnCm] * [AltoEnCm] * [AnchoEnCm]) / 6000)
)

How to set a calculated column using a subquery

It is not possible to have a Computed Column with a Sub Query,

A computed column is computed from an expression that can use other
columns in the same table.

So it is not possible to have A Query but you can use Expressions Like

ColumnA-ColumnB+ColumnC

Instead, you can convert it as a View and Compute The Column values there

Like this

CREATE VIEW MyComputedvIEW
AS
SELECT
  *,
  CalculatedAmount = (SELECT sum(Amount) FROM shareholder.TransactionInput T 
                    WHERE T.ShareClassLabel = Amount.ShareClassLabel
                    AND T.ValuationDate < Amount.NAVDate
                    GROUP BY T.ShareClassLabel)
FROM YourTable

---

Related Topics