Managing Extended Properties

Since the creation of the first 3 * 5 card database, developers have been yearning for the ability to more fully define, outline, and clarify their database tables, columns, and other structures—and to do so in a way that can help front-end (and middle-tier) applications more easily adapt to the data. These extended object properties can define business rules, formatting criteria, and anything else you need them to. They can also be helpful in documenting the database. For example, you can assign and manage creator, editor, administrator, or other role names as extended properties. In addition, you can track changes to the objects based on date, site, or any other attribute.

On index cards, extended properties are easy to implement—they're simply the extra notes you write alongside Aunt Martha's pumpkin pie recipe or inside the recipe box. However, in the world of highly structured relational databases, this extended out-of-the-box information is harder to store because it doesn't fit within the predefined schema reserved for data rows with fixed data type columns. And until SQL Server 2000, you didn't have an easy way to manage this information or a standard way to access it. SQL Server 2000 implements a mechanism within each database to define, modify, and retrieve any and all extended properties you define. So, you can now write an application that automatically adjusts itself as field captions, masks, limits, defaults, or tool tips change. And you don't need to recompile and redeploy applications simply because a business rule changes or the application is now being run in France instead of China. Let's look at how to set up and manage SQL Server 2000 extended properties and examine how properly designed applications can take full advantage of this new feature.

The Challenges

Consider how you store and annotate your data—especially data that doesn't fit into neat buckets (e.g., when data annotations could be a phrase, URL, number, or mask). Suppose you don't know ahead of time what type of data you'll need to store in a database table. You might define a table row that has a column for each data type you expect to encounter. This approach has several drawbacks that make it tough to implement and tougher to justify to your boss and the systems administrator (sa). For example, suppose someone decides to store data that you didn't expect during the design. Also, what do you do about the wasted space? In SQL Server 7.0 and earlier, you can define columns as only one basic data type or another. For example, you can specify that a column should contain an integer or that it should contain a string, but you can't specify that it can be of either type.

Sometimes, you need to add additional notes—or properties—to the database schema. These properties further clarify or define database objects such as tables, columns, or stored procedure parameters. These properties are really just business rules, which can change from time to time, as most properties do. So, you need a way to add new properties and change, delete, and retrieve properties as necessary. These additional properties could store various types of information. For example, you might use MaxValue to store the maximum or minimum acceptable value for the column, Mask to tell the front end how to manage the numbers and characters in a formatted string, or Caption to describe the column to a user. The list of possible properties is nearly endless.

Another challenge is figuring out how to build an adaptable front-end application or middle-tier component to deal with constantly evolving business rules. For example, suppose your application needs to ensure that the upper limit on shoe sales to customer type 6 is 200. If you validate the input number at data-entry time, your code doesn't have to wait until the next update operation violates a server-side rule to discover when something is amiss. By using an extended property to retrieve the upper and lower limits for an input value or a set of acceptable values, you can eliminate hard-coding these values in your application. If you hard-code the value range limit in the application, you'll have a hard time changing the limit when the business rule changes—you could end up recoding, recompiling, and redeploying the application, a time-consuming and expensive task.

Some Solutions

The sql_variant data type and extended properties—two new SQL Server 2000 features—let you address these problems nicely. Let's explore these new features in detail so that you can create your own "sparse" table and create, modify, and retrieve SQL Server extended properties.

Visual Basic (VB) developers will tell you that the Variant data type has changed the way they can address data-access problems. This data type isn't necessarily a good thing for VB developers: Variants are generally inefficient when misused. However, variants are handy when the developer doesn't know what type of data needs to be stored or passed from tier to tier. In situations in which the data your code is passing from tier to tier, or even in process, is unstructured, or "fuzzy," the Variant data type can be a good choice. In VB, the Variant data type lets developers specify a program variable that refers to nearly any type of data: a number, string, picture, or object. VB stores the data and additional data-description information to help VB and the programmer manage the information. (Note that in its new Visual Basic.NET Framework, Microsoft replaced the Variant data type with the Object data type.)

Now, suppose your application needs to store additional information for a given customer. You don't know what that information will look like, but you know it will be a number (an integer, float, or decimal), a string, a date, or perhaps a Boolean value. One approach to defining this extra, extended property information is to create a table that looks like Table 1.

This table defines 100 columns, each specifying an extended property. Each column has a different purpose and a use-appropriate data type. The ObjectID column contains a relational reference to a unique entity (e.g., a database object, a customer). If you wanted to find the Prop3 value for a particular customer, you could easily fetch the value by using a simple SELECT statement. However, this table is extremely wide because it contains mostly nulls, so it isn't efficient to manage. And what if you need to define more properties as your business rules evolve? What if more than one row defines a specific property or the combination of several properties exhausts SQL Server's row capacity? You might need to consider another approach.

