557 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Temporary Tables in SQL - a Training Blog Part four of a four-part series of blogs |
---|
If you want training in how to use temporary tables in SQL, read on! This blog shows how to create temporary tables and how to drop them safely whether or not they exist. Finally, the blog lists the pros and cons of using temporary tables in SQL.
This article is part of a complete SQL tutorial. Another way to learn SQL is to attend a Wise Owl SQL training course. |
As our final part of this blog covering training on temporary tables, what are the advantages and disadvantages of using temporary tables?
If you've found this blog useful, consider booking a place on our advanced SQL course (online or classroom, depending on whether you're in the UK or not).
There are 3 main advantages of using temporary tables in SQL:
Advantage | Notes |
---|---|
Simplicity of coding | Temporary tables behave just like normal ones; you can sort, filter and join them as if they were permanent tables. |
Speed | Because SQL Server has less logging and locking overheads for temporary tables (after all, you're the only person who can see or use the temporary table you've created), they execute more quickly. |
Access rights/security | You can create a temporary table and insert, delete and update its records without worrying about whether you have sufficient rights to change data in permanent tables, or whether you might be accidentally doing so. |
The disadvantages of using temporary tables include:
Advantage | Notes |
---|---|
Not as fast as table variables | Although using temporary tables is quicker than using permanent ones, there is still an overhead involved compared to using table variables (an alternative technique, which I might blog about some day!). |
Can not update in functions | You can not use INSERT, UPDATE or DELETE statements against temporary tables in user-defined functions (you can with table variables). This is NOT a big disadvantage, however! |
Parts of this blog |
---|
|
Some other pages relevant to the above blogs include:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2023. All Rights Reserved.