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 |
|
coder
Starting Member
16 Posts |
 Posted - 2007-02-26 : 11:27:35
|
| Hi,I was wondering how to modify a column's properties, like its datatype or length.I know I'm supposed to do something like this:ALTER TABLE MyTable ALTER COLUMN MyColumn int(20)My main concern is, what will the database do to all the data in the column, if the column used to be, say nvarchar(50)? Will I lose the data in the column if I change the datatype? And what if I had data in this column that was longer than 20 characters? Will the data now be truncated? What can I do to make sure that nothing happens to the data once I change the datatype?Any help would be greatly appreciated! Thanks! =) |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2007-02-26 : 11:39:23
|
| Well, you really don't supply a concreate exampleIf you want to change a data type of char to int, you will need to check to make sure all of the data is validSELECT * FROM table where ISNUMERIC(Col) = 0Will shouw you all rows that are not numeric, in which case, the Alter would fail.I suggest you use Enterprise Mangler, change the column type, and save the script to seel what M$ had in mind. (Here's a hint, it won't be an ALTER)Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxAdd yourself!http://www.frappr.com/sqlteam |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-02-26 : 15:30:11
|
| If you are shrinking the size of the varchar column, then yes you could lose data if there is any data that is larger than the new size. It would truncate it to make it fit. Tara Kizer |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-02-27 : 03:20:43
|
| This is often handled by:Drop constraints on Old tableCreate new TEMP table with all the columns using the right datatype / size / etcCopy the data from Old table to New table with any required truncation and/or explicit data conversionDrop the old dataRename the Temp table to the original table nameCreate PK, Indexes, Constraints, FKs etc.Kristen |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-02-27 : 04:08:15
|
| If you change the size of a fixed length column then it will update each row in the table. It will leave the old data there but copy it to the new column. It will also mean that the data offsets do not follow the order of the column IDs - not a problem. It also means that you are increasing the size of each row and may spill over the page size unnecessarily.If you change the size of a variable length column it will just change the metadata and not affect the data.seehttp://www.nigelrivett.net/SQLAdmin/AlterTableProblems.html==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
how to modify a column without changing data