Column update structure change

[DaveP]

hi
I want to increase a varchar(5000)
to varchar(8000) on a table that
has approximately million rows.....
What is the impact on the server

or any good recomendations of a action to accomplish this in the best and
fastest way.


thanks davep

[AlterEgo]

Re: Column update structure change

Dave,

First of all, SQL Server maximum rowsize = 8,060 bytes. Make sure you keep
under that limit when you add the #bytes from all of the other columns.

Access to the table, and possibly other objects that are dependent on that
table will be blocked until the schema change is complete. Also, other
non-dependent processes will also be slowed down significantly. A million
row table on recent sever technology should not take an excessive amount of
time to alter.

-- Bill

"DaveP" <dvs_bis@sbcglo bal.netwrote in message
news:S3Jyh.5951 1$wc5.47614@new ssvr25.news.pro digy.net...

hi
I want to increase a varchar(5000)
to varchar(8000) on a table that
has approximately million rows.....
What is the impact on the server
>
or any good recomendations of a action to accomplish this in the best and
fastest way.
>
>
thanks davep
>
>

[Erland Sommarskog]

Re: Column update structure change

DaveP (dvs_bis@sbcglo bal.net) writes:

I want to increase a varchar(5000)
to varchar(8000) on a table that
has approximately million rows.....
What is the impact on the server

This is a metadata change, so it will be about instant.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

Re: Column update structure change

AlterEgo (alterego55@dsl extreme.com) writes:

First of all, SQL Server maximum rowsize = 8,060 bytes. Make sure you
keep under that limit when you add the #bytes from all of the other
columns.

This applies to SQL 2000 only. On SQL 2005 rows can span pages. Not so
that you can have three char(8000) on the same page, but three varchar(8000)
is OK.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[DaveP]

Re: Column update structure change

thanks for the responses...
dave

"Erland Sommarskog" <esquel@sommars kog.sewrote in message
news:Xns98D24AF 9ACFYazorman@12 7.0.0.1...

DaveP (dvs_bis@sbcglo bal.net) writes:

>I want to increase a varchar(5000)
>to varchar(8000) on a table that
>has approximately million rows.....
>What is the impact on the server

>
This is a metadata change, so it will be about instant.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
>
Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx