Поделиться через

Внутренние таблицы

  • Статья

Добавления: 14 апреля 2006 г.

SQL Server автоматически создает внутренние таблицы для поддержки следующих функциональных возможностей:

  • полнотекстового поиска;
  • уведомлений о запросах;
  • первичных XML-индексов;
  • компонента Service Broker.

Внутренние таблицы являются побочным результатом других действий пользователя. Например, при создании первичного XML-индекса SQL Server автоматически создает внутреннюю таблицу для сохранения разобранных данных XML-документа. Внутренние таблицы создаются в схеме sys любой базы данных и имеют уникальные имена, сформированные системой, указывающие на их функцию, например, xml_index_nodes_2021582240_32001 или queue_messages_1977058079.

Данные внутренних таблиц недоступны для пользователей, а их схема является жесткой и неизменной. Невозможно ссылаться на имена внутренних таблиц в инструкциях языка Transact-SQL. Например, нельзя выполнить инструкцию SELECT * FROM <sys.internal_table_name>. Однако можно обращаться с запросами к представлениям каталогов для просмотра метаданных внутренних таблиц.

Просмотр метаданных внутренних таблиц

Метаданные, связанные с внутренними таблицами, можно просмотреть с помощью представления каталога sys.internal_tables. С помощью него можно просмотреть схему внутренних таблиц. Так как внутренние таблицы во многих характеристиках схожи с пользовательскими, представление sys.internal_tables наследует столбцы из представления каталога sys.objects и имеет тип «IT». Как и для пользовательских таблиц, метаданные столбцов внутренних таблиц видимы в представлении каталога sys.columns, а метаданные созданных системой индексов и статистика по внутренним таблицам видимы в представлениях каталогов sys.indexes и sys.stats.

Также можно получить информацию о выделении пространства и его использовании путем объединения метаданных с другими представлениями каталогов. См. подраздел «Хранение внутренних таблиц» далее в этом разделе.

На следующей иллюстрации показана модель данных каталога верхнего уровня.

Диаграмма представлений каталога для внутренних таблиц

Разрешения на просмотр метаданных внутренних таблиц

Для просмотра метаданных внутренних таблиц базы данных необходимо иметь одно из следующих разрешений или состоять в одной из следующих групп:

  • разрешение CONTROL SERVER;
  • разрешение CONTROL в базе данных;
  • членство в группе db_owner или sysadmin;
  • пользователь, который может просматривать родительскую сущность (XML-индекс, очередь или полнотекстовый каталог), может просматривать внутреннюю таблицу для этой сущности.

Просмотр метаданных XML-индекса

На следующей иллюстрации показана структура метаданных для внутренней таблицы XML-индекса.

Диаграмма представлений каталога XML-индексов

Чтобы понять взаимосвязь представлений каталогов, показанную на иллюстрации, представьте, что первичный XML-индекс Xp создан для таблицы T. Метаданные для таблицы находятся в представлении каталога sys.tables, а метаданные для XML-индекса находятся в представлении каталога sys.xml_indexes. Метаданные для внутренней таблицы Ti создаются SQL Server для сохранения данных XML-индекса в представлении sys.internal_tables.

Чтобы выяснить связь между внутренней таблицей Ti и пользовательской таблицей T, можно объединить столбец parent_id представления sys.internal_tables со столбцом object_id представления sys.tables. Чтобы выяснить связь между внутренней таблицей Ti и XML-индексом Xp, можно объединить столбцы parent_id и parent_minor_id представления sys.internal_tables со столбцами object_id и index_id представления sys.xml_indexes. См. пример Ж ниже.

Просмотр метаданных компонента Service Broker

На следующей иллюстрации показана структура метаданных для внутренней таблицы очереди компонента Service Broker. Сообщения, уведомления о запросах и о событиях компонента Service Broker используют очереди компонента Service Broker. Кроме того, компонент Service Broker также использует внутреннюю таблицу для хранения информации обо всех службах компонента Service Broker во всех базах данных. Эта внутренняя таблица находится в системной базе данных tempdb.

