Detailed calculation column in SQL Server

The computed column differs from the column that requires us to assign the value manually or by the program, whose value is derived from the computed value of the other columns in the table. For example, if a table contains a quantity column number and a price column, we can create a calculated column amount amount to represent the result value of the quantity * unit price, and after the amount column is created, the value of the calculated amount is used in the program. Instead of taking out the value of the number column and the price column, you can simply take the value of the amount column.

So how is this computed column to be built?

First look at the SQL method to create:

CREATE TABLE table1

(

Number Decimal (18,4),

Price Money,

Amount as Number*price--here is the computed column

)

Computed columns are information that does not require us to specify the data type and whether it is allowed to be null, and SQL Server automatically assigns the data type as appropriate.

Building a computed column in Microsoft SQL Server Management Studio is more straightforward. As shown, you can simply fill in the formula for the computed column in the computed column specification-formula in the column properties.

In the picture above we also see the "is persistent" this option, what is the use of this option?

Computed column If there is no special setting, it will be a virtual column, that is, the column actually does not exist, but each time you want to take the value of this column, SQL will be calculated by the formula of the computed column once, and then return the results to us. There are problems, for example, that each calculation consumes a certain amount of time, and you cannot create an index on that column. So can you save the results of the computed column, and return the results directly to us each time we fetch the data, instead of counting each time. Oh, congratulations, when you create a computed column, the "Is persistent" option is hooked up to achieve our goal, when the computed column is a real column, or you can create an index on that column.

If you want to see all the computed columns that already exist and whether the computed column is persistent, you can take advantage of the Sys.computed_columns view

Attention:

1, if the computed column is not set to "is persistent", then it is not allowed to do check,foreign key or NOT NULL constraint. Of course, if we set constraints such as check for computed columns in Microsoft SQL Server Management Studio, SQL Server automatically sets the column to "is persistent."

2, computed columns cannot be used again as part of a computed column in.

3, in the trigger, you can not update the computed column to determine, otherwise the following error will be reported:

Column "cannot be used in the IF UPDATE clause because it is a computed column. ”

Detailed calculation column in SQL Server