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.

What is ISNUMERIC in SQL Server 2008R2?

Definition:-

ISNUMERIC () is a function to determines where an expression is valid numeric type or not.

ISNUMERIC return 1 when input expression is valid numeric data type, otherwise it return 0.

Syntax:-

ISNUMERIC (expression)

ReturnType:-

Int

Note:-

ISNUMERIC returns 1 for some characters that are not numbers, such as plus (+), minus (-), and valid currency symbols such as the dollar sign ($).

Example:-

Select ISNUMERIC('123')

Select ISNUMERIC(123)

Output:

1

References taken from MSDN.

How to get columns value separated by comma in MS-SQL?

There are many scenario come to show columns value separated by comma.

This can be achieved by FOR XML PATH with STUFF function.

Now you have tabled like below.

 

Now you want to fetch the records from table to show student name , corresponding subject which is separated by comma.

SQL-Query:-

 Select distinct s1.studentname,

 STUFF((select distinct ','+s2.[subject]

 from tbl_student s2 where s1.studentname=s2.studentname

 for xml Path(''),Type).value('.','varchar(max)'),1,1,'')subject

 from tbl_student s1

OutPut:-