Knowledgy Consulting, LLC
"Atlanta's Premier Business Intelligence and Data Warehouse Consultants!"
Undocumented Secrets of SQL Internals
It's always fun to look under the hood of a relational database management system to find out what makes it tick. All of the secret, undocumented code that the vendor creates, not only is a great resource, it allows you to really understand the internal engineering allowing you to optimize the system for outstanding performance in it's particular environment. Here I'll show you some of that hidden code, focusing on Sql Server 2005 and 2008.
In Sql Server 2005, Microsoft implemented a large number of dynamic views for administration, monitoring and performance. These views typically access tables that are invisible to DBA's and anyone else working the system. You won't see them in any tool including Studio Manager. Here is the SQL for some of these dynamic. You'll see that if you try to locate the tables these views are accessing you won't find them and in fact, you'll get an error message that says they don't exist. Here are some more undocumented system views.
Here's the underlying query for the sys.sysmembers dynamic management view:
SELECT
memberuid = convert(smallint, member_principal_id),
groupuid = convert(smallint, role_principal_id)
FROM
sys.database_role_members
This is the query the sys.database_role_members. Notice the undocumented invisible system table it's accessing:
SELECT
indepid AS role_principal_id,
depid AS member_principal_id
FROM
sys.sysmultiobjrefs
WHERE
class = 25 AND
depsubid = 0 AND
indepsubid = 0 AND
Has_access('RL', depid, indepid) = 1 -- MRC_DBROLEMEMBER
sys.sysobjects
CREATE VIEW sys.sysobjects
AS
SELECT name,
id,
xtype = TYPE COLLATE database_default,
uid = CONVERT(SMALLINT,nsid),
info = CONVERT(SMALLINT,0),
status = CONVERT(INT,0),
base_schema_ver = CONVERT(INT,0),
replinfo = CONVERT(INT,0),
parent_obj = pid,
crdate = created,
ftcatid = CONVERT(SMALLINT,CASE TYPE
WHEN 'U' THEN Objectproperty(id,'TableFulltextCatalogId')
ELSE 0
END),
schema_ver = CONVERT(INT,0),
stats_schema_ver = CONVERT(INT,0),
TYPE = CONVERT(CHAR(2),CASE TYPE
WHEN 'UQ' THEN 'K'
WHEN 'PK' THEN 'K'
ELSE TYPE COLLATE database_default
END),
userstat = CONVERT(SMALLINT,CASE TYPE
WHEN 'S' THEN 1
WHEN 'U' THEN 1
ELSE 0
END),
sysstat = CONVERT(SMALLINT,CASE TYPE
WHEN 'S' THEN 1
WHEN 'V' THEN 2
WHEN 'U' THEN 3
WHEN 'P' THEN 4
WHEN 'RF' THEN 4
WHEN 'X' THEN 4
WHEN 'D' THEN 6
WHEN 'R' THEN 7
WHEN 'TR' THEN 8
WHEN 'PK' THEN 9
WHEN 'UQ' THEN 9
WHEN 'C' THEN 10
WHEN 'F' THEN 11
ELSE 0
END),
indexdel = CONVERT(SMALLINT,0),
refdate = created,
version = CONVERT(INT,0),
deltrig = CONVERT(INT,CASE TYPE
WHEN 'U' THEN Objectproperty(id,'TableDeleteTrigger')
WHEN 'TR' THEN pid
ELSE 0
END),
instrig = CONVERT(INT,CASE TYPE
WHEN 'U' THEN Objectproperty (id,'TableInsertTrigger')
ELSE 0
END),
updtrig = CONVERT(INT,CASE TYPE
WHEN 'U' THEN Objectproperty(id,'TableUpdateTrigger')
ELSE 0
END),
seltrig = CONVERT(INT,0),
category = CONVERT(INT,CASE
WHEN TYPE = 'D'
AND pid <> 0 THEN 2048
ELSE 0
END
+ CASE
WHEN TYPE = 'P'
AND (status & 256) <> 0 THEN 16
ELSE 0
END
+ (status & 1)
* 2
+ (status & 16)
* 2),
cache = CONVERT(SMALLINT,0)
FROM sys.sysschobjs
WHERE nsclass = 0
AND pclass = 1
AND Has_access('CO',id) = 1
UNION ALL
SELECT name,
id = object_id,
xtype = TYPE COLLATE database_default,
uid = CONVERT(SMALLINT,schema_id),
info = CONVERT(SMALLINT,0),
status = CONVERT(INT,0),
base_schema_ver = CONVERT(INT,0),
replinfo = CONVERT(INT,0),
parent_obj = Sysconv(INT,0),
crdate = create_date,
ftcatid = CONVERT(SMALLINT,0),
schema_ver = CONVERT(INT,0),
stats_schema_ver = CONVERT(INT,0),
TYPE = TYPE COLLATE database_default,
userstat = CONVERT(SMALLINT,0),
sysstat = CONVERT(SMALLINT,CASE TYPE
WHEN 'V' THEN 2
WHEN 'P' THEN 4
WHEN 'X' THEN 4
ELSE 0
END),
indexdel = CONVERT(SMALLINT,0),
refdate = create_date,
version = CONVERT(INT,0),
deltrig = CONVERT(INT,0),
instrig = CONVERT(INT,0),
updtrig = CONVERT(INT,0),
seltrig = CONVERT(INT,0),
category = CONVERT(INT,2),
cache = CONVERT(SMALLINT,0)
FROM sys.system_objects$
sys.key_constraints
CREATE
VIEW sys.key_constraints AS
SELECT
o.name,
o.object_id,
o.principal_id,
o.schema_id,
o.parent_object_id,
o.type,
o.type_desc,
o.create_date,
o.modify_date,
o.is_ms_shipped,
o.is_published,
o.is_schema_published,
idx.indid AS unique_index_id,
o.is_system_named
FROM
sys.objects$ o
LEFT JOIN sys.sysidxstats idx
ON
idx.id = o.parent_object_id AND
idx.name = o.name
WHERE
o.type IN ( 'PK', 'UQ' )
sys.sql_dependencies
CREATE VIEW
sys.sql_dependencies
AS
SELECT
dp.class,
i.name AS class_desc,
dp.depid AS object_id,
dp.depsubid AS column_id,
dp.indepid AS referenced_major_id,
dp.indepsubid AS referenced_minor_id,
sysconv ( bit, dp.status & 4 ) AS is_selected, -- OBJDEP_READCS
sysconv ( bit, dp.status & 2 ) AS is_updated, -- OBJDEP_RESULT
sysconv ( bit, dp.status & 1 ) AS is_select_all -- OBJDEP_SELECT
FROM
sys.sysmultiobjrefs dp
LEFT JOIN sys.syspalvalues i
ON
i.class = 'DPCL' AND
i.value = dp.class
WHERE
dp.class <= 4 -- MRC_OLDDEPENDS,MRC_EXPRTOOBJ,MRC_EXPRTOTYPE,MRC_EXPRTOXMLNS,MRC_EXPRTOPRTFUNC
