[AccessD] How does SQL Server do updates

I suppose it depends do you update clustered index field(s)' value(s) or not:

- if not then MS SQL Server should first try to update row/record in place by reallocating free space usually existing on every page, if not - then split the page...
- if clustered index field is updated then record should be moved to the page to be in order with clustered index, and again MS SQL server should first try to insert record by using free space on target page, if not - then split the page...

"deleted" and "inserted" are logical/virtual tables...

Thank you.

-- Shamil


Mon, 8 Oct 2012 11:01:27 -0700 от Francisco Tapia <fhtapia at gmail.com>:
>	
>
>
	
	
>
		
		
			
>that's been my experience as well...
>
>
-Francisco
>
--------------------------
>
You should follow me on twitter here <http://twitter.com/seecoolguy>
>
Blogs: SqlThis! <http://bit.ly/sqlthis>  | XCodeThis!<http://bit.ly/xcodethis>
>
>
<http://db.tt/JeXURAx>
>
>
>
>
>
On Mon, Oct 8, 2012 at 8:36 AM, Arthur Fuller <fuller.artful at gmail.com>wrote:
>
>
> John,
>
>
>
> SQL Server doesn't ever physically update a row, but instead does an insert
>
> and a delete. This can be verified by creating a trigger For Update, inside
>
> which you'll find the tables #inserted and #deleted. In #inserted you'll
>
> find some of the original row's columns, with new values for the columns
>
> you changed. Then SQL deletes the row(s) in #deleted, and inserts the
>
> "replacement" rows in #inserted.
>
>
>
> HTH,
>
> Arthur
>
> --
>
> AccessD mailing list
>
> AccessD at databaseadvisors.com
>
> http://databaseadvisors.com/mailman/listinfo/accessd
>
> Website: http://www.databaseadvisors.com
>
>
>
-- 
>
AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>
Website: http://www.databaseadvisors.com
>