Temporary Table Constructs

Video Transcription

Welcome back everyone. In this video, we’re going to continue our discussion on programmable objects. But in turn, we’re going to revisit some things that we’ve already covered around temporary tables. So, temporary table contracts are an important idea because as we get into programmable objects, like stored procedures, we need to understand that not everything that we do with SQL is physical storage, right? A lot of the times, this is logical representation. And in some cases, these are only logical to within within a certain session. So a single connection to the database, or global, which is available to everyone that is connected to the database added at any given time. So different things can be utilized in this sense. So things like things that we haven’t covered yet, our table types, so we can actually define our own custom types within SQL. And those can be made available. We’ve also we’ve done things like use CTE keys, and user defined functions as well, which we’ll also revisit here in just a few. But first off, we’re just going to cover mostly just variables and custom types. But let’s take a look at some examples of these variables that we’ve seen. So far. Most of the time that we’ve used them are scalar type data variables, right, we declare a variable, assign it a value, and then we utilize that as part of our SQL queries. But we can also create tables, and store those tables as a variable as well.

So here is our person table that we’ve typically used in the past. And so we can actually run this and store a full table as part of a variable, right. So this table itself does not exist physically in the database. It’s not part of our schema, but it is local to our current execution right within our batch. So we have this person variable available to us, that has all of these columns, and it will behave and act and just like a table normally would. But it is stored inside of a variable instead of a physical table in our database. The first line here is really the only difference between our our normal variable declaration versus the table variable, right, and we just have table here, instead of like in var car or anything like that. Alright, so now let’s go ahead and let’s go ahead and keep on finishing out creating our demo database that we’ve seen so far. Um, so here we have the address type table. Again, very, very similar, very, very small table. But I will also include the person address table, which has a little bit more complicated, and it starts to show a little bit of the limitations here with variables. So most table constraints are fully supported on table variables. But we are not allowed to actually name them.

So I’m gonna go and run this real quick. So that runs perfectly fine. But if I uncomment, this line, you can see that actually come up with an error. So I can’t actually physically name my constraints. Although I can add, I can have a check constraint, I just can’t name that check constraint. Because again, those these constraints are not physically stored in your database, it is stored as part of the variable, it’s stored as part of the variable. Similar issue goes along with foreign keys. So foreign keys are not allowed. So you’d get an error if you uncomment. This. So just kind of be aware of some of those limitations when you’re working with table variable names. But we can run queries on on these, so we can insert data into our variables. That works just fine. If I did a select, select star, whoops, wrong key, select star from and then let’s go ahead and just pull out the people from the people variable. And you can see that we get all of our data out and it shows up just like a normal SELECT query would on a regular table. So this is pretty useful if we need a quick temporary table to execute some queries on So there, there are some beneficial situations where we can get that from, we can also run some more complicated queries on this. So we have an insert select from. And so we can run a more complicated insert with a join. And then we can also do joins on the temporary tables, through the, through the different variables.

So here I’m joining the person address temp table with person and address type. And so if we execute that, now we can get all of the people and all the all the information for each of the people out along with all their addresses. From from our temporary tables, so getting there are not super common use cases for for a variable, a table variable. But they do provide some extra flexibility when we’re working with that. So this is temporary variables, right temporary, temporary, temporary table variables. But we can also so that’s here. Sorry about that. Brock, if you would mind cutting that last touch out. So those are all of our table variables, these are temporary constructs that do not physically exist in your database. But temp tables are not are not stored in a variable and are stored in your database. So these are physically present. But they do have a specific scope. And some certain scenarios, they’re only visible to a single database connection or a single session. Other temporary tables are global, and so they’re visible to all people who are connected to your database. So let’s take a look at a couple examples of those. So here is an example of a temporary table. Tables that have a pound sign in front of them are going to be are going to be stored in Microsoft SQL servers, temporary database structures. So if we execute this, sorry, let me unhighlight that, execute this, get all of our information back out as normal.

