Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Computed Columns

By Andy Warren,

Computed columns are one of those features that we all know about but tend to forget about when it's time to implement. Are they really a useful option since we're denormalizing our data in some cases? Can they boost performance? The answer to both is yes and they can actually save you some effort compared to other options. We'll use a simple reporting example to drive our discussion.

Let's use Adventureworks for the examples. We'll start simple. You've been helping build reports and a very common task is to list data from Person.Contact including showing contact names as lastname then a comma and a space, then a first name (Warren, Andy). Because you're a power user you just create it in your select statement, like this:

select lastname + ', ' + firstname as Fullname from person.contact

One way to to help out the less technical users is to create a view that encapsulates that usage, like this:

create view vPersonContact as

--note, you could alternatively use the * to indicate all columns, I just prefer to specify

select 
	  lastname + ', ' + firstname as Fullname
	  ,ContactID
      ,NameStyle
      ,Title
      ,FirstName
      ,MiddleName
      ,LastName
      ,Suffix
      ,EmailAddress
      ,EmailPromotion
      ,Phone
      ,PasswordHash
      ,PasswordSalt
      ,AdditionalContactInfo
      ,rowguid
      ,ModifiedDate
from person.contact

You'll need to grant select permissions on the view to the same users that currently have select permissions on the Person.Contact table. At that point they can use the view in place of the table and get the fullname column for free. Conceivably not all the users will 'get' that the view is really the table plus a column. You could - using a little care and doing this off hours - rename the Person.Contact table to Person.ContactBase, and then modify your view as follows:

create view Person.Contact as

--note, you could alternatively use the * to indicate all columns, I just prefer to specify

select 
      lastname + ', ' + firstname as Fullname
      ,ContactID
      ,NameStyle
      ,Title
      ,FirstName
      ,MiddleName
      ,LastName
      ,Suffix
      ,EmailAddress
      ,EmailPromotion
      ,Phone
      ,PasswordHash
      ,PasswordSalt
      ,AdditionalContactInfo
      ,rowguid
      ,ModifiedDate
from person.contactbase

This can take a little work if you have foreign keys, but it's doable, and ends up being being a nice transparent change to your report writers and to any application using the table/view. I'm leaving that as an exercise for you, and we'll just proceed using the first view. Now let's see what happens when they start to take advantage of our new column in unexpected ways such as filtering against it:

select * from vPersonContact where fullname like '%, andy'

In my copy of Adventureworks I have separate indexes on lastname and firstname. You can see the user is trying to identify rows in the table with a firstname of Andy. SQL typically doesn't give us great query plans with a leading wild card so it goes back to the dependable clustered index scan (essentially a table scan, meaning it has to look at every row in the table to see if it matches or doesn't). There's nothing to stop them from filtering on firstname and displaying fullname, but most users wouldn't know that or tend to do that by default. But, let's look at what would happen if they did:

select * from Person.Contact where firstname='andy'

We get a much better plan (always use Profiler to confirm that the plan truly is more effective by comparing reads for the two queries). Clearly we'd like to avoid a table scan and the best way short of turning our users into DBA's is to index the column. One way we can accomplish this is to modify the view to change it to an indexed view which would allow us to index fullname after creating a unique clustered index on the view. Indexed views are useful, but they add some weight and complexity that we might not yet need. Another option would be to add FullName to the table as a real column, and then create insert and update triggers on the table that maintain the column based on changes to either first or last name. We could then index FullName and avoid the extra complexity of the indexed view. Our final solution and of course our target for today is to use a computed column which is almost the the same as adding the column and creating the triggers as you'll see next.

To add a computed column via TSQL the syntax is easy:

ALTER TABLE Person.Contact ADD FullName  AS lastname + ', ' + firstname

By default computed columns are not persisted; that is, it's really just adding the expression to the table much as we added it to the view earlier. It's cleaner than the view if you always want the column to be present. Each time we access the column SQL will calculate the value and then display or filter against it appropriately. It will not change our query performance though as we're still doing it all on the fly. Here's what the plan looks like now:

If we change the definition to persisted that will eliminate the overhead of calculating the value every time, the trade off is that we have to store the calculated value and maintain it (essentially our trigger based solution from above). We'll just add another column called FullName2 that will be persisted and then retest our query:

ALTER TABLE Person.Contact ADD FullName2  AS lastname + ', ' + firstname PERSISTED 

Just a minor change in the plan and one that probably doesn't greatly affect performance as we're still scanning the table. The final option we have is to index the column, but does it make a difference whether we index the persisted or non persisted column? We'll create a non clustered index on each column and then run our queries again:

The query plans are identical which makes sense since regardless of how we got the values, the final index would turn out exactly the same. We get an index scan because we're still using the leading wild card (maybe training them not to use that isn't such a bad idea after all) and that usually eliminates a seek as an option. It's still considerably faster than a table scan though and that's better than where we started.

At this point I haven't tested with more complex computed columns to see if I can see a difference in overhead to maintain them. Persisting and indexing would take up more space than just indexing or just persistently used singly. I typically opt to just index the computed column rather than persist it, but that's not a best practice - just my practice. The best guidance I can offer is that if you're about to write triggers to maintain a column take a hard look at computed columns first, then indexed views, as a way to let SQL do all the work for you in a standard way. Denormalizing data should not be our first choice and we want to look at all the options, but as you can see from the example we really were denormalizing to help our users rather than just to boost performance.

I blog once a week or so at http://blogs.sqlservercentral.com/blogs/andy_warren/default.aspx about SQLServer, SQL user groups, and related topics. I hope you'll visit and comment occasionally!

Total article views: 12385 | Views in the last 30 days: 11
 
Related Articles
FORUM

how to devied fullname into firstname and lastname

how to devied fullname into firstname and lastname

FORUM

Lastname, Firstname switch

I dont know if this is any help but this is the code I started with: SELECT HOST9006.DESCRIPTION,...

FORUM

Match firstname lastname

Hi, Could anyone help me with T-SQL to match names. I have to match names from one database to ba...

FORUM

Split out FirstName, LastName, MiddleName

One column name as [MemberName] in which stored data like LastName, FirstName M (with initial mi...

FORUM

Create FirstName and LastName to Replace Existing FirstName and LastName

Hello Everyone I hope that you all are having a very nice day. I am wanting to change all the Fi...

Tags
database design    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones