SQL Server Magazine

The Mother of All Lookup Tables
If you're interested in more examples of bit arithmetic in system stored procedures, look at procedures that reference the table called spt_values. Open the procsyst.sql file, which contains the code for all the system procedures, and search for this table. Spt_values isn't really a system table because it has few of the properties of system tables that I mentioned in "Beyond Point-and-Click Administration." The table is, however, part of the SQL Server product, and it does have the property IsMSShipped.

The spt_values table is really just a big lookup table. Much of the information in the system tables is stored as encoded values, either alone in a column or as a bit in an integer field, as it is in the sysobjects table's status column. Many system stored procedures that report properties of an object or of the whole server look at the values of individual bits, then translate the values into meaningful English values. In many cases, SQL Server performs this translation by joining the system table that you're examining with the spt_values table.

For example, the procedure sp_helpindex reports properties that belong to each index. The sysindexes table's status column stores the properties. By checking sysindexes.status to determine whether a particular bit is on, then looking in spt_values for the name that corresponds to that property, sp_helpindex can give you meaningful information about your indexes. For example, if the second bit in sysindexes.status is 1, the index is unique.

In spt_values, the column called type specifies the type of information represented. No documentation is available to explain the types, but most type values are intuitive—you can figure them out by examining the table. For example, type C denotes a configuration value, type L denotes the name for a locking mode, type DBR denotes permission for a database role, and type I denotes index properties. The value in the name column of a type-I row specifies the index property. The sp_helpindex procedure saves in a collection of variables all the name values for the rows that have type I; I believe the procedure does this just to avoid having to access the spt_values table repeatedly. The variable called @des4 stores the bit that's in the third position because when the third bit is 1, the status column has a value of 2², or 4. A bit in the first position gives the status column a value of 2⁰, or 1. The variable @des64 stores a bit in the seventh position and gives status a value of 2⁶, or 64. When you look at the row in spt_values with the type I and the number 64, you'll see that the row has the name value statistics, which means an index row that has a 1 for this bit is really just statistics, not a true index.

Listing 1, page 7, shows a lightly edited code snippet from sp_helpindex. One feature of sp_helpindex is that it copies rows from the requested sysindexes table into a temporary table called #spindtab, and the status column in sysindexes becomes the stats column in the temporary table.

The relevant sections of Listing 1 are the parts that perform bit arithmetic. If the bit in the second position of the stats column is 1, the index is unique and the variable @des2 contains the string unique, which the sp_helpindex procedure will then concatenate to the list of the index properties. If the bit in the second position was the only bit equal to 1, you could just check to see whether stats = 2, but this check is insufficient because multiple bits might be set to 1. To determine whether the second position bit is 1, you have to apply the bitwise AND operator between the stats value and 2. When you use a bitwise AND between a collection of bits and 2 (which is all 0s except for a 1 in the second bit), all the bits that aren't in the second position will result in 0 because 0 AND anything is 0. But in the second position, the bit in the stats column will keep its original value. If the bit in the stats column was 1 to begin with, stats & 2 won't equal 0, and the sp_helpindex procedure will concatenate the value unique to the index properties. If the value in the second position was 0, stats & 2 will result in 0, and the description of the index won't contain the string unique. To see what all the other index descriptions are—for example, the ones stored in @des2096 and @des4096—you can inspect the spt_values table.

Another procedure that makes heavy use of the information in spt_values is the sp_lock procedure. If you really want to understand how to use spt_values with the system tables, I recommend that you study sp_lock in detail.

Many SQL Server professionals never need to inspect individual bits in a status field or do any bit arithmetic. Microsoft has supplied numerous system procedures and property functions that give you almost all the information you could want about objects in your databases. But if you want to understand how SQL Server really keeps track of your data, there's no better place to start than the code in the system stored procedures. And because much of this code involves bit manipulation, you need to get comfortable working with bits. My goal in this column wasn't to provide a complete lesson in bit arithmetic, but to show you why you might want to understand these kinds of operations. Keep reading T-SQL Solutions for more examples of the power of bit arithmetic.

End of Article

Prev. page     1 [2]     next page -->