Recently I needed to add a computed column to a table and realized that I didn’t remember the syntax. This short post show how to do this.
Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.
Adding the Column
I had a table, OrderHeader, and wanted to add a new column, OrderedByDate. I can do this with a simple ALTER statement and an ADD. The inserting part is the computation. You use as AS clause with the computation instead of a datatype. For example, my code was:
ALTER TABLE dbo.OrderHeader
ADD OrderedBy AS OrderDate;
GO
This added a copy of my OrderDate column with a new name. This is useful for zero downtime deployments in some cases, and in this case, I wanted to just have a copy. However, if I wanted some calculation, I could easily do that by specifying this as I would in a SELECT statement. For example, if I wanted the computed column to be a week later, I could do this:
ALTER TABLE dbo.OrderHeader
ADD OrderedBy AS DATEADDD(DAY, 7, OrderDate);
GO
This would add a week to the original value and return that. I can likewise do any sort of string or numeric manipulation I want. A common one is adding or multiplying two columns together for a new value. For example, adding various charges for a total in an order.
When you do this as a computed column that is not persisted, no space is taken in the actual table rows. If you persist this, then space is used.
More information on Microsoft Learn.
SQL New Blogger
I realized that I needed to double check the syntax in the docs, and when I did, I took this as an opportunity to write a short blog post. I grabbed a link, wrote some code, and then spent 10 minues knocking out this post.
If some employer does this a lot, they might search your blog to see if you can do this. A nice few posts on how to do this, what persisted does, how you index this, etc. Take a few minutes and start blogging on topics like this throughout your week.
Voice of the DBA 