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.
| Author |
Topic |
|
stowaway
Starting Member
7 Posts |
 Posted - 2010-12-08 : 04:38:39
|
| I created about a year ago.. (stupidly before I did my Database concepts class)I decided to use nchar(10) for phone numbers thinking that numbers shouldnt be over 10 characters.. this has turned into a big mistake... as some people use spaces. other people are international etc etc.. The database is full.. is there a way to make it nchar(20) or even varchar(20) which would suit me better so i dont have to filter the white padding spaces out.. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-08 : 04:48:51
|
| >> The database is fullWhat do you mean by that - if you have run out of space then you won't be able to expand the size of the column as that would take more space.I take it this column is not nullable in which case you can't change it in place - will need to update all the existing data. Given that you should change to varchar(20) - if that is enough space.This means copying the table dropping the old and renaming the new + creating any indexes and constraints and permissions involved.You can get management studio to generate a script by using the table designer.But if you have no space in the database and can't expand it then you are stuck anyway.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
pk_bohra
Master Smack Fu Yak Hacker
1182 Posts |
Posted - 2010-12-08 : 04:52:17
|
| You are increasing the size of the column. I don't see any issue unless some contraints exists on column.Example:create table #test(telNo nchar(10))Insert into #testValues ('12536 1255')Alter table #testalter column telno nvarchar(20) |
|
|
|
stowaway
Starting Member
7 Posts |
Posted - 2010-12-08 : 05:03:38
|
| sorry to get my terminology wrong. when i said full i meant not empty. as in it had data in it..Secondly. I should have jsut tried SQL instead of using the SQL MANAGEMENT which was telling me i had to drop it :)I did the alter table and it worked.simple fix for a simple problem :) thanks for the help |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-12-08 : 05:32:05
|
| Not quite so simple.By altering a fixed length column you will have created a duplicate of the column in the table as it can't expand the current column without move all of the data in each row. What it does is leaves the old column data where it is and creates a new copy at the end of the row.What you probably have now is the old rows still physically in the table but not used.New copies of the rows with the altered column duplicated - the old version not accessible and the new version appended to the fixed length columns.For new data it shouldn't be an issue - the columns will physically not be in the order as displayed but that's not an issue.Have a look athttp://www.nigelrivett.net/SQLAdmin/AlterTableProblems.htmlThat's a bit out of date but is still mostly applicable. xoffset no longer exists and but you can see how the table is physically stored.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
Change in datatype Size without drop?