In this tutorial, we’ll discuss SQL temp tables and views. These two can be handy if you want to pull a specific set of data from a physical table for testing and debugging. We’ll also talk about the 2 types of temporary tables and their differences.
With SQL temp tables and views, you can freely manipulate the set of data using multiple commands without querying it repeatedly since it’s already stored in a separate table.
Even though these two work similarly, you have to take note of their differences. That’ll help you decide the best method you can use in a specific situation.
Based on the name itself, temporary tables are used to store data temporarily in a session.
We can also do multiple operations on them just like on a physical table. Imagine that you have a SQL command that’s very long. You can simply put that in a temporary table and start debugging or troubleshooting the errors without affecting the original query. Moreover, temporary tables are powerful and often used in SQL.
Two Types Of Temporary Tables In SQL
There are 2 types of temporary tables in SQL. These are the local and global temporary tables.
Local temporary tables only exist in a session where it was created. We won’t be able to access this table in other sessions. Because of this, local temp tables will no longer exist once the session where it was created is closed.
In contrast, global temporary tables are accessible in all sessions when created. But once all sessions are closed, it will no longer exist and it won’t be accessible anymore.
Take note that you can simply identify what type of temporary table is being used in a query by checking the “#” symbol for local and “##” for the global temporary table.
Local temporary tables usually start with “#” in their table name.
On the other hand, global temporary tables start with “##” before the table name.
Additionally, if you want to quickly create any table, you can follow the command: SELECT * INTO #customers FROM dbo.courses as an example.
This command will create a new temporary table named #customers with all the data from dbo.courses table.
Views In Microsoft SQL Server Management Studio
Let’s now discuss SQL views with this sample command.
Let’s say for example we don’t want to repeatedly execute a command like this. You can simply put it in a view by following the command below.
Creating a view is similar to creating tables. Using this will allow us to execute the query that we entered by selecting the view name we’ve created. In this case, it’s CustomersbyYear_v.
It’s important to note that views are different from temporary tables. That’s because views are visible in your database just like a physical table unless you delete it. Furthermore, views don’t store any data or values.
Instead, it will just render the command that you’ve entered right after the CREATE VIEW command. You can also run any table operation in views.
Most importantly, we can easily identify views as they are usually represented by putting “v” at the beginning or end of the view name.
Creating Temporary Tables And Views In SQL
For this example, I have this query that will combine and show the records from Sales.SalesOrderHeader and Sales.Customer C after executing it.
Creating Local Temp Tables In SQL
Imagine that you’ve written a query that’s 200 lines long and you need to debug and perform certain operations on it. You can’t directly edit the query as it might cause more errors during the process.
In that case, using the previous example, we’re going to create a local temporary table by adding the command “INTO #test_local” before the FROM command.
Once we run this command, #test_local will now contain the combined records of Sales.SalesOrderHeader and Sales.Customer C. As a result, this is what you’ll see when you select the #test_local table.
After that, we can now perform any operations on the #test_local table. We’ll select all records from #test_local then we’ll change the order of records by CustomerID using the following command.
As a result, the records are now sorted by their CustomerID. But since we used a local temporary table, we can’t access or use #test_local table in a different session. The next example shows what’s going to happen if we try to access #test_local in SQLQuery2.sql.
Based on the previous example, upon selecting the #test_local, it just displayed an error message. This is because the #test_local table only exists in the session where it was created, which is SQLQuery1.sql.
Creating Global Temp Tables In SQL
Next, we’ll use the same query that we used in #test_local. But this time, we’ll use a global temporary table.
Now if we select ##test_global and order the records by CustomerID, it will show the same output as what we had in #test_local since we used the same query.
After that, we’ll try to access the ##test_global table in a different session by selecting it. This works seamlessly since we are using a global temporary table.
At this point, we are highlighting the difference between local and global temp tables.
Remember that local temp tables are only accessible in a session where it was created. Once you close that session, it’ll not exist anymore.
On the other hand, global temp tables are visible to all sessions, unless you close all active sessions or the application itself.
Creating Views in SQL
We’re now going to create a view. In this example, we have an existing query like this.
We can start creating a view by adding the CREATE VIEW command to that query.
Upon running the command, a view will be created in the database that we’re working on. In this case, it’s AdventureWorks2012.
We should also see the dbo.CustomerView_v in the Views folder after refreshing it. To refresh the Views folder, right-click on it then select Refresh.
Click the + icon on the right of the Views folder and the dbo.CustomerView_v will be visible.
You can open this view by right-clicking on dbo.CustomerView_v then choose Select Top 1000 Rows from the options. This will display 1000 records in that view.
After this, we can now use dbo.CustomerView_v and perform any operation on it just like a table. For example, we want to select the records with SalesOrderID greater than 50000 in dbo.CustomerView_v. In this case, we’ll use the following command.
Now we’re able to run an operation on the view that we’ve created.
***** Related Links *****
SQL Data Extraction Using OFFSET And FETCH
Filter In SQL Using IN, NOT IN, LIKE, And NOT LIKE
Basic SQL Table Operations
Conclusion
To sum up, we’ve learned the 2 types of temporary tables along with their differences and why we need to use them. Additionally, we’ve discussed that temporary tables serve as a way to fix errors in your queries without damaging the actual query.
Keep in mind that temporary tables are only visible to sessions where it was created. Hence, it’ll no longer exist once we’ve closed the active sessions or the application itself.
We’ve also learned that views don’t store data or records. It only shows results based on the query that you want to be rendered. These views are stored in our database once we create them.
By learning these two, you now have the ability to quickly execute long commands and modify big chunks of dataset with ease.
All the best,
Hafiz
