What happens when I alter a table of MS SQL server 2014

Question

0.00/5 (No votes)

See more:

I would just like to know what happens when I alter a giant table in this database. E.g. when I increase the data space in a column or remove or add one...
Is the table recreated in a long process or can this happen without further changes on storage level...?

Thank you in advance!

What I have tried:

The Microsoft pages didn't give me even a start of Information about this!

Posted 9-May-16 1:04am

herrstress

Updated 9-May-16 1:56am

Add a Solution

Comments

deepankarbhatnagar 9-May-16 7:23am

What do you want to ask, I am not understanding.. where is the problem, have you tried something..?

3 solutions

Solution 3

I assume you are asking about running an ALTER TABLE [] ADD/DORP/ALTER COLUMN [] query, and not about Management Studio...
There are several steps in such query and a lot of depends on, what the column is, and what the state of the current table is (I mean, does the column part of an index, how the physical pages are built, and so on).
Globally, theses are the steps:
1. Change schema (including schema-lock)
2. Rebuild pages (physically and logically) - not always as may the current pages are sufficient...
2.a. Delete/insert the data may exists (since 2012 default values of new columns are not written all over, but defined only once). This step may include locks on the pages while running, but these locks are very short...

So altering can be quick or slow, depending on the kind of ALTER and the current state of the data...

For instance, adding a new column with NO NULL and default value will be down almost immediately, while changing a string column's size can be time consuming, if the change will render the column off-page (means all data of that column have to be moved to new data pages)...

Wit experience, you will be able to predict the time, but it is always a good practice to try first on a test database...

(All this have nothing to say of the case when you modify columns as part of indexes, which complicates all the scenario)

Posted 9-May-16 1:56am

Kornfeld Eliyahu Peter

Solution 1

Basically nothing special would happen. If you just increase the charcount on a column that's nothing special, if you add a new column it should also work fine but i personally would recreate the table, import the data from the old into the new one and then delete the old table. That's definately a "cleaner" way (in my opinion).

Posted 9-May-16 1:49am

HobbyProggy

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2016-05-09T01:50:00

Solution 2

If you increase or decrease a column width, then every piece of data in the table has be be examined and expanded of contracted appropriately. This normally means that SQL will do an internal drop and recreate of the table, which is a time consuming process.
Generally speaking, you don't want to do this on a "live" DB: do it while the DB is offline and it won't annoy anyone!

Posted 9-May-16 1:50am

OriginalGriff

Comments

Kornfeld Eliyahu Peter 9-May-16 7:58am

"SQL will do an internal drop and recreate of the table" - since 2012 there is a huge improvement on how SQL handles data pages, and this almost always will prevent total recreation...

What happens when I alter a table of MS SQL server 2014

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

What happens when I alter a table of MS SQL server 2014

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us