Altering column from varchar(2048) to varchar(max) performance

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 General Discussion » Altering column from varchar(2048) to...

Altering column from varchar(2048) to varchar(max) performance

Rate Topic

Display Mode

Topic Options

Author

Message

Mike Byrd

Posted Wednesday, July 29, 2009 12:25 PM

SSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 8:02 AM
Points: 80, Visits: 341

If I run following TSQL (original column defined as VARCHAR(2048)

ALTER TABLE dbo.SystemLog
ALTER COLUMN LogData VARCHAR(MAX);

It takes ~75 minutes to complete, but if I run (original column defined as VARCHAR(2048)

ALTER TABLE dbo.SystemLog
ALTER COLUMN LogData VARCHAR(8000);

It only takes 2 seconds to complete (refreshed copy of original table).

Can anybody tell me why the conversion from VARCHAR(2048) to VARCHAR(MAX) takes so long. It was my understanding that data within a varchar(max) column was retained in the table data if it fit within the page, but if not, then moved elsewhere like the deprecated TEXT field was. Obviously something is going on.

Any ideas please? Thanks in advance.

Mike Byrd

Post #761866

ALZDBA

Posted Wednesday, July 29, 2009 1:16 PM

SSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 2:17 AM
Points: 6,862, Visits: 8,049

altering to varchar(8000) is only a catalog operation (and maybe an index op if the column is indexed) because it stays the same datatype, only the var length changes.
Changing to datatype varchar(max) is actually a change to an other datatype ! (max characteristics)

Johan

     Jul 13

Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

- How to post Performance Problems
- How to post data/code to get the best help

- How to prevent a sore throat after hours of presenting ppt ?

"press F1 for solution", "press shift+F1 for urgent solution" BigGrin

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me Hehe

Post #761922

Jeff Moden

Posted Wednesday, July 29, 2009 4:24 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:24 PM
Points: 32,913, Visits: 26,806

ALZDBA (7/29/2009)

This also drives the data to an "out of row" condition. It has to find a reference and then go outside the row and page to find the data. All of that takes extra time.

--Jeff Moden

"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #762019

« Prev Topic | Next Topic »

Permissions