Wednesday, December 29, 2010

Notes on GetSchemaTable

I recent found myself using the SqlDataReader.GetSchemaTable method. I made some notes detailing what it will return for various data types in SQL Server 2005.

Notes on GetSchemaTable
 Numeric 
ColumnNameDataTypeColumnSizePrecisionScaleNotes
bigintInt64819255 
bitBoolean1255255 
decimalDecimal17180 
intInt32410255 
moneyDecimal819255 
numericDecimal17180 
smallintInt1625255 
smallmoneyDecimal410255 
tinyintByte13255 
floatDouble815255 
realSingle47255 
datetimeDateTime8233 
smalldatetimeDateTime4160 
charString20255255 
varcharString20255255 
textString2147483647255255isLong
ncharString20255255 
nvarcharString20255255 
varchar(max)String2147483647255255isLong
nvarchar(max)String2147483647255255isLong
ntextString1073741823255255isLong
timestampByte[]8255255IsRowVersion
xmlString2147483647255255isLong

I created these notes through the simple method of creating a table with every possible data type. Or rather, every data type that I am interested in, which is very nearly the same thing--I mean, who uses varbinary, really? I then used the GetSchemaTable method on my new table, and inspected the results.

There is a page on MSDN that appears to contain this information, but upon closer inspection, it must be talking about something else entirely. For instance, it claims that the SQL Server data type "varchar" has no mapping whatsoever, not even as a System.String.

Certainly, this makes sense upon further reflection: it's possible to put any varchar value into a string, but not the other way around. Still, it's not terribly useful if you just want to know what sort of data type you can reasonably expect to get out of a given column.

I further note that the MSDN documentation states that the NumericPrecision column should be null for non-numeric data types, but this is simply not true.

No comments:

Post a Comment