What is the difference between round and ceiling in SQL Server?

In SQL Server, both the ROUND and CEILING functions are used to perform mathematical rounding of numerical values, but they have different behaviors:

1. ROUND:

   • The ROUND function is used to round a numeric value to a specified number of decimal places.
   • It performs "normal" rounding, which means that if the decimal part is equal to or greater than 0.5, it rounds up; otherwise, it rounds down.
   • It can be used with both positive and negative numbers.
   • The ROUND function takes two arguments: the numeric value to be rounded and the number of decimal places to round to.

   Example:

   
   SELECT ROUND(3.14159, 2); -- Returns 3.14 

2. SELECT ROUND(3.6); -- Returns 4 
3. SELECT ROUND(-3.6); -- Returns -4

4. CEILING:

   • The CEILING function is used to round a numeric value up to the nearest integer that is greater than or equal to the original value.
   • It always rounds up, regardless of the decimal part of the number.
   • It is commonly used when you want to ensure that a value is rounded up to the nearest whole number, even if the decimal part is very small.
   • The CEILING function takes a single argument, which is the numeric value to be rounded up.

   Example:

   SELECT CEILING(3.14159); -- Returns 4
   SELECT CEILING(3.6); -- Returns 4
   SELECT CEILING(-3.6); -- Returns -3

In summary, the key difference between ROUND and CEILING in SQL Server is in how they handle rounding. ROUND performs standard rounding based on the decimal part of the number, while CEILING always rounds up to the next integer, regardless of the decimal part. Your choice between them depends on the specific rounding behavior you need for your calculations.