But notice that everywhere I actually use a table name, I’m using a pound sign instead in front. Now important part here for this is this here. So if I highlight this and run, here we go. And notice this is this is available to my current session. So that this, I executed all of this first, right created the temporary table construct. So this physically exists in my database now. Go to databases, right? You won’t actually see this here and my schema here. But if you are not, Yep, here you go, here is my Temp DB and table roles. It’s not going to let me expand it here in the file explorer. But this Temp DB right here, that is where this table actually exists. So you can kind of dive dive down into that if you’d like to in your own time. But you can see the results of that right here. So here is and by the way, this SP help. This goes for any table in the in your SQL Server. Okay. So this is a, this is the name of the table. So hashtag person, the owner is DBO so this is owned by the database system. This is a user created table. And this is when that table was created. So this is all in one session. This is all on one session. And so if I highlight this and open up a new new tab and run this, ah, no object at person or pound person does not exist in database Temp DB.

If I go back over here and run this again, on this side, it still works right it still works. So this One database connection. So one session, this tab over here is my second session. So it’s a new connection being made to my database. And so therefore, my temp table only exists within the session that it was created. It is not available to other sessions. Let’s do, let me do some cleanup here. Let me go ahead and drop the temporary table real quick. And then I’m going to create a new one. Now, okay, so here, I’m going to create a another temporary table. So select insert into so doing a SELECT INTO. So I’m pulling out all the customers from the wide world importers database Customers table and inserting it into a and inserting it into a temporary table. So if I give this a run, see everything works. But let’s try this from my other session. Again, still only local to my individual session, not available to all sessions yet. So we’ll get to global ones here in just a minute. But this is probably the most common use case for temporary tables, where you want to make a quick copy of a table. And then you could actually execute example, query queries on that temp table instead of the real one. And so you can try things out to see what happens make sure everything is running properly. But again, it’s is typically a general edge case here, you don’t have constraints either on this temporary table as it is right now. So you would have to add constraints to this, if you want the full copy of your original table does not duplicate the constraints from the table he pull from. So you have to add those manually, you have to add those manually. But nonetheless, this is a handy way to create duplicate or duplicate data from an existing table.

So what if we go ahead and drop that table real quick? What if we wanted the our temporary tables to be available to everyone, right, everyone. So a single pound sign denotes a temporary table that is available to the current session. A table that is created with a double pound sign in front of its name denotes a global, temporary, very global temporary table. So this table works, this is just going to work exactly like my previous example. Everything works fine in this session. But now, if I try to run this query over here in my second session, ah, it works. This is just a quick and easy way to create global temporary tables. Very useful for again, like I said, trying out queries on data that can be destroyed if you need to. So and this way, you can create all of your normal constraints as part of this. So default constraints, check constraints, all those sorts of things can be added here. And so very easy way to try out very quick and easy, harmless way to try out queries, if you don’t have a test database ready to go.

In addition to temporary variables, we can also create our own custom table types. So let’s take a look at this example here. So here we have this type. We haven’t actually seen this syntax before create type. But I can create my own custom types inside of SQL Server. So create type, and I’m calling this a demo dictionary, and then as table. So this type is going to be a table. And within this table I have two columns key and value. Just like what a typical dictionary would have right a dictionary is a key value store. And so we can have a column that represents the key being the primary key make forcing the key to be unique. And then we have a value associated with that. And then we can declare variables of that type. So declare states normal variable declaration here, and then here’s my type. And then, since that is a table type, I can run queries against it, just like my, my table variables that I showcased earlier. So I can run an insert. And then I can also run select on that query as well. Important thing to note here is the different batches. So I if, if I have my old one, I drop it first, before I create it.id, that is in its own batch. And then the Create type, since it’s creating a database object, that also has to be in its own batch. But that is still visible down here, when I create my variable that’s still visible down here. So I can run my insert on my table variable just like I was showcasing earlier. And I can run a SELECT clause on my newly created variable, table variable as well.

So these sorts of things are very useful for as, as I showcased here, creating ad hoc types in our SQL queries. So things like a dictionary is very useful. Things that it may not necessarily store physically, but useful for when executing large and complicated seek sequences of queries as a result, but that is going to conclude all of our examples on temporary table constructs.