Tabelle interne
SQL Server crea automaticamente tabelle interne per supportare le funzionalità seguenti:
Indici XML primari
Indici spaziali
Service Broker
Notifiche delle query
Rilevamento delle modifiche
Le tabelle interne sono un effetto collaterale di un'azione dell'utente. Quando ad esempio si crea un indice XML primario, SQL Server crea automaticamente una tabella interna per rendere persistenti i dati del documento XML suddiviso. Le tabelle interne vengono visualizzate nello schema sys di ogni database e dispongono di nomi univoci, generati dal sistema che ne indicano la funzione, ad esempio, xml_index_nodes_2021582240_32001 o queue_messages_1977058079.
Le tabelle interne non includono dati accessibili all'utente e i loro schemi sono fissi e non modificabili. Non è possibile fare riferimento ai nomi delle tabelle interne nelle istruzioni Transact-SQL. Non è ad esempio possibile eseguire un'istruzione quale SELECT * FROM <sys.internal_table_name>. È tuttavia possibile eseguire query sulle viste del catalogo per vedere i metadati delle tabelle interne.
Visualizzazione dei metadati delle tabelle interne
È possibile visualizzare i metadati associati alle tabelle interne utilizzando la vista del catalogo sys.internal_tables. Utilizzando tale vista è possibile visualizzare lo schema delle tabelle interne. Poiché le tabelle interne hanno molte caratteristiche in comune con le tabelle utente, la vista sys.internal_tables eredita le colonne della vista del catalogo sys.objects e ha il tipo 'IT'. Analogamente a quanto avviene per le tabelle utente, i metadati delle colonne per le tabelle interne sono visibili nella vista del catalogo sys.columns e i metadati per gli indici e le statistiche generati dal sistema sulle tabelle interne sono visibili nelle viste del catalogo sys.indexes e sys.stats.
Tramite l'unione con altre viste del catalogo, è possibile ottenere anche informazioni relative all'allocazione e all'utilizzo dello spazio. Vedere "Archivio delle tabelle interne" di seguito in questo argomento.
Nella figura seguente viene illustrato il modello di dati del catalogo di livello principale.
.gif "Diagramma di viste del catalogo delle tabelle interne")
Autorizzazioni alla visualizzazione dei metadati delle tabelle interne
Per visualizzare i metadati delle tabelle interne in un database, è necessaria una delle autorizzazioni o appartenenza ai gruppi seguenti:
Autorizzazione CONTROL SERVER.
Autorizzazione CONTROL nel database.
Appartenenza al gruppo db_owner o sysadmin.
Un utente in grado di visualizzare l'entità padre (coda oppure indice XML o spaziale) è in grado di visualizzare la tabella interna per tale entità.
Visualizzazione dei metadati dell'indice XML
Nella figura seguente viene illustrata la struttura dei metadati per una tabella interna o un indice XML.
.gif "Diagramma di viste del catalogo degli indici XML")
Per comprendere le relazioni tra le viste del catalogo illustrate nella figura, si supponga che un indice XML primario Xp venga creato nella tabella T. I metadati della tabella si trovano nella vista del catalogo sys.tables, i metadati dell'indice XML nella vista del catalogo sys.xml_indexes. I metadati della tabella interna Ti creata da SQL Server per rendere persistenti i dati dell'indice XML si trovano nella vista sys.internal_tables.
Per individuare la relazione tra la tabella interna Ti e la tabella utente T, è possibile unire la colonna parent_id della vista sys.internal_tables e la colonna object_id della vista sys.tables. Per individuare la relazione tra la tabella interna Ti e l'indice XML Xp, è possibile unire le colonne parent_id e parent_minor_id di sys.internal_tables alle colonne object_id e index_id di sys.xml_indexes. Vedere l'esempio G seguente.
Visualizzazione dei metadati dell'indice spaziale
I metadati di indici spaziali sono quasi sempre equivalenti ai metadati degli indici XML. Le differenze sono che gli indici spaziali utilizzano sys.spatial_indexes invece di sys.xml_indexes e che è necessario utilizzare sys.spatial_index_tessellations per visualizzare i parametri spaziali per un indice spaziale.
Nella figura seguente viene illustrata la struttura dei metadati per una tabella interna di un indice spaziale.
.gif "Diagramma di viste del catalogo di un indice spaziale")
Per comprendere le relazioni tra le viste del catalogo illustrate nella figura, si supponga che un indice spaziale Si venga creato nella tabella T. I metadati della tabella sono nella vista del catalogo sys.tables mentre i metadati dell'indice spaziale si trovano nelle viste del catalogo sys.spatial_indexes e sys.spatial_index_tessellations. I metadati della tabella interna Ti creata da SQL Server per rendere persistenti i dati dell'indice spaziale si trovano nella vista sys.internal_tables.
Per individuare la relazione tra la tabella interna Ti e la tabella utente T, è possibile unire la colonna parent_id della vista sys.internal_tables e la colonna object_id della vista sys.tables. Per individuare la relazione tra la tabella interna Ti e l'indice spaziale Si, è possibile unire le colonne parent_id e parent_minor_id di sys.internal_tables alle colonne object_id e index_id di sys.xml_indexes. Per ulteriori informazioni, vedere l'esempio L riportato di seguito in questo argomento.
Visualizzazione dei metadati di Service Broker
Nella figura seguente viene illustrata la struttura dei metadati per una tabella interna in una coda di Service Broker. I messaggi di Service Broker, le notifiche delle query e le notifiche degli eventi utilizzano le code di Service Broker. La funzionalità Service Broker utilizza inoltre una tabella interna per archiviare le informazioni relative a tutti i servizi di Service Broker in tutti i database. Questa tabella interna si trova nel database di sistema tempdb.
.gif "Diagramma di viste del catalogo di Service Broker")
Visualizzazione dei metadati di notifica delle query
Nella figura seguente viene illustrata la struttura dei metadati per una tabella interna in una sottoscrizione di notifica delle query. Le tabelle interne vengono utilizzate per archiviare i parametri della sottoscrizione di notifica delle query.
.gif "Diagramma di viste del catalogo delle notifiche di query")
Archivio delle tabelle interne
Le tabelle interne sono collocate nello stesso filegroup dell'entità padre. È possibile utilizzare la query del catalogo illustrata nell'esempio F seguente per restituire il numero di pagine utilizzate dalle tabelle interne per i dati all'interno di righe, all'esterno di righe e LOB (Large Object).
È possibile utilizzare la procedura di sistema sp_spaceused per restituire i dati relativi all'utilizzo dello spazio per le tabelle interne. sp_spaceused indica lo spazio nelle tabelle interne nei modi seguenti:
Quando il nome di una coda è specificato, viene fatto riferimento alla tabella interna sottostante associata alla coda e viene indicato l'utilizzo dello spazio da parte della tabella.
Le pagine utilizzate dalle tabelle interne di indici XML, spaziali e full-text vengono incluse nella colonna index_size. Quando viene specificato il nome di una tabella o vista indicizzata, le pagine per gli indici XML, spaziali e full-text per l'oggetto vengono incluse nelle colonne reserved e index_size.
Esempi
Negli esempi seguenti viene illustrato come eseguire query sui metadati delle tabelle interne utilizzando le viste del catalogo.
A. Visualizzazione delle tabelle interne che ereditano le colonne dalla vista del catalogo sys.objects
SELECT * FROM sys.objects WHERE type = 'IT';
B. Restituzione di tutti i metadati delle tabelle interne, inclusi quelli ereditati da sys.objects
SELECT * FROM sys.internal_tables;
C. Restituzione delle colonne delle tabelle interne e dei tipi di dati colonna
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. Restituzione degli indici delle tabelle interne
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. Restituzione delle statistiche delle tabelle interne
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. Restituzione delle informazioni sulle partizioni e sulle unità di allocazione delle tabelle interne
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. Restituzione dei metadati delle tabelle interne per gli indici 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. Restituzione dei metadati delle tabelle interne per le code di 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. Restituzione dei metadati delle tabelle interne per tutti i servizi di Service Broker
SELECT *
FROM tempdb.sys.internal_tables
WHERE internal_type_desc = 'SERVICE_BROKER_MAP';
GO
J. Restituzione dei metadati delle tabelle interne per le sottoscrizioni di notifica delle query
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. Restituzione dei metadati delle tabelle interne per gli indici spaziali
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