January 4, 2022 at 8:10 pm
I have a big ColumnStore table partitioned on a DATE column (billions of rows)
That table is structured as a Fact table in a star schema, so only DATE, INT and NUMERIC data types involved
Inside that table, I have a column with the following definition : NUMERIC(28,8)
This is what my customer initially wanted
Now, he realized that it's a bit overkill and would like a NUMERIC(19, 2)
Is there a way to alter that column without SQL Server reconstructing the whole table behind the scene?
A simple ALTER TABLE ALTER COLUMN doesn't seem to work
Again, this is a partitioned table which is also a ColumnStore
Billions of rows
Couple of hundred GBs of data
Copying and reconstructing the whole thing is not really an option
Thanks,
Vince
January 5, 2022 at 12:09 am
Going from 28 to 19 would reduce the storage required, so, as you've discovered, I think SQL would create a new column and copy the data over to it.
If you just want the final column data type to be correct, you could rename the original table and create a view with the original table name that included:
CAST(that_column AS decimal(19, 2)) AS that_column
so that the column would now be (19, 2) rather than (28, 8).
If you need more details on how to do that -- it's really easy -- just let me know.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 5, 2022 at 1:10 pm
Thanks for the answer,
We already have views on top of our tables, so it would be easy to do.
But that would serve no purpose.
The main goal would have been to reduce the size of inserted data in order to accelerate the inserts.
Going from 28 to 19 would have saved us 4 bytes per distinct value
The customer doesn't really care to see the column size diminished.
Plus a live conversion upon consumption of large amount of data could have a negative impact.
I guess the main option would be to create a second table with the new structure.
Then we could keep the old one for historical means and fill the new one going forward.
Eventually we could progressively transfer the old data from one table to the other.
But it's a complex thing to do and I don't think that it's worth all the effort
The real insert gain is hard to evaluate anyway, possibly not that much
Thanks,
Vince
January 5, 2022 at 1:26 pm
as you state your table is partitioned maybe you can switch out each partition onto a staging table, convert the table (repeat for all partitions, one table per partition) and then after all partitions are done (main table will be empty at this point) alter main table and switch the staging tables back onto main table.
if the older partitions aren't used this would have only a small impact on the users with minimal downtime to convert the most recently still used partitions.
edit: using the staging/switch option means extra space requirements are nearly null
January 5, 2022 at 2:31 pm
Thanks for the answer,
We already have views on top of our tables, so it would be easy to do. But that would serve no purpose. The main goal would have been to reduce the size of inserted data in order to accelerate the inserts. Going from 28 to 19 would have saved us 4 bytes per distinct value The customer doesn't really care to see the column size diminished. Plus a live conversion upon consumption of large amount of data could have a negative impact. I guess the main option would be to create a second table with the new structure. Then we could keep the old one for historical means and fill the new one going forward. Eventually we could progressively transfer the old data from one table to the other. But it's a complex thing to do and I don't think that it's worth all the effort The real insert gain is hard to evaluate anyway, possibly not that much
Thanks,
Vince
So you're not using ROW compression? Because that would shrink the decimal values to the actual size needed anyway.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 5, 2022 at 4:53 pm
removed
For better, quicker answers, click on the following...
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/
January 5, 2022 at 7:46 pm
So you're not using ROW compression?
Because that would shrink the decimal values to the actual size needed anyway.
I don't think you could use Row Compression on a ColumnStore...
It's already compressed at a Column level
I'm I wrong here?
January 5, 2022 at 7:48 pm
as you state your table is partitioned maybe you can switch out each partition onto a staging table, convert the table (repeat for all partitions, one table per partition) and then after all partitions are done (main table will be empty at this point) alter main table and switch the staging tables back onto main table.
if the older partitions aren't used this would have only a small impact on the users with minimal downtime to convert the most recently still used partitions.
edit: using the staging/switch option means extra space requirements are nearly null
Very interesting idea Frederico.
I'd have to test it but theoretically, I think it should work
Thanks,
Vince
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply