Data Types

SQL Server data types

SQL Server associates columns, expressions, variables, and parameters with data types

Data types determine what kind of data can be stored in the field:
       Integer, characters, dates, money, binary strings, etc.

SQL Server supplies several built-in data tyes
Developers can also define custom type
      Aliases in T-SQL
      User-defined types in .NET code
Built-in data types are categorized as shown in the table below

 SQL Server Data Type Categories
 Exact numeric
 Unicode characters
 Approximate numeric
 Binary strings
 Date and Time
 Others
 Character strings


Numeric data types

Exact Numeric

 Data Type
 Range
 Storage (bytes)
 tinyint
 0 to  255
 1
 smallint
 -32, 768, to 32, 768
 2
 int
 2 ^ 31 (-2, 147, 483, 648) to
2 ^ 31 - 1 (2, 147, 483, 647)
 4
 Bigint
 -2 ^ 63 - 2 ^ 63-1
(+ / - 9 Quintilian)
 8
 bit
 1, 0 or NULL
 1
 decimal / numeric
 - 10 ^ 38 + 1 through  10 ^ 38 -1
When maximum precision is used
 5 - 17
 money
 -922, 337, 203, 685, 477.5808 to
922, 337, 203, 685, 477.5807
 8
 smallmoney
 - 214, 748.3648  to  214, 748.3647
 4


Decimal/ numeric are functionally equivalent and use precision and scale parameters:

DECLARE  @mydecimal  AS  DECIMAL  8 , 2)

Approximate Numeric

 Data Type
 Range
 Storage (bytes)
 float(n)
 -1.79E + 308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308
 Depends on value of  n, 4 or 8
 real
 -3.40E + 38 to -1.18E - 38, 0 and 1.18E - 38 to 3.40E + 38
 4

float(24) is the ISO synonym for float

In float(n), n is the number of bytes used to store the mantissa of the float number in scientific notation

Values of float are truncated when converted to integer types

Binary String data types

Binary Strings

 Data Type
 Range
 Storage (bytes)
 binary(n) 
 1-8000 bytes
 n bytes
 varbinary(n)
 1-8000 bytes
 n bytes + 2
 varbinary(MAX)
 1-2.1 billion (approx) bytes
 actual length + 2


Other data types


 Data Type

Range

Storage (Bytes)

                 Remarks

 rowversion

Auto-generated

8

Successor type to timestamp

 uniqueidentifier

Auto-generated

16
 Globally unique identifier (GUID)

 xml

0 - 2 GB

0 - 2 GB
Stores XML in native hierarchical structure

 cursor

N/A

N/A

Not a storage data type

 hierarchyid

N/A
Depends on content
Represents position in a hierarchy

 sql_variant

0 - 8000 bytes
Depends on content
Can store data of various data types

 table

N/A

N/A 
Not a storage data type, used for query and programmatic operations


Converting strings with PARSE

PARSE is new function in SQL Server 2012 that converts strings to date, time, and number types


 PARSE element

Comment

String_value

Formatted nvarchar(4000) input

Data_type

Requested data type output

Culture
 Optional string in .NET culture form:
en-US, es-ES, ar-SA, etc.


PARSE example:

SELECT  PARSE ('02/12/2012'  AS datetime2  USING 'en - US')  AS parse_result ;