Another Approach

Fortunately, SQL Server 2000 supports variants through the new sql_variant data type. Similar in many ways to the VB Variant data type, sql_variant can store information returned from any SQL Server­supported data types except text, ntext, image, timestamp, and sql_variant. Actually, the sql_variant type isn't a base data type but a way to tell SQL Server that the data type will be set when the data is stored in that column. So the data type for a particular column can be any of the supported sql_variant data types. Although the implementation isn't the same as that of the VB Variant data type, the sql_variant data type is flexible enough to solve the changing-rule problem. Because sql_variant morphs itself into almost any SQL Server data type, SQL Server sets the column's "real" data type based on the data it writes to the column. For example, a sql_variant data type "becomes" an integer when SQL Server writes an integer value to the column. If you use sql_variant in an extended property table, the customer data properties now look slightly different, as Table 2 shows.

In this case, you can define the table with three columns: one to identify the object, another to name the property being managed, and a third to hold the data for that property. This table is an example of a sparse table, which lets you define as many properties as the current business rules dictate. The sql_variant Info column can hold any non-BLOB data type, so you can easily store almost any type of information, even if during the design phase, you didn't anticipate needing to handle that type of data.

Managing Object Properties

After you write a front-end application that can adapt to an ever-changing set of properties, you need to manage the objects that make up the database. Creating a database table, adding columns to the table, and defining rules for the table are relatively easy. However, making sure the front-end application that captures data for those columns properly validates the data can be a challenge. To address this and other problems, SQL Server 2000 introduced extended properties. SQL Server 2000 Books Online (BOL) clearly describes how you can create, modify, or delete extended properties. (See the section "Using Extended Properties on Database Objects.") However, BOL gives little guidance beyond a few suggestions about how to use these properties.

You can use system stored procedures or Query Analyzer to define and store extended properties in your database. You save extended properties in your database in a hidden sparse table as sql_variant values. After you define your extended properties, you can use the SQL Server function FN_LISTEXTENDEDPROPERTY() to retrieve them, as I describe later.

After you define the extended properties, you'll want your application to include a query or two to retrieve these values and modify the client-side logic or control properties to reflect the object properties stored in the database. For example, you could set the MaskedEdit control's Mask property to reflect the format of a date or phone number. You'll probably want your application to retrieve these values once on startup and again if these properties change. SQL Server won't notify you when extended properties change. But you could create an extended property called timechanged for the database, a table, or a specific object and specify that the extended property be set when another property changes. You could then have your application periodically check the timechanged property. I discuss the techniques for fetching and managing extended properties later in this article.

You can assign extended properties to nearly any SQL Server object, including databases, tables, columns, views, rules, triggers, stored procedure parameters, constraints, and indexes. To add, update, and delete extended properties, you use the following three system stored procedures:

After you store the extended properties in the database, your application can use the system function FN_LISTEXTENDEDPROPERTY() to retrieve the values by name.

The sp_addextendedproperty stored procedure accepts eight parameters. Below is a typical example of how to use this procedure; it sets a single extended property named caption with a value of Employee ID on table T1, column id, owned by user dbo.

EXEC sp_addextendedproperty 'caption', 'Employee ID', 'user', dbo, 'table', T1, 'column', id

Table 3 shows the valid parameters for extended-property stored procedures. The first two sp_addextendedproperty parameters name the extended property and the value—the three columns of Table 3 define how to address the object to contain the extended property. In the previous example, the attribute name is caption and the attribute value is Employee ID. Because the Value argument is a sql_variant data type, you can pass anything except a timestamp, text, or image type. The parameters that you subsequently pass to the sp_addextendedproperty stored procedure are separated into levels, each providing additional granular addressing to the object attribute being defined. Level 0 is either "User" or a user-defined data type, so in most cases, you'll simply provide the string "User" as your third parameter and use "dbo" or the appropriate object owner (with permission to set or change a property) as the fourth parameter. The six remaining sp_addextendedproperty parameters point to the object. For example, to reference a specific SQL Server table object, you need to pass two levels—the user name and table name—as parameters to sp_addextendedproperty. You can leave off the remaining Level 2 parameters. For example, to set two extended properties on the Publishers table, you could code

