SQL Server Data Types and Their .NET Framework Equivalents

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):

C#

   [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

See Also

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.