SQL SERVER ( OPERATORS & SYSTEM FUNCTIONS ) REAL TIME INTERVIEW QUESTIONS AND ANSWERS

OPERATORS

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:-  [sql]Select COALESCE(empno, empname, salary) from employee[/sql]

2. What is the use of =,==,=== operators?

= is used to assign one value or variable to another variable.
== is used for comparing two strings or numbers.
=== is used to compare only string with the string and number with numbers.

3. What is ISNULL () Operator?

    ISNULL function is used to check whether value given is NULL or not NULL in SQL Server.  This function also provides to replace a value with the NULL.

4. Try to use UNION ALL statement instead of UNION, Whenever possible.

    The UNION ALL Statement is much faster than UNION, because UNION ALL Statement deos not look for duplicate rows, and UNION Statement does look for duplicate rows, whether or not they exist.

5. How can you compare a part of the name rather than the entire name?

    Select * from people where empname like  '%ab%'
Would return a recordset with records consisting empname the sequence 'ab' in empname.

6. What is MERGE Statement?

    MERGE is a new feature that provides an efficient way to perform multiple DML operations. In previous versions of SQL Server, we had to write separate statements to INSERT, UPDATE or DELETE data based on certain conditions, but now using MERGER statement we can include the logic of such data modifications in one statement that even checks when the data is matched then just update it and when unmatched then insert it.
One of the most important advantage of MERGER Statement is all the data is read and processed only one.

7. How do enable and disable identity property?

8. What is better -2nd Normal Form or 3rd Normal Form? why?

9. I want convert lowercase letters into uppercase how do you do?  

10. How do you concatenate first name and last name?

SYSTEM FUNCTIONS

1. Write a SQL Query to find first Week Day of Month?

    SELECT DATENAME(dw, DATEADD(dd, -DATEPART(dd, GETDATE())+1, GETDATE())) AS FirstDay

2. What is the system function to get the current user's user id?

    USER_ID(). Also check out other system functions like USER_NAME(), SYSTEM_USER, SESSION_USER, CURRENT_USER, USER, SUSER_SID90, HOST_NAME().

3. What are the different types of replication ? Explain.

   STUFF function to overwrite existing characters, Using this syntax, STUFF ( string expression, start , length, replacement characters), string expression is the string that will have characters substituted, start is the starting position, length is the number of characters in the string that are substituted, and replacement characters are the new characters interjected into the string.
    REPLACE function to replace existing characters of all occurrence, Using this syntax REPLACE (string expression, search string, replacement string), where every incidence of search string found in the string expression will be replaced with replacement string.

4. What is the STUFF Function and how does it differ from the REPLACE Function?

5. What is the purpose of Floor Function? 

   FLOOR Function is used to round up a non-integer value to the previous least integer.
 Ex :-
[sql]FLOOR(6.7)[/sql]
Returns 6.

6. 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
Ex ;-  [sql]SIGN(-35) returns -1[/sql]

7. What is the SUBSTR?

     The SUBSTR function is used to return specific portion of string in a given string.
EX:- [sql]SUBSTR("Smiley",3)[/sql]

8. 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.

9. What is DATEPART?

   Returns an integer representing the specified date part of the specified date.

10. How to get rid of the tim epart from the date returned by GETDATE Function? (top)

   We have to use the COVERT function to strip the time off the date. Any of the following commands will do this :

SELECT CONVERT (char, GETDATE(), 101)
SELECT CONVERT (char, GETDATE(), 102)
SELECT CONVERT (char, GETDATE(), 103)
SELECT CONVERT (char, GETDATE(), 1)

11. How to get the first day of the week, last day of the week and last day of the month using T-SQL date functions?(top)

       DECLARE @ Date datetime
SET @Date = '2001/08/31'
SELECT DATEADD(dd, -(DATEPART(dw, @Date) - 1), @Date) AS 'First day of the week'
SELECT DATEADD(dd, -(DATEPART(dw, @Date) - 7), @Date) AS 'Last day of the week'
SELECT DAY(DATEADD(d,-DAY(DATEADD(m,1,@Date)),DATEADD(m,!,@Date))) AS 'Last day of the month'

12. What is snapshot replication how is it different from Transactional replication?

13. What is OPENXML in SQL Server?

14. What is RANK Function?
Returns the rank of each row within the partition of a result set.
syntax:RANK() OVER ([PARTION_BY_CLAUSE] ORDER_BY_CLAUSE) 15. Diference between Rank and Dense Rank?
Rank:It calculates the rank with gaps
Dense Rank:It calculates rank without gaps.