TSQL INTERVIEW QUESTIONS [Database Interview Questions]

http://sqlage.blogspot.in/search/label/TSQL%20INTERVIEW%20QUESTIONS

Here are TSQL Interview Questions and Database Interview questions. I am going to do my best to provide a good list of real time questions.  I am not going to categories the questions depending upon topics but rather have them one after one as you never know what interviewer is going to ask you next ;). All the best with Database developer interview!

1.         What is Heap?

2.         What are DMVs in SQL Server and name two of them that will you use to find out long running queries or stored procedures?

3.         If you need to find out the last time the job was run, which database will help you to find that information?

4.         If I want you to provide me the list of all SQL Server Agent jobs with Schedules which database and tables will you use?

5.         What are 2 differences between Delete and Truncate?

6.         Let me know at least five differences between a stored procedure and a function?

7.         What is parameter sniffing in Stored Procedure, how you will overcome that?

8.         Can you create a function and have dynamic SQL in it?

9.         What are CROSS APPLY and OUTER APPLY in SQL Server and why we need to use it?

10.     What are window functions in TSQL, IN SQL 2012 new window functions are added such as FIRST_VALUE, LAST_VALUE, LEAD, LAG, Explain the definition with scenario where you will use these?

11.     There is duplicate data in table, Write three different queries to find out those duplicate records?

12.     What is difference between ISNULL ( ) AND COALESCE ( ), which one you prefer and why?

13.     I heard indexes are good, Can I create cluster index on multiple columns or cluster index can be created only on one column?

14.     I need to create non-cluster index on 20 columns, data type of each column is Char (7000), Can I include all of the columns in non-cluster index? What is max number of columns I can include in non-cluster index, what other options I have if there is limit on number of columns?

15.     One of our developer has created 5 functions. Whenever new database is created these functions should be created in it without running creates statements for these functions every time after create database. What are my options?

16.     I have two tables with same columns, if I want to find out all records those are in Table A and not in Table B ,  Let me know the at least two queries How I can do that?

17.     I want the list of all tables with column name with data type name, which system table I can use to get that information.

18.     In one of our database, we have 100 stored procedures, each stored procedure has comments. I want to find if comment are something like this ‘Creator Name=Aamir’ , What options I have?

19.     If you need to script a table with data, how will you do that?

20.     A big update is happening on one of the table, you are still interested to read the data even update is running, what will be your Select query?

21.     How will you find blocking processes?

22.     You have ten tables in database and all have cluster indexes on them, If I ask you to give me the total row number in each table without using count(*) from table, which table can help you out without using count(*)?

23.     If we want to put table name and total record count in each of the table in database, what will be your approach?

24.     I have 100 SQL Server agent jobs, each job has different TSQL Statements , how can you find a job that is having this statement ‘ Select count(*) from dbo.test’ ?

25.     I want to audit information such as who created table, who has dropped table, what are my options?

26.     I have a Audit Table that has couple of columns, If I want to send email evey morning with all these records in email, how will you be able to do that in SQL Server?

27.     We have an application that is running on SQL Server 2005. Our company has decided to have no more SQL server 2005 but only SQL Server 2012. We have migrated the Database to SQL Server 2012. How I will set the compatibility to SQL Server 2005 so application can run without any issues?

28.     When I run this query I receive no records ( Select * From dbo.TEST) but when I run (Select * from dbo.test) , I get 10 records. In both queries table name is Test. Which database property is playing role here and how we can address that to return records in any case?

29.     If I run Select 100/11 what out I will get?

30.     How do you handle exceptions in TSQL?

31.     List at least 5 system functions those you have used or will use to Retrieve Error information

32.     What is uncommittable state?

33.     Let’s say you have found bunch of duplicate records in table, we want to keep only one record for each of duplicate set, How will you do that?

34.     What are the things you will look into when you do performance tuning of TSQL query?

35.     What is difference between Rollup and Cube, What are alternative of these in SQL Server 2012?

36.     I have sale column in one of the table, Explain two different queries those you can use to have running total in your final output?

37.     I have two tables with same column and data types. If I need to get distinct records from both of tables how I can do that?

38.     Have you heard Wait for, Break, Continue key words in TSQL , explain about each of them?

39.     If you have create a table with identity (1, 1), is that possible that sequence can break and if yes then in which situation that happen?

40.     What are common table expressions (CTE), what is Recursive CTE, in which situation you have used it? If you want to limit recursion how you can do that in CTE?

41.     What type of parameters you can provide to stored procedure? Explain where did you used them and why?

42.     What string functions you have used, if you need to find the starting position of character or string which function you will use?

43.     What is the difference between LEN() AND LENGTH( ) Function?

44.     What is the difference between ISNULL () AND NULLIF() Functions?

45.     Can you insert ordered data into SQL Server table and will SQL Server store data in ordered you have inserted? And if you will write Select Query to extract that data, will you retrieve in order the way you have inserted or not?

46.     Explain a scenario where you needed to write Correlated subquery?

47.     When do you like to create filter index, do they take less or more space than cluster index on Disk? What are advantages of using Filter index?

48.     Sometime when you run your query you receive this warning with result sets as well “Warning: Null value is eliminated by an aggregate or other SET operation”. How can you avoid this?

49.     If you need to find the difference in days between two dates which function you will use?

50.     Can you use having clause without group by?

51.     Let’s say you have tableA on SERVERA and ServerB. If you want to see all the records from TableA on both servers, how you will be able to do that?

52.     There is Excel file siting on your Desktop. You want to write a query to read that file and see the records in SSMS. How will you do that in TSQL?

