Detailed calculation column in SQL Server

Source: Internet
Author: User
Tags microsoft sql server management studio microsoft sql server management studio sql server management sql server management studio

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.