use select statement in default while declaring a table

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

Answer

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