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 |
|
friend
Starting Member
6 Posts |
 Posted - 2011-08-07 : 03:53:10
|
| Hi all,Good Afternoon.i have one query ,i am using sqlserver2005, when i am updating a value in the table , how the request is sending and wht is the interprocess is going on .in one interview somebody asked this question, can any body knows please let me know.ThanksFriend |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-07 : 06:12:49
|
| http://sqlinthewild.co.za/index.php/2011/06/21/are-all-updates-split-into-delete-insert/--Gail ShawSQL Server MVP |
 |
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-08-07 : 11:24:53
|
| The ANSI model of an UPDATE is that it acts as if 1) You go to the base table. It cannot have an alias because an alias would create a working table that would be updated and then disappear after the statement is finished, thus doing nothing. 2) You go to the WHERE clause. All rows (if any!) that test TRUE are marked as a subset. If there is no WHERE clause, then the entire table is marked. The name of this set/pseudo-table is OLD in Standard SQL. 3) You go to the SET clause and construct a set/pseudo-table called NEW. The rows in this table are build by copying values from the columns are not mentioned from the original row to the NEW row. The columns are assigned all at once. That is, the unit of work is a row, not one column at a time. 4) The OLD subset is deleted and the NEW set is inserted. Those are the proprietary terms used in SQL Server, too. This is why UPDATE Foobar SET a = b, b = a;Swaps the values in the columns a and b. The engine checks constraints and does a ROLLBACK if there are violations. In full ANSI/ISO Standard SQL, you can use row constructors to say things like: UPDATE Foobar SET (a, b) = (SELECT x, y FROM Floob AS F1 WHERE F1.keycol= Foobar.keycol);The proprietary, non-standard UPDATE.. FROM.. syntax is a total disaster in the ANSI model and in implementation, but that is another rant. Trying to UPDATE the temporary result of a JOIN syntax would be useless that temporary result disappears at the end of the statement and never touches the base tables. --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-07 : 12:14:39
|
| However that's purely a theoretical 'standards' model and SQL does many of the points differently.It does not, in many cases, delete the old and insert the new (see my blog post for proof). The results are the same, the internal methods are different. SQL does allow the updated table to have an alias, it does allow update with a join (though that can be messy and risky if you don't know what you're doing) via the UPDATE ... FROM syntax.--Gail ShawSQL Server MVP |
|
|
|
jcelko
Esteemed SQL Purist
547 Posts |
Posted - 2011-08-07 : 13:30:20
|
| >> It does not, in many cases, delete the old and insert the new (see my blog post for proof). The results are the same, the internal methods are different. <<The point that that the results have to be the same. The Standards are loaded with the term "effectively", so you always have a base line to test any optimization against. Teach the noobs the basics first, then get fancy. >> SQL [T-SQL dialect] does allow the updated table to have an alias, it does allow update with a join (though that can be messy and risky if you don't know what you're doing) via the UPDATE ... FROM syntax. <<Half right; SQL:2003 added the UPDATE clause alias, which I wanted to get removed, but I am re-thinking that position. But the UPDATE.. FROM is still dialect, dangerous and laced with cardinality problems. In fact, there is talk of deprecating it now that T-SQl has the MERGE statement and it is not needed. "Hey.Ma, we done got brakes on the pick-em up truck! yall kin quit driving inta trees to stop!" --CELKO--Books in Celko Series for Morgan-Kaufmann PublishingAnalytics and OLAP in SQLData and Databases: Concepts in Practice Data, Measurements and Standards in SQLSQL for SmartiesSQL Programming Style SQL Puzzles and Answers Thinking in SetsTrees and Hierarchies in SQL |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-07 : 15:13:25
|
| Yes, the results are the same, but if someone's asking about how things actually work internally then telling him how the standard theoretically says things should work is about as useful as if someone asks how a TV works internally and you reply 'magic'It's a stupid interview question (unless the person is being interviewed for a position on the database engine development team), but that's no reason to answer a question incorrectly.And no, I wasn't half-right. T-SQL supports the aliasing of the updated table and T-SQL supports the Update From. Whether it's 'standard' or not I frankly don't care. When I work with SQL Server I use T-SQL. When I work with Oracle I use PL-SQL. When I work with MySQL I use whatever they refer to their dialect as.--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
internal process of update statement in sqlserver?