| Author |
Topic |
|
rmg1
Constraint Violating Yak Guru
256 Posts |
 Posted - 2011-10-04 : 04:49:30
|
| Hi allI've adjusted a table (reducing nvarchar field sizes) and the new table is larger (for data space) than the old.Am I safe to assume that this is because of all the changes to the field sizes and that the data space will reduce after the drive has been defragged?If not, how else can I reduce the size of the table? |
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-04 : 05:06:10
|
| Might be that you have a Fill Factor on the index, and changing the column size has recreated ?? the table, thus at optimal fill =- which may be less dense than before.Did you change from VARCHAR to Nvarchar perhaps?"the new table is larger (for data space) than the old"How are you querying that size? |
 |
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2011-10-04 : 05:45:12
|
| I'm right-clicking on the table in question and choosing Properties to get the Index Spacve and Data Space in MB.All the altered fields have remained NVARCHAR and have just had the length reduced. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-04 : 05:56:47
|
| Drive defrag, no. Clustered index rebuild, maybe. Also look up DBCC CleanTable.--Gail ShawSQL Server MVP |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-04 : 06:16:36
|
| "Clustered index rebuild, maybe"Won't changing the size of the column have required a recreate? or might that have done INSERTS in something other than clustered index order? (Not thought about that before, I don't think there is an ORDER BY on the script generated by SSMS - might be worthwhile doing that in future so that the newly re-created table is fully filled? |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2011-10-04 : 06:27:50
|
| OK, thanks for the input.I'm still a newbie on all this lot so can someone do me a favour and explain this in laymans terms (I'm learning as I go)? |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-04 : 08:20:04
|
quote:
Originally posted by Kristen
"Clustered index rebuild, maybe"Won't changing the size of the column have required a recreate?
No. It would likely have been just a metadata operation, not affecting the rows until there's a rebuild. I'm not 100% sure, but I think that's how nvarchar column size reduction works--Gail ShawSQL Server MVP |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2011-10-04 : 08:33:02
|
| I'm going to do a full rebuild on the index and see what happens with that. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-04 : 08:37:29
|
| I got off my lazy bones and tried reducing a VARCHAR(4) to VARCHAR(3) here, using SSMS designer, and it generated a recreate-table script (not that that is conclusive that it cann't be done without).The SSMS script has no ORDER BY for the INSERT INTO NewTable(Col1, COl2, ...) SELECT Col1, COl2, ... FROM OldTableBut maybe SQL is likely to do it in clustered index order anyway - the INSERT uses WITH (HOLDLOCK TABLOCKX) if that makes it any more likely. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2011-10-04 : 08:37:54
|
quote:
Originally posted by rmg1
I'm going to do a full rebuild on the index and see what happens with that.
Sounds reasonable  |
|
|
|
rmg1
Constraint Violating Yak Guru
256 Posts |
Posted - 2011-10-04 : 08:48:20
|
| I did it slightly differently.I created the table, did an INSERT and then changed the field sizes (use ALTER COLUMN) to make sure nothing was lost.I'm only working on 10,000 rows as a test but SQL decided to double the disk space for data (it went from 5MB to 10MB).Now I'm working on 10,000,000 rows (which is taking a while), I'll do the same and see what happens with that. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-10-04 : 09:11:19
|
quote:
Originally posted by Kristen
I got off my lazy bones and tried reducing a VARCHAR(4) to VARCHAR(3) here, using SSMS designer, and it generated a recreate-table script (not that that is conclusive that it cann't be done without).
It can be done without. SSMS generates terrible code and does schema modifications in a stupid way.--Gail ShawSQL Server MVP |
|
|
|
|
Table size on SQL Server 2008 R2