SQL Server Internals: spt_values, Part 1

Paul Munkenbeck

Although most SQL Server system tables are well-documented, there are a few that are rarely mentioned, and one of these is the spt_values table -- a core lookup table that's essential to the operation of many system functions. In the first installment of this two-part series, Paul shows you how an understanding of the contents and use of spt_values can give fascinating insight into both the internals and history of SQL Server.

I ran across the spt_values table when I was examining the source code for some of SQL Server's built-in system stored procedures such as sp_spaceused and sp_lockinfo. A simple select * from spt_values reveals a reference table that seems to contain lots of interesting lookup information. Because I could find no details about the table's structure or content in the reference manuals, I was (naturally enough) driven to investigate it for myself.

The spt_values table started life back in the Sybase version of SQL Server and is still important in both the Sybase and Microsoft versions today. There are common elements of spt_values that are still included in both products, but the table is clearly evolving in different ways. New entries have been added to spt_values in Microsoft versions 6.0 and 6.5.

It's actually a table of tables. It consists of around 20 separate "lookup tables" that have been combined into a single format so they can be stored in a single physical table. Many of these individual lookup tables are designed to be used by the family of sp_help system procedures. These lookup tables decode the various status flags and bitmap settings (see the sidebar "Bitmaps, Binary Notation, and Integers" for more on bitmaps) -- as held in the system tables -- into meaningful descriptions and are very useful if you write your own queries against the system tables and want readable descriptions.

Table 1 depicts the structure of the spt_values table itself, and Table 2 lists all of the lookup tables that comprise spt_values. The name column contains the title of the lookup table, which is sometimes –– but not always –– explanation enough. In the rest of this article, I'll describe the content and purpose of each of these lookup tables.

Table 1: The structure of the spt_values table. In SQL 6.0, spt_values had one index, clustered on number and type. In SQL 6.5, there is instead a non-clustered index over these columns, and also a new unique clustered index on type, number, and name.

Table 2: Lookup tables in spt_values.

Lookup table "A" decodes the bitmap held in the srvstatus column of the sysservers table and also provides a list of options available under sp_serveroption as displayed by sp_helpserver. The last two entries were new values added in SQL Server 6.5.

Lookup table "A" -- sysservers types.

Lookup table "B" is used by several system procedures to decode an on/off bit setting into a meaningful text description. If the bitmap column or variable can contain null, the third entry in the lookup table is obtained by using the ISNULL function to replace null with the value 2.

Lookup table "B" -- yes or no.