Диаграмма представлений каталога компонента Service Broker

Просмотр метаданных полнотекстовых каталогов

На следующей иллюстрации показана структура метаданных для внутренних таблиц полнотекстовых каталогов и индексов. Внутренние таблицы используются для хранения свободного списка в полнотекстовом каталоге и схемы документа в полнотекстовом индексе. В свободном списке хранятся неиспользованные идентификаторы документов. Идентификатор документа — это 4-байтовое целое число, используемое внутри полнотекстового каталога для уникального идентифицирования документа. Внутренняя таблица схемы документа хранит сопоставление между полнотекстовым ключом и идентификатором документа.

Диаграмма представлений полнотекстовых каталогов

Просмотр метаданных уведомлений о запросах

На следующей иллюстрации показана структура метаданных для внутренней таблицы подписки на уведомления о запросах. Внутренние таблицы используются для хранения параметров подписки на уведомления о запросах.

Диаграмма представлений каталога для уведомлений о запросах

Хранение внутренних таблиц

Внутренние таблицы размещаются в той же файловой группе, что и родительская сущность. С помощью запроса к каталогу, проиллюстрированного далее в примере Е, можно узнать количество страниц, занимаемых внутренними таблицами под хранение данных «в строке», «вне строки» и данных больших объектов (LOB).

С помощью системной процедуры sp_spaceused можно получить сведения об использовании пространства внутренними таблицами. Процедура sp_spaceused возвращает пространство внутренних таблиц следующими способами.

  • При указании имени запроса базовая внутренняя таблица, связанная с запросом, находится по ссылке и сообщается объем пространства, занятого ей.
  • Страницы, используемые внутренними таблицами XML-индексов и полнотекстовых индексов, включаются в столбец index_size. При указании имени таблицы или индексированного представления страницы, используемые XML-индексами и полнотекстовыми индексами этого объекта, включаются в столбцы reserved и index_size.

Примеры

Следующие примеры демонстрируют, как обратиться с запросом к метаданным внутренней таблицы с помощью представлений каталогов.

А. Просмотр внутренних таблиц, наследующих столбцы от представления каталога sys.objects

SELECT * FROM sys.objects WHERE type = 'IT';

Б. Возвращаются все метаданные внутренней таблицы (в том числе те, которые наследуются от представления каталога sys.objects)

SELECT * FROM sys.internal_tables;

В. Возвращаются столбцы и типы данных столбцов внутренней таблицы

SELECT SCHEMA_NAME(itab.schema_id) AS schema_name
    ,itab.name AS internal_table_name
    ,typ.name AS column_data_type 
    ,col.*
FROM sys.internal_tables AS itab
JOIN sys.columns AS col ON itab.object_id = col.object_id
JOIN sys.types AS typ ON typ.user_type_id = col.user_type_id
ORDER BY itab.name, col.column_id;

Г. Возвращаются индексы внутренней таблицы

SELECT SCHEMA_NAME(itab.schema_id) AS schema_name
    , itab.name AS internal_table_name
    , idx.*
FROM sys.internal_tables AS itab
JOIN sys.indexes AS idx ON itab.object_id = idx.object_id
ORDER BY itab.name, idx.index_id;

Д. Возвращается статистика внутренней таблицы

SELECT SCHEMA_NAME(itab.schema_id) AS schema_name
    ,itab.name AS internal_table_name
    , s.*
FROM sys.internal_tables AS itab
JOIN sys.stats AS s ON itab.object_id = s.object_id
ORDER BY itab.name, s.stats_id;

Е. Возвращается информация о секциях и единицах размещения внутренней таблицы

SELECT SCHEMA_NAME(itab.schema_id) AS schema_name
    ,itab.name AS internal_table_name
    ,idx.name AS heap_or_index_name
    ,p.*
    ,au.*
