From the course: Developing Microsoft SQL Server 2016 Databases

Create temporary tables - SQL Server Tutorial

From the course: Developing Microsoft SQL Server 2016 Databases

Start my 1-month free trial Buy for my team

Create temporary tables

“

- (Speaker) Occasionally, you'll need to store tabular data temporarily in the database and won't need it to persist or get saved across login sessions. Sequel Server supports two different ways to create a temporary storage location for your data. The first one that I want to take a look at is called a temporary table. Let's start this example by creating a new database called temporary tables, and then we'll switch into it to make it the active database. Then, we'll turn our attention to this next statement to create a table. To create a temporary table, we'll modify a standard create table command in one very small but very significant way: we'll add a single hash sign or a pound symbol right before the table's name, and that's it, that's all that's required to make this table a temporary table instead of a permanent, persistent table. We're going to create a temporary table called TMP books. Inside of this table, we'll have four different columns, the ISBN which will will store character data, the title, which will be an nvarchar 100 data type, the author's first name, and the author's last name. Let's go and highlight these lines, lines eight through 14, and we'll execute them, and that will complete the command successfully and our table is created. Now, let's go ahead and take a look inside of object explorer. I'll expand my databases folder, and find the folder for the temporary tables database that we just created, then we'll come into the tables view, and we'll notice that we actually don't see the table that we just created. We might try right-clicking on it and choosing refresh, but that actually doesn't do anything for us either. Temporary tables are actually physically stored in the temp DB, not the active database. We can find the temp DB by going into the system databases, then here's temp DB, and then down to the temporary tables folder, and there's the books table that we just created. Now, even though this table is actually stored in temp DB, we can actually just work with it as if it were in our temporary tables database. Let's go ahead and scroll down a little bit further, and insert some values into our table. We'll just use a standard insert statement, and then we'll just call the table as it's actually named, with the hash symbol and then TMP books. We're going to insert five different books into our table, the first one being Anna Karenina by Leo Tolstoy, War and Peace, Ulysses, Emma, and Pride and Prejudice. Let's go ahead and highlight line 17 through 23 and execute those. Five rows were added into our table, and now we can select from the table, just like any other table. Go ahead and highlight lines 25 and 26, and execute those, then, in the results window, we'll see the data values that we just added in. So, we can see that we can work with a temporary table just like any other table. There's actually two different types of temporary tables, local and global. Local temporary tables have the single hash symbol, just like the one we created. These can be used by the current user during the current session only. They're automatically deleted when the user logs off from the server instance. Global temporary tables have two hash symbols or a double-hash as the first characters of their name. They're actually visible to any user after they're created, not just the current user, but they also get deleted when all users referencing the table disconnect from their instances of Sequel Server. So, we've seen that temporary tables can be used just like any other table, but here's where the difference comes in: let's go ahead and log off from our server, first I'll close my query window tab, then I'll come over to the object explorer and press this icon to disconnect. Then I'll press this icon to reconnect to the object explorer, and it'll ask me to log back into Sequel Server. Then, we can go back to the databases window, expand the system databases, and expand temp DB, and then finally the temporary tables folder within temp DB, and you'll notice that there's no tables within there anymore. If we pull up our script file once again, I'll go to the file tab, come down to recent files, and then reselect the 02_02 temporary tables sql file, we can go ahead and run that select statement one more time to select star from TMP books, I'll execute that, and it says that the object was actually not found. Now, actually I just realized that I'm running this against master, so just to be sure, let's go ahead and use temporary tables, that'll switch my active database to temporary tables, then we'll scroll down and run that select statement one more time, but you'll see that it actually doesn't matter because temp books doesn't exist anymore. So, because I'm in a new session, the temporary table created in the last session is no longer available. Remembering to leverage temporary tables when you only need to hold data for a brief period while working in a single in a single session is a great way to keep your database from getting littered with lots of tables that would otherwise have to be managed and deleted manually. Before I go, though, I do want to make sure that I delete the temporary tables database that we created. Let's scroll down a few more lines, we'll switch into temp DB and then delete the temporary tables database, and incidentally, the reason that we switched databases before we dropped the current database is because it actually won't let us delete the database when we're working in it. So, that's why we prepend this with the use temp DB statement, just to move into a different database. I'll highlight lines 35 through 38 and execute those. So, even though that the temporary tables automatically get removed when you close the session, the database itself obviously doesn't.

Contents