Lookup table "C" (I've shown only a sample of the rows) appears to be the one that dictates the overall structure of spt_values, into which all the other lookup tables have been "shoehorned." It holds all of the sp_configure options. Column number is the configuration option identifier as used in tables sysconfigures (latest configured values) and syscurconfigs (current running values). Columns low and high specify the valid range of values the option can take. The status column is a bitmap with two bits. Bit 0x01 indicates a dynamic option –– one that can take effect immediately without a restart of SQL Server. Bit 0x10 specifies an advanced option –– one that's hidden from sp_configure unless the "show advanced" option is turned on.

On the rare occasions that spt_values is mentioned in any of the official documentation, it's usually accompanied with warnings of dire trouble if you attempt to change anything in it. However, back in SQL Server 4.21A, there was actually an instance when Microsoft considered that the range of values allowed for one particular option could be extended. PSS article Q110352 suggested that the value in the high column for entry "max async IO" could be increased beyond the current limit of 50. This was to cater to "unforeseen technical advances" in high performance disk subsystems. Interestingly, the upper limit in SQL 6.0 was increased to 255, and it's now 1024 in 6.5!

Lookup table "C" -- configuration options.

Most of the entries in lookup table "D" are held to provide descriptions for the sp_helpdb procedure. Apart from two particular entries, they all decode the bitmap held in the status column of sysdatabases. The two exceptions are "published" and "subscribed." These interpret the bitmap held in the category column of the sysdatabases table, but mapped into the high two bytes of the number column. This effectively multiplies the category value by 0x10000 or 65536. Many of the entries in this lookup table are options that can be set using the sp_dboption stored procedure. These can be identified as all the ones that are masked by the bitmap held in the entry "ALL SETTABLE OPTIONS."

Several changes were made to this table from 6.0 to 6.5. The entry "don't recover" was renamed as "loading," and the entries "pre-recovery" and "recovering" were added. Even so, some of the possible values for sysdatabases.status (as mentioned in the Transact-SQL Reference Manual) aren't in this lookup table yet; namely, value 2 (in transition) and 8192 (being checkpointed).

Lookup table "D" -- database status.

New in 6.5, lookup table "DBV" decodes the version column of the sysdatabases table, which holds the internal version number of the SQL Server code with which a database was created. The range of possible values in version is stored in the low and high columns. The production versions of SQL Server 6.0 and 6.5 have values of 406 and 408, respectively. The other values were those assigned to beta versions. Interestingly, the number column in spt_values is populated with what looks like a release date.

Lookup table "DBV" -- sysdatabases.version.

Lookup table "E" is retained from the early versions of SQL Server where it provided values specific to the particular environment and platform on which the DBMS was running. Now that the Microsoft version of SQL Server runs exclusively on Windows NT, it's become rather redundant, but several system procedures still refer to it. The number column simply identifies the entry in the table. It's the low column that holds the lookup value. Entry 1 holds the number of bytes in a page, currently 2K, but watch this space to see how the larger page sizes will be recorded in later versions.

Lookup table "E" -- SQL Server host type.

Some system table columns, such as sysusages.segmap, use all 32 bits of a bitmap stored as a 4-byte int datatype. On many platforms, integer datatypes are stored as signed integers, which means that the high bit is reserved as the sign bit (see the sidebar "Bitmaps, Binary Notation, and Integers"). This can make it difficult to handle the case where this bit is used in a bitmap. Entry 2 in the lookup table holds the value of an integer where only the high bit is set -- that is, 0x80000000. As a signed two's complement integer, this is interpreted as the decimal value -2,147,483,648.

In other system tables, such as the sysdevices.low and sysdevices.high columns, there are 1-byte bitmaps stored in the high byte of a 4-byte int datatype. These are extracted by using the CONVERT function to interpret them as binary and then picking off the high byte with the SUBSTRING function. Easy? Well, not on some platforms -- notably some implementations of SQL Server on OS/2. In those days, the conversion routines returned the bytes in the physical order as used by the Intel 80x86 processor -- that is, with the high-byte fourth. By the time SQL Server was implemented on NT, the convert to binary function had been amended to reverse the byte order into the more intuitive sequence. Entry 3 in the lookup table indicates the position of the high byte, which is why it always shows 1 these days.

Lookup table "F" decodes the bitmap held in the status column of the sysremotelogins table. It lists the options that can be set using the sp_remoteoption procedure, currently limited to just the one option! A "trusted" remote login won't have its password checked. The table is also used by sp_helpremotelogin to list the current status of the options, hence the blank entry that's used when no options are set.

Lookup table "F" -- sysremotelogins types.

Lookup table "G" is a peculiar lookup table. It was introduced with SQL Server 6.5, but I can't find any function that uses it. In SQL Server 6.0, the literal "SQL Server Internal Table" appears in the Enterprise Manager current activity windows to indicate a lock on a temporary query worktable. The relevant front-end windows are populated by calls to system stored procedures sp_processinfo and sp_lockinfo. In 6.0, both of these had "SQL Server Internal Table" hard-coded for the object name as a special case for when the locked object id is zero, indicating a worktable.

Lookup table "G" -- general misc. strings.

If a developer were to replace this hard-coded description with an entry from a lookup table in spt_values, then lookup table "G" is exactly what you'd expect them to implement. However, if you look at Enterprise Manager in 6.5, there's a completely different description for the worktable (see the "SFL" lookup table).

Perhaps the development team changed their minds part-way through, but forgot to remove the code that creates lookup table "G" from the final release. Or, maybe lookup table "G" is used by some other internal process that I've been unable to identify. Does anyone know anything more?

Lookup table "I" decodes the status column of the sysindexes table and is used by sp_helpindex to describe the various characteristics with which the index was created.

Lookup table "I" -- index types.

In SQL Server 4.2x, referential integrity had to be managed using a combination of triggers and stored procedures sp_primarykey, sp_foreignkey, sp_commonkey, sp_dropkey, sp_helpjoins, and sp_helpkey. I remember it well, but not with much affection! System table syskeys held information about which columns were defined as keys. With SQL Server 6.x came support for ANSI standard declarative referential integrity (DRI) through the CONSTRAINTS syntax as part of CREATE and ALTER TABLE. There are new procedures -- sp_helpconstraint, sp_pkeys, and sp_fkeys -- to return information about your DRI. The older method became obsolete, and the associated procedures and syskeys table are now included for backward compatibility only. They're not even mentioned in the 6.0 documentation set. However, spt_values still includes lookup tables to support them.

Lookup table "J" defines which datatypes are compatible enough to be used in joins or as foreign keys. The unique key for this lookup table is the low column. This is the datatype identifier as held in the type column of tables systypes and syscolumns. The lookup table's number column defines those datatypes that are compatible. For example, although all of the integer datatypes (int, smallint, and tinyint) have different physical characteristics, they're similar enough to be compatible and are given the same number value.

Even two columns defined with the same datatype might have different type values in syscolumns. This is because for most datatypes a distinction is made between a column definition that uses NULL and one that uses NOT NULL. As an example, a column defined with int NOT NULL is an int (type 56), whereas int NULL is an intn (type 38).

Lookup table "J" -- compatible types.

Lookup table "K" is present only for backward compatibility with the now obsolete methods of implementing referential integrity based on the syskeys table. It decodes the type column in table syskeys and was used by sp_helpkey to display a description for each defined key.

Lookup table "K" -- syskeys types.

Lookup table "L" decodes the type column in the syslocks table. It's used by the sp_lock system procedure to display a meaningful description for each lock currently in place. In syslocks, if a particular lock is blocking another process, bit 0x100 is set in the type column. This effectively adds 256 to the lock type and requires the lookup table to have two entries for each lock type. The descriptions for the blocking lock types have the string "-blk" added to them. To save space (and reduce boredom), I've shown only representative data –– in this case, the first and last blocking lock entries in the lookup table.

Lookup table "L" -- lock types.

Lookup table "N" was present in SQL Server 6.0, but it was removed in 6.5. It lists the query-processing options available to DBLib applications and the SET command. I can't find anything in version 6.0 that actually uses it. The only place I know where session settings are displayed in 6.0 is in the DBCC USEROPTIONS command, and this clearly doesn't use descriptions from this lookup table. The clue to this is the "statistics io and ti(me)" entry. Yes, that's the full description that the developers attempted to insert into the column, but they forgot it was only 20 characters wide! DBCC USEROPTIONS displays the two "statistics" options as separate items.

Lookup table "N" -- options bits.

The management of session settings has been completely overhauled in 6.5, and there's a new "SOP" lookup table that lists the user options held as a bitmap in the new @@OPTIONS variable. In fact, the @@OPTIONS variable did exist in 6.0, but it wasn't documented, had a completely different bitmap from the 6.5 version, and doesn't even match with all the entries in this lookup table. [Look for the remainder of this two-part series describing tables "O" and on in next month's issue. -- Ed.]

