Archive

Posts Tagged ‘Delete’

What are SQL Query Hints?

July 5, 2014 Leave a comment

SQL server usually selects the best query plan required to execute a T-SQL query. However, there might be cases where the plans selected are not the best. In such situations, you can use the query hints to suggest the best possible plan/method of executing a query. The Query hints can be applied on all operators in a query. These hints apply to SELECT, DELETE, INSERT, UPDATE and MERGE. You can use the Query hints in the OPTION clause after the query.

Few commonly used Query hints with examples are as below.

Read more…

 

Understanding MERGE Transact SQL Statement

July 5, 2011 1 comment

By using “Merge”, you can perform insert, delete and update on a table in a single SQL statement. This helps in synchronizing the tables as well.

However while using merge there are several points that you need to keep in mind. Few

  • In a “Merge” statement, a “When Matched” clause with a search condition cannot appear after a ‘When Matched’ clause with no search condition.
  • A “Merge” statement must be terminated by a semi-colon (;).
  • An action of type ‘DELETE’ is not allowed in the ‘WHEN NOT MATCHED’ clause of a MERGE statement.
  • At least one of the three MATCHED clauses must be specified, but they can be specified in any order. A variable cannot be updated more than once in the same MATCHED clause.
  • Any insert, update, or delete actions specified on the target table by the MERGE statement are limited by any constraints defined on it, including any cascading referential integrity constraints.

Few examples of merge are as given below.

Read more…