SQL TUTORIAL
SQL Server Data Types

SQL Server Data Types

Overview

A data type is an attribute that specifies the type of data that the object can hold like integer data, character data, monetary data, date and time data, binary strings and so on.

SQL Server provides a basic data types for each column, local variable, expression and parameter that can hold data, such as numeric, character and dates.

Data type conversion

  • When data from one object is moved to, compared with, or combined with data from another object, the data may have to be converted from the data type of one object to the data type of the other.
  • Data types can be converted either implicitly or explicitly.
    • Implicit conversion - Sql Server automatically converts the data from one data type to another.
    • Explicit conversions use the CAST or CONVERT functions. The CAST and CONVERT functions convert a value from one data type to another.

Data type precedence

  • When an operator combines two expressions of different data types, the rules for data type precedence specify that the data type with the lower precedence is converted to the data type with the higher precedence.
  • If the conversion is not a supported implicit conversion, an error is returned.
  • When both operand expressions have the same data type, the result of the operation has that data type.

SQL Server uses the following precedence order for data types:

1. user-defined data types (highest) 11. real 21. text
2. sql_variant 12. decimal 22. image
3. xml 13. money 23. timestamp
4. datetimeoffset 14. smallmoney 24. uniqueidentifier
5. datetime2 15. bigint 25. nvarchar (including nvarchar(max) )
6. datetime 16. int 26. nchar
7. smalldatetime 17. smallint 27. varchar (including varchar(max) )
8. date 18. tinyint 28. char
9. time 19. bit 29. varbinary (including varbinary(max) )
10. float 20. ntext 30. binary (lowest)

Precision and Scale

  • Precision is the total number of digits in a number.
  • Scale is the total number of digits to the right of the decimal point in a number.
  • In SQL Server, the default maximum precision of numeric and decimal data types is 38.

Length

  • For a numeric data type is the number of bytes that are used to store the number.
  • For a character string or Unicode data type is the number of characters.
  • For binary, varbinary, and image data types is the number of bytes.

SQL Server Data Types
Overview Data type categories