use select statement in default while declaring a table

Question

I want to set a default value to a column while declaring a table , but the value is not constant , actually it's based on some other data from another table . I'm interested to know if there is such thing like code below is SQL-server :

create table Test_Table(
Id int primary key ,
Name varchar(50),
Sum int default (select Count(*) from AnotherTable where Condition)
)

Welcome to Stack Overflow. Have you tried the following? https://stackoverflow.com/questions/92082/add-a-column-with-a-default-value-to-an-existing-table-in-sql-server?page=1&tab=votes#tab-top — abestrad, Feb 13 '19 at 08:56
Do you actually want the default value to change when the count changes in the other table? — Salman A, Feb 13 '19 at 09:05

EzLo · Accepted Answer · 2019-02-13T09:42:25.063

You can't add a DEFAULT constraint that depends on other tables.

Please note that you are trying to store a computed column that might be redundant (from a normalization perspective). It's usually a common approach to do this when the calculation is very heavy and can't be done on demand.

You have a few workarounds for this, each one with pros and cons:

Use a view that calculates this value each time it's needed. With a view, the value is not actually stored and it's computed on each query that uses it so you don't have to keep it updated. You will need to add/remove columns from the view if you add/remove columns from the original table.
```
CREATE VIEW dbo.vTest_Table 
AS
SELECT
    T.Id,
    T.Name,
    Sum = (SELECT COUNT(*) FROM AnotherTable AS A WHERE /*Condition linking T with A*/)
FROM
    Test_Table AS T
```

Use a trigger to keep the value updated from the tracking table. This means you need to add the column and keep it updated whenever there's a change on the expression you want calculated. This adds processing overhead compared to other solutions whenever the change is done, but selecting the value will be faster as it's already calculated. Also has the possibility of being indexed.

CREATE TRIGGER utrTestTableUpdateSum ON AnotherTable -- Trigger on the other table!!
    AFTER INSERT, UPDATE, DELETE
AS
BEGIN

    ;WITH AffectedRows AS
    (
        SELECT KeyColumn FROM inserted
        UNION
        SELECT KeyColumn FROM deleted
    ),
    Aggregates AS
    (
        SELECT
            T.KeyColumn,
            COUNT(*)
        FROM
            AnotherTable AS T
            INNER JOIN AffectedRows AS A ON A.KeyColumn = T.KeyColumn
        GROUP BY
            T.KeyColumn
    )
    UPDATE T SET
        Sum = /* Expression */
    FROM
        Aggregates AS A
        INNER JOIN Test_Table AS T ON /*Joining columns*/

END

score 0 · Answer 2 · answered Feb 13 '19 at 09:17

Tables are static data, what you are describing is dynamic, you need a view:

create table mytable
(
id int primary key,
name varchar(50)
);

create view myview
as
select t1.*, t3.thecount
from mytable t1
inner join
(
select t2.something, count(t2.*)
from othertable t2
group by t2.something
) t3
on t1.id = t2.something

I've put this as an inner join assuming that the "condition" that you used in your example implies some kind of relationship between your two different tables

You can select from a view in the same way as selecting from a table.

use select statement in default while declaring a table

2 Answers2