Here is the quickly baked test script:

DROP TABLE TestCalendar

CREATE TABLE [dbo].[TestCalendar](

[ID] [bigint] NOT NULL,

[N_Date] [datetime] NOT NULL,

[WeekDay] [smallint] NULL,

PRIMARY KEY (ID) WITH FILLFACTOR = 100

)

CREATE TABLE [dbo].[Tmp_TestCalendar](

[ID] [bigint] NOT NULL,

[N_Date] [datetime] NOT NULL,

[WeekDay] [int] NULL,

PRIMARY KEY (ID) WITH FILLFACTOR = 100

)

SET NOCOUNT ON

INSERT INTO dbo.TestCalendar ( ID, N_Date, WeekDay )

SELECT tg.N ID, DATEADD(dd, N, 0) N_Date, CONVERT(SMALLINT, n%7 +1 ) WeekDay

FROM Service.dbo.TallyGenerator(0, 50000, NULL, 1) tg

ORDER BY ID

WHILE EXISTS (SELECT * FROM dbo.TestCalendar T1

WHERE NOT EXISTS (SELECT * FROM dbo.[Tmp_TestCalendar] T2

WHERE T2.ID = T1.ID)

)

BEGIN

INSERT INTO dbo.Tmp_TestCalendar ( ID, N_Date, WeekDay )

SELECT TOP 1000 ID, N_Date, WeekDay

FROM dbo.TestCalendar T1

WHERE NOT EXISTS (SELECT * FROM dbo.[Tmp_TestCalendar] T2

WHERE T2.ID = T1.ID)

ORDER BY ID

END

DBCC SHOWCONTIG (TestCalendar) WITH ALL_INDEXES--, TABLERESULTS

DBCC SHOWCONTIG (Tmp_TestCalendar) WITH ALL_INDEXES--, TABLERESULTS

EXEC sys.sp_spaceused @objname = N'TestCalendar', @updateusage = 'true'

ALTER TABLE [dbo].TestCalendar ADD WeekDay_int int NULL;

EXEC sys.sp_spaceused @objname = N'TestCalendar', @updateusage = 'true'

GO

UPDATE [dbo].TestCalendar

SET WeekDay_int = WeekDay

EXEC sys.sp_spaceused @objname = N'TestCalendar', @updateusage = 'true'

ALTER TABLE [dbo].TestCalendar DROP COLUMN [WeekDay];

EXEC sp_rename 'dbo.TestCalendar.WeekDay_int', 'WeekDay', 'COLUMN';

EXEC sys.sp_spaceused @objname = N'TestCalendar', @updateusage = 'true'

DBCC SHOWCONTIG (TestCalendar) WITH ALL_INDEXES--, TABLERESULTS

DROP TABLE dbo.TestCalendar

EXECUTE sp_rename N'dbo.Tmp_TestCalendar', N'TestCalendar', 'OBJECT'

EXEC sys.sp_spaceused @objname = N'TestCalendar', @updateusage = 'true'

DBCC SHOWCONTIG (TestCalendar) WITH ALL_INDEXES--, TABLERESULTS

And here what SHOWCONTIG returns:

Initial population of TestCalendar:

DBCC SHOWCONTIG scanning 'TestCalendar' table...

Table: 'TestCalendar' (997578592); index ID: 1, database ID: 2

TABLE level scan performed.

- Pages Scanned................................: 168

- Extents Scanned..............................: 23

- Extent Switches..............................: 22

- Avg. Pages per Extent........................: 7.3

- Scan Density [Best Count:Actual Count].......: 91.30% [21:23]

- Logical Scan Fragmentation ..................: 1.79%

- Extent Scan Fragmentation ...................: 4.35%

- Avg. Bytes Free per Page.....................: 60.1

- Avg. Page Density (full).....................: 99.26%

"Sequentially" populated Tmp_TestCalendar:

DBCC SHOWCONTIG scanning 'Tmp_TestCalendar' table...

Table: 'Tmp_TestCalendar' (1061578820); index ID: 1, database ID: 2

TABLE level scan performed.

- Pages Scanned................................: 180

- Extents Scanned..............................: 27

- Extent Switches..............................: 26

- Avg. Pages per Extent........................: 6.7

- Scan Density [Best Count:Actual Count].......: 85.19% [23:27]

- Logical Scan Fragmentation ..................: 2.78%

- Extent Scan Fragmentation ...................: 11.11%

- Avg. Bytes Free per Page.....................: 40.3

- Avg. Page Density (full).....................: 99.50%

Number of pages is slightly bigger, as we replaced 2 byte smallint with 4 byte int.

Now - after altering the column in TestCalendar:

DBCC SHOWCONTIG scanning 'TestCalendar' table...

Table: 'TestCalendar' (997578592); index ID: 1, database ID: 2

TABLE level scan performed.

- Pages Scanned................................: 335

- Extents Scanned..............................: 43

- Extent Switches..............................: 334

- Avg. Pages per Extent........................: 7.8

- Scan Density [Best Count:Actual Count].......: 12.54% [42:335]

- Logical Scan Fragmentation ..................: 99.70%

- Extent Scan Fragmentation ...................: 4.65%

- Avg. Bytes Free per Page.....................: 3469.0

- Avg. Page Density (full).....................: 57.14%

As promised - number of pages doubled.

Fragmentation - through the roof.

Scan density - miserable.

Must be a lot of data manipulations done on the way, I suppose.

And we ended up with a mess in the table you have to tidy up afterwards.

Rebuilding indexes on a big table would require as much resources as the whole previous exercise.

And now - after renaming Tmp_TestCalendar to TestCalendar:

DBCC SHOWCONTIG scanning 'TestCalendar' table...

Table: 'TestCalendar' (1061578820); index ID: 1, database ID: 2

TABLE level scan performed.

- Pages Scanned................................: 180

- Extents Scanned..............................: 27

- Extent Switches..............................: 26

- Avg. Pages per Extent........................: 6.7

- Scan Density [Best Count:Actual Count].......: 85.19% [23:27]

- Logical Scan Fragmentation ..................: 2.78%

- Extent Scan Fragmentation ...................: 11.11%

- Avg. Bytes Free per Page.....................: 40.3

- Avg. Page Density (full).....................: 99.50%

Nothing's changed comparing to Tmp_TestCalendar.

Not a single byte of the data got relocated.

We've got perfectly allocated and continuous data and index pages.

Which way you'd suggest to choose for half a TB table?