DataType in sql server and C#.net

Data Type of SQL Server

Exact numeric

Type	From	To
bigint	-9,223,372,036,854,775,808	9,223,372,036,854,775,807
int	-2,147,483,648	2,147,483,647
smallint	-32,768	32,767
tinyint	0	255
bit	0	1
decimal	-10^38 +1	10^38 –1
numeric	-10^38 +1	10^38 –1
money	-922,337,203,685,477.5808	+922,337,203,685,477.5807
smallmoney	-214,748.3648	+214,748.3647

numeric and decimal are Fixed precision and scale data types and are functionally equivalent.

Approximate numeric

Type	From	To
float	-1.79E + 308	1.79E + 308
real	-3.40E + 38	3.40E + 38

datetime and smalldatetime

Type	From	To
datetime (3.33 milliseconds accuracy)	Jan 1, 1753	Dec 31, 9999
smalldatetime (1 minute accuracy)	Jan 1, 1900	Jun 6, 2079

Character Strings

Type	Description
char	Fixed-length non-Unicode character data with a maximum length of 8,000 characters.
varchar	Variable-length non-Unicode data with a maximum of 8,000 characters.
varchar(max)	Variable-length non-Unicode data with a maximum length of 231 characters (SQL Server 2005 only).
text	Variable-length non-Unicode data with a maximum length of 2,147,483,647 characters.

Unicode Character Strings

Type	Description
nchar	Fixed-length Unicode data with a maximum length of 4,000 characters.
nvarchar	Variable-length Unicode data with a maximum length of 4,000 characters.
nvarchar(max)	Variable-length Unicode data with a maximum length of 230 characters (SQL Server 2005 only).
ntext	Variable-length Unicode data with a maximum length of 1,073,741,823 characters.

Binary Strings

Type	Description
binary	Fixed-length binary data with a maximum length of 8,000 bytes.
varbinary	Variable-length binary data with a maximum length of 8,000 bytes.
varbinary(max)	Variable-length binary data with a maximum length of 231 bytes (SQL Server 2005 only).
image	Variable-length binary data with a maximum length of 2,147,483,647 bytes.

Other Data Types

• sql_variant: Stores values of various SQL Server-supported data types, except text, ntext, and timestamp.
• timestamp: Stores a database-wide unique number that gets updated every time a row gets updated.
• uniqueidentifier: Stores a globally unique identifier (GUID).
• xml: Stores XML data. You can store xml instances in a column or a variable (SQL Server 2005 only).
• cursor: A reference to a cursor.
• table: Stores a result set for later processing.

Characters

CHAR (synonym = CHARACTER) = fixed-length single-byte strings
VARCHAR (synonym = CHAR VARYING {equal to the Oracle VARCHAR2}) = variable-length single-byte character strings: preferred if lot of null values expected
TEXT {equal to the Oracle LONG} = variable-length single-byte character strings
NCHAR (synonym = NATIONAL CHAR) = fixed-length unicode character strings
NVARCHAR (synonym = NATIONAL CHARACTER VARYING)

Numeric

BIGINT - n = 8 bytes: [-2^(8n-1),...0,..., 2^(8n-1)-1]
INT (synonym: INTEGER) - 4 bytes
SMALLINT - 2 bytes
---------------------------------------------------------------------
TINYINT - 1 byte - [0,...,2^(8n)-1]
---------------------------------------------------------------------
MONEY - 8 bytes - considered as a numeric as well as a miscelleneous data type
SMALLMONEY - 4 bytes - considered as a numeric as well as a miscelleneous data type
_______________________________________
--------------------------------------------------------------------
Precision = N° digits stored
Precision = [1,...,9] => 5 bytes
Precision = [10,...,19] => 9 bytes
--------------------------------------------------------------------
Scale = N° decimal digits
--------------------------------------------------------------------
REAL (synonym: FLOAT[(n)] : n = [1,...,7]): Precision = 7 => 4 bytes
FLOAT (synonym: DOUBLE PRECISION or FLOAT[(n)] : n = [1,...,15] ): Precision = 15 => 8 bytes
DECIMAL (synonym: DEC or NUMERIC {approximately equal to the Oracle NUMBER}): Precision= variable => 217 bytes

Date & Time

DATETIME {equal to the Oracle DATE} - 8 bytes of storage
SMALLDATETIME - 4 bytes of storage

Miscellaneous Data Types

BINARY - stores strings of fixed-length bits, values are entered and displayed using HEX
VARBINARY {equal to the Oracle VARBINARY} - variable BINARY
_______________________________________
--------------------------------------------------------------------
IMAGE {equal to the Oracle LONG RAW} - binary values or pictures more than 8KB
_______________________________________
--------------------------------------------------------------------
MONEY - 8 bytes - considered as a miscelleneous as well as a numeric data type
SMALLMONEY - 4 bytes - considered as a miscelleneous as well as a numeric data type
_______________________________________
--------------------------------------------------------------------
TABLE - stores result of a function, local variables and are autodeleted after end of function. Columns in tables cannot be type TABLE
_______________________________________
--------------------------------------------------------------------
SQL_VARIANT - Stores any data type other than TEXT or IMAGE. Stored data cannot be part of primary key, foreign key, computed column, and indexes or unique keys longer than 900 bytes.
_______________________________________
--------------------------------------------------------------------
UNIQUEIDENTIFIER - Global/Universal Unique Identifier; 128-bit unique value world-wide
_______________________________________
--------------------------------------------------------------------
XML - stores upto 2GB XML document fragments

Data Type in C#.net

Data Type	Description	Example
object	The base type of all types	object obj = null;
string	String type - sequence of Unicode characters	string str = "Mahesh";
sbyte	8-bit signed integral type	sbyte val = 12;
short	16-bit signed integral type	short val = 12;
int	32-bit signed integral type	int val = 12;
long	64-bit signed integral type	long val1 = 12; long val2 = 34L;
bool	Boolean type; a bool value is either true or false	bool val1 = true; bool val2 = false;
char	Character type; a char value is a Unicode character	char val = 'h';
byte	8-bit unsigned integral type	byte val1 = 12; byte val2 = 34U;
ushort	16-bit unsigned integral type	ushort val1 = 12; ushort val2 = 34U;
uint	32-bit unsigned integral type	uint val1 = 12; uint val2 = 34U;
ulong	64-bit unsigned integral type	ulong val1 = 12; ulong val2 = 34U; ulong val3 = 56L; ulong val4 =78UL;
float	Single-precision floating point type	float val = 1.23F;
double	Double-precision floating point type	double val1 = 1.23; double val2 = 4.56D;
decimal	Precise decimal type with 28 significant digits	decimal val= 1.23M;

Types in C#

C# supports two kinds of types: value types and reference types.

Types	Description
Value Types	Includes simple data types such as int, char, bool, enums
Reference Types	Includes object, class, interface, delegate, and array types

Value Types- Value type objects direct contain the actual data in a variables. With value types, the variables each have their own copy of the data, and it is not possible for operations on one to affect the other.

int i = 10;

Reference Types- Reference type variables stores the reference of the actual data. With reference types, it is possible for two variables to reference the same object, and thus possible for operations on one variable to affect the object referenced by the other variable.

MyClass cls1 = new MyClass();

Data Type Conversions

C# supports two types of conversions. Implicit conversions and explicit conversions.

Implicit conversions are direct conversion. For example:

int iVal = 34;
long lVal = intValue;

Explicit conversions includes type casting. conversion. For example:

long lVal = 123456;
int iVal = (int) lVal;