A Bit About Bits

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.