In this article, I would like to show how to use the formula child property of the computed column specification in SQL Server. The formula child property is used to display the formula for the computed column. The column Properties is displayed when the column is selected. The Properties window will be shown in the bottom pane of the Table Designer. Some properties are read-only and some can be changed for certain data types of columns. So let's take a look at a practical example of how to use the formula child property of computed column specification property in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.
Creating table in SQL Server
First I have created a Table named TringleArea which has three columns named Base, Height, and AreaofTringle with datatype INT. The table TringleArea has three columns as below:
Creating Function in SQL Server
Now create a function to calculate the area of the triangle which takes two arguments and returns an integer value. The function AreaofTringle has two arguments as below:
- CREATE FUNCTION [dbo].[AreaofTringle](@Base INT, @Height INT)
- RETURNS INT
- AS
- BEGIN
- DECLARE @Result INT
- SET @Result = @Base * @Height/2
- RETURN
- @Result
- END
Now execute the above function in the query analyzer of SQL Server Management Studio.
Now the function needs to pass parameters. Now open the table and select a column from the table. The column Properties will be displayed to that selected column. We select the AreaofTringle column.
Now select the column property computed column specification and expand it.
Now add the function name along with the parameters for the formula.
Now execute the insert query and look at the result. You can use the simple insert query as below:
- INSERT INTO TringleArea VALUES(6, 4)
- go
- INSERT INTO TringleArea VALUES(60, 50)
Now use a select statement to see the calculated result.