Calculated Columns

Today I wanted to demonstrate one way to create calculated columns in a SQL Server table. A Calculated column is a column where the data is not entered by a system. Instead, the value is derived by some formula or function available to the database.

A calculated column may be persisted or not. You would persist a calculated column if the value is static. It is created at the time the record is instantiated, and the value never changes. Using a persisted column makes it so that the calculation is only performed once, instead of every time a record with that column is retrieved.

So, why would you not persist a calculated value? How about those instances where the value is a constantly changing result. The value for the column depends on some external feature which may result in a different value. A good example of this would be a person’s age. In a database we often track the birthdate of an individual in order to determine their age. The obvious thing is that a persons age changes daily. In this case, we need a function to determine age based on the birthdate of an individual.

I created a function to calculate the annual age of an entity based on two different values. The first value is the start date for the age. In the case of a person, this would be their birth date. The second value is provided by something for comparison. The result of the function should return the number of years between the first date and the second date, taking into account the Month and Day of both values.

Here is the source for a SQL Server function I created to calculate age. There are probably some better solutions you could find. But, this gets you started.

CREATE FUNCTION dbo.Age(@BirthDate DATETIME, @CompareDate DATETIME)
RETURNS INT
AS
BEGIN
    DECLARE @Result INT = null

    IF @BirthDate IS NOT NULL
    BEGIN
        DECLARE @Adjustment INT = 0
        DECLARE @Bmonth INT = DATEPART(month, @BirthDate)
        DECLARE @Cmonth INT = DATEPART(month, @CompareDate)
        DECLARE @BDay INT = DATEPART(day, @BirthDate)
        DECLARE @CDay INT = DATEPART(day, @CompareDate)
    
        SET @Adjustment = CASE WHEN @Bmonth > @CMonth THEN 1 
                               WHEN @Bmonth = @Cmonth AND @Bday > @Cday THEN 1
                               ELSE 0 END

        SET @Result = DATEDIFF(year, @BirthDate, @CompareDate) - @Adjustment
    END
    RETURN @Result
END
GO

Now that you have a function you can create your table. I am going to create a table called Person. The user will enter FirstName, LastName and Birthdate values into this table. It will have a fourth column defined by a call to our Age function, returning the age of the person real time.

CREATE TABLE Person
    (
    FirstName nvarchar(50) NOT NULL,
    LastName nvarchar(50) NOT NULL,
    BirthDate datetime NULL,
    Age  AS dbo.Age(BirthDate, GetDate())
    )  ON [PRIMARY]
GO

I’ll enter four records into the table.

INSERT INTO Person (FirstName, LastName, BirthDate) 
VALUES 
('John', 'Smith', '10/3/2001')
,('Sammy', 'Slider', '6/4/2003')
,('George', 'Jetson', '6/20/1963')
,('Bill', 'Bunions', null)

Here are the results from the table.

SELECT * FROM Person

That’s all there is to creating a calculated column in a SQL Server table. When you do use calculated columns that are not persisted, it is even more important not to use the SELECT * method of returning data, especially if you do not require the calculated column. Using SELECT * will run the calculation on all of the records returned, using up CPU cycles unnecessarily.

Cheers,

Ben