The log operation here is Modify Row. so in this case, the update was done as an in-place update.<\/p>\n Second test, a heap, no indexes at all, and an update that changes the row size (however plenty free space on the page, so no issues with forwarding pointers here)<\/p>\n <\/p>\n Again we have a modify row, so that again was an in-place update.<\/p>\n Last on the heap, let’s see if things change when there’s lots and lots of rows (I’m going to use an update that does not change the row size, I don’t want to involve forwarding pointers in the discussion here)<\/p>\n Still the modify row operation, so it’s not a case that lots of rows will cause SQL to split the update into a delete-insert pair.<\/p>\n Moving on, let’s try a table with a clustered index, an update of a non-key column that does not change the size of the row. Anyone willing to bet what we’ll see?<\/p>\n The log records are a little more complex, there’s a few more than in the previous case, but the operation for the update is still the same – LOP_Modify_Row<\/p>\n Second test with a clustered index, an update of a non-key column that does change the size of the row.<\/p>\n Still have a modify row operation here. Still an in-place update.<\/p>\n Lastly an update of the clustered index key value.<\/p>\n Now we do have a split update. We’ve got a delete_rows and an insert_rows operation in the log. This was not done as an in-place update<\/p>\n So what can we conclude here? Does SQL do all updates as split updates?<\/p>\n It should be clear that, for cases where the index key is not changed, SQL can do updates as in-place updates. I’m not going to try and claim that it always will, that would be silly, there are lots of scenarios that I haven’t looked at (page splits and forwarded rows being among the most obvious), but it can and will do in-place updates.<\/p>\n For updates that change the key values, SQL will not do those as in-place updates. Paul explained that in one of his debunking posts a while back – http:\/\/sqlskills.com\/BLOGS\/PAUL\/post\/Do-changes-to-index-keys-really-do-in-place-updates.aspx<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"![\"UpdateHeapFixedSize\"](\"https:\/\/www.sqlinthewild.co.za\/wp-content\/uploads\/2011\/06\/UpdateHeapFixedSize.png\" "\\"UpdateHeapFixedSize\\"")
<\/a><\/p>\nCREATE TABLE TestingUpdate2 (\r\nID INT IDENTITY,\r\nSomeString VARCHAR(50)\r\n)\r\n\r\nINSERT INTO TestingUpdate2 (SomeString)\r\nVALUES\r\n('One'),('Two'),('Three'),('Four'),('Five'),('Six'),('Seven'),('Eight'),('Nine')\r\n\r\nCHECKPOINT -- truncate the log, DB is in simple recovery.\r\n\r\nUPDATE TestingUpdate2\r\nSET SomeString = 'NotFour'\r\nWHERE ID = 4 -- one row\r\n\r\nSELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog\r\n\r\nDROP TABLE TestingUpdate2<\/pre>\n<\/a><\/p>\nCREATE TABLE TestingUpdate3 (\r\nID INT IDENTITY,\r\nSomeString CHAR(50)\r\n)\r\n\r\nINSERT INTO TestingUpdate3 (SomeString)\r\nSELECT TOP (1000000) ' ' FROM msdb.sys.columns a CROSS JOIN msdb.sys.columns b\r\n\r\nCHECKPOINT -- truncate the log, DB is in simple recovery.\r\n\r\nUPDATE TestingUpdate3\r\nSET SomeString = 'Something'\r\n\r\nSELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog\r\n\r\nDROP TABLE TestingUpdate3<\/pre>\n
<\/a><\/p>\nCREATE TABLE TestingUpdate4 (\r\nID INT IDENTITY,\r\nSomeString CHAR(50)\r\n)\r\n\r\nCREATE CLUSTERED INDEX idx_ID ON TestingUpdate4 (ID)\r\n\r\nINSERT INTO TestingUpdate4 (SomeString)\r\nVALUES\r\n('One'),('Two'),('Three'),('Four'),('Five'),('Six'),('Seven'),('Eight'),('Nine')\r\n\r\nCHECKPOINT -- truncate the log, DB is in simple recovery.\r\n\r\nUPDATE TestingUpdate4\r\nSET SomeString = 'NotFour'\r\nWHERE ID = 4 -- one row\r\n\r\nSELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog\r\n\r\nDROP TABLE TestingUpdate4<\/pre>\n<\/a><\/p>\nCREATE TABLE TestingUpdate5 (\r\nID INT IDENTITY,\r\nSomeString VARCHAR(50)\r\n)\r\n\r\nCREATE CLUSTERED INDEX idx_ID ON TestingUpdate5 (ID)\r\n\r\nINSERT INTO TestingUpdate5 (SomeString)\r\nVALUES\r\n('One'),('Two'),('Three'),('Four'),('Five'),('Six'),('Seven'),('Eight'),('Nine')\r\n\r\nCHECKPOINT -- truncate the log, DB is in simple recovery.\r\n\r\nUPDATE TestingUpdate5\r\nSET SomeString = 'NotFour'\r\nWHERE ID = 4 -- one row\r\n\r\nSELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog\r\n\r\nDROP TABLE TestingUpdate5<\/pre>\n<\/a><\/p>\nCREATE TABLE TestingUpdate6 (\r\nID INT,\r\nSomeString CHAR(50)\r\n)\r\n\r\nCREATE CLUSTERED INDEX idx_ID ON TestingUpdate6 (ID)\r\n\r\nINSERT INTO TestingUpdate6 (ID, SomeString)\r\nVALUES\r\n(1,'One'),(2,'Two'),(3,'Three'),(4,'Four'),(5,'Five'),(6,'Six'),(7,'Seven'),(8,'Eight'),(9,'Nine')\r\n\r\nCHECKPOINT -- truncate the log, DB is in simple recovery.\r\n\r\nUPDATE TestingUpdate6\r\nSET SomeString = 'NotFour',\r\nID = 42\r\nWHERE ID = 4 -- one row\r\n\r\nSELECT Operation, Context, AllocUnitName, [Transaction Name], Description FROM fn_dblog(NULL, NULL) AS TranLog\r\n\r\nDROP TABLE TestingUpdate6<\/pre>\n
<\/a><\/p>\n