INF: Differentiating Between Timestamp and Varbinary Types

• Microsoft SQL Server Programmer's Toolkit, version 4.2

SUMMARY

Using the DB-Library (DB-Lib) calls dbcoltype() and dbprtype(), you cannot differentiate between the varbinary and timestamp column types. Dbcoltype() returns 45 for both types, and dbprtype() translates these both as being "binary."

There are several methods you can use to differentiate between these two column types, but they each have problems associated with them.

You can use the dbcolname() call to determine if the name of the column in question is "timestamp"; however, although this seems like a logical solution, there is a potential problem. It has been verified that browse mode can be used to update a table with a timestamp column that was not named "timestamp"; therefore, the timestamp column may not always have this unique name.

A timestamp column is designated as a varbinary coltype. Only the usertype (which is basically unused by DB-Lib except for special columns such as timestamp) contains the timestamp data type value (80). Db-lib does not provide a mechanism for getting to this information as an exposed API. This information can be found in the SQL Server internal tables described in Appendix B of the "Microsoft SQL Server System Administrator's Guide."

MORE INFORMATION

Consider the situation where you want to determine whether column 10 of the table "alltypes" is actually type timestamp or varbinary. The only reliable way to make this differentiation is to execute a query such as the following:

      select usertype from syscolumns
      where colid = 10
        and
      id in
      (select id from sysobjects
       where name = 'alltypes')

Results

80 = timestamp

 4 = varbinary

Of the two workarounds given above, Microsoft recommends using the first choice for the following reasons:

1. It is easy to implement.

2. It is based on a DB-Lib call.

3. It does not rely on internal information that is subject to change.