Blog
Computed Columns in SQL Server Management Studio (SSMS)
By Don Kiely | December 18, 2012
Computed columns in SQL Server Management Studio (SSMS) allow you to configure a column within a table that automatically calculates a value based on an expression that can include references to other columns. For example, in the Course table you could have a PricePerUnit and a Units column, plus a Total column, as shown in the Figure below, where the Total column has the following Formula property:
([PricePerUnit] * [Units])If a value in either the PricePerUnit or the Units column changes, the Total column is automatically recomputed. However, by default this value is not actually stored-it is calculated and returned when needed, as in a query or view. The calculated value is stored on disk only if you index the column, which is what happens if you set Is Persisted to Yes.
TIP: When you modify a table's design with Management Studio, such as to add the formula for the Total computed column, and then click the Generate Change Script button on the left side of the toolbar, you'll see the T-SQL that Management Studio uses to make the change. This T-SQL does not represent the most efficient way of making the change. (You'll need to uncheck the Prevent saving changes that require the table to be recreated in the Tools|Options dialog box in the Designers|Table and Database Designers page. This option is checked by default.)
For every change or set of changes that you make, Management Studio creates a new table, copies any data in the old table into the new one, deletes the old table, and then renames the new one with the same name as the old. This is not necessary- it is just an easy way for Management Studio to avoid creating different scripts for every kind of change.
When you write the T-SQL yourself, you can use an ALTER TABLE statement that makes only the necessary change or changes to the existing table without creating a new table. That can make a big difference in the time required to change a table that contains lots of data.
Save the change to the Course table. You'll get a Save dialog box that verifies that you want to change the tables in the database, as shown in the Figure below. Select Yes to make the change.
This post is an excerpt from the online courseware for our SQL Server 2012: Configuring Management Studio course written by expert Don Kiely.
