SQL Server tough Interview questions

1. What is the output of the following query? SELECT 1 UNION SELECT NULL UNION SELECT '1'

2. What do you expect the result of the following query to be? No cheating, don't run until you've answered!WITH DATA (Numbers) AS(SELECT NULL UNION ALL SELECT NULL UNION ALLSELECT NULL UNION ALLSELECT 1 UNION ALL SELECT 2 UNION SELECT 3)SELECT COUNT(ALL Numbers) AS NULLNumberCount FROM DATA WHERE Numbers IS NULL

3. You are trying to run some scripts using SQLCMD but keep getting errors that your password has expired. SSMS is not installed on this machine. What should you do?

4. What does sp_validname do?

5. You receive some data from UI and need to populate table Customer if the name received from UI does not exist in database. Which of options below should be used for this purpose:1.IF NOT EXISTS ( SELECT * FROM Customer WHERE Name = @CustomerName ) INSERT INTO [Customer] ( [Name]) VALUES ( @CustomerName)2.INSERT INTO [Customer] ( [Name])SELECT @CustomerNameWHERE NOT EXISTS ( SELECT * FROM Customer WHERE Name = @CustomerName )

6. What will be the count against each category? Each row in the result is shown as a pair in the answers.CREATE TABLE #CATEGORY(CATID INT ,VAL1 INT)INSERT INTO #CATEGORY VALUES(1,NULL)INSERT INTO #CATEGORY VALUES(2,1)INSERT INTO #CATEGORY VALUES(3,2)CREATE TABLE #DATA(VAL1 INT)INSERT INTO #DATA VALUES(1)INSERT INTO #DATA VALUES(1)INSERT INTO #DATA VALUES(1)INSERT INTO #DATA VALUES(2)INSERT INTO #DATA VALUES(2)INSERT INTO #DATA VALUES(3)SELECT C.CATID, COUNT(*) FROM #DATA D INNER JOIN #CATEGORY C ON C.VAL1 = D.VAL1 OR C.VAL1 IS NULLGROUP BY C.CATID

7. Cursor optimization we all know that cursors are slow on SQL Server and should be avoided when possible. However, there are a few cases where they can't be avoided, for instance because an external process has to be invoked for each row, or because you run into one of the very rare problems where a cursor actually runs faster than a set-based solution.So, suppose you find yourself in a situation where you do need a cursor - which cursor option must be specified in order to assure maximum performance, assuming the cursor fits in cache?

8. You are developing security policy for your SQL Servers and have all of the data entry clerks needing access to a series of tables. You create a Data Entry role and assign the proper permissions as well as add the users.
You then find out that Bob is a part of the HR group because of cross training and needs to use the same objects from the same application except for the Vacation table, to which he should not have access. No column permissions are assigned. What should you do?

9. What is the difference between a WHERE clause and a HAVING clause?

10. A developer is building an application against SQL Server 2000 and comes to see you regarding his design. This portion of the application is providing reporting on a series of clients that have been selected. His design is as follows:
Display a list of clients that can be selected
Loop through the list of clients and call a stored procedure to get data for each client (same data for each
Asemble this information and display a grid to the user To improve performance, what suggestions would you give the developer?

11. I want to append output data from a query into an existing file, which command prompt tool will help me?

12. If I create a database in simple recovery mode in SQL Server 2005, then backup the database, then add 1000 records, then change the recovery mode to full, then add 500 more. Can I recover using the log backups?

13. In SQL Server 2008, what is the maximum number of table sources that you can have in a single SELECT query?

14. What does the CURRENT_TIMESTAMP function return?