FROM sys.internal_tables AS itab
JOIN sys.indexes AS idx
--     JOIN to the heap or the clustered index
    ON itab.object_id = idx.object_id AND idx.index_id IN (0,1)
JOIN   sys.partitions AS p 
    ON p.object_id = idx.object_id AND p.index_id = idx.index_id
JOIN   sys.allocation_units AS au
--     IN_ROW_DATA (type 1) and ROW_OVERFLOW_DATA (type 3) => JOIN to partition's Hobt
--     else LOB_DATA (type 2) => JOIN to the partition ID itself.
ON au.container_id =  
    CASE au.type 
        WHEN 2 THEN p.partition_id 
        ELSE p.hobt_id 
    END
ORDER BY itab.name, idx.index_id;

Ж. Возвращаются метаданные внутренней таблицы для XML-индексов

SELECT t.name AS parent_table
    ,t.object_id AS parent_table_id
    ,it.name AS internal_table_name
    ,it.object_id AS internal_table_id
    ,xi.name AS primary_XML_index_name
    ,xi.index_id as primary_XML_index_id
FROM sys.internal_tables AS it
JOIN sys.tables AS t 
    ON it.parent_id = t.object_id
JOIN sys.xml_indexes AS xi 
    ON it.parent_id = xi.object_id
    AND it.parent_minor_id  = xi.index_id
WHERE it.internal_type_desc = 'XML_INDEX_NODES';
GO

З. Возвращаются метаданные внутренней таблицы для очередей компонента Service Broker

SELECT q.name AS queue_name
    ,q.object_id AS queue_id
    ,it.name AS internal_table_name
    ,it.object_id AS internal_table_id
FROM sys.internal_tables AS it
JOIN sys.service_queues  AS  q ON it.parent_id = q.object_id
WHERE it.internal_type_desc = 'QUEUE_MESSAGES';
GO

И. Возвращаются метаданные внутренней таблицы для всех служб компонента Service Broker

SELECT * 
FROM tempdb.sys.internal_tables 
WHERE internal_type_desc = 'SERVICE_BROKER_MAP';
GO

К. Возвращаются метаданные внутренней таблицы для полнотекстовых каталогов и индексов

--
-- Internal table for full-text catalog free list
--
SELECT ftc.name AS fulltext_catalog_name
    ,ftc.fulltext_catalog_id
    ,it.name AS internal_table_name
    ,it.object_id AS internal_table_id
FROM sys.internal_tables AS it
JOIN sys.fulltext_catalogs AS ftc ON it.parent_id = ftc.fulltext_catalog_id
WHERE it.internal_type_desc = 'FULLTEXT_CATALOG_FREELIST' ;
GO
--
-- Internal table for full-text document map
--
SELECT OBJECT_NAME(fti.object_id) AS table_containing_fulltext_index
    ,it.name AS internal_table_name
    ,it.object_id AS internal_table_id
FROM sys.internal_tables AS it
JOIN sys.fulltext_indexes AS fti ON it.parent_id = fti.object_id
WHERE it.internal_type_desc = 'FULLTEXT_INDEX_MAP';
GO

Л. Возвращаются метаданные внутренней таблицы для подписок на уведомления о запросах

SELECT qn.id AS query_subscription_id
    ,it.name AS internal_table_name
    ,it.object_id AS internal_table_id
FROM sys.internal_tables AS it
JOIN sys.dm_qn_subscriptions AS qn ON it.object_id = qn.object_id
WHERE it.internal_type_desc = 'QUERY_NOTIFICATION';

См. также

Основные понятия

Индексы для столбцов типа данных xml
Основные сведения уведомлений о событиях
Использование уведомлений запросов

Другие ресурсы

Очереди
Архитектура компонента Full-Text Search

Справка и поддержка

Получение помощи по SQL Server 2005