homepage Welcome to WebmasterWorld Guest from 54.145.191.14
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
Changing Field Size in SQL Server
on a Live database... I know!
Mr_Brutal

10+ Year Member



 
Msg#: 3 posted 1:47 pm on Aug 30, 2005 (gmt 0)

Hello all,

A quick question please - does changing field size of a varchar field from 2000 to 3000 have any detrimental effects to the data, when working with SQL Server?

Im using Enterprise Manager to work with the database. In MS Access I can change up with no problems and lose data if changing down to less than the size of the data and also get a warning when im decreasing.

Can anyone whose done this on SQL Server give me some pointers. Oh and its a LIVE database with some fairly important data in it :-) (I know! - im just having to support it)

Cheers

 

Mr_Brutal

10+ Year Member



 
Msg#: 3 posted 2:34 pm on Aug 30, 2005 (gmt 0)

Hello all,

Decided to risk it... and it turned out that you can't have much more than 8000 bytes in a single SQL Server row anyway!

So it will require a proper redevelopment rather than a quick fix, which if it comes back to me will allow me to set up a testing environment.

In case anyone ever wants to know a solution is to use a separate table with just a primary key in it and link back the larger fields to the original table/s.

Cheers

incywincy

10+ Year Member



 
Msg#: 3 posted 2:41 pm on Aug 30, 2005 (gmt 0)

Hi Mr Brutal.

I think you just highlighted the case for having an off-line development server. I have this kind of set up and i can mess with my test database as much as I like before applying changes to the live web server.

fischermx

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 3 posted 2:47 pm on Aug 30, 2005 (gmt 0)

You should have no problem in increasing a Varchar column's lenght.
However keep in mind that the total row size should not exced 8KB.

arran

5+ Year Member



 
Msg#: 3 posted 3:36 pm on Sep 5, 2005 (gmt 0)

Also note that during the "alter table" command there will be an exclusive lock on the table so all other processes wishing to access this table will be temporarily blocked.

arran.

carguy84

WebmasterWorld Senior Member 5+ Year Member



 
Msg#: 3 posted 6:35 pm on Sep 9, 2005 (gmt 0)

If you need more space then varchar will allow, use 'ntext' or 'text'

Easy_Coder

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3 posted 1:14 am on Sep 10, 2005 (gmt 0)

Mr_Brutal (next time) if you don't have a testing environment you could simply:

- Script out the table and in the script rename it to something new like [tablenameTEST] and then create that new test table.
- Use DTS to copy the LIVE data into the test table.
- Expand the table column to fit your needs
- Run some unit tests at it.

aspdaddy

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3 posted 5:03 pm on Sep 10, 2005 (gmt 0)

use 'ntext' or 'text'

Bear in mind if you do this you cannot group by text datatypes, so queries and views where you use Max(), Sum() or Count() will probably have to be re-written.

Mr_Brutal

10+ Year Member



 
Msg#: 3 posted 8:33 am on Sep 12, 2005 (gmt 0)

Hi Easy_Coder,

Thats a good idea - basically just copy the table and then try it. Should have thought of that...

My only concern was that it was Live but im sure copying tables on Live databases is well coded for.

Cheers

Red_Eye

10+ Year Member



 
Msg#: 3 posted 3:32 pm on Sep 12, 2005 (gmt 0)

Mr_Brutal.

I have also had a similar problem to this. rather than copy the whole table I have created a new table with a varchar coloumn of 2000 added some data, then increased it to 3000 just to test the concept and it has worked, might save a bit of time.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved