Each column, local variable, expression, and parameter has a related data type. Expressions must often be converted between data types. This data type conversion will either occur implicitly or explicitly. See also General DB Datatypes.

System Data Types

The most common data types may be grouped into data groups. The examples below are typical data types for ISO, but they are very year dependent. EGs: SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003. The "-->" indicates the equivalent in Microsoft SQL Server.

  • Binary. Raw 0s and 1s.
    • Fixed Length
      • boolean --> bit. 0 or 1 or NULL. Non-zero integers converted to 1. Stored as 1 b, not 1 B.
      • bit[(n)]
      • binary[(n)] --> binary[(n)]. Default n = 10. Max n = 8000. Binary constants are represented as hexadecimal with this syntax: 0xHexadecimal. EG: 0x2A = 2A hexadecimal = 42 decimal = 52 octal = 00101010 binary = * ASCII. Stored as n+4.
    • Variable Length
      • bit varying[(n)], varbit[(n)].
      • binary varying[(n)] --> varbinary[(n)]. Default n = 50. Max n = 8000.
    • Huge Length
      • --> image. Like varbinary, but the max = 2^31 - 1 (~2.15e9) B. Default size = 16 B.
  • Strings. Bytes assumed to be characters or text. May be single-byte encoded (usu. ISO 8859 Latin 1 or ANSI 1252 Windows) or double-byte encoded (probably Unicode).
    • Fixed Length
      • char[(n)], character[(n)] --> char[(n)]. Default n = 10. Max n = 8000.
      • national char[(n)], national character[(n)]. --> nchar[(n)]. Default n = 10. Max n = 4000. Unicode literals are specified with a leading N. EG: N'A Unicode string'.
      • --> sysname. A system-supplied user-defined data type frequently used for referencing database objects. It is actually nvarchar(128).
    • Variable Length
      • char varying[(n)], character varying[(n)] --> varchar[(n)]. Default n = 50. Max n = 8000.
      • national char varying[(n)], national character varying[(n)]. Default n = 50. Max n = 4000.
    • Huge Length
      • text --> text. Max characters = 2^31 - 1 (~2.15e9).
      • national text --> ntext. Max characters = 2^30 - 1 (~1.07e9).
  • Integer. Exact whole numbers without fractions. EG: 3.
    • --> tinyint. 0 to 255.
    • int2, smallint --> smallint. ~±2^15 (~±3.28e4)
    • int4, int, integer --> int. ~±2^31 (~±2.15e9)
    • int8, bigint --> bigint. ~±2^63 (~±9.22e18)
  • Decimal. Whole numbers with fractions. EG: 3.5.
    • Fixed point. These numbers are exactly represented with binary numbers. p = Precision = # of significant digits. s = Scale = # of decimal places.
      • num, numeric(p,s ), dec, decimal(p,s) --> decimal[(p[,s])], numeric[(p[,s])]. ±10^38. Default p = 18. Default s = 0. Max p = 38. p >= s >= 0. If p = 1-9, then stores as 5 B. p = 10-19, then 9 B (the default size). p = 20-28, then 13 B. p = 29-38, then 17 B.
      • money
        • --> smallmoney. ~±214,748.3648. Comparable to decimal(10,4). Stored as 4 B.
        • --> money. ~±2^63 (~±9.22e14). Comparable to decimal(19,4). Stored as 8 B.
    • Floating point. Numbers with a decimal portion that is approximated with binary numbers.
      • float4, float(24), real, single precision
        • --> real. ~±3.40e38. p = 7. Stored as 4 B.
        • --> float[(n)] when n is 1-24, then p = 7, and it is stored as 4 B. ~±1.79E308. n = number of bits used to store the fractional part of the significand of the number in binary.
      • float8, float(53), float, double precision --> float[(n)] when n is 25-53, then p = 15, and it is stored as 8 B. ~±1.79E308.
  • Date and Time
    • date
    • interval. An interval of time.
    • time
    • time with time zone
    • timestamp
      • --> smalldatetime. Date and time value between 1900-01-01/2079-06-06. Accurate to the minute. Stored as two 2 B integers.
      • --> datetime. Date and time value between 1753-01-01/9999-12-31. Accurate to 3.33 ms. Stored as two 4 B integers.
    • rowversion --> timestamp. A database-wide unique number that gets updated every time a row gets updated.
  • Miscellany. There are proprietary DBMS datatypes. There are even user defined datatypes which is usually an object collecting various other data types. In OOP (Object Oriented Programming) the distinction between datatypes and object types gets blurry, especially when the datatype is a pointer that can point to nearly any kind of object.
    • --> cursor. A reference to a cursor. Cannot be used for a column.
    • --> sql_variant. A data type that stores values of various SQL Server-supported data types, except text, ntext, timestamp, and sql_variant.
    • --> table. A special data type used to store a result set for later processing. Use DECLARE @local_variable to declare variables of type table. Cannot be used for a column.
    • --> uniqueidentifier. A GUID (Globally Unique Identifier) with this syntax: xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx. Note that each x is a hexadecimal. EG: 6F9619FF-8B86-D011-B42D-00C04FC964FF. Stored as 16 B.

Explicit Data Type Conversion

T-SQL provides two functions to explicitly convert an expression from one data type to another. CAST is preferred because it is based upon the SQL-92 standard, but CONVERT has some formatting options not available via CAST.

CAST(expression AS DataType[(length)])
-- EG:
CAST(@myVar As char(50))
CONVERT(DataType[(length)], expression[, style]])
-- EG:
CONVERT(char(50), @myVar)

For CONVERT(), the style parameter is an integer for formatting. Here is the stuff from the MSDN Library.

For datetime or smalldatetime. ** Input when converting to datetime; output when converting to character data.

Without century
(yy)
With century
(yyyy)

Standard

Input/Output**
- 0 or 100 Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
2 102 ANSI yy.mm.dd
3 103 British/French dd/mm/yy
4 104 German dd.mm.yy
5 105 Italian dd-mm-yy
6 106 - dd mon yy
7 107 - Mon dd, yy
8 108 - hh:mm:ss
- 9 or 109 Default + milliseconds mon dd yyyy hh:mi:ss:mmmAM (or PM)
10 110 USA mm-dd-yy
11 111 JAPAN yy/mm/dd
12 112 ISO yymmdd
- 13 or 113 Europe default + milliseconds
Approximates RFC 822
dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120 ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
- 126 ISO8601 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
- 130 Hijri dd mon yyyy hh:mi:ss:mmmAM
- 131 Hijri dd/mm/yy hh:mi:ss:mmmAM

For float or real.

Value Output
0 (default) Six digits maximum. Use in scientific notation, when appropriate.
1 Always eight digits. Always use in scientific notation.
2 Always 16 digits. Always use in scientific notation.

For money or smallmoney.

Value Output
0 (default) No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.
1 Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.
2 No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.

Implicit Data Type Conversion

If two expressions are combined and they are not the same data type, then the result will be implicitly converted according to these rules of precedence.

  • sql_variant (highest)
  • datetime
  • smalldatetime
  • float
  • real
  • decimal
  • money
  • smallmoney
  • bigint
  • int
  • smallint
  • tinyint
  • bit
  • ntext
  • text
  • image
  • timestamp
  • uniqueidentifier
  • nvarchar
  • nchar
  • varchar
  • char
  • varbinary
  • binary (lowest)

Here is a chart about implicit data type conversions in SQL Server from the MSDN library:

Implicit data type conversions in SQL Server

Miscellany

When providing Unicode values in SQL Server, prefix it with a capital 'N'. EG:

INSERT INTO tblScrap (Name) VALUES (N'This text is in Unicode. Σ.')

A User-Defined Data Type can be created with the sp_addtype systems stored procedure. Here is the syntax and some notes:

sp_addtype [@typename = ] type,
[@phystype = ] SystemDataType
[, [ @nulltype = ] '{NULL | NOT NULL | NONULL}' ]
[, [ @owner = ] 'OwnerName' ]
  • A User-Defined Data Type must be unique to the database. sp_addtype can add the data type to a specific database or all databases in an instance of SQL Server if master is the current database.

  • SystemDataType only allows these data types:
    'binary( n )' image smalldatetime
    Bit int smallint
    'char( n )' 'nchar( n )' text
    Datetime ntext tinyint
    Decimal numeric uniqueidentifier
    'decimal[ ( p [, s ] ) ]' 'numeric[ ( p [ , s ] ) ]' 'varbinary( n )'
    Float 'nvarchar( n )' 'varchar( n )'
    'float( n )' real


GeorgeHernandez.comSome rights reserved