November 10, 2016 at 3:03 am
Hi guys,
I was looking into the differences when we issue an update vs delete/insert. Below is my setup and observation
I will be using AdventureWorks2012 Database for creating a sample table
CREATE TABLE dbo.UDI(
[BusinessEntityID] [int] NULL,
[EmailAddressID] [int] IDENTITY(1,1) NOT NULL,
[EmailAddress] [nvarchar](50) NULL)
insert into dbo.UDI(EmailAddress)
select EmailAddress from person.EmailAddress
alter table dbo.UDI add constraint uix unique (EmailAddress)
create clustered index cix on dbo.UDI(EmailAddressID)
dbcc ind('AdventureWorks2012','dbo.UDI',2)
--176 Pages before Update, update made the page count to 178
--Page has 118 records, update made the record count to 59
DBCC TRACEON(3604)
DBCC PAGE('AdventureWorks2012',1,424392,3)
I see the page counts as 178 for the non-clustered index.
I took the first record which was in page 424392, this page had around 118 records.
I updated the email address which was in this page from a0@adventure-works.com TO a00@adventure-works.com
After updating the email address just by a character, I could see 2 new pages added to the index, also the page content (which hold this record) got decreased from 118 to 59
However, when I tried to Delete and Insert the record (with a00@adventure-works.com), I didn't see any change in the page count or data count within the pages.
Now my question is , why updating a record with just a character caused extra pages to be pulled in ?
November 10, 2016 at 4:18 am
What is the fill factor for the index?
😎
November 10, 2016 at 4:36 am
It is 0
However I can see some free 4096 bytes on the page before making the update
November 10, 2016 at 10:01 pm
Expecting some replies here
November 11, 2016 at 7:16 am
er.mayankshukla (11/10/2016)
Expecting some replies here
Maybe try being a bit more polite?
November 14, 2016 at 2:00 am
Ohh, I didn't mean to sound rude.
My bad, if my words expressed a different meaning.
I was doing some code runs with different scenarios and I guess I came around some conclusions.
The difference between Update and Delete/Insert can be seen when we create a Index with the fillfactor of (100/0)
The update caused the page split and contributed to the fragmentation, whereas Delete/Insert didn't, and this actually made sense.
Using fixed length columns like char, did save page splits in spite of using a fillfactor of 0
Any comments ??
November 16, 2016 at 3:33 am
With a fill factor of 0, the page would be full and hence the update will cause page to split. That is understood
Now when I delete the row and re-insert the new row with an extra column, still the row would require an extra bit as it required in case of update.
Then why there is no page split in case of delete & insert scenario.
Kindly check the page information of the table for one of the page that had the row, I planned to update or delete / Insert
Initially the page header information is:
m_slotCnt 196
m_freeCnt 13
m_freeData7787
After we did the update,
m_slotCnt 98
m_freeCnt 4093
m_freeData7822
It happened as the page was splitted and the some data was shifted to a new page, causing more space on the current page
After we did the Delete insert:
m_slotCnt 196
m_freeCnt 12
m_freeData7788
here we see it used one byte from the freeCnt, why didn't it use the same 1 byte when we used the update statement.
November 16, 2016 at 7:37 am
Now, I am no SQL expert, but if I had to guess, I would say it is part of how SQL Server handles ACID.
If you do an update and something goes bad in the back end before the update is committed, you still need the old data. This would make rolling back the transaction a lot faster too as the data still exists on disk until the commit.
whereas if you do a delete followed by an insert, the delete would empty the page and the insert would add data into it. it would be seen as 2 steps in a single transaction (unless you put transaction terminators on there, in which case it would be 2 transactions).
You could always check your transaction logs and see exactly what it is doing:
SELECT [RowLog Contents 0],
[RowLog Contents 1],
[Current LSN],
Operation,
Context,
[Transaction ID],
AllocUnitId,
AllocUnitName,
[Page ID],
[Slot ID]
FROM sys.fn_dblog(NULL,NULL)
code nabbed from stackoverflow (http://stackoverflow.com/questions/9014531/read-sql-server-transaction-log)
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 20, 2016 at 11:02 pm
If you do an update and something goes bad in the back end before the update is committed, you still need the old data. This would make rolling back the transaction a lot faster too as the data still exists on disk until the commit.
whereas if you do a delete followed by an insert, the delete would empty the page and the insert would add data into it. it would be seen as 2 steps in a single transaction (unless you put transaction terminators on there, in which case it would be 2 transactions).
That makes sense, anyone would like to add some thoughts to this ?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply