Database Reference
In-Depth Information
2.
Alteration requires changing the metadata only, but SQL Server needs to scan the table
data to make sure it conforms to the new definition. You can think about changing a
nullable column to be not nullable as an example. SQL Server needs to scan all data rows
in the table to make sure that there are no null values stored in a particular column before
changing the table metadata. Another example is changing a column data type to one with
a smaller scope of domain values. If you change an
int
column to
smallint
, SQL Server
needs to check if there are any rows with values outside of the
smallint
boundaries.
3.
Alteration requires changing every data row in addition to the metadata. An example of
such an operation is changing a column data type in a way that requires either a different
storage format or type conversion. For example, when you change a fixed-length
char
column to
varchar
, SQL Server needs to move the data from the fixed- to the variable-
length section of the row. Another example is when changing
char
data type to
int
. This
operation works as long as all
char
values can be converted to
int
, but SQL Server must
physically update every data row in the table converting the data.
■
Starting with SQL Server 2012, adding a new noT nULL column with the default constraint is a metadata-only
operation. Prior versions of SQL Server scan and update every data row in the table.
Note
Unfortunately, table alteration never decreases the size of a data row
.
When you drop a column from a table, SQL
Server does not reclaim the space that the column used.
When you change the data type to decrease the data length, for example from
int
to
smallint
, SQL Server
continues to use same amount of the storage space as before while checking that row values conform to the new data
type domain values.
When you change the data type to increase the data length, for example from
int
to
bigint
, SQL Server adds the
new column under the hood and copies the original data to the new column in all data rows, leaving the space used
by the old column intact.
Let's look at the following example. Listing 1-18 creates a table and checks the column offsets on the table.
Listing 1-18.
Table alteration: Table creation and original column offsets check
create table dbo.AlterDemo
(
ID int not null,
Col1 int null,
Col2 bigint null,
Col3 char(10) null,
Col4 tinyint null
);
select
c.column_id, c.Name, ipc.leaf_offset as [Offset in Row]
,ipc.max_inrow_length as [Max Length], ipc.system_type_id as [Column Type]
from
sys.system_internals_partition_columns ipc join sys.partitions p on
ipc.partition_id = p.partition_id
join sys.columns c on
