Archive

Posts Tagged ‘table designer’

SQL Server – ALTER COLUMN – Management Studio v. T-SQL

04.08.2011 No comments

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:

image

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!

Vishal