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...

There are two types of Execution plan in SQL Server i.e. Estimated execution plan Actual execution plan Estimated execution plan is generated based on the statistics available for the SQL table columns and indexes. The estimated execution plan may differ from the actual execution plan as the latter is generated when the query is executed and depicts the execution mechanism followed by the server in order to generate the query in the best possible way (least time). Execution plan is the steps taken by the SQL server optimiser to reach the SQL query results in best possible way. We always have questions such as why our query is running slow, how we can reduce the time to execute the query. The query which is to be executed is submitted to the SQL Server, the query optimiser is responsible for parsing the query and generating the execution plan. When the query is submitted to the system, there are many underlying operation that are happening in the backend before the result is...

The following steps show the logical processing order, or binding order, for a SELECT statement. This order determines when the objects defined in one step are made available to the clauses in subsequent steps. For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. However, they can be referenced by subsequent clauses such as the ORDER BY clause.  Note that the actual physical execution of the statement is determined by the query processor and the order may vary from this list. which gives the following order: FROM ON JOIN WHERE GROUP BY WITH CUBE or WITH ROLLUP HAVING SELECT DISTINCT ORDER BY TOP Complete SELECT query SELECT DISTINCT column, AGG_FUNC( column_or_expression ), … F...