computed columns in SQL Server 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.

#创建方法: 1. Create by SQL Method:

1 Create Table table1 2 (3      Number int , 4      Money , 5      as  Number * -- Computed Columns 6 )

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.

2. Create a computed column in Microsoft SQL Server Management Studio:

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. Of course, when you create a computed column, you hook up the "is persistent" option to achieve our goal, when the computed column is a real column, or you can create an index on that column.

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

#注意:

1, computed column if not set to "is persistent", then it is not used 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:

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

------------------------------------------------------------------

computed columns in SQL Server server