当我更改MS SQL Server 2014表时会发生什么 [英] What happens when I alter a table of MS SQL server 2014

问题描述

我想知道当我在这个数据库中改变一个巨大的表时会发生什么。例如。当我增加一列中的数据空间或删除或添加一个...

是否在一个漫长的过程中重新创建表格，或者这可以在没有进一步更改存储级别的情况下发生...？





提前谢谢！



我尝试过：



Microsoft页面甚至没有给我一个关于此的信息的开头！

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!

推荐答案

我假设你问的是运行 ALTER TABLE [] ADD / DORP / ALTER COLUMN [] 查询，而不是关于Management Studio ......

这样的查询有几个步骤，很多依赖于，列是什么，当前表的状态是什么（我的意思是，索引的列部分，物理页面是如何构建的）等等。

在全球范围内，论文是以下步骤：

1.更改架构（包括架构锁定）

2.重建页面（物理上和逻辑上） - 并不总是当前页面足够ient ...

2.a.删除/插入数据可能存在（因为2012年新列的默认值不是全部写入，而是仅定义一次）。这一步可能包括在运行时对页面进行锁定，但这些锁定非常短......



因此，根据ALTER的类型，更改可以快速或慢速和数据的当前状态...



例如，添加一个NO NULL和默认值的新列几乎会立即下降，同时更改字符串列的大小可能很耗时，如果更改将使列离页（表示该列的所有数据都必须移动到新数据页）...



有经验，你可以预测时间，但首先尝试测试数据库总是一个好习惯...



（所有这些都有当您将列修改为索引的一部分时，没有什么可说的，这会使所有场景复杂化）

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)

如果增加或减少列宽，则表中的每个数据都有适当审查和扩大合同。这通常意味着SQL将执行内部删除并重新创建表，这是一个耗时的过程。

一般来说，您不希望在实时数据库上执行此操作：当数据库离线时它不会惹恼任何人！

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!

基本上没有什么特别的事情会发生。如果你只是增加一个没有什么特别的列的charcount，如果你添加一个新列，它也应该工作正常，但我个人会重新创建表，将数据从旧的导入到新的，然后删除旧表。这绝对是一种更清洁的方式（在我看来）。

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).

这篇关于当我更改MS SQL Server 2014表时会发生什么的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！