Prime Study Hub

OPTION (MERGE JOIN) in SQL Server The MERGE JOIN query hint is a best available join algorithm in SQL server. It is based on first sorting  both data sets according to the join conditions and then traversing through the sorted data sets and finding matches. The MERGE JOIN itself is very fast, but it can be an expensive choice if sort operations are required. This produces the best optimal execution plan. The syntax is, OPTION ( MERGE JOIN ); Now, Lets see how to use MERGE JOIN with SELECT statement.? select * from tbl_EmployeeDetails as e inner join tbl_BankDetails as b on b.EmpId=e.Id OPTION(MERGE JOIN); The statement returned optimal execution plan is, The MERGE JOIN operator gets a row from each input and compares them. In above statement, it merges all joins and first sorting data sets and then traversing through the sorted data sets and finding matches, if they are equal the rows are returned. If they are not equal, the lower-value row is removed

The OPTION ( LOOP JOIN ) would enforce LOOP JOIN across all joins in the query. Using the OPTION ( LOOP JOIN ) appears to allows the query optimizer to join the tables using the nested loops in which ever order SQL server decides it is optimal. The OPTION clause must be a last clause in a statement. The syntax is, OPTION ( LOOP JOIN ); Now, Lets see how to use LOOP JOIN with SELECT statement.? select *from tbl_EmployeeDetails as e inner join tbl_BankDetails as b on b.EmpID=e.Id inner join tbl_SalesDetails as s on s.BankId=b.Id OPTION(LOOP JOIN); The statement returned execution plan is, The above statement loops through all the joins that starts from sales details and goes by bank details to employee details in which the order that SQL server thinks it is optimal. This does not follow the order of joins that we specify. Suppose, if you want the SQL server to follow the order of joins that you specify you have to use FORCE ORDER in OPTION clause. Howe

The FORCE ORDER is a query hint it executes the order of the tables exactly specified in a statement. When we use this query hint in a statement it will tell SQL server not to change the order of the joins in the query. Basically, the SQL server rearrange your joins to be in the order that it thinks it will be optimal for your query to execute. Now, Lets see the execution plan without the FORCE ORDER: The above execution plan demonstrates the optimal order of the joins returned by the SQL server. As you can see the order starts from the sales details and goes by bank details to employee details. Suppose if you don't want the SQL server to change the order of the joins in a query you can use FORCE ORDER to stop the default ordering. The syntax for FORCE ORDER query hint is, OPTION ( FORCE ORDER ); Now, Lets see the execution plan with the FORCE ORDER: select * from tbl_EmployeeDetails as e inner join tbl_BankDetails as b on e.Id=b.EmpID inner join tbl_S

OPTION Clause in SQL Server The OPTION clause is used to query the table with different query hint. This clause can be specified in different statements like SELECT, UPDATE, MERGE and DELETE. Only one OPTION clause can be specified in a statement. The syntax for OPTION clause is, OPTION ( < query_hint > [ ,....n ] ) The different types of query_hint is as follows, FORCE ORDER LOOP JOIN MERGE JOIN HASH JOIN FORCE EXTERNALPUSHDOWN DISABLE EXTERNALPUSHDOWN The query_hint  specifies in which way the SQL server Database Engine processes the statement. Using an OPTION clause with SELECT, UPDATE, MERGE and DELETE statement  Lets see how to use OPTION clause in a SELECT statement.? declare @q int; set @q=1; select * from tbl_EmployeeDetails where Id>@q order by Id OPTION(OPTIMIZE FOR (@q UNKNOWN)); The OPTIMIZE FOR UNKNOWN is a optimizer hint. It is used when there is no parameter values have been passed to the query at all. Instructs query opt

LAG Function in SQL Server The LAG function is used to access previous row data along with current row data. This function was introduced in SQL Server 2012. Using this function is easy to compare values in the current row with values in the previous row. It is just the opposite of LEAD function. The syntax for LAG function is, LAG([scalar_expression], offset, default_value) OVER([partition_by_clause] [order_by_clause]) In LAG function partition_by_clause is optional, order_by_clause is required. You can specify any number of columns in the order_by_clause. The  offset  denotes the number of rows to lag. The  default_value  to return if the number of rows to lag goes beyond last row in a table or partition. If the  default_value  is not specified NULL is returned. In order to achieve this we are going to use "tbl_EmployeeDetails" table for our demo. CREATE TABLE [dbo].[tbl_EmployeeDetails]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [Employee] [varchar](

LEAD Function in SQL Server The LEAD function is used to access subsequent row data along with current row data. This function was introduced in SQL Server 2012. Using this function is easy to compare values in the current row with values in the subsequent row. The syntax for LEAD function is, LEAD([scalar_expression], offset, default_value) OVER([partition_by_clause] [order_by_clause]) In LEAD function partition_by_clause is optional, order_by_clause is required.You can specify any number of columns in the order_by_clause. The offset denotes the number of rows to lead. The default_value to return if the number of rows to lead goes beyond first row in a table or partition. If the default_value is not specified NULL is returned. In order to achieve this we are going to use "tbl_EmployeeDetails" table for our demo. CREATE TABLE [dbo].[tbl_EmployeeDetails]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [Employee] [varchar](450) NULL, [Salary] [decimal](18,