Computed/Virtual Columns

Some days a go I discovered a wonderful thing called computer columns when I stumbling on MS SQL server. There is a free MS SQL server 2005 express that you can download off a Microsoft site. Yes, I know, FREE and from microsoft.

(P.S. for those who didn’t read, Bill Gates is now the 3^rd richest man in the world after 13 years of being number one.)

I also read a really good article that explains indexes on computed columns. The benefits of speeding up searches with them and adding business rules. Obviously, the business rules were particularly interesting to me.

Jay Pipes had a similar webinar about the benefits of speeding up searches, but he suggested you do that by creating a new column and placing in it there. His example was, you can speed up a search for “SELECT * FROM Contacts WHERE family_name LIKE ‘%stone’” by making a column that reverses the family name and then you search by “SELECT * FROM Contacts WHERE reverse_family_name LIKE ‘enots%’”. This will save the query to go through all the records to find the results.

So I looked a bit more into it how to developing business rules in computed columns and I found out that I can’t use “If”s but I can use “Case”. I also found out that the values you are looking at must be in the same table and the same row. Otherwise its non-deterministic and cannot work.

I am pretty happy with these computed columns and I consider it to cover most of what I have been looking for. I am also aware that there is some work being done on this to be implemented into MySQL, since I saw some information about it on the MySQL forge.

I would also like to add some business rules in the form of validations for data to keep data quality high. I was thinking something along the lines of creating new data types with some constraints and limits inside them, but that didn’t work out at all in MySQL.

For example, creating an “email” data-type that will have some regular expressions check on it and default size.

I found something else in the land of XML which is kind of what I was looking for, called schematron, but I need to look into it more.

I’m going to learn some more about this when I have time. In the near future I know I have to learn about installing a CRM system that uses MS SQL and I want to see if I can use any computed columns to help with this system. So if I find anything interesting, I'll blog about it.

Thank you for reading my blog