SQL Server: Adding a column increases table size more than expected -

-

to migrate new schema, doing 2 things table:

  1. changing varchar column nvarchar
  2. adding bigint column

to test how affects size, generated 180,000 rows. varchar column changing set null on every row.

here results of sp_spaceused (all sizes in kb):

          rows     reserved     data   index_size  unused           ----     --------     ----   ----------  ------ before    180000     110976     43168       67288     520 after     180000     120320     52536       67296     488

so data grew more 9mb. have expected grow 1.4 mb, because added 8-byte numeric column.

if create table , generate data, size looks more expect:

          rows     reserved     data   index_size  unused           ----     --------     ----   ----------  ------         180000       117760    46184        71352     224

so there adding column causes sp_spaceused report more data in use added?

no 1 explained why happened. dave gugg gave great hint.

sql stores rows in pages. (that 8k, not exactly) , row must fit 1 page (not really, given long varchars can stored on separate page using pointer - yours null, lets ignore now.)

the pages allocated in clusters.

when inserted rows had nice fresh set of pages , clusters , rows neat.

as make change, particularly adding column, sql must update every row.

updating row requires changing 1 or more pages - because changed row may no longer fit on same page. in fact, because of way rollback works, think sql likes move changed rows new page.

so, tightly arranged rows got distributed , took many more pages , clusters. on time many changes, averages out. not every update adds pages - adding column making each row larger , less fit prior neighbors.

we cannot more specific because did not provide schema. if had, we'd have sense of expected rows per page before , after change.


Comments

Post a Comment

Popular posts from this blog

yii2 - Yii 2 Running a Cron in the basic template -

-
i have seen instructions on running cron in advanced template, cant figure out how on basic template. have following controller in basic controllers folder. <?php namespace app\controllers; use yii\console\controller; /** * cron controller */ class croncontroller extends controller { public function actionindex() { echo "cron service runnning"; } public function actionmail($to) { echo "sending mail " . $to; } } i have navigated root of application , tried these comands yii cron php yii cron im getting unknown comand "cron" i know old question failed find actual answer this. if @ code yii exec file you'll notice requires /common/config/aliases.php file console/config/main.php. can naturally change these or copy on these advanced template. hope solves problem wanting same thing.
Read more

asp.net - 'System.Web.HttpContext' does not contain a definition for 'GetOwinContext' Mystery -

-
i realize question might seem trivial some, it's these types of things find myself fighting quite bit , want make sense of despite seeming losing battle in .net (for me anyway). so, if following: using system.web; ... applicationuser user = system.web.httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); that produces error in title , red getowincontext() , error cannot resolve symbol 'getowincontext()' however, if following (remove system.web in front of httpcontext ), works expected (or @ least no errors): using system.web; ... applicationuser user = httpcontext.getowincontext().getusermanager<applicationusermanager>().findbyid(system.web.httpcontext.user.identity.getuserid()); however, if (same line that's working using system.web commented out): //using system.web; ... applicationuser user = httpcontext.getowincontext().getusermana...
Read more

mercurial graft feature, can it copy? -

-
there new feature in mercurial 3, called 'graft' (graft local). job of moving change-set different branch. is there way "copy" change-set branch? i interested in in moving stuff qa branch production branch, still need code reside in qa. want copy. found , have tried few things, maybe it'll become clear more tinkering , reading... does have better way of 'cherry picking' change-sets move cross branch (or trunk)??? graft copies changesets, doesn't move them. hg graft (emphasis mine): this command uses mercurial's merge logic copy individual changes other branches without merging branches in history graph. known 'backporting' or 'cherry-picking' . default, graft copy user, date, , description source changesets.
Read more