Microsoft SQL Server 2012 Internals

Special indexes vs. ordinary indexes

The means of creating, maintaining, and using special indexes seem, at first glance, to be significantly different from “ordinary” indexes, but when you have a chance to inspect these indexes, you’ll find that they are really implemented by using internal tables and ordinary B-tree indexes on the internal tables. Internal tables are similar in concept to what’s referred to as “side-tables” in some other databases’ extender technologies, but you can’t use the internal tables directly. You can list the internal tables in a particular database by using the query

SELECT * FROM sys.internal_tables;

Running this query against a newly created database shows tables that are used for service broker built-in queues, as ...