Start Ms sql updating multiple rows

Ms sql updating multiple rows

Because the MERGE statement performs a full table scan of both the source and target tables, I/O performance can be affected when using the TOP clause to modify a large table by creating multiple batches.

column a poor candidate for keys, especially primary keys.

Any update made to the row changes the rowversion value and, therefore, changes the key value.

You mentioned that in one of your old post as well.

Performs insert, update, or delete operations on a target table based on the results of a join with a source table.

For example, inserting a row if it does not exist, or updating the row if it does match.

When simply updating one table based on the rows of another table, improved performance and scalability can be achieved with basic INSERT, UPDATE, and DELETE statements.

Do not attempt to improve query performance by filtering out rows in the target table in the ON clause, such as by specifying clause.

For any given row, the second WHEN MATCHED clause is only applied if the first is not.

For example, you can synchronize two tables by inserting, updating, or deleting rows in one table based on differences found in the other table.

Performance Tip: The conditional behavior described for the MERGE statement works best when the two tables have a complex mixture of matching characteristics.

WITH s AS (SELECT My Group, Count(My Group) AS [Count], MAX(Count(My Group)) OVER () AS [Max My Group], MIN(Count(My Group)) OVER () AS [Min My Group] FROM My Table GROUP BY My Group) SELECT Agg, My Group, V.[Count] FROM s CROSS APPLY (VALUES ( 'Max', CASE WHEN [Count] = [Max My Group] THEN [Count] END), ('Min', CASE WHEN [Count] = [Min My Group] THEN [Count] END)) V(Agg, [Count]) WHERE V.[Count] IS NOT NULL WITH s AS ( SELECT My Group, Count(My Group) AS [Count], RANK() OVER (ORDER BY Count(My Group)) AS [rasc], RANK() OVER (ORDER BY Count(My Group) DESC) AS [rdesc] FROM My Table GROUP BY (My Group) ) SELECT CASE WHEN [rasc] = 1 THEN 'Min' ELSE 'Max' END AS 'Agg', [My Group], [Count] FROM s WHERE [rasc] = 1 OR [rdesc] = 1 WITH s AS ( SELECT My Group, Count(My Group) AS [Count], MAX(Count(My Group)) OVER () AS [Max], MIN(Count(My Group)) OVER () AS [Min] FROM My Table GROUP BY My Group ) SELECT CASE WHEN [Count] = [Max] THEN 'Max' ELSE 'Min' END AS [Agg], [My Group], [Count] FROM s WHERE [Count] IN ([Max], [Min]); Thanks @Martin Smith for pointing that out.