In "Roll Your Own System Stored Procedures," November 2001, I told you about the procedure sp_MS_marksystemobject, which let me create in the master database a stored procedure that would access the sysfiles system table in the current database. You can usually access system tables in the current database without running sp_MS_marksystemobject—as long as you want to access system tables other than sysfiles. But for some reason, sysfiles is a special case. To write a stored procedure that can access sysfiles in any database, you must run sp_MS_marksystemobject with your procedure name as a parameter. Because sp_MS_marksystemobject affects individual bits in the status field of the sysobjects table, you'll understand the procedure better if you have a basic grasp of bit arithmetic.
What Does sp_MS_marksystemobject Do?
In "Beyond Point-and-Click Administration," October 2001, I mention that you can access the text of all system procedures—either by using the sp_helptext system stored procedure or by looking at the code in the procsyst.sql file, which the SQL Server installation process puts in the \install subdirectory.
After performing a lot of error checking, this procedure updates the status column in the sysobjects table simply by changing a couple of bits:
UPDATE sysobjects SET status = status | 0xC0000000 WHERE id = object_id(@objname, 'local')
The object name is the parameter that's passed to the sp_MS_marksystemobject procedure (@objname), and the UPDATE statement uses the object_id() function to map that object name to an object ID. The statement above updates the status column for the row in sysobjects that has the given object ID. The status column in the sysobjects table isn't documented—SQL Server Books Online (BOL) says only that this column is "Reserved. For internal use only." SQL Server's system procedures and internal routines treat the status column as a collection of bits, and typically each bit denotes whether a particular property belongs to the object. Because the status column isn't documented, you won't find a list of what each bit means. But by examining the system procedures, you can discover what some of the bits are for.
To work with individual bits, you need to understand the basics of bit arithmetic. I won't give you a complete lesson about bit arithmetic because several articles planned for future issues of T-SQL Solutions will cover the topic in detail. For a basic explanation of bit arithmetic, see Itzik Ben-Gan's SQL Server Magazine T-SQL Black Belt column "Auxiliary Tables, Bit by Bit," http://www .sqlmag.com, InstantDoc ID 21079. But for now, you need to know how the AND (&) and OR (|) operators work on individual pairs of bits. AND returns a value of true if both bits are 1 and returns a false value otherwise. OR returns true if either bit is 1 and returns false only if both bits are 0. The bitwise AND operator tells SQL Server to apply a bitwise AND to each corresponding pair of bits in the two operands. The bitwise OR operator means that SQL Server will apply a bitwise OR to each corresponding pair of bits in the two operands.
The UPDATE statement in the sp_MS_marksystemobject procedure updates the status column by using the bitwise OR operator; the two operands of the OR are the current value of the status column and the hexadecimal value C0000000. If you convert that hex value to binary, it becomes 11000000000000000000000000000000. Ones are in the 31st and 32nd bit positions, and all the other bit positions contain zeros. The OR operator means that the bits in the 31st and 32nd positions in the status column will be 1 no matter what value they had before the update, and all the other bits remain unchanged. Using the OR operator when one of the operands is 1 always results in a 1; using the OR operator when one of the operands is 0 results in the value of the other operand. In other words, if you execute VALUE OR 1, the result is 1; if you execute VALUE OR 0, the result is VALUE. Another way of looking at this UPDATE operation is that you're "turning on" the 31st and 32nd bits by running the sp_MS_marksystemobject procedure. Therefore, you can conclude that those bits must indicate that the procedure has been marked as the type System.
The distinction between System and User objects other than system tables isn't really a concern when you're working with T-SQL code. The distinction comes into play only when you list objects in Enterprise Manager. The code that Enterprise Manager runs examines each object's IsMSShipped object property value, and if the value of IsMSShipped is 1, Enterprise Manager lists the object as a system object. Using the UPDATE statement in the earlier example to set the bits in sysobjects will give you the object property value of IsMSShipped. However, because Microsoft strongly recommends that you avoid directly updating the system tables, if you have a procedure that will do the same thing, you should use the procedure. So, use the sp_MS_marksystemobject procedure instead of issuing an UPDATE statement for the sysobjects table.
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 22, or 4. A bit in the first position gives the status column a value of 20, or 1. The variable @des64 stores a bit in the seventh position and gives status a value of 26, 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 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.
