Whenever we need to update a column length, we can do the same by using wither Table designer in Management Studio or by using T-SQL. Changing this using Management Studio adds an overhead of recreating the table, which can cause external fragmentation in the database. Let’s compare both these methods, I have created a sample table as below:

USE   SqlAndMe
 
– Table structure
CREATE Table ProductList
(
      ProductID   INT,
      Name        VARCHAR(50)
)
 
– Sample Data
INSERT INTO ProductList
VALUES (1,'Adjustable Race'),
       (2,'Bearing Ball'),
       (3,'BB Ball Bearing'),
       (4,'Headset Ball Bearings')

You can check the data and index pages of the table by using DBCC IND(@Databasename, @Tablename, @IndexID).

–@IndexID -1 – for all Index and data pages
DBCC IND('SqlAndMe', ProductList, -1)

Partial Result Set:

PageFID       PagePID       IAMFID        IAMPID        ObjectID        ……
——-           ——-           ——            ——            ———-            ……
1             268           NULL          NULL          1461580245      ……
1             267           1             268           1461580245          ……

From the output of DBCC IND, we can see that paged occupied by the table ProductList are 268 and 267.

Now, let’s alter column [Name] of ProductList to VARCHAR(100):

1. ALTER COLUMN using Management Studio:

You can launch the design view by right-clicking table in object explorer and choosing “Design”, and make the changes:

The script generated by Management Studio for this change is as below:

/* To prevent any potential data loss issues,
you should review this script in detail before
running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_ProductList – (1)
(
      ProductID int NULL,
      Name varchar(100) NULL
)  ON [PRIMARY]
GO
ALTER TABLE dbo.Tmp_ProductList SET (LOCK_ESCALATION = TABLE)
GO
IF EXISTS(SELECT * FROM dbo.ProductList) – (2)
       EXEC(‘INSERT INTO dbo.Tmp_ProductList (ProductID, Name)
             SELECT ProductID, Name FROM dbo.ProductList WITH
             (HOLDLOCK TABLOCKX)’)
GO
DROP TABLE dbo.ProductList – (3)
GO
EXECUTE sp_rename N'dbo.Tmp_ProductList', N'ProductList', 'OBJECT'  – (4)
GO
COMMIT

As you can see from this script that Management Studio does not modify existing table. Instead it,

1. Creates a new table with modified structure,
2. Copies data from old table to new table,
3. DROPs old table, and finally
4. Renames new table to old table’s name

This approach causes the table to move to different set of pages in the database, causing external fragmentation. Also, if you have a large table, this can be time consuming.

After, changing the column using Management Studio, you can verify the movement of the table by using DBCC IND:

DBCC IND('SqlAndMe', ProductList, -1)

Partial Result Set:

PageFID       PagePID       IAMFID        IAMPID        ObjectID
——-           ——-           ——            ——            ———-
1             279           NULL          NULL          1461580245
1             276           1             279           1461580245

From the output you can see that the PagePID have changed.

2. ALTER COLUMN using T-SQL:

Changing column length using T-SQL is straight forward, you can change it using below T-SQL code:

ALTER TABLE ProductList
ALTER COLUMN [Name] VARCHAR(100)

Also, this approach changes the structure in-place, un-like Management Studio it makes changes to original table itself. You can verify the same by checking PagePIDs from the output of DBCC IND.

DBCC IND('SqlAndMe', ProductList, -1)

Partial Result Set:

PageFID       PagePID       IAMFID        IAMPID        ObjectID
——-           ——-           ——            ——            ———-
1             268           NULL          NULL          1461580245
1             267           1             268           1461580245

Hope This Helps! Cheers!