| title | ms.custom | ms.date | ms.prod | ms.reviewer | ms.suite | ms.technology | ms.tgt_pltfrm | ms.topic | ms.assetid | caps.latest.revision | author | ms.author | manager | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
SQL Server Data Type Mappings |
03/30/2017 |
.net-framework |
|
article |
fafdc31a-f435-4cd3-883f-1dfadd971277 |
8 |
JennieHubbard |
jhubbard |
jhubbard |
SQL Server Data Type Mappings
SQL Server and the .NET Framework are based on different type systems. For example, the .NET Framework xref:System.Decimal structure has a maximum scale of 28, whereas the SQL Server decimal and numeric data types have a maximum scale of 38. To maintain data integrity when reading and writing data, the xref:System.Data.SqlClient.SqlDataReader exposes SQL Server–specific typed accessor methods that return objects of xref:System.Data.SqlTypes as well as accessor methods that return .NET Framework types. Both SQL Server types and .NET Framework types are also represented by enumerations in the xref:System.Data.DbType and xref:System.Data.SqlDbType classes, which you can use when specifying xref:System.Data.SqlClient.SqlParameter data types.
The following table shows the inferred [!INCLUDEdnprdnshort] type, the xref:System.Data.DbType and xref:System.Data.SqlDbType enumerations, and the accessor methods for the xref:System.Data.SqlClient.SqlDataReader.
| SQL Server Database Engine type | .NET Framework type | SqlDbType enumeration | SqlDataReader SqlTypes typed accessor | DbType enumeration | SqlDataReader DbType typed accessor |
|---|---|---|---|---|---|
| bigint | Int64 | xref:System.Data.SqlDbType.BigInt | xref:System.Data.SqlClient.SqlDataReader.GetSqlInt64%2A | xref:System.Data.DbType.Int64 | xref:System.Data.SqlClient.SqlDataReader.GetInt64%2A |
| binary | Byte[] | xref:System.Data.SqlDbType.VarBinary | xref:System.Data.SqlClient.SqlDataReader.GetSqlBinary%2A | xref:System.Data.DbType.Binary | xref:System.Data.SqlClient.SqlDataReader.GetBytes%2A |
| bit | Boolean | xref:System.Data.SqlDbType.Bit | xref:System.Data.SqlClient.SqlDataReader.GetSqlBoolean%2A | xref:System.Data.DbType.Boolean | xref:System.Data.SqlClient.SqlDataReader.GetBoolean%2A |
| char | String Char[] |
xref:System.Data.SqlDbType.Char | xref:System.Data.SqlClient.SqlDataReader.GetSqlString%2A | xref:System.Data.DbType.AnsiStringFixedLength, xref:System.Data.DbType.String |
xref:System.Data.SqlClient.SqlDataReader.GetString%2A xref:System.Data.SqlClient.SqlDataReader.GetChars%2A |
| date (SQL Server 2008 and later) |
DateTime | xref:System.Data.SqlDbType.Date | xref:System.Data.SqlClient.SqlDataReader.GetSqlDateTime%2A | xref:System.Data.DbType.Date | xref:System.Data.SqlClient.SqlDataReader.GetDateTime%2A |
| datetime | DateTime | xref:System.Data.SqlDbType.DateTime | xref:System.Data.SqlClient.SqlDataReader.GetSqlDateTime%2A | xref:System.Data.DbType.DateTime | xref:System.Data.SqlClient.SqlDataReader.GetDateTime%2A |
| datetime2 (SQL Server 2008 and later) |
DateTime | xref:System.Data.SqlDbType.DateTime2 | None | xref:System.Data.DbType.DateTime2 | xref:System.Data.SqlClient.SqlDataReader.GetDateTime%2A |
| datetimeoffset (SQL Server 2008 and later) |
DateTimeOffset | xref:System.Data.SqlDbType.DateTimeOffset | none | xref:System.Data.DbType.DateTimeOffset | xref:System.Data.SqlClient.SqlDataReader.GetDateTimeOffset%2A |
| decimal | Decimal | xref:System.Data.SqlDbType.Decimal | xref:System.Data.SqlClient.SqlDataReader.GetSqlDecimal%2A | xref:System.Data.DbType.Decimal | xref:System.Data.SqlClient.SqlDataReader.GetDecimal%2A |
| FILESTREAM attribute (varbinary(max)) | Byte[] | xref:System.Data.SqlDbType.VarBinary | xref:System.Data.SqlClient.SqlDataReader.GetSqlBytes%2A | xref:System.Data.DbType.Binary | xref:System.Data.SqlClient.SqlDataReader.GetBytes%2A |
| float | Double | xref:System.Data.SqlDbType.Float | xref:System.Data.SqlClient.SqlDataReader.GetSqlDouble%2A | xref:System.Data.DbType.Double | xref:System.Data.SqlClient.SqlDataReader.GetDouble%2A |
| image | Byte[] | xref:System.Data.SqlDbType.Binary | xref:System.Data.SqlClient.SqlDataReader.GetSqlBinary%2A | xref:System.Data.DbType.Binary | xref:System.Data.SqlClient.SqlDataReader.GetBytes%2A |
| int | Int32 | xref:System.Data.SqlDbType.Int | xref:System.Data.SqlClient.SqlDataReader.GetSqlInt32%2A | xref:System.Data.DbType.Int32 | xref:System.Data.SqlClient.SqlDataReader.GetInt32%2A |
| money | Decimal | xref:System.Data.SqlDbType.Money | xref:System.Data.SqlClient.SqlDataReader.GetSqlMoney%2A | xref:System.Data.DbType.Decimal | xref:System.Data.SqlClient.SqlDataReader.GetDecimal%2A |
| nchar | String Char[] |
xref:System.Data.SqlDbType.NChar | xref:System.Data.SqlClient.SqlDataReader.GetSqlString%2A | xref:System.Data.DbType.StringFixedLength | xref:System.Data.SqlClient.SqlDataReader.GetString%2A xref:System.Data.SqlClient.SqlDataReader.GetChars%2A |
| ntext | String Char[] |
xref:System.Data.SqlDbType.NText | xref:System.Data.SqlClient.SqlDataReader.GetSqlString%2A | xref:System.Data.DbType.String | xref:System.Data.SqlClient.SqlDataReader.GetString%2A xref:System.Data.SqlClient.SqlDataReader.GetChars%2A |
| numeric | Decimal | xref:System.Data.SqlDbType.Decimal | xref:System.Data.SqlClient.SqlDataReader.GetSqlDecimal%2A | xref:System.Data.DbType.Decimal | xref:System.Data.SqlClient.SqlDataReader.GetDecimal%2A |
| nvarchar | String Char[] |
xref:System.Data.SqlDbType.NVarChar | xref:System.Data.SqlClient.SqlDataReader.GetSqlString%2A | xref:System.Data.DbType.String | xref:System.Data.SqlClient.SqlDataReader.GetString%2A xref:System.Data.SqlClient.SqlDataReader.GetChars%2A |
| real | Single | xref:System.Data.SqlDbType.Real | xref:System.Data.SqlClient.SqlDataReader.GetSqlSingle%2A | xref:System.Data.DbType.Single | xref:System.Data.SqlClient.SqlDataReader.GetFloat%2A |
| rowversion | Byte[] | xref:System.Data.SqlDbType.Timestamp | xref:System.Data.SqlClient.SqlDataReader.GetSqlBinary%2A | xref:System.Data.DbType.Binary | xref:System.Data.SqlClient.SqlDataReader.GetBytes%2A |
| smalldatetime | DateTime | xref:System.Data.SqlDbType.DateTime | xref:System.Data.SqlClient.SqlDataReader.GetSqlDateTime%2A | xref:System.Data.DbType.DateTime | xref:System.Data.SqlClient.SqlDataReader.GetDateTime%2A |
| smallint | Int16 | xref:System.Data.SqlDbType.SmallInt | xref:System.Data.SqlClient.SqlDataReader.GetSqlInt16%2A | xref:System.Data.DbType.Int16 | xref:System.Data.SqlClient.SqlDataReader.GetInt16%2A |
| smallmoney | Decimal | xref:System.Data.SqlDbType.SmallMoney | xref:System.Data.SqlClient.SqlDataReader.GetSqlMoney%2A | xref:System.Data.DbType.Decimal | xref:System.Data.SqlClient.SqlDataReader.GetDecimal%2A |
| sql_variant | Object * | xref:System.Data.SqlDbType.Variant | xref:System.Data.SqlClient.SqlDataReader.GetSqlValue%2A * | xref:System.Data.DbType.Object | xref:System.Data.SqlClient.SqlDataReader.GetValue%2A * |
| text | String Char[] |
xref:System.Data.SqlDbType.Text | xref:System.Data.SqlClient.SqlDataReader.GetSqlString%2A | xref:System.Data.DbType.String | xref:System.Data.SqlClient.SqlDataReader.GetString%2A xref:System.Data.SqlClient.SqlDataReader.GetChars%2A |
| time (SQL Server 2008 and later) |
TimeSpan | xref:System.Data.SqlDbType.Time | none | xref:System.Data.DbType.Time | xref:System.Data.SqlClient.SqlDataReader.GetDateTime%2A |
| timestamp | Byte[] | xref:System.Data.SqlDbType.Timestamp | xref:System.Data.SqlClient.SqlDataReader.GetSqlBinary%2A | xref:System.Data.DbType.Binary | xref:System.Data.SqlClient.SqlDataReader.GetBytes%2A |
| tinyint | Byte | xref:System.Data.SqlDbType.TinyInt | xref:System.Data.SqlClient.SqlDataReader.GetSqlByte%2A | xref:System.Data.DbType.Byte | xref:System.Data.SqlClient.SqlDataReader.GetByte%2A |
| uniqueidentifier | Guid | xref:System.Data.SqlDbType.UniqueIdentifier | xref:System.Data.SqlClient.SqlDataReader.GetSqlGuid%2A | xref:System.Data.DbType.Guid | xref:System.Data.SqlClient.SqlDataReader.GetGuid%2A |
| varbinary | Byte[] | xref:System.Data.SqlDbType.VarBinary | xref:System.Data.SqlClient.SqlDataReader.GetSqlBinary%2A | xref:System.Data.DbType.Binary | xref:System.Data.SqlClient.SqlDataReader.GetBytes%2A |
| varchar | String Char[] |
xref:System.Data.SqlDbType.VarChar | xref:System.Data.SqlClient.SqlDataReader.GetSqlString%2A | xref:System.Data.DbType.AnsiString, xref:System.Data.DbType.String | xref:System.Data.SqlClient.SqlDataReader.GetString%2A xref:System.Data.SqlClient.SqlDataReader.GetChars%2A |
| xml | Xml | xref:System.Data.SqlDbType.Xml | xref:System.Data.SqlClient.SqlDataReader.GetSqlXml%2A | xref:System.Data.DbType.Xml | none |
* Use a specific typed accessor if you know the underlying type of the sql_variant.
[!INCLUDEssNoVersion] Books Online Reference
For more information about [!INCLUDEssNoVersion] data types, see Data Types (Database Engine).
See Also
SQL Server Data Types and ADO.NET
SQL Server Binary and Large-Value Data
Data Type Mappings in ADO.NET
Configuring Parameters and Parameter Data Types
ADO.NET Managed Providers and DataSet Developer Center