SQL Joins Archives - Page 10 of 16 - SQL Authority with Pinal Dave

SQL SERVER – Effect of Order of Join In Query

August 2, 2008

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Index, SQL Joins, SQL Scripts, SQL Server

Let us try to understand this subject with example. We will use Adventurworks database for this purpose. Table which we will be using are HumanResources.Employee (290 rows), HumanResources.EmployeeDepartmentHistory (296 rows) and HumanResources.Department (16 rows). We will be running following two queries and observe the output. In the resultset the order…

SQL SERVER – SQL SERVER – Simple Example of Recursive CTE – Part 2 – MAXRECURSION – Prevent CTE Infinite Loop

July 29, 2008

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

CTE, SQL Joins, SQL Scripts

Yesterday I wrote about SQL SERVER – SQL SERVER – Simple Example of Recursive CTE. I right away received email from regular reader John Mildred that if I can prevent infinite recursion of CTE. Sure! recursion can be limited. Use the option of MAXRECURSION. USE AdventureWorks GO WITH Emp_CTE AS…

SQL SERVER – Simple Example of Recursive CTE

July 28, 2008

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

CTE, SQL Joins, SQL Scripts

Recursive is the process in which the query executes itself. It is used to get results based on the output of base query. We can use CTE as Recursive CTE (Common Table Expression). You can read my previous articles about CTE by searching at http://search.SQLAuthority.com . Here, the result of…

SQL SERVER – PIVOT and UNPIVOT Table Examples

June 7, 2008

Pinal Dave

SQL, SQL Server, SQL Tips and Tricks

PIVOT, SQL Joins, SQL Scripts, UNPIVOT

I previously wrote two articles about PIVOT and UNPIVOT tables. I really enjoyed writing about them as it was interesting concept. One of the Jr. DBA at my organization asked me following question. “If we PIVOT any table and UNPIVOT that table do we get our original table?” I really…

SQL SERVER – Order Of Column In Index

June 2, 2008

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Index, SQL Joins, SQL Scripts

I just found one of my Jr. DBA to create many indexes with lots of column in it. After talking with him I found out that he really does not understand how really Index works. He was under impression that if he has more columns in one index, that index…

SQL SERVER – 2000 – SQL SERVER – Delete Duplicate Records – Rows – Readers Contribution

May 4, 2008

Pinal Dave

SQL, SQL Performance, SQL Server, SQL Tips and Tricks

SQL Joins, SQL Scripts, SQL Server

I am proud on readers of this blog. One of the reader asked asked question on article SQL SERVER – Delete Duplicate Records – Rows and another reader followed up with nice quick answer. Let us read them both together.

SQL SERVER – Converting Subqueries to Joins

April 23, 2008

Pinal Dave

SQL Performance

SQL Joins, SQL Performance, SQL Scripts, SQL Server

There is always more than one way to do one thing in any programming languages. In SQL Server there is always more than one way to achieve same result set. It is quite often I see that developers write subqueries in place of joins or joins in place subqueries.