reducing the size of a column in sql table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

reducing the size of a column in sql table

Author

Topic

cirugio
Yak Posting Veteran

90 Posts

Posted - 2012-03-20 : 15:19:33

I currently have a sql table which we receive from the vendor that houses over 800 fields. The vendor provides us with the maximum size of each field, even though our company may not utilize the entire length for that specific field. For Example:

dbo.CustData

Instrument_ID (nvarchar(40),null)
Term_maturity (numeric(8,0),null)
Payment_amt (numeric(18,3),null)
.
.
.

I would like to rewrite this table so that each field is smaller. I was utilizing the ALTER TABLE ALTER COLUMN commands and it works fine when I increase/decrease the size of my numeric fields. I just get an error when I try to reduce the size of my
nvarchar field. The error basically states "String or binary data would be truncated. The statement has been terminated". Is there a way around this. Below is the query I created:

ALTER TABLE CustData ALTER COLUMN Instrument_ID nvarchar(20),null;
ALTER TABLE CustData ALTER COLUMN Term_maturity numeric(10,0), null;
ALTER TABLE CustData ALTER COLUMN Payment_amt numeric(15,5),null;

Any suggestions or better way of doing this would be greatly appreciated it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 15:22:07

that means you already have some data in field which is having length above the one you're trying to set it
so first check that and make sure you set it having length enough to hold current data

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirugio
Yak Posting Veteran

90 Posts

Posted - 2012-03-20 : 15:36:17

Thanks Visakh16,
So what you are telling me, is that basically it will never make the field smaller if it truncates values. Is there a way around this, or I have no choice?

Also, can you tell me, is there a way to have one Alter Table statement when modifying mutliple columns? If yes, would you mind providing me an example? thanks again

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-20 : 15:41:57

quote:
Originally posted by cirugio

Thanks Visakh16,
So what you are telling me, is that basically it will never make the field smaller if it truncates values. Is there a way around this, or I have no choice?

Also, can you tell me, is there a way to have one Alter Table statement when modifying mutliple columns? If yes, would you mind providing me an example? thanks again

If you want to reduce the size of column that already has data larger than you want to make it you need to update the column first with the "truncated" value; then alter the column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 15:42:33

it it already have values it wont truncate automatically. you've to first apply update logic to truncate it and then do ALTER ....COLUMN

also ALTER TABLE.... ALTER COLUMN ... has to be repeated for each column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirugio
Yak Posting Veteran

90 Posts

Posted - 2012-03-20 : 15:43:43

How about if I just want to truncate whats already in there?

cirugio
Yak Posting Veteran

90 Posts

Posted - 2012-03-20 : 15:52:03

Visakh16, can you provide me with an example of how I would update it with the truncated value. Sorry, I am a bit unclear.
For instance:

. if the field I want to truncate is: Instrument_id nvarchar(40) and contains the value of 'John Smith 445 N. Denvor Street, Colardo'
. and I want to it to truncate to 30 characters so that the field would contain 'John Smith 445 N. Denvor Stree' without specifically typing in the values in the update statement.

how would I use the update function ? Would I have to use a substring in the update? Sorry, I am fairly new to sql, so have a little learning curve. Thanks again.

X002548
Not Just a Number

15586 Posts

Posted - 2012-03-20 : 15:56:52

I would NOT TOUCH anything from the Vendor. You can void any service agreement you may have or violate some other contractual arrangement...

BUT, Just like every SCRUB Vendor I've seen...a table with 800 columns...every product I've seen is GARBAGE

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 15:58:48

quote:
Originally posted by cirugio

Visakh16, can you provide me with an example of how I would update it with the truncated value. Sorry, I am a bit unclear.
For instance:

. if the field I want to truncate is: Instrument_id nvarchar(40) and contains the value of 'John Smith 445 N. Denvor Street, Colardo'
. and I want to it to truncate to 30 characters so that the field would contain 'John Smith 445 N. Denvor Stree' without specifically typing in the values in the update statement.

how would I use the update function ? Would I have to use a substring in the update? Sorry, I am fairly new to sql, so have a little learning curve. Thanks again.

you would do it like


UPDATE table
SET Column=LEFT(Column,30)

but why should you do it that way
are you aware that this will cause of loss of information. I wont do that unless I'm sure that whatever data it had was junk or had no relevance

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirugio
Yak Posting Veteran

90 Posts

Posted - 2012-03-20 : 16:03:28

Perfect! Thank you Visakh16.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 16:06:12

quote:
Originally posted by cirugio

Perfect! Thank you Visakh16.

you didnt answer my question
are you aware of consequences??

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

cirugio
Yak Posting Veteran

90 Posts

Posted - 2012-03-20 : 16:46:46

Yes, I am aware of the consequences. The data that is in the tables is basically garbage test data that was not cleared out from when the applications was in UAT. The fields which I would be working with are just user fields and do not relate to production data. My changes will not impact anything. Thank you for validating.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-20 : 16:59:10

ok...then fine with that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources