SQL Server - Data - Asked By Mohamed .. on 08-Oct-11 03:43 AM

What all are the datatypes,
aneesa replied to Mohamed .. on 08-Oct-11 03:44 AM

SQL Server Data Types

Character strings:

Data type Description Storage
char(n) Fixed-length character string. Maximum 8,000 characters n
varchar(n) Variable-length character string. Maximum 8,000 characters  
varchar(max) Variable-length character string. Maximum 1,073,741,824 characters  
text Variable-length character string. Maximum 2GB of text data  

Unicode strings:

Data type Description Storage
nchar(n) Fixed-length Unicode data. Maximum 4,000 characters  
nvarchar(n) Variable-length Unicode data. Maximum 4,000 characters  
nvarchar(max) Variable-length Unicode data. Maximum 536,870,912 characters  
ntext Variable-length Unicode data. Maximum 2GB of text data  

Binary types:

Data type Description Storage
bit Allows 0, 1, or NULL  
binary(n) Fixed-length binary data. Maximum 8,000 bytes  
varbinary(n) Variable-length binary data. Maximum 8,000 bytes  
varbinary(max) Variable-length binary data. Maximum 2GB  
image Variable-length binary data. Maximum 2GB  

Number types:

Data type Description Storage
tinyint Allows whole numbers from 0 to 255 1 byte
smallint Allows whole numbers between -32,768 and 32,767 2 bytes
int Allows whole numbers between -2,147,483,648 and 2,147,483,647 4 bytes
bigint Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807 8 bytes
decimal(p,s) Fixed precision and scale numbers.

Allows numbers from -10^38 +1 to 10^38 –1.

The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0

5-17 bytes
numeric(p,s) Fixed precision and scale numbers.

Allows numbers from -10^38 +1 to 10^38 –1.

The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.

The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0

5-17 bytes
smallmoney Monetary data from -214,748.3648 to 214,748.3647 4 bytes
money Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8 bytes
float(n) Floating precision number data from -1.79E + 308 to 1.79E + 308.

The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.

4 or 8 bytes
real Floating precision number data from -3.40E + 38 to 3.40E + 38 4 bytes

Date types:

Data type Description Storage
datetime From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds 8 bytes
datetime2 From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds 6-8 bytes
smalldatetime From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute 4 bytes
date Store a date only. From January 1, 0001 to December 31, 9999 3 bytes
time Store a time only to an accuracy of 100 nanoseconds 3-5 bytes
datetimeoffset The same as datetime2 with the addition of a time zone offset 8-10 bytes
timestamp Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable  

Other data types:

Data type Description
sql_variant Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp
uniqueidentifier Stores a globally unique identifier (GUID)
xml Stores XML formatted data. Maximum 2GB
cursor Stores a reference to a cursor used for database operations
table Stores a result-set for later processing
Mohamed .. replied to aneesa on 08-Oct-11 03:46 AM
Thanx.
Reena Jain replied to Mohamed .. on 08-Oct-11 03:47 AM
Hi,

SQL Server offers six categories of data types for your use:
  • http://databases.about.com/od/sqlserver/a/exact_numeric.htm store numeric values where you wish to specify the precision of the variable. They may include integer or decimal numbers and are the most common category of data type used for numeric information. You can http://databases.about.com/od/sqlserver/a/exact_numeric.htm.

  • http://databases.about.com/od/sqlserver/a/approx_numeric.htm are less precise than exact numeric data types. They allow for the specification of the number of digits to store precisely while the remainder of a variable’s value is subject to rounding error. You can http://databases.about.com/od/sqlserver/a/approx_numeric.htm.

  • http://databases.about.com/od/sqlserver/a/date_time.htm allow the storage of timestamps. You can http://databases.about.com/od/sqlserver/a/date_time.htm.

  • http://databases.about.com/od/sqlserver/a/char_string.htm contain text-based values. You can http://databases.about.com/od/sqlserver/a/char_string.htm.

  • http://databases.about.com/od/sqlserver/a/binary.htm allow you to store any type of binary data, including entire files of up to 2GB. You can http://databases.about.com/od/sqlserver/a/binary.htm.

  • http://databases.about.com/od/sqlserver/a/other_types.htm allow for the storage of unique identifiers, cursors, tables and XML. You can http://databases.about.com/od/sqlserver/a/other_types.htm.
for more check this msdn link
http://msdn.microsoft.com/en-us/library/aa258271%28v=sql.80%29.aspx
Anoop S replied to Mohamed .. on 08-Oct-11 06:50 AM

SQL: Data Types


The following is a list of general SQL datatypes that may not be supported by all relational databases.

Data Type Syntax Explanation (if applicable)
integer integer
smallint smallint
numeric numeric(p,s) Where p is a precision value; s is a scale value. For example, numeric(6,2) is a number that has 4 digits before the decimal and 2 digits after the decimal.
decimal decimal(p,s) Where p is a precision value; s is a scale value.
real real Single-precision floating point number
double precision double precision Double-precision floating point number
float float(p) Where p is a precision value.
character char(x) Where x is the number of characters to store. This data type is space padded to fill the number of characters specified.
character varying varchar2(x) Where x is the number of characters to store. This data type does NOT space pad.
bit bit(x) Where x is the number of bits to store.
bit varying bit varying(x) Where x is the number of bits to store. The length can vary up to x.
date date Stores year, month, and day values.
time time Stores the hour, minute, and second values.
timestamp timestamp Stores year, month, day, hour, minute, and second values.
time with time zone time with time zone Exactly the same as time, but also stores an offset from UTC of the time specified.
timestamp with time zone timestamp with time zone Exactly the same as timestamp, but also stores an offset from UTC of the time specified.
year-month interval Contains a year value, a month value, or both.
day-time interval Contains a day value, an hour value, a minute value, and/or a second value.
Suchit shah replied to Mohamed .. on 08-Oct-11 06:51 AM

