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.
Before starting, it is important to ask yourself the following questions when implementing a subquery, view, temporary table or a memory table.
Once you know you the answer to these questions, you can decide which of the following solutions works best.
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:
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.
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:
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.
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:
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 is the most powerful and most common way of creating a subset of data. Use a view for the following:
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.
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.
Tainyan staff who teach SQL.