asked
System Out of Memory Error
Hi, I am getting an error message when I try to add a column to a table(I am adding the new column in the middle of an already exist sequence of column order). The table has almost 40million rows. Error Source: mscorlib The Error Message: Exception of the type 'System.OutOfMemoryException' was thrown and another error message -'Unable to modify table. Timeout expired.The timeout period elapsed prior to completion of the operation Please, I will appreciate if there is a workaround adding a column to an existing able with so much data and the added column can be added to any position in the sequence order of columns and not at the end of columns list. I will appreciate if someone can help with this. Thanks
@ThomasRushton ♦♦ I bet the OP tries to do it through SSMS GUI. It only takes to have a quick glance at the script which GUI generates to appreciate how horrendous is the exercise of attempting to accomplish it through GUI (not that there is anything wrong with it). In fact, the resources needed to make it happen make the attempt borderline dangerous to the health and even existence of the database. A number of years ago I developed a technique for the client company to accomplish the same task which allowed 50 to 100 fold improvements in performance, they are still using the technique today. It is not generic though, so I need to have the structure of the table (column list and PK specification) in order to come up with the script to add the column in the middle. @Gehima2016 Please let me know the structure of the table (PK and defaults if any should be included) and I will come up with the script to add the column in the middle of existing columns. If this is a heap table without a unique index then you are out of luck though. Please let me know.
Are you trying to add the column using SQL Server Management Studio's GUI, or through T-SQL?
18 People are following this question.
