CODEWITHSUNDEEP
sqlsql-serverperformancesql-server-2008sql-server-2008-r2
Nordes
Nordes

Reputation: 2521

SQL Server - Performance of increase decimal precision/scale

I would like to know what is the impact of modifying a SQL Server decimal column.

By example I have Decimal(12,2) column and I want to change it to Decimal(13,3)

Facts:

Question 1: If I have over 10 millions or more row, how it will react?

Question 2: Is it going to take a lot of time to upgrade the precision+scale or will it be instantaneous (I am talking about performance of the change being applied)?

I actually tried on small table and it seems to be instantaneous, but I want to be sure before doing it on a huge table.

Upvotes: 4

Views: 1475

Answers (2)

Martin Smith
Martin Smith

Reputation: 453608

The binary representation is different so it needs to perform a logged update to each row.

SELECT CAST(CAST(1 AS Decimal(12,2)) AS VARBINARY), 
       CAST(CAST(1 AS Decimal(13,3)) AS VARBINARY)

Returns

0x0C02000164000000
0x0D030001E8030000

Upvotes: 4

Raj
Raj

Reputation: 10853

It certainly is not going to be instantaneous. I just tried on a table with 175 million rows of data in my test DB. The query has been running for the past 10 minutes and is still in progress.

Also, if there are any indexes on this column, you will have to drop and recreate those indexes, which will add to the total time required.

Raj

Upvotes: 2

Related Questions