Sidebar: Bitmaps, Binary Notation, and Integers

Many SQL Server system tables store information as a bitmap. Under Windows NT, and many other platforms, each byte contains 8 bits. Each bit can be either on (value 1) or off (value 0), and this makes them ideal for use as yes/no flags. A bitmap can occupy one or more bytes, depending on the number of flags to be held. Each bit in the bitmap could be defined as a separate column using the bit datatype. However, bitmaps are easier to manage when stored together in full bytes using the binary or integer datatypes.

In a binary datatype, the eight bits of each byte are grouped into two quartets of  four bits each. The binary value of each quartet is represented by a single character from 0 to 9 or A to F (A to F represent the values 10 to 15), as follows:

0000 = 00100 = 41000 = 81100 = C

0001 = 10101 = 51001 = 91101 = D

0010 = 20110 = 61010 = A1110 = E

0011 = 30111 = 71011 = B1111 = F

To distinguish a binary representation from a regular number, SQL Server uses a notation whereby the binary value is prefixed with the string 0x. So, for example, a one-byte binary datatype that contains the bitmap settings 01011010 would be displayed as 0x5A.

Integer datatypes are even more convenient for handling bitmaps because you can do arithmetic and binary operations with them; tinyint is one byte or 8 bits, smallint is two bytes or 16 bits, and int is four bytes or 32 bits. We visualize the bits as increasing in numerical significance from right to left. The low (rightmost) bit is addressed as bit zero, the next to the left is bit 1, and so on.

