Changing a Computed Column–#SQLNewBlogger

Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers.

I was working with a computed column the other day, and realized I had the wrong definition. In this case, I was performing some large calculation, and the result was larger than an int. However the first part of the formula was an int, which resulted in an implicit conversion to an int.

I needed to change the formula, and then realized that plenty of people might not work with computed columns much, and not realize how you alter a computed column.

You don’t.

In fact, you need to drop the column and add it back. In my case, this was what I did. Here was my table:

CREATE TABLE SiteStats
(
StatID INT IDENTITY(1,1) PRIMARY KEY NONCLUSTERED
, StateDate DATE DEFAULT SYSDATETIME()
, StatMonth TINYINT
, StatYear int
, PageVisits INT
, TimeOnSite TIME
, Engagement AS (PageVisits * DATEDIFF(SECOND, CAST(’00:00:00′ AS TIME), TimeOnSite))
)

I wanted to cast the PageVisits part of the column to a bigint to solve the issue. I first needed to do this:

ALTER TABLE dbo.SiteStats
DROP COLUMN Engagement

Once that’s done, I can do this:

ALTER TABLE dbo.SiteStats
  ADD Engagement AS (CAST(PageVisits AS BIGINT) * DATEDIFF(SECOND, CAST(’00:00:00′ AS TIME), TimeOnSite));
GO

Now I have a new definition that works great.

Some of you might realize that this could be an issue with columns in the middle of the table, and it is. However you shouldn’t worry about column order. Select the columns explicitly and you can order them anyway you want.

SQLNewBlogger

A quick post, five minutes. Even if you had to search for how this works, you could do this in 10-15 minutes, tops. Research, write why you did this and potential issues with your system.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Blog and tagged , , . Bookmark the permalink.