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)
Performance wise so many computed cols are good or just create a col and used to update that col is best…
is there any way to add variables in the conditions???