Widening Identity Columns

Aaron Bertrand looks at converting an identity integer into an identity bigint:

This is a very disruptive change to the structure of the table, obviously. (And an interesting side observation: the physical order of the columns, RowID and filler, have been flipped on the page.) Reserved space jumps from 136 KB to 264 KB, and average fragmentation bumps up modestly from 33.3% to 40%. This space does not get recovered by a rebuild, online or not, or a reorg, and – as we’ll see shortly – this is not because the table is too small to benefit.

Note: this is true even in the most recent builds of SQL Server 2016 – while more and more operations like this have been improved to become metadata-only operations in modern versions, this one hasn’t been fixed yet, though clearly it could be – again, especially in the case where the column is an IDENTITY column, which can’t be updated by definition.

Read the whole thing.  The clustered key scenario (which will be later in the series) is a bit more interesting to me, as that would be a more common use case for identity values.

Related Posts

Upgrading Cassandra To Version 3

Mikhail Chinkov has a process for upgrading Cassandra from version 2 to the latest release of 3: At first sight it should be obvious. Cassandra is a distributed storage and you’re able to upgrade each node independently. But also it’s a kind of tricky, because Cassandra has so many concepts and moving parts. Introducing such […]

Read More

Updating @@SERVERNAME

Eitan Blumin has a script to change what you get when you reference @@SERVERNAME: If, for whatever reason, the Windows Computer Name is changed after SQL Server is already installed, then @@SERVERNAME and the information in sysservers would not automatically reflect the change. This means that @@SERVERNAME contains the incorrect value for the machine name. Sometimes, and especially in production environments, […]

Read More

Categories

February 2016
MTWTFSS
« Jan Mar »
1234567
891011121314
15161718192021
22232425262728
29