Tip of the Day : Data Type Synonyms

SQL Server Helper - Tip of the Day

Data Type Precedence

When an operator combines two expressions of different data types, the rules of 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 operations will be that data type.

Here are a couple of examples on errors that are usually encountered due to the implicit conversion of data types based on the data type precedence outlined below:

SELECT 'The Current Date is ' + GETDATE()

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
SELECT @@ROWCOUNT + ' Rows Affected'

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value ' Rows Affected' to data type int.

SQL Server uses the following precedence for data types, from highest to lowest:

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

Back to Tip of the Day List Next Tip