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 |
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
 Posted - 2003-11-04 : 09:15:35
|
| HII have a very simple table with two columns of type SMALLINT. According to BOL, each should take up 2 bytes, therefore a total of 4 bytes per row in this table.My question - the table has 4 million rows, which give or take should take up 16Mb of data (there are no indexes on the table at the moment). However, sp_spaceused reports 50Mb for both 'reserved' and 'data'. Can anyone explain where or why the extra space is getting used?Raymond |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-11-04 : 13:08:46
|
| If you have a primary key, that will also use space as it creates an index to maintain uniqueness. There is also a few bytes of overhead per row of data, even without any indexes this would add up very quickly for a table with that many rows. And there will be even more overhead if either column is nullable. |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-11-05 : 00:57:13
|
| see http://www.nigelrivett.net/PageStructure.htmlThere is overhead for every page and for each record. Looks like about 10 bytes per rec but haven't tested it.==========================================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. |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2003-11-06 : 06:10:13
|
| Thanks guys.You're right NR, my calculations show abut 10 bytes overhead as well. That's bigger than the row size which is a pity. The reason for the concern is that my provider charges us for db size and this seemingly innocuos table is taking up a helluva lot of space!Guess I'll have to live with it as I can't make the design any simpler.Raymond |
|
|
|
|
|
|
using smallint