| Author |
Topic |
|
jonsey_54
Starting Member
8 Posts |
 Posted - 2007-03-29 : 06:46:15
|
| Hi,I am adding a persisted computed column to a very large table using an ALTER TABLE statement. The table has about 120 million rows so it is quite large.When I run the ALTER TABLE COMMAND it runs for a few hours and eventually fails because we run out of transaction log space.Just wondering if anyone has any smart ideas about adding a computed persisted column to a large table.Any ideas or suggestions would be much appreciated. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
jonsey_54
Starting Member
8 Posts |
Posted - 2007-03-29 : 07:19:26
|
| Thanks Peter,I switched on simple logging before I ran the ALTER TABLE command but it still caused the transaction log to run out of space.Besides truncating the table and adding the data in stages again is there any other way to add a computed column to a table to achieve maximum performance but without having to manually add data in stages? |
 |
|
|
jonsey_54
Starting Member
8 Posts |
Posted - 2007-03-29 : 07:42:13
|
| Is there anyway to add a computed column to a table but specify that it doesn't have to do the computations for existing data in the table. Only all new rows added should have their value computed.I guess I could add a normal column and setup a trigger to insert the value of the computed column each time a new row is inserted.But is there anyway to get the same job done with specifying a computed column and some sort of criteria (e.g. a WHERE CLAUSE)?Thanks |
|
|
|
jonsey_54
Starting Member
8 Posts |
Posted - 2007-03-29 : 09:24:59
|
| Hi,could anyone let me know why the below alter statement doesn't work:CREATE TABLE TEST(AGE INT,AMOUNT INT)alter table test alter column amount COMPUTE([Age] * 10) PERSISTEDI am getting the message:Incorrect syntax near the keyword 'COMPUTE'.Many Thanks |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-03-29 : 09:28:41
|
| Tryalter table test alter column amount as ([Age] * 10) PERSISTEDoralter table test alter column amount = ([Age] * 10) PERSISTEDPeter LarssonHelsingborg, Sweden |
|
|
|
jonsey_54
Starting Member
8 Posts |
Posted - 2007-03-29 : 09:38:08
|
| Hi Peter, thanks for suggestions but neither of those work. I am getting "syntas error near AS" and "syntax error near ="Guessing it is something to do with the parentheses??Thanks |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2007-03-29 : 13:31:40
|
quote:
Originally posted by jonsey_54
Guessing it is something to do with the parentheses??
I've not done much with COMPUTED columns, but it looks like you cannot ALTER a column to make it computer. It appears you can only ADD a computed column.Please feel free to correct me if I am wrong.-Ryan |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
mEmENT0m0RI
Starting Member
2 Posts |
Posted - 2009-10-22 : 17:17:36
|
| Not making the computed column persisted should still allow you to index it as long as the computed result is deterministic. Indexing then can be done online in SQL 2005 Ent, depending on the final SELECT, you can make this column a part of the covering index - this way the materialized value would get pulled from the leaf level of the index.HTH |
|
|
|
mailselvaraj
Starting Member
1 Post |
Posted - 2011-06-20 : 13:32:52
|
| Hi Jonsey,Did you get the final solution to fix this problem. I am also got into same problem now and we are using SQL 2008 R2.Please share the solution if you already applied.Thanks |
|
|
|
|
Adding a persisted computed column to a very large