53.     You have few word documents those you want to save in SQL Table, What data type will you use to save the word document in SQL Table?

54.     Let’s say you have used dynamic SQL, you have created a temp table inside Dynamic SQL , Will you be able to read data from that temp table after execution of dynamic SQL?

55.     What keyword will you use to retrieve unique rows from table?

56.     Can I execute stored procedure inside Function?

57.     What is for xml, in which Scenario you have used?

58.     Can you use Case Statement in Order by Clause?

59.     Which data type will you use which should be time zone aware?

60.     What is Sparse Column?

61.     What are different ways to run dynamic SQL; if I need to pass parameter to dynamic SQL How I can do that?

62.     Cross join gives you Cartesian product, where have you used Cross Join?

63.     If I need to add “Total” Line at the end of result set, what option do I have?

64.     Can we send emails in HTML Format from SQL Server, How will you do that?

65.     You have built a big dynamic SQL query. The total length of this built query is more than 20,000 characters, now you want to see if your built query looks good, how will you print that query?

66.     How will you transpose column into row by using TSQL?

67.     Can you execute stored procedure in View?

68.     Can you declare variable in Create View Statement if not then what object you prefer to create so can us in Select statement like view?

69.     What is Self-Join; explain a scenario where you need to use that?

70.     What are the steps to create partitioned table, what are benefits of creating it?

71.     What is Switch Operator in TSQL?

72.     If you need to get month name which TSQL Function you will use?

73.     If you need to get Year which function you will use?

74.     If you need to extract data from Oracle table and join with SQL table, what steps are required, explain sample query as well?

75.     What new features are available in SSMS those were not in previous versions? Any third party tools those can be add in to SSMS and are helpful for daily use?

76.     Can you save query results to flat file from SSMS?

77.     What are partitioned views and where we need to create these views?

78.     To connect to SQL Azure, do you need any different tool or you can use SSMS?

79.     What is Key Lookup operator (bookmark lookup) and how you can remove that?

80.     What is estimated Execution plan?  How we can display execution plan in text?

81.     What is plan cache and how does it relate to Execution plan?

82.     Which is better Index Scan or Index Seek? In which scenario Scan is better than seek?

83.     What type of Trigger can be created on Views?

84.     What are different ways available to set value to Variable?

85.     What is @@RowCount?

86.     What is computed column and have you used it, Is there any disadvantages of using computed column?

87.     Is ORDER BY clause valid in views, inline functions, derived tables, subqueries, and common table expressions?

88.     I want to create view with order by clause by any means, how I can do that?

89.     Which function we can use to concatenate strings in SQL Server 2012?

90.     How many different values I can store in bit type data type?

91.     When you should run UPDATE STATISTICS?

92.     To see the total space taken by database which query you will run?

93.     What is data compression, what level of compression is available?  Should we enable on OLTP tables or DWH Tables to get benefit of this feature?

94.     What is CDC, in which version it was introduced? To Enable CDC on table, do you have to enable on Database?

95.     Can you enable CDC on single column or you have to enable on all the columns in table?

96.     If you drop Column from Source on which CDC was enabled, will it be dropped from CDC table as well?

97.     If you include a new column in Source, Will it be automatically included in CDC?

98.     If you need to find out, CDC is enabled on Database, how will you do that?

99.     What is difference between Left Join and Inner Join?

100. What are SQL Constraints and which one you have used often? Explain with scenarios where do you need them?

101. Can we create triggers on temp table and system tables?

102. If you need to save more than two 2 GB file in SQL Table, which data type will you choose?

103. What is the lowest level of transaction lock Table, Row or Field?

104. Can we create unique constraint on more than one columns?

105. Can we create more than one unique constraint per table?

106. Can you use these functions in SQL Server and what they will return {fn NOW()},{fn CURRENT_TIMESTAMP()}. In TSQL they are equivalent to which functions?

107. What is difference between Convert () and format () function?

108. Can you create Primary key constraint on a column that has one null value?

109. Can you create Unique Constraint on a column that has one null value?

110. We can create only one cluster index per table, Is this statement true?

111. What is the difference between a trigger and stored procedure?

112. What are Deleted and Inserted tables in SQL Server? Can you update these tables?

113. I want to create only a table with one column and that should be identity (1,1) . After creating this table. I want to populate this table from 1 to 100000, what will be my script?

114. There are some missing values in our identity column, if I want to fill those gaps which strategy I should use?

115.  I have created a table with Identity (1, 1) and it has no record at the moment. If I want to insert a record with Id=100, How can I do that?

116. I have a table that has 300 million records; I need to delete 100 million records from this table, Explain different approaches those can be used?

117. What are different types of cursors available in SQL Server? Which one you use often?

118. If you do not want to write your code by using cursor, what alternatives you have in TSQL?

119. Define the steps in TSQL cursor?

120. What is Columnstore Index? Should be consider using Columnstore indexes in dataware house or in OLTP database?

121. To generate manual checkpoint which TSQL statement will you use?

122. You are doing performance tuning on your query, Can you display  Estimated Execution plan and Actual Execution plan together?

123. Can estimated execution plan be different from Actual execution plan?  And if yes what is the reason behind that?

124. What is difference between DMV and DMF?

125. Which system table or DMV you can use to find out all the tables those do not have cluster index?

126. What are benefits of using sp_executesql over EXEC?

127. What is federated database?

128. Can I update the column on which I have created columnstore index?

129. What is sql variant data type?

130.  Can you use ranking functions without using Order by clause?