OLE DB/ODBC, SNAC, and the new “MAX” data types

After having been immersed in .NET since the alpha version, I'd lost touch with how many products use OLE DB and ODBC. After all, when you talk about SQL Server and SQL 92/99/2003 standards, the standard API for a SQL database *is* still SQL CLI (call-language interface). And the canonical implementation of SQL-CLI is ODBC. When I spoke recently about SQL Server 2005 Web Services as a way to acheive interop between unlike client architectures for SQL Server, someone reminded me that ODBC drivers for "unlike architectures" have existed for years. Uh, yes, of…course.

Parts of SQL Server 2005 like linked servers, replication, reporting services, and DTS (renamed SQL Server Integration Services (SSIS) last week) use OLE DB and ODBC as well. Although SSIS supports ADO.NET too.

SQL Server 2005 ships with a new communication layer known as SNI and a new OLE DB provider and new ODBC driver that use it. These are bundled together in a part of the product that runs on both server and client called SQL Native Client (or SNAC for short). Folks that have existing non-.NET applications are *really* interested in:

1. How the SNAC provider/driver supports the new data types
2. Any subtle differences between the new provider/driver and the current ones

Two weeks ago, someone asked about support of the new "MAX" data types VARCHAR(MAX), NVARCHAR(MAX), and VARBINARY(MAX) in both SQLOLEDB/SQL Server ODBC and SNAC OLE DB/ODBC. For example, does VARCHAR(MAX) resemble VARCHAR or TEXT? So I took out my handy-dandy OLE DB Rowset Viewer and had a look. I looked at column metadata (DBSCHEMA_COLUMNS) and the  DBCOLUMNINFO structure (after a SELECT) of the following table:

CREATE TABLE testclob (
  avar VARCHAR(8000),
  amax VARCHAR(MAX),
  atext TEXT)

Answer is: VARCHAR(MAX) looks almost exactly like a TEXT data type (as far as the API is concerned of course; they're way different as far as TSQL is concerned), both in SQLOLEDB and the SNAC OLE DB provider. And both providers yield almost the same metadata. The big caveat there is the "almost". Here's the two almosts:

1. Both VARCHAR(MAX) and TEXT have the same capabilities/properties with respect ISLONG and MAYDEFER (ie, supports deferred fetch). But, in the Schema Rowset the character_max_length and octet_length is 2147483647 (2gb) for TEXT and 0 (that zero) for VARCHAR(MAX). Be careful using this to size to allocate buffers.

2. SNAC's DBCOLUMNINFO listed (maximum) ColumnSize as 4294967295 (4 gb?) rather than 2 gb as SQLOLEDB provider did.

I'd thought that SNAC's behavior was going to be identical to SQLOLEDB to ease migration, and it appears as though, except for the one metadata anomaly this is true. And I didn't really expect SNAC and SQLOLEDB to be exactly the same regarding *new* features; after all, that's the point of using the SNAC provider/driver, new feature support. There may be a few other subtle differences, more on this later.

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.