EXEC sp_addextendedproperty 'Contents', 'Sample publisher info', 'user', 'dbo', 'table', 'Publishers' GOEXEC sp_addextendedproperty 'DateCreated', 'March 3, 1999', 'user', 'dbo', 'table', 'Publishers'

Note that the sp_addextendedproperty stored procedure applies to the current database. To add extended properties to a specific table column, you must pass the user, table, and column name. The code in Listing 1 illustrates how to add extended properties to the Address column of the Publishers table in the Pubs database. Notice how the fifth sp_addextendedproperty parameter names the Level 1 object type (e.g., 'table', 'view', 'rule') and the sixth Level 2 parameter names the object itself.

These stored procedures can be intimidating because they have so many parameters, but once you understand how the parameters address individual objects or groups of objects, they're simple to use. BOL provides a table that describes the values you can set the stored procedure parameters to and what combinations are valid. To find this table, search for sp_addextendedproperty.

An Easier Way

Although you can use system stored procedures to manage extended properties, using Query Analyzer is much easier. You can also use Query Analyzer to document these extended properties so that they're easier to manage and edit. When you point to specific objects in the Query Analyzer treeview and right-click, any object that supports extended properties will enable an Extended properties menu item, as Figure 1 shows.

Clicking Extended properties opens a new dialog box that helps you manage the extended properties for the selected object. This dialog box, which Figure 2 shows, is intuitive and easy to use, but beware: It lets you add more than one property with the same name. Because the entries you make aren't committed to the database until you click Apply or OK, duplicate entries remain undetected until the dialog box attempts to update the database. Note that the Value field accepts Unicode strings, so you can easily paste or type in the special characters that other languages use. You use this dialog box to add extended properties to any object that supports an extended attribute.

You'll want to retrieve these predefined extended properties and use the values to morph your application's client-side business rules or simply change the captions for the data columns your application or component is managing. You use the system-level function FN_LISTEXTENDEDPROPERTY() to fetch specific extended properties from individual objects or all of the extended properties associated with an object. The function's parameters follow the same hierarchical pattern as the stored procedures used to manage extended properties. So, to return a specifically named extended property, you pass all three levels of parameters to the function. If you pass null or default instead of one or more levels, the function returns all extended properties under the specified level. For example, the following code returns all defined extended properties for the Publishers table:

SELECT   objType, objName, Type, ValueFROM   ::fn_listextendedproperty (null, 'user', 'dbo', 'table','publishers', null, null)

The following three examples show how to return the extended properties at various granular levels. Note that when you pass the first argument to FN_LISTEXTENDEDPROPERTY() as null, the query returns all recorded extended attributes for the selected object. When you provide the extended attribute name, the query returns only the value of that specific attribute.

The first of these examples returns all the current database object's extended properties, but none of the extended properties defined for the database's subobjects, such as data types, tables, and columns:

SELECT   *FROM   ::fn_listextendedproperty(null, null, null, null, null, null, null)

The following example returns a single row—the extended property named Contents from the Publishers table:

SELECT   *FROM   ::fn_listextendedproperty ('Contents', 'user', 'dbo', 'table','publishers', null, null) 

The following example returns a specific extended property by name (EnglishCaption) from the Publishers table column properties:

SELECT   *FROM   ::fn_listextendedproperty ('EnglishCaption', 'user', 'dbo', 'table','publishers', 'column', null)

Here's an even easier way to use extended properties. After you've used Query Analyzer to define an extended property set, you can simply ask Query Analyzer to script the extended properties for you. This approach can save you the trouble of coding and debugging these stored procedures as well as provide an easy way to learn how to code them.

What's in a Name?

Another, more complex challenge faces developers who use extended properties: No standard way exists to define these name-value pairs.

Extended property names aren't case-sensitive unless the database is set up to be case-sensitive. So, your company needs to standardize a naming convention and rules that dictate who defines extended properties as well as how and where to define them. Other developers and administrators who use and reference these objects hard-code the names you give to these properties and their arguments. Therefore, you can't let these names change after the object is published and developers have begun to write code against it. Making such changes is easy, so your team's discipline and training must make sure that no one rearranges, reorganizes, or renames the properties or changes their data types. Although you can add extended attributes to existing objects, you must remember to maintain backward compatibility with the earlier version of your database.

You can find more information about extended properties and the sql_variant data type on the Microsoft Developer Network (MSDN) Web site (http://msdn.microsoft.com) and in SQL Server 2000 BOL. These new SQL Server 2000 technologies can have a significant impact on your applications' ability to manage business rules, deal with data-driven displays and validation rules, manage data object documentation, and much more. The possibilities are limited only by your imagination.