SQL SERVER – Computed Column and Conditions with Case Statement

When I do not find anything on my blog, I write a blog post about it, so next time I do not have to recreate the scripts from the beginning. Just another day, I was doing performance tuning for an organization, I had to create a computed column with CASE statement and I suddenly realize that I do not have such a script on my blog so I decided to write a blog post on this subject.

I have created this script from AdventureWorks database which is a sample database for SQL Server. You can write any complicated CASE statement from following example.

First, we will retrieve data from our table.

USE AdventureWorks2014
GO
SELECT [WorkOrderID]
,[OrderQty]
FROM [Production].[WorkOrder]

The result of the script above is as follows:

Now let us add a computed column to the table where I have used CASE statement. With the help of CASE statement, now I can write conditional logic.

-- Add Computed Column with Case Statement
ALTER TABLE [Production].[WorkOrder]
ADD OrderVol AS CAST
(
CASE
WHEN OrderQty < 10 THEN 'Single Digit' WHEN OrderQty >= 10 AND OrderQty < 100 THEN 'Double Digit' WHEN OrderQty >= 100 AND OrderQty < 1000 THEN 'Three Digit'
ELSE 'Super Large'
END AS VARCHAR(100)
)
GO

Now let us write one more SELECT statement to retrieve data from the table and this time we will include our computed in the SELECT statement.

-- Now retrieve computed column
SELECT [WorkOrderID]
,[OrderQty]
,[OrderVol]
FROM [Production].[WorkOrder]
GO

Here is the result set created from the SELECT statement and you can see that based our logic and condition we are able to see different value in our column.

Now, due to any reason, if you want to drop this column, you can use the following script.

-- Clear Computed Column
ALTER TABLE [Production].[WorkOrder]
DROP COLUMN OrderVol
GO

Additionally, here are a list of the blog post which talks about the same subject:

• SQL SERVER – Computed Column – PERSISTED and Storage
• SQL SERVER – Computed Column – PERSISTED and Performance
• SQL SERVER – Computed Column – PERSISTED and Performance – Part 2
• SQL SERVER – Computed Column and Performance – Part 3
• SQL SERVER – Computed Column – PERSISTED and Storage – Part 2
• SQL SERVER – Computed Columns – Index and Performance

I strongly encourage that you read above blog posts, and based on the same, make appropriate decision about how to use this logic in your application. Please leave a comment in the comment section with your thoughts. I will be happy to read them and learn along with you.

Reference: Pinal Dave (https://blog.sqlauthority.com)