Compartir a través de

  • Artículo

Tablas internas

SQL Server crea automáticamente tablas internas para admitir las siguientes características:

  • Índices XML principales

  • Índices espaciales

  • Service Broker

  • Notificaciones de consultas

  • Seguimiento de cambios

Las tablas internas son un efecto secundario de alguna acción del usuario. Por ejemplo, cuando se crea un índice XML principal, SQL Server crea automáticamente una tabla interna para hacer que persistan los datos del documento XML dividido. Las tablas internas aparecen en el esquema sys de cada base de datos y tienen nombres únicos y generados por el sistema que indican su función, por ejemplo, xml_index_nodes_2021582240_32001 o queue_messages_1977058079.

Las tablas internas no contienen datos accesibles para el usuario y su esquema es fijo e inalterable. No se puede hacer referencia a los nombres de las tablas internas en las instrucciones de Transact-SQL. Por ejemplo, no se puede ejecutar una instrucción como SELECT * FROM <sys.internal_table_name>. Sin embargo, se pueden consultar vistas de catálogo para ver los metadatos de las tablas internas.

Ver los metadatos de las tablas internas

Puede ver los metadatos asociados a las tablas internas mediante la vista de catálogo sys.internal_tables. Con esta vista puede ver el esquema de las tablas internas. Puesto que las tablas internas tienen muchas de las mismas características que las tablas de usuario, la vista sys.internal_tables hereda las columnas de la vista de catálogo sys.objects y tienen el tipo 'IT'. Al igual que en las tablas de usuario, los metadatos de las columnas de las tablas internas son visibles en la vista de catálogo sys.columns y los metadatos de las estadísticas y los índices generados por el sistema en las tablas internas están visibles en las vistas de catálogo sys.indexes y sys.stats.

En combinación con otras vistas de catálogo, también se puede obtener información acerca de la asignación y la utilización del espacio. Vea "Almacenamiento de tablas internas", más adelante en este tema.

En la ilustración siguiente se muestra el modelo de datos de los catálogos de nivel superior.

Diagrama de vistas de catálogo de tabla interna

Permisos para ver los metadatos de las tablas internas

Para ver los metadatos de las tablas internas en una base de datos necesita uno de los permisos siguientes o bien pertenecer a grupos que se indican:

  • Permiso CONTROL SERVER.

  • Permiso CONTROL en la base de datos.

  • Pertenencia a grupos db_owner o sysadmin.

  • Un usuario que puede ver la entidad primaria (el índice espacial o XML, o la cola) puede ver la tabla interna de dicha entidad.

Ver los metadatos de los índices XML

En la ilustración siguiente se muestra la estructura de los metadatos de una tabla interna de un índice XML.

Diagrama de vistas de catálogo del índice XML

Para comprender las relaciones de la vista de catálogo que se muestran en la ilustración, suponga que el índice XML principal Xp se crea en la tabla T. Los metadatos de la tabla se encuentran en la vista de catálogo sys.tables y los metadatos del índice XML están en la vista de catálogo sys.xml_indexes. Los metadatos de la tabla interna Ti que crea SQL Server para hacer persistentes los datos del índice XML están en la vista sys.internal_tables.

Para buscar la relación entre la tabla interna Ti y la tabla de usuario T, puede combinar la columna parent_id de la vista sys.internal_tables con la columna object_id de la vista sys.tables. Para encontrar la relación entre la tabla interna Ti y el índice XML Xp, puede combinar las columnas parent_id y parent_minor_id de sys.internal_tables con las columnas object_id e index_id de sys.xml_indexes. Vea el ejemplo G a continuación.

Ver los metadatos de los índices espaciales

Los metadatos de los índices espaciales son en gran medida el equivalente a los metadatos de índices XML. Las diferencias son que los índices espaciales utilizan sys.spatial_indexes en lugar de sys.xml_indexes y que es necesario utilizar sys.spatial_index_tessellations para ver los parámetros espaciales de un índice espacial.

En la ilustración siguiente se muestra la estructura de los metadatos de una tabla interna de una cola de índice espacial.

Diagrama de vistas de catálogo del índice espacial