The high (leftmost) bit takes on a special significance, as we shall soon see. Apart from this high bit, every other bit is interpreted as the value of 2 raised to the power of its bit number. The integer value of the bitmap column is the sum of the powers of 2 of all those bits that are set on. So, a 1-byte integer bitmap that contains the bit settings 01011010 would be displayed as the value 90:

Bit numbers:76543210

powers of 2: 6432168421

tinyint bitmap:01011010

integer value (64 + 0+ 16+ 8+ 0+2+ 0) =90

Now, SQL Server integers can take positive or negative values. To achieve this, they're stored using a method known as "signed two's complement." With this method, the high bit is a sign bit. It doesn't contribute to the absolute value of the integer but is used instead to indicate whether the value is positive (high bit off) or negative (high bit on). To interpret the value of a bitmap held in two's complement, you must reverse each of  the bit settings and then add 1. 

The reason for this peculiar technique is because of the way that binary addition is handled by the CPU. The arithmetic unit itself doesn't "understand" the sign bit –– it just takes two bit strings and does binary arithmetic on them. If, after adding together two binary strings, the result is larger than the intended storage location, then the leading bits are truncated, leaving just the rightmost (that is, least significant) bits. 

If you take any binary string, reverse all the bits, and then add it to the original binary string, the result is always a string of all 1s. Adding 1 to this result causes the high bit to overflow but leaves zeros in all other positions. Therefore, a bitmap added to its two's complement always results in zero. This is exactly the result we want when adding an integer to its own negative value! Signed two's complement arithmetic is a little tricky for mere humans, so here's an example:

Bitmap1010 0110

Reversed01011 001

+ 10000 0001

= 2's complement01011010

powers of 2: 6432168421

integer value- (64 + 0+ 16 +8+ 0+2+ 0   ) = -90

Try a couple of examples of this for yourself. First of all, start with the end result above, and see how you get back to the original. Next see what happens with a bitmap of all zeros. The following table gives some examples of 8-bit bitmaps and their corresponding values when interpreted as binary and integer datatypes. You can invent similar examples for 16-bit and 32-bit binary arithmetic.

BitmapBinaryInteger

0000 00000x00Zero

0111 11110x7F+127 (The maximum 8-bit integer value)

1111 11110xFF-1

0000 00010x01+1

1000 00000x80-128 (The minimum 8-bit integer value)

Download sample code for this article here

Paul Munkenbeck has been a DBA for over 10 years. He has worked on all types of databases, from desktop to multi-gigabyte mainframe. He was first exposed to Microsoft SQL Server as a database consultant on a project early in 1995. He loved it so much that he is now DBA for a leading UK real estate company that uses SQL Server as its strategic operational  database. MUNKY@compuserve.com, 101502.2537@compuserve.com