Где хранятся временные таблицы sql server
В дополнение к табличным переменным можно определять временные таблицы. Такие таблицы могут быть полезны для хранения табличных данных внутри сложного комплексного скрипта.
Временные таблицы существуют на протяжении сессии базы данных. Если такая таблица создается в редакторе запросов (Query Editor) в SQL Server Management Studio, то таблица будет существовать пока открыт редактор запросов. Таким образом, к временной таблице можно обращаться из разных скриптов внутри редактора запросов.
После создания все временные таблицы сохраняются в таблице tempdb , которая имеется по умолчанию в MS SQL Server.
Если необходимо удалить таблицу до завершения сессии базы данных, то для этой таблицы следует выполнить команду DROP TABLE .
Название временной таблицы начинается со знака решетки #. Если используется один знак #, то создается локальная таблица, которая доступна в течение текущей сессии. Ели используются два знака ##, то создается глобальная временная таблица. В отличие от локальной глобальная временная таблица доступна всем открытым сессиям базы данных.
Например, создадим локальную временную таблицу:
И с этой таблицей можно работать в большей степени как и с обычной таблицей — получать данные, добавлять, изменять и удалять их. Только после закрытия редактора запросов эта таблица перестанет существовать.
Подобные таблицы удобны для каких-то временных промежуточных данных. Например, пусть у нас есть три таблицы:
Выведем во временную таблицу промежуточные данные из таблицы Orders:
Здесь вначале извлекаются данные во временную таблицу #OrdersSummary. Причем так как данные в нее извлекаются с помощью выражения SELECT INTO, то предварительно таблицу не надо создавать. И эта таблица будет содержать id товара, общее количество проданного товара и на какую сумму был продан товар.
Затем эта таблица может использоваться в выражениях INNER JOIN.
Подобным образом определяются глобальные временные таблицы, единственное, что их имя начинается с двух знаков ##:
Обобщенные табличные выражения
Кроме временных таблиц MS SQL Server позволяет создавать обобщенные табличные выражения (common table expression или CTE), которые являются производными от обычного запроса и в плане производительности являются более эффективным решением, чем временные. Обобщенное табличное выражение задается с помощью ключевого слова WITH :
В отличие от временных таблиц табличные выполнения хранятся в оперативной памяти и существуют только во время первого выполнения запроса, который представляет это табличное выражение.
Is there a way to get a list of all current temporary tables in SQL Server?
I realize that temporary tables are session/connection bound and not visible or accessible out of the session/connection.
I have a long running stored procedure that creates temporary tables at various stages.
Is there a way I can see the list of current temporary tables? What privileges do I need to be able to do so?
Is there a way I can see the particular SQL statement being executed inside a running stored procedure? The procedure is running as a scheduled job in SQL Server.
I am using SQL Server 2000.
Thanks for your guidance.
5 Answers 5
Is this what you are after?
You can get list of temp tables by following query :
you can remove the ## line if you want to include global temp tables.
For SQL Server 2000, this should tell you only the #temp tables in your session. (Adapted from my example for more modern versions of SQL Server here.) This assumes you don’t name your tables with three consecutive underscores, like CREATE TABLE #foo___bar :
If you need to ‘see’ the list of temporary tables, you could simply log the names used. (and as others have noted, it is possible to directly query this information)
If you need to ‘see’ the content of temporary tables, you will need to create real tables with a (unique) temporary name.
You can trace the SQL being executed using SQL Profiler:
[These articles target SQL Server versions later than 2000, but much of the advice is the same.]
If you have a lengthy process that is important to your business, it’s a good idea to log various steps (step name/number, start and end time) in the process. That way you have a baseline to compare against when things don’t perform well, and you can pinpoint which step(s) are causing the problem more quickly.
Introduction to Temporary Tables in SQL Server
A temporary table in SQL Server, as the name suggests, is a database table that exists temporarily on the database server. A temporary table stores a subset of data from a normal table for a certain period of time.
Temporary tables are particularly useful when you have a large number of records in a table and you repeatedly need to interact with a small subset of those records. In such cases instead of filtering the data again and again to fetch the subset, you can filter the data once and store it in a temporary table. You can then execute your queries on that temporary table. Temporary tables are stored inside “tempdb” which is a system database. Let’s take a look at how you can use a temporary data in a simple scenario.
Preparing the Data
Let’s first prepare some dummy data. We will use this data to create temporary tables.
Run the following script on your database server.
CREATE DATABASE schooldb
CREATE TABLE student
(
id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gender VARCHAR(50) NOT NULL,
age INT NOT NULL,
total_score INT NOT NULL,
)
INSERT INTO student
VALUES (1, ‘Jolly’, ‘Female’, 20, 500),
(2, ‘Jon’, ‘Male’, 22, 545),
(3, ‘Sara’, ‘Female’, 25, 600),
(4, ‘Laura’, ‘Female’, 18, 400),
(5, ‘Alan’, ‘Male’, 20, 500),
(6, ‘Kate’, ‘Female’, 22, 500),
(7, ‘Joseph’, ‘Male’, 18, 643),
(8, ‘Mice’, ‘Male’, 23, 543),
(9, ‘Wise’, ‘Male’, 21, 499),
(10, ‘Elis’, ‘Female’, 27, 400);
The above SQL script creates a database ‘schooldb’. In this database, a table called ‘student’ is created and some dummy data added into the table.
Creating A Temporary Table
There are two methods of creating temporary tables.
Method 1
The simplest way of creating a temporary table is by using an INTO statement within a SELECT query. Let’s create a temporary table that contains the name, age, and gender of all the male student records from the student table.
USE schooldb;
SELECT name, age, gender
INTO #MaleStudents
FROM student
WHERE gender = ‘Male’
Take a look at the above query. Here we created a temporary table “#MaleStudents” which stores the name, age, and gender of all the male student records from student table. To define a temporary table, we use the INTO statement after the SELECT statement. The name of a temporary table must start with a hash (#).
Now, to see where this table exists; go to “Object Explorer -> Databases -> System Databases-> tempdb -> Temporary Tables”. You will see your temporary table name along with the identifier. Take a look at the following figure:
You must be wondering about the “000000000006” at the end of the table name. This is a unique identifier. Multiple database connections can create temporary tables with the same name, therefore to differentiate between the temporary tables created by different connections, the database server automatically appends this unique identifier at the end.
You can perform operations on the temporary table via the same connection that created it. Therefore, in the same query window that created the “#MaleStudents” table, execute the following query.
Since, the #MaleStudents table contains the name, age, and gender of all the male students. The above query will fetch following results.
To create a new connection you can simply open a new query window in “SQL Server Management Studio”. Now, keep the previous connection open and create another “MaleStudents” table using method 2 in a new query window (new connection).
Method 2
The second method is similar to creating normal tables. Take a look at the following query. Here again, we shall create #MaleStudents temporary table. Remember, this query must be executed by a new connection.
USE schooldb;
CREATE TABLE #MaleStudents
(
name VARCHAR(50),
age int,
gender VARCHAR (50))
INSERT INTO #MaleStudents
SELECT name, age, gender
FROM student
WHERE gender = ‘Male’
Now, if you execute the above query, you should see two #MaleStudents temporary tables with different unique identifiers inside the tempdb. This is because these two tables have been created by two different connections. Take a look at the following screenshot.
SQL Server Temporary Tables
Summary: in this tutorial, you will learn how to create SQL Server temporary tables and how to manipulate them effectively.
Temporary tables are tables that exist temporarily on the SQL Server.
The temporary tables are useful for storing the immediate result sets that are accessed multiple times.
Creating temporary tables
SQL Server provided two ways to create temporary tables via SELECT INTO and CREATE TABLE statements.
Create temporary tables using SELECT INTO statement
The first way to create a temporary table is to use the SELECT INTO statement as shown below:
The name of the temporary table starts with a hash symbol ( # ). For example, the following statement creates a temporary table using the SELECT INTO statement:
In this example, we created a temporary table named #trek_products with two columns derived from the select list of the SELECT statement. The statement created the temporary table and populated data from the production.products table into the temporary table.
Once you execute the statement, you can find the temporary table name created in the system database named tempdb , which can be accessed via the SQL Server Management Studio using the following path System Databases > tempdb > Temporary Tables as shown in the following picture:
As you can see clearly from the picture, the temporary table also consists of a sequence of numbers as a postfix. This is a unique identifier for the temporary table. Because multiple database connections can create temporary tables with the same name, SQL Server automatically appends this unique number at the end of the temporary table name to differentiate between the temporary tables.
Create temporary tables using CREATE TABLE statement
The second way to create a temporary table is to use the CREATE TABLE statement:
This statement has the same syntax as creating a regular table. However, the name of the temporary table starts with a hash symbol ( # )
After creating the temporary table, you can insert data into this table as a regular table:
Of course, you can query data against it within the current session:
However, if you open another connection and try the query above query, you will get the following error:
This is because the temporary tables are only accessible within the session that created them.
Global temporary tables
Sometimes, you may want to create a temporary table that is accessible across connections. In this case, you can use global temporary tables.
Unlike a temporary table, the name of a global temporary table starts with a double hash symbol ( ## ).
The following statements first create a global temporary table named ##heller_products and then populate data from the production.products table into this table:
Now, you can access the ##heller_products table from any session.
Dropping temporary tables
Automatic removal
SQL Server drops a temporary table automatically when you close the connection that created it.
SQL Server drops a global temporary table once the connection that created it closed and the queries against this table from other connections completes.
Manual Deletion
From the connection in which the temporary table created, you can manually remove the temporary table by using the DROP TABLE statement:
In this tutorial, you have learned about SQL Server temporary tables and how to create and remove them effectively.