SQL Server Data Types and Their .NET Framework Equivalents

SQL Server Data Types and Their .NE...

Community Content

In this section

Statistics Annotations (3)

Click here to expand

Collapse All

Language Filter

SQL Server 2005 Books Online (November 2008)

SQL Server Data Types and Their .NET Framework Equivalents

Updated: 5 December 2005

The following table lists Microsoft SQL Server data types, their equivalents in the common language runtime (CLR) for SQL Server in the System.Data.SqlTypes namespace, and their native CLR equivalents in the Microsoft .NET Framework.

SQL Server data type	CLR data type (SQL Server)	CLR data type (.NET Framework)
varbinary	SqlBytes, SqlBinary	Byte[]
binary	SqlBytes, SqlBinary	Byte[]
varbinary(1), binary(1)	SqlBytes, SqlBinary	byte, Byte[]
image	None	None
varchar	None	None
char	None	None
nvarchar(1), nchar(1)	SqlChars, SqlString	Char, String, Char[]
nvarchar	SqlChars, SqlString SQLChars is a better match for data transfer and access, and SQLString is a better match for performing String operations.	String, Char[]
nchar	SqlChars, SqlString	String, Char[]
text	None	None
ntext	None	None
uniqueidentifier	SqlGuid	Guid
rowversion	None	Byte[]
bit	SqlBoolean	Boolean
tinyint	SqlByte	Byte
smallint	SqlInt16	Int16
int	SqlInt32	Int32
bigint	SqlInt64	Int64
smallmoney	SqlMoney	Decimal
money	SqlMoney	Decimal
numeric	SqlDecimal	Decimal
decimal	SqlDecimal	Decimal
real	SqlSingle	Single
float	SqlDouble	Double
smalldatetime	SqlDateTime	DateTime
datetime	SqlDateTime	DateTime
sql_variant	None	Object
User-defined type(UDT)	None	Same class that is bound to the user-defined type in the same assembly or a dependent assembly.
table	None	None
cursor	None	None
timestamp	None	None
xml	SqlXml	None

Automatic Data Type Conversion with Out Parameters

A CLR method can return information to the calling code or program by marking an input parameter with the out modifier (Microsoft Visual C#) or <Out()> ByRef (Microsoft Visual Basic) If the input parameter is a CLR data type in the System.Data.SqlTypes namespace, and the calling program specifies its equivalent SQL Server data type as the input parameter, a type conversion occurs automatically when the CLR method returns the data type.

For example, the following CLR stored procedure has an input parameter of SqlInt32 CLR data type that is marked with out (C#) or <Out()> ByRef (Visual Basic):

   [Microsoft.SqlServer.Server.SqlProcedure]
   public static void PriceSum(out SqlInt32 value)
   { … }

Visual Basic

    <Microsoft.SqlServer.Server.SqlProcedure> _

Public Shared Sub PriceSum( <Out()> ByRef value As SqlInt32)

…

End Sub

After the assembly is built and created in the database, the stored procedure is created in SQL Server with the following Transact-SQL, which specifies a SQL Server data type of int as an OUTPUT parameter:

CREATE PROCEDURE PriceSum (@sum int OUTPUT)
AS EXTERNAL NAME TestStoredProc.StoredProcedures.PriceSum

When the CLR stored procedure is called, the SqlInt32 data type is automatically converted to an int data type, and returned to the calling program.

Not all CLR data types can be automatically converted to their equivalent SQL Server data types through an out parameter, however. The following table lists these exceptions.

CLR data type (SQL Server)	SQL Server data type
Decimal	smallmoney
SqlMoney	smallmoney
Decimal	money
DateTime	smalldatetime
SQLDateTime	smalldatetime

Other Resources

SQL Server Data Types in the .NET Framework
Data Type Conversion (Database Engine)

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release	History
5 December 2005	New content: Added the Automatic Data Type Conversion with Out Parameters section.

Tags

: Add a tag

Community Content

Add new content

Annotations

So sad Nullable<Int32> is not implemented !

Vincent DEWISME | Edit | Show History

Thought the nullable possibility was written in the SQL stored procedure parameters genotype. It was then obvious to add a Nullable<T> mapping in the CLR stored procedure for each CLR types...

Nevermind, I use an extra boolean parameter to tell the stored proc if the value is null, waiting to migrate my DBs to SQL Server 2008.

Tags

: Add a tag

Flag as ContentBug

What about NULLABLE types?

Mike DePouw | Edit | Show History

This content leaves out a big piece of information which is, how do null values work on value types? Does a SqlInt allow nullability? It's a struct, so no then, right?

Tags

: Add a tag

Flag as ContentBug

This version is out-dated

LyalinDotCom ... Tai Yee - MSFT | Edit | Show History

This page was last updated in 2005 and is therefore outdated since a new version exists. It can be found at the following URL:

http://msdn2.microsoft.com/en-us/library/ms131092(SQL.100).aspx

(New version was last updated in 2007)

[Tai Yee - MSFT] Actually, this topic is correct as written for SQL Server 2005. The topic that you referred to is for the SQL Server 2008 product. SQL Server 2008 provides additional features and functionality over SQL Server 2005.

Tags

: Add a tag

Flag as ContentBug