February 9, 2009 at 4:41 pm
GSquared (2/9/2009)
GilaMonster (2/9/2009)
Vijaya Kadiyala (2/9/2009)
What if the table already exists, so in this case we have to use Update Statement right!!Why?
Alter Table Add .... and add the computed column to the existing table. Since it's computed, the column doesn't actually store data and can't be updated. It's only calculated when the data is retrieved
I took his question as meaning the table already exists and there is already a column in it for that data.
Yes, the column can be dropped and rebuilt, but you have to make darn sure that there is no code trying to update that column if you're changing from data storage to calculated. That can be complex in some environments, where not all the database code is in procs.
I think that in a case that you drop a none computed column and replace it with a computed column, you can use an "instead of trigger" in order to make sure that no one will get a runtime error because he tried to update the new computed column.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
February 9, 2009 at 4:49 pm
[font="Verdana"]It's usually not too hard to do a code search within the database and applications to find whether a column is referenced.
I agree that the use of an instead of trigger should allow any "un-caught" code to still work as expected.[/font]
February 9, 2009 at 5:36 pm
Bruce W Cassidy (2/9/2009)
[font="Verdana"]You can also persist the calculated column, in which case it will only be calculated the once. Bits are good choices for persistance, since they take up soooo much space (not). 😀And of course you can index calculated columns. So all in all, the calculated column is a far better solution.
[/font]
Heh... dang it... ya beat me on the "persisted" option.
Shifting gears a bit... not all Calculated Columns can be indexed... the formula for the calculated column must be deterministic in order to add an index to it.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 9, 2009 at 5:38 pm
You could always use a view.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 10, 2009 at 3:48 am
Adi Cohn (2/9/2009)[hrI think that in a case that you drop a none computed column and replace it with a computed column, you can use an "instead of trigger" in order to make sure that no one will get a runtime error because he tried to update the new computed column.
That's a good tip.
Of course, one would have to ask, in this particular example, why the Expired flag was being updated from several places that would warrant having the trigger, but it could be very usefull for other calculated columns.
Derek
February 10, 2009 at 7:43 am
Bruce W Cassidy (2/9/2009)
[font="Verdana"]You can also persist the calculated column, in which case it will only be calculated the once. Bits are good choices for persistance, since they take up soooo much space (not). 😀And of course you can index calculated columns. So all in all, the calculated column is a far better solution.
[/font]
Since the column is calculated against getdate, there's no point in persisting it, since it will have to be recalculated at runtime every time it's queried.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 10, 2009 at 8:15 am
GSquared (2/10/2009)
Bruce W Cassidy (2/9/2009)
[font="Verdana"]You can also persist the calculated column, in which case it will only be calculated the once. Bits are good choices for persistance, since they take up soooo much space (not). 😀And of course you can index calculated columns. So all in all, the calculated column is a far better solution.
[/font]
Since the column is calculated against getdate, there's no point in persisting it, since it will have to be recalculated at runtime every time it's queried.
Since the column is calculated against getdate, it cannot be persisted. For a calculated column to be persisted it must, among some other requirements, reference only deterministic, precise functions. Getdate is not deterministic.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 10, 2009 at 12:19 pm
[font="Verdana"]Sigh. There's always a critic. 😛
Quite right, of course. A calculated column with getdate() in it will not persistable.[/font]
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply