Change VARCHAR column size

[andyoye]

On my production sql server(sql server 2000, SP4), I have a table with column like

xyz varchar(2000)

Q1: I guess the max size a varchar data-type can have is 8000 ??

Q2: How can I increase the size of the column that has data in it?

Q3: Can I change it to CHAR type? as the column is going to have then 20,000 characters.

Thanks

 

[gmmastros]

xyz varchar(2000)

Q1: I guess the max size a varchar data-type can have is 8000 ??

Yes

Q2: How can I increase the size of the column that has data in it?

Alter Table [blue]TableName[/blue] Alter Column [blue]ColumnName[/blue] VarChar([blue]Size[/blue])

This will work as long as the new size is bigger than the old size.

Q3: Can I change it to CHAR type? as the column is going to have then 20,000 characters.

You can change it to char, but it won't help. SQL Server has a limit on the size of row. Basically, you add all the sizes of all the fields in a table. It cannot exceed 8060 bytes. The exception is with text fields. Text fields store a 16 byte pointer in the row. The real data is stored elsewhere. You can store gigabytes worth of data in a text field.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[andyoye]

So what should I do now?? Create a new table, drop the old table and change name of the new table to old table's name?

Kindly provide more detail....

How can I store 20,000 characters in a table with 4 columns.

currently my table stucture is

xyz varchar(2000)
abc varchar(2000)
pqr varchar(2000)
def varchar(2000)

 

[mrdenny]

As qmmastros said in order to store more than 8000 characters per row you'll need to use a field with the data type of text.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[andyoye]

Thanks all, but what should I do with my existing table?

Can I change varchar to text with Alter table command?

 

[andyoye]

Like I said,

current table description is

fname varchar(2000)
lname varchar(2000)
address varchar(2000)

I want to store more then 8000 bytes in EACH field

 

[mrdenny]

Yes you should be able to do this with an ALTER TABLE command.

alter table Table1
alter column fname TEXT
go
alter table Table1
alter column lname TEXT
go
alter table Table1
alter column address TEXT
go

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[andyoye]

Q1:What will be the impact on performace after converting to text?

Q2:How would I be able to see the text entered into those fields? I guess QA is not going to work with that long strings.

thanks in advance

 

[gmmastros]

I see this:

[blue]fname varchar(2000)
lname varchar(2000)
address varchar(2000)[/blue]

and immediately see [!]red flags[/!]. I assume you're storing first name, last name and address. Can you explain why you need such large fields to store relatively small amounts of data?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

Q1: There is a performance impact is using Text fields so much. The major impact will be storage.

Q1: You can tell QA to display more info that the 256 characters it will display. I beleive that it limit's out at about 8k however per field. Beyond that you'll need to write an app to view/edit the data.

gmmastros does bring up a good point. How long are the first name, last name and address of your users / customers that you need more than a couple of hundred characters per record?

Typically when I define these fields 50 characters for first name and last name and 100 characters for address are more than enough.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[andyoye]

I just mentioned fname, lname, address for ease.
In reality my form has 10 questions, so actuall table looks like

ques1 varchar(2000)
ques2 varchar(2000)
.
.
.
ques10 varchar(2000)


ques1 varchar(2000)

Q: Does "text" acts like varchar for storage? I mean its variable in lenght like varchar?

 

[gmmastros]

I sugest you normalize this table. There are plenty of good tutorials on database normalization. Google on 'database normalization' and spend 30 minutes reading. You'll be glad you did.

If this were my system, I would have a Question table. This table would have a QuestionNumberId (ranging from 1 to 10), and then a single Question varchar(2000) field with the actual question. By designing the table this way, you can easily add another question (just insert another record in to the table), and you'll be able to store your question in a varchar field instead of a text field.

When you want to get the 3rd question...
Select Question From Questions Where QuestionNumber = 3

I hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[mrdenny]

And yes text is a variable length field. It supports up to 2 Gigs per record.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com