INF: Using dbdata() Versus dbbind()

• Microsoft SQL Server Programmer's Toolkit, version 4.2

SUMMARY

This article explains the relative tradeoffs between dbdata() and dbbind() when processing SQL Server data in an application program.

MORE INFORMATION

If you use dbdata() instead of dbbind(), you must handle data conversion yourself. All data will be in native SQL Server format. Variable-length character data will not have a trailing NULL. Money will be 8-byte scaled binary (1 = .01 cent). Datetime will be a pair of 4-byte binary integers, the first of which is the days since January 1, 1900 and the other of which is 1/300 of a second since midnight. If you use the provided data-conversion routines, you will be doing much the same thing that dbbind() does. For very large amounts of data, there will be an advantage to using dbdata() if the data is used in place. If it is copied somewhere else, that advantage is lost.

If the data will remain in native format while being processed in the program (that is, fixed-length character and binary data), there is a performance disadvantage with dbbind() because it will spend some time figuring out that it has nothing to do.

The biggest performance disadvantage with dbbind() is due to its biggest advantage: dynamic handling of data type and length at execution time. Note that you don't tell dbbind() the format or length of the data in the database. It gets that at execution time. What you do tell it is the format you want the data to be in for your program. If the actual data format differs from what you specified, dbbind() will convert it.

This provides insulation from changes in the definition of data in the database. If you use dbdata() and the format of the data in the database changes (such as ZIPCODE increasing from five to nine digits), your program will probably give incorrect results (if it runs at all); however, with dbbind(), the data will be converted and clipped automatically, and the program will run correctly without even being recompiled.

For highest performance, use dbdata(). For data independence and insulation from changes in database format, use dbbind().