Objects that contain data have an associated data type that defines the kind of data; for example, character, integer, or binary, the object can contain. The following objects have data types:

  • Columns in tables and views.

  • Parameters in stored procedures.

  • Variables.

  • Transact-SQL functions that return one or more data values of a specific data type.

  • Stored procedures that have a return code, which always has an integer data type.

Assigning a data type to an object defines four attributes of the object:

  • The kind of data contained by the object.

  • The length or size of the stored value.

  • The precision of the number (numeric data types only).

  • The scale of the number (numeric data types only).

For more information about data type precision, scale and length, see http://msdn.microsoft.com/en-us/library/ms190476(v=SQL.100).aspx.

Transact-SQL has these system data types.

http://msdn.microsoft.com/en-us/library/ms187745(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms188362(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms177603(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms176089(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms131049(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms190498(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/bb630352(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms187819(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/bb677335(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/bb630289(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms187746(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms173773(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/bb677290(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms174409(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms187745(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms179882(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms186939(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms187993(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms187746(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms186939(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms173773(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms182776(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms182418(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms187745(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms179882(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms173829(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms175010(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms187993(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/bb677243(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms182776(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms187745(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms188362(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms176089(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms187942(v=SQL.100).aspx

http://msdn.microsoft.com/en-us/library/ms187339(v=SQL.100).aspx

All data stored in Microsoft SQL Server must be compatible with one of these base data types. The cursor data type is the only system data type that cannot be assigned to a table column. It can be used only with variables and stored procedure parameters.

Suchit shah replied to Mohamed .. on 08-Oct-11 06:51 AM
Data Type Description

bigint

Integer (whole number) data from –2^63 (–9,223,372,036,854,775,808) through 2^63–1 (9,223,372,036,854,775,807). Storage size is 8 bytes.

integer

Integer (whole number) data from –2^31 (–2,147,483,648) through 2^31–1 (2,147,483,647).

Storage size is 4 bytes.

smallint

Integer data from –32,768 to 32,767. Storage size is 2 bytes.

tinyint

Integer data from 0 to 255. Storage size is 1 byte.

bit

Integer data with a value of either 1 or 0.

Storage size is 1 bit.

numeric (p, s)

Synonyms:

decimal(p,s) and dec (p,s)

Fixed-precision and scale-numeric data from –10^38+1 through 10^38–1. The p variable specifies precision and can vary between 1 and 38. The s variable specifies scale and can vary between 0 and p.

Storage size is 19 bytes.

money

Monetary data values from (–2^63/10000) (–922,337,203,685,477.5808) through 2^63–1 (922,337,203,685,477.5807), with accuracy to a ten-thousandth of a monetary unit. Storage size is 8 bytes.

float

Floating point number data from –1.79E +308 through 1.79E+308

Storage size is 8 bytes.

real

Floating precision number data from –3.40E+38 through 3.40E+38.

Storage size is 4 bytes.

datetime

Date and time data from January 1, 1753, to December 31, 9999, with an accuracy of one three-hundredth second, or 3.33 milliseconds. Values are rounded to increments of .000, .003, or .007 milliseconds.

Stored as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system's reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight. Seconds have a valid range of 0–59.

Format Example
yyyy/mm/ddhh:mm:ss1947/08/15 03:33:20
mm/dd/yyyyhh:mm:ss04/15/1947 03:33:20
dd mmm yyyy hh:mm:ss15 Jan 1947 03:33:20
dd mmmm yyyy h:mm:ss15 January 1947 03:33:20

national character(n)

Synonym:nchar(n)

Fixed-length Unicode data with a maximum length of 4000 characters. Default length = 1. Storage size, in bytes, is two times the number of characters entered.

national character varying(n)

Synonym:nvarchar(n)

Variable-length Unicode data with a length of 1 to 4000 characters. Default length = 1. Storage size, in bytes, is two times the number of characters entered.

ntext¹

Variable-length Unicode data with a maximum length of (2^30–2)/2 (536,870,911) characters. Storage size, in bytes, is two times the number of characters entered.

ms172424.note(en-us,SQL.100).gifNote:
ntext is no longer supported in string functions.


nchar

Fixed-length Unicode character data of n characters. n must be a value from 1 through 4,000. The storage size is two times n bytes.

binary(n)

Fixed-length binary data with a maximum length of 8000 bytes. Default length = 1.

Storage size is fixed, which is the length in bytes declared in the type.

varbinary(n)

Variable-length binary data with a maximum length of 8000 bytes. Default length = 1.

Storage size varies. It is the length of the value in bytes.

image¹

Variable-length binary data with a maximum length of 2^30–1 (1,073,741,823) bytes.

Storage is the length of the value in bytes.

uniqueidentifier

A globally unique identifier (GUID). Storage size is 16 bytes.

IDENTITY [(s, i)]

This is a property of a data column, not a distinct data type.

Only data columns of the integer data types can be used for identity columns. A table can have only one identity column. A seed and increment can be specified and the column cannot be updated.

s (seed) = starting value

i (increment) = increment value

ROWGUIDCOL

This is a property of a data column, not a distinct data type. It is a column in a table that is defined by using the uniqueidentifier data type. A table can have only one ROWGUIDCOL column.

Timestamp/rowversion

This is an automatically generated unique binary number.

Storage size is 8 bytes.