Or are there odd edge cases To be clear: I'm not asking if 3 is the right answer. I'm asking if it's a reliable answer, or are there edge cases where SQL Server will leave out rows that matched but didn't require a change. Update : A couple of people have asked or hinted at what kind of "reliability" issues I'm worried about. The fact is they're quite nebulous, but, I don't know, replication? Indexes it could use to avoid seeking to rows because it knows that b is already 1 , and so it skips those?
Update : I was hoping for someone with a more "insider" view of how SQL Server works to answer this question, but it looks like the triggers example and others I've played with by xacinay is as close as we're going to get. And it seems pretty darned solid; if it behaves that way in the normal case and it didn't behave that way despite partitioning or whatsit, as someone said, surely that would qualify as a bug.
It's just empirical rather than academic. It simply does what its told to: finds data source, filters rows according to provided condition, and applies 'set' changes to filtered rows. That's the way SQL Server works without any reservations. MySQL may work different. A row counting procedure is not a part of the SQL standard. So, you have to look before you leap for those kinds of artefacts every time you switch from one RDBMS to another.
The SQL Server changes all rows, it does not verify that a value is in fact different before changing it, as that would require a lot of overhead on update commands. I think the documentation is correct because no matter that one row in your example already had 1 as the value in [b] , that row still satisfied the criteria in the WHERE clause so the value was 'updated'.
In summary you are asking if rowcount is deterministic. Really if it was non-deterministic you don't think you would see that in the documentation? It is reasonable to assume deterministic. I seriously doubt there is an edge case that it fails but it it does then Microsoft will accept it as a bug.
They are not going to come back with rowcount is nondeterministic - the documentation did not explicitly state deterministic. Numerous examples that "affected" in MMSQL TSQL counts a row set to the same value it only cares about the where And you have examples that MSSQL does in fact assign the same value timestamp You assert but SQL could care so how do I do I know there is not an edge case that is does Because that is not reasonable behavior Any program should produce consistent output The order without sort is not guaranteed but the rows are the same - that is specifically documented by Microsoft and SQL as nondeterministic If rowcount was nondeterministic then I trust Microsoft would document that It is reasonable to assume rowcount is deterministic C and Java do not always have the same behavior That is not a reason for me to suspect C and Java are not reliable.
The query optimizer will decide how most efficiently process the query it is not going to change the query. Introducing a predicate on [b] in the first query is changing the query. A proper database just does not do that. I highly suspect that in MySQL if you look at the query plan there will be a predicate on [a] in the first query. Rather than require proof that is reliable create an edge case that proves it is not reliable.
If you can create a situation it is not reliable I submit Microsoft would accept it as a bug. What is the business case? If you have business case that a value must be updated with same value then by definition you have something to test.
The only examples I can thing of are timestamp or trigger. If you have true need to for an update to the same to take place then you have something to measure. Do you have any evidence that a value is not updated to the same value? If you still don't trust it then wrap it in a transaction. August 5, at pm Hi, I have about 5 inserts and 4 updates in a stored procedure.
The way I am thinking of doing is below: Update 1 Statement SPCount 1, 'Update1', value --Above update, first parameter 1 is the application id It's irrelevant so you can ignore that, second parameter 'Update1' is the description and the third parameter is the rowcount value stored for the updated items. Update 2 Statement As a seperate question Thank you.
S -- :hehe:. You can use Rowcount and your value variable that way. August 6, at am The script would then look like this:. The MERGE statement is a bit special, as it can perform insert, updates and deletes at the same time. ROWCOUNT returns the total number rows affected by the MERGE statement which is correct, but this might make debugging or troubleshooting a bit more difficult, since you do not know how many rows were deleted, updated or inserted. We need to reset the data to rerun the example, so run the script above that creates the temporary tables and inserts the sample data.
A downside of this approach is the data duplication into the logging table, which can be troublesome when the MERGE statement has to handle large amounts of data. Related Articles. Delete duplicate rows with no primary key on a SQL Server table. Rolling up multiple rows into a single row and column for SQL Server data.
Popular Articles. How to tell what SQL Server versions you are running. Resolving could not open a connection to SQL Server errors. Ways to compare and find differences for SQL Server tables and data. Searching and finding a string value in all columns in a SQL Server table.
0コメント