last operation

1. what is hint

Hints are ways of the user “hinting” to the SQL Server Engine what method of execution it should take
Hints override the normal execution plan created by SQL Server when it performs a query or syntax

2. type of hints

2.1 table hints

• Used to control the locks used on a table and even what indexes to use in queries
• 在dirty data,because isolation level, you cannot read, but select with(nolock), you can read

2.1.1 note

2.2 join hints

2.2.1 merge join

• when two table have same size, and both of them are index, it goes for merge join
• Used for tables that are similar in size, it compares values that are matching across from each other. If duplicates, then Cartesian Product

2.2.2 hash join

• one of two table doesn't index
• sql servel build buld phase on one table,then probe phase
• Creates two hash tables in memory, fills them with data based on keys, and compares smaller table to large one

2.2.2.1 Remote Hash Join

– Performs the same hash join, but the build and probe phases are done on the linked server instead if it’s smaller

2.2.3 loop join

• when a huge table join a small table ,it goes to loop join
• Creates a nested loop in which each value is compared from the smaller table to the larger row by row

2.3query hints

Applies specified hint to entire query as it’s executed
Applies to Select, Insert, Update, and Delete

2.3.1 type:

• maxdop: maximum degree of parallelism for the query
• maxrecursion hint can use