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 |
|
Westley
Posting Yak Master
229 Posts |
 Posted - 2007-08-30 : 04:54:09
|
| Guys, Just wondering, do we know why is SQL actually doing when we do the following:ALTER TABLE <table Name> ALTER COLUMN <column name> varchar(15) NOT NULLWhat do it really do? Do it need to update every single record, as i notice that it use up a lot of transaction log size and wondering what is doing inside SQL.Thanks |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-30 : 05:01:05
|
| Depends what the column was before.Add a NULL varchar column and I don't suppose it does anything except change the schema (actually I suppose it needs to set every Row to be NULL in that column)Add a DEFAULT too and that WILL need to put a value on every row.Change a row from varchar(10) to varchar(11) probably does nothing (except change the schema). Whereas change from varchar(11) to varchar(10) will have to Trim any over-length rows.Lots that MIGHT need to be done on a Column change!Plus it tends to muck up the efficiency of the storage - so you might be better with:Create new tableCopy data acrossDrop original tableRename new table to original nameplus all the inherent sort-out of PKs, FKs, Indexes, etc.Kristen |
 |
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2007-08-30 : 05:04:05
|
| it was pretty much from a varchar(15) NULL to a varchar(15) NOT NULL, with all the records inside already non-NULL. I expect it should be fast and just a simple change, but it seems like its not. So I'm just wondering is it trying to create the table and copying the data for me inside SQL, but from the profiler trace, there is nothing showing in that trace as well, and its using like 20G of log files, and I cannot see what its using it. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-08-30 : 05:21:18
|
| "So I'm just wondering is it trying to create the table and copying the data for me inside SQL"No, I don't think it ever does that. When that becomes necessary then SQL disallows the ALTER COLUMN approach, and you have to resort to scripting the change more fully.(If you are using the GUI tools they will automatically generate the script for the Create/Copy/Drop/Rename stuff)Kristen |
|
|
|
Westley
Posting Yak Master
229 Posts |
Posted - 2007-08-30 : 22:20:51
|
| Yeah, i know the GUI does the recreation of tables as you can see that from the script. I just trying to find out why it use up that much log space and why it is taking that long. Seems like nothing much I can find from BOL :) |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2007-09-04 : 15:59:45
|
| i think there was discussion before with regards to altering column definitions...mssql recreates the table with the new definition, i think that wasBut I'm not a guru and my memory is starting to fail me...--------------------keeping it simple... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-04 : 16:23:07
|
| Nigel has some stuff on his site about how the system info. is shuffled about if you do an in-situ ALTER of the table (rather than creating a new table, and copying the data across)Kristen |
|
|
|
|
|
|
|
|
Alter column