Tip of the Day : Data Type Precedence

Welcome to SQL Server Helper !!!

This site is intended for those who are beginning to use SQL Server as part of their day-to-day activities.  You will find in this site a collection of useful functions, triggers, stored procedures and tips and tricks related to SQL Server.

Should you have any comments or questions regarding this site or if you want to ask SQL Server-related questions, e-mail us here.

We hope we are able to help and we are glad to help!!!

SQL Server Tip of the Day - November 27, 2021

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

SQL Server 2012

SQL Server 2008

User-Defined Functions

Date Functions

A collection of useful user-defined functions that deal with dates.

String Functions

A collection of useful user-defined functions that deal with strings (varchar/char/nvarchar/nchar).

Tree Functions

A collection of useful user-defined functions that deal with tree or hierarchical design structures.

Table-Valued Functions

A collection of useful table-valued user-defined functions that can be used to join with other tables.

SQL Server Built-in Functions

A reference to all built-in functions available within SQL Server grouped into categories.

Tips and Tricks

A collection of useful SQL Server-related tips and tricks:

SQL Server Error Messages

A list of SQL Server error messages and for certain error messages, discusses ways on how to solve the error or work around them:

Frequently Asked Questions