1.        What are the series of steps that happen on execution of a query in a Query Analyzer?

2.        What are Clustered and Non-clustered indexes?  When do u use each of them?  What is the data that is stored on applying either a clustered or a non-clustered index?  He basically asked questions related to the architecture of indexes.

3.        There is table that only has only non-clustered index.  Now, to the table a new clustered is applied.  What will happen to the data in the non-clustered before and after applying the clustered index.  Will it be the same or not?

4.        What are functions in SQL Server?  Why do we use them?  What are the different types of user defined function that we have?

5.        What are stored procedures and why do we use them?

6.        What are the differences between functions and stored procedures?

7.        What are cursors and why do we use them?  How do you declare a cursor?  How do you declare a simplest cursor?

8.        Suppose, I have a dynamic cursor attached to table in a database.  I have another means by which I will modify the table.  What do you think will the values in the cursor be, when use the cursor?

9.        What are joins?  What are the different kinds of joins?  What will happen to the rows the left outer join when there are no matching rows in the left table?  What is the difference between inner and outer joins?

10.     What are ‘jobs’ in SQL Server?  How do we create one?

11.     What are the different types of Back-ups possible in SQL Server and how would you do that?

12.     What is the particular thing called in sql server for joins?  (I did not understand what he meant.  He was looking for some SQL parlance to it when joins are executed in the server)

13.     What is Candidate key, alternate key and composite key?

14.     What is the difference between Delete Table and Truncate Table?

15.     What is clustered index and non clustered index and what is the difference

16.     Difference between “Delete  from Table”  and “TRUNCATE Table”

17.     You have one database with a table which has huge volume of data and you don’t know anything about the table structure and how it’s used and all. There is a performance problem in the application and you have to create indexes for this table. How will you go about it? What are the steps you will take to create the indexes?

18.     What are the different ways by which you will transfer data between two different databases in two different servers. This tranformation has to be done every week on particular time. 

19.     Questions on Linked servers  & DTS. Any drawbacks in using DTS?

20.     You have a table for auditing in an application. This table is read once in a while. This table grows very fast and has  more volume of data. This table has a clustered index, primary key & a non clustered index. What’s your suggestion on that? Give reason

21.     Difference b/w text and image datatype.( this was related to my Project)

22.     bcp ( Purpose of it).

23.     what are the different authentication modes in Sqlserver. If a user is logged under windows authentication mode, how to find his userid.

24.     What is MSDTC? Explain on that.

25.     I have a table with one column which has many records which are not distinct. I need to find the distinct values from that column and number of times it’s repeated.

26.     Difference B/w RDBMS and DBMS

27.    What are the different types of back-ups available in sqlserver?

28.    What are the different types of replications available in sqlserver and brief about each?

29.    suppose there is a replication happening from System A to System B and suddenly some problem is there during the replication, data replicated at system B is 80% only, what is your first step in analyzing the same.(This is a mission critical client , which may lose some lakhs in seconds)/

30.    Table A contains column1 which is primary key and has 2 values (1, 2) and Table B contains column1 which is primary key and has 2 values (2, 3). Write a query which returns the values that are not common for the tables and the query should return one column with 2 records.

31.    There are 3 tables Titles, Authors and Title-Authors. Write the query to get the author name and the number of books written by that author, the result should start from the author who has written the maximum number of books and end with the author who has written the minimum number of books.

32.    What is snap shot replication how is it different from Transactional replication

33.    There is a public role which has access to all the tables and Sales role which has access to only few tables, and a user is given access under public and sales role, now will he be able to access all the rows or only few ? Justify

34.    What is third normalization? Give an example for the same

35.    Two tables are there empA and empB. Select all the records from table A which are not there in table B .

36.    There are three fields in a table Name, Person and Roll. Find name where Amithbh and Vinod both acted ?

37.    What is the diff B/w Union and Union all ?

38.     What is the command used to find all the tables/views in a given database?

39.     Explain the architecture of SQL Server Engine.

40.     In a company there are employees and they can have multiple designations, so give table structures for the required tables and what relationship should/will they have(1 to 1, 1 to many….).

41.     Difference between Joins and Sub Queries , which is better and why, give an example?

42.     If a database is normalized by 3 NF then how many no of tables it should contain in minimum? How many minimum if 2NF and 1 NF?

43.     Write a query to delete all duplicate rows from a given table i.e. leaving only one instance of a record.

44.     Write a query to get the second highest rank candidate information from student table.