April 22, 2014 at 2:01 pm
We ALTER'ed a table - increasing the size (numeric 12 to numeric 17). It took over 40 minutes to run on a table that has 10 million rows. With that kind of response time, I'm guessing SQL Server is modifying every row and increasing it's size? Although the actual physical number of bytes needed to store an numeric 12 is the same as numeric 17 should still be 9 bytes.
April 22, 2014 at 2:12 pm
john.p.lantz (4/22/2014)
We ALTER'ed a table - increasing the size (numeric 12 to numeric 17). It took over 40 minutes to run on a table that has 10 million rows. With that kind of response time, I'm guessing SQL Server is modifying every row and increasing it's size? Although the actual physical number of bytes needed to store an numeric 12 is the same as numeric 17 should still be 9 bytes.
When you say you altered the table how did you do it? I have a feeling you used the gui in SSMS to alter the table? SSMS does not generate spectacular scripts for this type of thing using the UI. It will create a new table with the new datatypes, then insert all the data from the current table into the new table, drop the existing table and finally rename the new table to the original table name.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 22, 2014 at 2:23 pm
No - didn't use the GUI. Actually ran the following ALTER statement
ALTER TABLE [dbo].[table_name]
ALTER COLUMN [annualized_edf_01yr_pct] Numeric(17,7)
And in full disclosure - we were actaully ALTER'ing over a dozen columns in the same table.
April 22, 2014 at 2:54 pm
You might want to check for blocking due to schema locks when modifying a column.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply