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

Question

According to the documentation, proprietary (binary) data can be imported or exported using bcp formatted in SQL Server proprietary data formats. Examples include SQLFLT8, SQLFLT4, SQLMONEY, or SQLNUMERIC.

Does anyone know what data formats are for different types, or where the documentation defining these formats can be found. For example, is SQLFLT8 stored as an IEEE double-precision number or in some other format?

Edit: From the answers of kevchadders and Andrew I had a little insight worked a bit with searching on #define and typedef to find out if I can find C header files with definitions. This caused the odbcdss.h file; The answer I posted below contains some excerpts from a file that looks pretty promising.

Answer 1

I'm not sure if the theory will be preserved, but sorting out the internal type memory can be achieved using some SQL and a little bit of clarification. I did this for the new datetime2 / datetimeoffset on my blog to get the internal binary format, as I was interested to know how they got the extra precision.

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

This is 123400, if you consider it as a decimal number, the money is stored up to 4 decimal places, so it will indicate 12.3400 as the value, otherwise this value is 1 in the 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 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 it looks like this is a signed 8-byte number, as it just picks the number from FF ... etc. A quick check with -0.0001 yields all 0xFFF .... FFF as expected, and -0.0002 yields 0xFF .... FFE as expected.

I am sure this is for BCP. I'm not sure, but as an internal storage format, I would prefer an 8-byte signed integer having an estimated 4 decimal places.

Answer 2

Some further searches for #define and typedef in combination with data types display this header file ( odbcss.h ), linked here. . The first line has #Defines for magic constants that directly correspond to SQL data type names. The lower fragment has some type types and structural definitions for reasonably looking data formats for types.

It looks like these might be appropriate format definitions.

Relevant fragments:

 // 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 3

Good question.

Not much on the Internet about this, but I found this Native File Storage Types (second table down), which shows each native type of file storage and that it is recorded in the corresponding host file data type.

eg. float = SQLFLT8
real = SQLFLT4
money = SQLMONEY
numeric = SQLNUMERIC

Sorry if you’ve already come across this list.