major query of daily use in sql server - use FLOOR,COALESCE,locks in database,SIGN

1. What is COALESCE in SQL Server?

COALESCE is used to return first non-null expression within the arguments. This function is used to return a non-null from more than one column in the arguments.

Example –

Select COALESCE(empno, empname, salary) from employee;

2. What is the purpose of FLOOR function?

FLOOR function is used to round up a non-integer value to the previous least integer. Example is given

FLOOR(6.7)

Returns 6.

3. Can we check locks in database? If so, how can we do this lock check?

Yes, we can check locks in the database. It can be achieved by using in-built stored procedure called sp_lock.

4. What is the use of SIGN function?

SIGN function is used to determine whether the number specified is Positive, Negative and Zero. This will return +1,-1 or 0.

Example –

SIGN(-35) returns -1

5. Which SQL server table is used to hold the stored procedure scripts?

Sys.SQL_Modules is a SQL Server table used to store the script of stored procedure. Name of the stored procedure is saved in the table called Sys.Procedures.

6. What is the difference between GETDATE and SYSDATETIME?

Both are same but GETDATE can give time till milliseconds and SYSDATETIME can give precision till nanoseconds. SYSDATE TIME is more accurate than GETDATE.

7. Which command is used for user defined error messages?

RAISEERROR is the command used to generate and initiates error processing for a given session. Those user defined messages are stored in sys.messages table.