SQL Query Structure

DELETE DUPLICATE ROWS FROM A TABLE: At times you will face the issue of duplicate data in the table. As we have to create reports and get data in Relational form, we have to remove the duplicate records and make the records unique.  There are various ways to achieve this and I am listing few of them over here. You have Table Emp_Details having columns: Name varchar(20), Age varchar(20), Address varchar(20) I. Using Temporary Table: Select *  into #temp from Emp_Details group by Name, Age, Address having count(*) =1 Now, Truncate the original table Truncate table Emp_Details Again, inserting the records from the temporary table to Emp_Details insert Emp_Details (Name, Age, Address) Select Name, Age, Address from #temp This will insert only distinct records. ii. Using CTE: With cte as ( Select  Name, Age, Address, Row_Number() over (partition by name, age, address order by name) as Num  from Emp_Details ) Delete from cte wher...

Nested Loop Join There are basically three join mechanisms in SQL Server: i. Nested Loop Join ii. Hash Join iii. Merge Join The Nested Loop Join is chosen by the SQL Server when the tables being used are of the comparable sizes and is been the default join mechanism being used. It is non-blocking,  doesn't require equi-join, data to be sorted and doesn't use tempdb. It is mostly suited for inner joins between tables having relatively smaller inputs and having an index on the joining key. Select a.id, b.id from Table1 a inner Join Table2 b on a.id=b.id If we look at its execution plan, it will be using Nested Loop join. Also, In cases where we are having cross join between two tables mostly Nested Loop join will be used by the SQL Execution plan as it is a product join and produces N*M rows. In cases, if we add certain where clause to the Cross Join query to limit our results, Nested Loop Join might not be used. Basic Example: There is a table with fou...