# Tag : set based approach

## Unfolding | Set Based Approach | Get Continuous Start Date of the Current Project – Episode 83

In this video, we are going to learn;

1. How and when to use Apply operator in SQL Server, and
2. How to compare rows on a given logic

You can download the script used in this example from below the video.

More

## Unfolding | Set Based Approach | Create Attendance Report – Episode 72

In this video, we are going to learn;

1. Generate date list from the given date range dynamically
2. Repeat the date list for each employee without using a loop or recursive CTE

You can download the script used in this example from below the video.

More

## Unfolding | Set Based Approach | Generate Rows Dynamically – Episode 6

In this video, we are going to learn;

1. How we can Generate rows dynamically for each record of a table in set based approach, and
2. How to do calculations on each row of a table generated dynamically

You can download the script used in this example from below the video.

More

## Unfolding | Set Based Approach | Tally Table – Episode 5

In this video, we are going to understand Tally table which is very useful in writing set based SQL queries. You can download the script which can be used to create the Tally table from below the video.

More

## Unfolding | Set Based Approach | How join works internally – Episode 4

In this video, we are going to understand how join works internally. If you have a good understanding of joins and how it works, you can skip this video and move to the next video in this series. If not, lets start understanding the joins. You can download the script used in this example from below the video.

Here is the example script used in this video.

More

## Unfolding | Set Based Approach | SQL Server Query execution architecture – Episode 3

In this video, we are going to understand the query execution architecture which helps us to understand the importance of set based approach. When a T-SQL statement gets submitted to SQL server, it goes through multiple steps before we get the final output. Lets have a look on SQL Server query execution architecture.

More

## Unfolding | Set Based Approach | Introduction to the course – Episode 2

This video will give you insight of the topic that we are going to cover in this series. We know that the thinking in terms of sets is creative and innovative and once you start understanding the concepts, believe me, you will find it is simple too. Writing set based queries needs a good understanding of SQL joins and other set based operators with some imagination ability, which we are going to learn in this course.

More

## Unfolding | Set Based Approach | Why set based approach – Episode 1

Welcome to my course “Unfolding | Set Based Approach”. This is my first video tutorial series. In this series, I am going to share the concepts and techniques being used to create set based SQL queries.

This course is not based on how to write queries (this you can find at many places), rather it is based on how to think in set based manner. This course contains real life examples with deep insights to explore set based concepts, so that you can start thinking in terms of sets. Lets watch this video which explains why we should use set based approach instead of cursors or loops.

Please do share and comment if you like this. Also, please subscribe to our newsletter to be updated.

More

## Order max and min value rows alternatively in SQL Server

In this blog post “Order max and min value rows alternatively in SQL Server”, we are going to learn how we can order the rows of a table by combining max and min value rows alternatively. For example, from an employee table based on the salary column, we need to extract the records ordered as;

First row: First highest salary

Second row: First lowest salary

Third row: Second highest salary

Fourth row: Second lowest salary

Fifth row: Third highest salary

Sixth row: Third lowest salary

and so on…….

We know that the SQL Server database engine is optimized to operate in a set oriented way instead of RBAR (Row By Agonizing Row, processing row by row in a loop). We are going to achieve the desired output in a set based approach, unlike loops or recursive CTEs. Let’s have this tutorial in action now.

Create the dummy employee table and … More

## Get hierarchical data in order2

In this post, we are going to learn how we can get hierarchical data in order. We are dealing with a user defined hierarchical data which can have “n” number of siblings and their siblings can also have “n” number of siblings in turn.

For demo purpose, we have a Product_Master table, in which a product can be marked as a parent of another product, and that child product can also be marked as a parent of another product, such kind of hierarchy is known as user defined hierarchy. As any product can be marked as another product’s parent, we can have any number of nested products in the hierarchy. We have to retrieve all these products with their associated parent in ordered way.

Ordered hierarchical data from above user defined hierarchy, can be achieved with a recursive common table expression (CTE) or using a loop. We … More