Para comprender las relaciones de la vista de catálogo que se muestran en la ilustración, suponga que el índice espacial Si se crea en la tabla T. Los metadatos de la tabla se encuentran en la vista de catálogo sys.tables y los metadatos del índice espacial están en las vistas de catálogo sys.spatial_indexes y sys.spatial_index_tessellations. Los metadatos de la tabla interna Ti que crea SQL Server para hacer persistentes los datos del índice espacial están en la vista sys.internal_tables.

Para buscar la relación entre la tabla interna Ti y la tabla de usuario T, puede combinar la columna parent_id de la vista sys.internal_tables con la columna object_id de la vista sys.tables. Para encontrar la relación entre la tabla interna Ti y el índice espacial Si, puede combinar las columnas parent_id y parent_minor_id de sys.internal_tables con las columnas object_id e index_id de sys.spatial_indexes. Para obtener más información, vea el ejemplo L más adelante en este tema.

Ver los metadatos de Service Broker

En la ilustración siguiente se muestra la estructura de los metadatos de una tabla interna de una cola de Service Broker. Los mensajes de Service Broker, las notificaciones de consultas y las notificaciones de eventos usan colas de Service Broker. Además, la característica Service Broker también utiliza una tabla interna para almacenar información acerca de todos los servicios de Service Broker de todas las bases de datos. Esta tabla interna está en la base de datos del sistema tempdb.

Diagrama de vistas de catálogo de Service Broker

Ver los metadatos de la notificación de consultas

En la ilustración siguiente se muestra la estructura de los metadatos de una tabla interna de una suscripción para la notificación de consultas. Las tablas internas se usan para almacenar los parámetros de la suscripción.

Diagrama de vistas de catálogo de notificación de consultas

Almacenamiento de tablas internas

Las tablas internas se colocan en el mismo grupo de archivos que la entidad primaria. Puede usar la consulta del catálogo que se muestra en el ejemplo F a continuación para devolver el número de páginas que usan las tablas internas para los datos almacenados consecutivamente, no consecutivamente y de objetos grandes (LOB).

Puede usar el procedimiento del sistema sp_spaceused para devolver datos de la utilización del espacio de las tablas internas. sp_spaceused informa del espacio de las tablas internas como se explica a continuación:

  • Cuando se especifica un nombre de cola, se hace referencia a la tabla interna subyacente asociada a la cola y se informa del consumo del almacenamiento.

  • Las páginas que se utilizan en las tablas internas de índices XML, índices espaciales e índices de texto completo se incluyen en la columna index_size. Cuando se especifica el nombre de una vista indizada o de una tabla, las páginas de los índices XML, espaciales y de texto completo para dicho objeto se incluyen en las columnas reserved e index_size.

Ejemplos

En los ejemplos siguientes se demuestra cómo consultar los metadatos de las tablas internas de consulta mediante vistas de catálogo.

A. Mostrar las tablas internas que heredan las columnas de la vista de catálogo sys.objects

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

B. Devolver todos los metadatos de las tablas internas (incluido los que se heredan de sys.objects)

SELECT * FROM sys.internal_tables;

C. Devolver las columnas de las tablas internas y los tipos de datos de las columnas

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;

D. Devolver los índices de las tablas internas

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;

E. Devolver estadísticas de las tablas internas

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;

F. Devolver información de la unidad de asignación y de la partición de las tablas internas

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;

G. Devolver los metadatos de las tablas internas para los índices 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

H. Devolver los metadatos de las tablas internas para las colas de 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

I. Devolver los metadatos de las tablas internas para todos los servicios de Service Broker

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

J. Devolver los metadatos de las tablas internas para las suscripciones de notificación de consultas

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';

K. Devolver los metadatos de las tablas internas para los índices espaciales

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
    ,si.name AS spatial_index_name
    ,si.index_id as spatial_index_id
FROM sys.internal_tables AS it
JOIN sys.tables AS t 
    ON it.parent_id = t.object_id
JOIN sys.spatial_indexes AS si 
    ON it.parent_id = si.object_id
    AND it.parent_minor_id  = si.index_id
WHERE it.internal_type_desc = 'EXTENDED_INDEXES';
GO