What are the binary storage formats for sqflt8, sqlmoney and other native SQL data types?

Question

According to the documentation, native (binary) data can be imported or exported with bcp formatted in the native SQL Server data formats. Examples of these are SQLFLT8, SQLFLT4, SQLMONEY or SQLNUMERIC.

Does anyone know either what the data formats for the various types are, or where documentation specifying these formats might be found. For example, is a SQLFLT8 stored as an IEEE double precision number or in some other format?

Edit: From the answers by kevchadders and Andrew I had a little epiphany did a little bit of googling for #define and typedef to see if I could find C header files with definitions. This came up with a file odbcdss.h; the answer I've posted below has some out-takes from the file, which looks quite promising.

Answer 1

Some further googling for #define and typedef in conjunction with the data types turned up this header file (odbcss.h) linked here.. The first line has #Defines for magic constants that correspond directly to the names of the SQL data types. The lower snippet has some typefs and struct definitions for sensible looking data formats for the types.

It looks like these could be the relevant format definitions.

The relevant snippets are:

// SQL Server Data Type Tokens. Returned by SQLColAttributes/SQL_CA_SS_COLUMN_SSTYPE.
#define SQLTEXT             0x23
#define SQLVARBINARY        0x25
#define SQLINTN             0x26
#define SQLVARCHAR          0x27
#define SQLBINARY           0x2d
#define SQLIMAGE            0x22
#define SQLCHARACTER        0x2f
#define SQLINT1             0x30
#define SQLBIT              0x32
#define SQLINT2             0x34
#define SQLINT4             0x38
#define SQLMONEY            0x3c
#define SQLDATETIME         0x3d
#define SQLFLT8             0x3e
#define SQLFLTN             0x6d
#define SQLMONEYN           0x6e
#define SQLDATETIMN         0x6f
#define SQLFLT4             0x3b
#define SQLMONEY4           0x7a
#define SQLDATETIM4         0x3a
#define SQLDECIMAL          0x37
#define SQLDECIMALN         0x6a
#define SQLNUMERIC          0x3f
#define SQLNUMERICN         0x6c

[ . . . ]

typedef char            DBCHAR;
typedef unsigned char   DBBINARY;
typedef unsigned char   DBTINYINT;
typedef short           DBSMALLINT;
typedef unsigned short  DBUSMALLINT;
typedef long            DBINT;
typedef double          DBFLT8;
typedef unsigned char   DBBIT;
typedef unsigned char   DBBOOL;
typedef float           DBFLT4;

typedef DBFLT4 DBREAL;
typedef UINT   DBUBOOL;

typedef struct dbvarychar
{
    DBSMALLINT  len;
    DBCHAR      str[DBMAXCHAR];
} DBVARYCHAR;

typedef struct dbvarybin
{
    DBSMALLINT  len;
    BYTE        array[DBMAXCHAR];
} DBVARYBIN;

typedef struct dbmoney
{               // Internal representation of MONEY data type
    LONG  mnyhigh;      // Money value *10,000 (High 32 bits/signed)
    ULONG mnylow;       // Money value *10,000 (Low 32 bits/unsigned)
} DBMONEY;

typedef struct dbdatetime
{               // Internal representation of DATETIME data type
    LONG  dtdays;       // No of days since Jan-1-1900 (maybe negative)
    ULONG dttime;       // No. of 300 hundredths of a second since midnight
} DBDATETIME;

typedef struct dbdatetime4
{           // Internal representation of SMALLDATETIME data type
    USHORT numdays;     // No of days since Jan-1-1900
    USHORT nummins;     // No. of minutes since midnight
} DBDATETIM4;

typedef LONG DBMONEY4;  // Internal representation of SMALLMONEY data type
                        // Money value *10,000

#define DBNUM_PREC_TYPE BYTE
#define DBNUM_SCALE_TYPE BYTE
#define DBNUM_VAL_TYPE BYTE
typedef const LPBYTE    LPCBYTE;
typedef DBINT *         LPDBINT;

#if (ODBCVER < 0x0300)
#define MAXNUMERICLEN 16

typedef struct dbnumeric
{                 // Internal representation of NUMERIC data type
    DBNUM_PREC_TYPE   precision; // Precision
    DBNUM_SCALE_TYPE  scale;     // Scale
    BYTE     sign;           // Sign (1 if positive, 0 if negative)
    DBNUM_VAL_TYPE    val[MAXNUMERICLEN];   // Value
} DBNUMERIC;
typedef DBNUMERIC DBDECIMAL;// Internal representation of DECIMAL data type
#else   //  Use ODBC 3.0 definitions since same as DBLib
#define MAXNUMERICLEN SQL_MAX_NUMERIC_LEN
typedef SQL_NUMERIC_STRUCT DBNUMERIC;
typedef SQL_NUMERIC_STRUCT DBDECIMAL;
#endif

#endif //   MAXNUMERICLEN

Answer 2

I'm not sure if the theory will hold, but finding out the internal storage of the types can be achieved using some SQL and a bit of figuring out. I did this for the new datetime2 / datetimeoffset on my blog to speifically get the internal binary format as I was interested to see how they got the additional accuracy.

As an example for Money

declare @test money
set @test = 12.34
select @test -- shows 12.34 as expected

declare @binaryValue binary(8)
set @binaryvalue = convert(binary(8),@test)
select @binaryvalue 

Output : 0x000000000001E208

That is 123400 when considered as a decimal number, money is stored to 4 decimal places so that would indicate 12.3400 as the value, reversing this in theory a value of just 1 in hex should be 0.0001

declare @test money
declare @binaryValue binary(8)
set @binaryvalue = 0x0000000000000001
set @test = convert(money,@binaryvalue)
select @test

Outputs 0.0001

The next thing I would then check is the negative numbers,

declare @test money
set @test = -12.34
select @test -- shows -12.34 as expected

declare @binaryValue binary(8)
set @binaryvalue = convert(binary(8),@test)
select @binaryvalue 

Output : 0xFFFFFFFFFFFE1DF8

So that looks like it is a signed 8 byte number, since it has just take the number away from FF...etc. A quick check with -0.0001 gives out all 0xFFF....FFF as expected and -0.0002 gives 0xFF....FFE as expected.

Whether this holds for BCP I am not sure, but as an internal storage format I would take a guess at a signed 8 byte integer that has an assumed 4 decimal places.

Answer 3

Good Question.

Doesn't seem much on the web about this but i found this Native File Storage Types (Second table down) which shows each native file storage type and what it is recorded in the corresponding host file data type.

e.g. float = SQLFLT8
real = SQLFLT4
money = SQLMONEY
numeric = SQLNUMERIC

Apologies if you have already come across this list.