Sql How to Read From Large Column Quickly

Irresolute the datatype of an large table

03 Aug 2020 10918 views 0 minutes to read Contributors

When yous change the datatype of (big) tables (in rows or in size) you accept to consider a few things:

•  the fourth dimension information technology takes to perform the activeness (=downtime of the table)
•  the corporeality of information that needs to be restructured (I/O operations)
•  the temporary LOG and TEMPDB workspace needed for the transactions

ALTER TABLE Modify Column VARCHAR(MAX)

1. Altering an existing table will be performed in a single transaction.
2. The time needed for altering a table will be depending on the speed of the I/O operations and the amount of bachelor retentiveness.
3. Because information technology is a single transaction, the size of the table x 1.v+ will need to fit in the LOG space.

The change argument will get through every record and attempt to restructure information technology to the new structure. It will need also to rebuild the indexes

In case the transaction is existence rolled back when yous run out of transaction log space it will accept 5x the time to roll back.

Prerequisites

• Make sure you lot have more than enough LOG space available. Practise non calculate it on one.5x the size of the table but preserve more than 2x the size of the table at least.
• Create boosted emergency logfiles on drives that have enough diskspace (if they are not used, you can drib them after the operation).
• Y'all volition have an enormous amount of data operations. Every tape will be updated and in case of an failure rolled back. Your deejay subsystem volition be fully utilized for the length of the operation.
• Your functioning will cake all admission to the affected tabular array.

An important rule to remember is that you cannot influence the functioning. Once it is started ii things can happen, ane the functioning volition complete succesfully, two the functioning volition be rolled back. During the operation you can only wait and monitor your system. You will also accept limited possibilities to intervene.
Because of this rule y'all accept to set up carefully. If you do not prepare and the functioning will neglect after 1 60 minutes, you will accept to wait four-five hours before it is rolled dorsum.

Alternatives

As a database administrator we always want to be able to control the actions we perform. Therefore nosotros have the road of least resistance and maximum control.

Instead of altering a large table practise the following:

1. rename the table you lot want to change
2. create a new table with the original name with the new datastructure
3. in batches - copy the data into the new tabular array

In case your table is in replication, remove information technology from replication, add the new table to replication without initializing (no snapshot will be created, the original subscriber table will stay in place and new records will exist replicated)

This approach makes your operation a very fast one. Your new structure will be directly available. The copying of information you tin control. You can do batch copies or segmented BCP out/in'south

Think of it this way, the result of both operations is equal. the corporeality of resources, time and control significantly works in your advantage.

Big Databases

Big databases require a different way of thinking and planning.

brownflery1992.blogspot.com

Source: https://www.sqltreeo.com/docs/changing-the-datatype-of-an-large-table

Share this post