Those of you who survived the subquery lesson are probably asking, “Isn’t there a simpler way to do all that?” The answer is, yes, there are simpler ways. We looked at the complex way first to help you understand how these things work. There are lots of alternatives to subqueries. We’ll take a look a brief look at each option for organizing or presenting a subset of data in today’s lesson and then discuss them in detail in the following lessons.

Which One Do I Need?

Before starting, it is important to ask yourself the following questions when implementing a subquery, view, temporary table or a memory table.

How long does this set of data need to exist?

How many processes need to access this set of data?

Is speed an important factor?

Will other programmers be reading and accessing this code?

Once you know you the answer to these questions, you can decide which of the following solutions works best.

The Sub Query

We’ve already learned about the subquery, so we won’t talk about it in too much detail here. However, we will discuss when its best to use one. Use a subquery in the following situation:

Speed and memory is more important than readability.

This data is only used in one place.

Subqueries are harder to read than the other options we’ll discuss. They do take up less resources than temporary tables and other options. However, as you saw in the subquery lesson, they aren’t very easy to read. The SQL becomes very convoluted and complex.

The Temporary Table

A temporary table in SQL exists for a limited period of time. They even get stored in their own private system database. A temporary table is best for the following scenarios:

Data needs to be updated in multiple steps.

Data needs to exist for more than a single query.

One or more processes might need to access the data.

Data doesn’t need to exist forever.

Readability is more important than speed.

Temporary tables are complex objects. You can use them just like a real table. That means you can insert, update, delete and modify them however you want. There are even ways for outside processes to access data that has been rearranged into a temporary table. Since they exist in their own database, however, there is additional overhead associated with using them.

Memory Table

We won’t talk about the memory table until a little bit later on. It is actually what SQL Server calls a variable. It’s best to use a memory table in the following circumstances:

Speed and memory usage is important.

No other processes need to use this data.

The data is only needed for the current process.

Dataset might need to be passed to another process.

A memory table exists inside the current process that we’re working on. We create and destroy it when our process starts and ends. This means it’s faster than a temporary table but still does all the same things. The catch is that no other processes can see this object under normal circumstances. Since it is a variable, however, there are ways to pass or accept a memory table from another process. We’ll talk about this later on.

The View

The view is the most powerful and most common way of creating a subset of data. Use a view for the following:

Data will be used regularly by many processes and users.

You wish to hide certain columns from any results for security reasons.

You want to be able to update actual tables by updating the subset of data.

A view actually links back to the live database table. That means whenever you run a query on a view, you’ll see up-to-date results. You can even run UPDATE, INSERT and DELETE statements on most views to change the tables behind them. A view is a somewhat permanent object in the database. Like a normal table, they are made to be kept for some time.

Why So Many Choices?

There is no “best option” for creating subsets of data. As you can see, each one has advantages and disadvantages. We’ll spend time getting familiar with everyone of these in the following lessons. Afterward, you’ll have the experience you need to decide which one is right for each situation.