Friday, August 21, 2015

SQL Server Interview Questions - Part1


In this article I have tried to cover all important questions generally asked in the interview. Please give your valuable suggestions and feedback to improve this article.I will write the answers for each question in future. 

1.            What are the different types of Logical Operator in SQL Server?
2.            What is Implicit and Explicit data conversion in SQL Server?
3.            What are correlated sub queries?
4.            What are Schemabinding views?
5.            What is the use of BREAK and CONTINUE keywords in While Loop?
6.            How to declare variable, assign value to a variable in SQL Server?
7.            What are the different steps to use Cursor?
8.            What is the difference between DDL and DML Triggers?
9.            What is a Trigger?
10.          What are the different types of Trigger?
11.          What is a view?
12.          What are the different types of View?
13.          What is an Index?
14.          What are the different types of Index?
15.          What is a linked Server?
16.          What is a Cursor?
17.          What is a Sub Query? Explain the properties of sub Query?
18.          What are the different types of Join?
19.          What is user defined Function?
20.          What is the difference between Stored Procedure and User defined function?
21.          What is an Identity column?
22.          What is the correct order of Query Process Phases?
23.          What are catalog views?
24.          What are Pessimistic and optimistic Locks?
25.          What are the different types of locks?
26.          What is the difference between an Update and Exclusive lock?
27.          What is NOLOCK hint?
28.          What is collation?
29.          What is the difference between INSTEAD OF TRIGGER and AFTER Trigger?
30.          What are the different types of SQL Commands?
31.          Which TCP/IP port does SQL Server run on? How can it be changed?
32.          What are the difference between clustered and a non-clustered index?
33.          What are the different index configurations a table can have?
34.          What are different types of Collation Sensitivity?
35.          What is OLTP (Online Transaction Processing)?
36.          What's the difference between a primary key and a unique key?
37.          What is difference between DELETE and TRUNCATE commands?
38.          When is the use of UPDATE_STATISTICS command?
39.          What is the difference between a HAVING CLAUSE and a WHERE CLAUSE?
40.          What are different Types of Sub-Queries?
41.          What is SQL Profiler?
42.          What are the authentication modes in SQL Server? How can it be changed?
43.          Which command using Query Analyzer will give you the version of SQL server and operating system?
44.          What is SQL Server Agent?
45.          Can a stored procedure call itself or recursive stored procedure? How much level SP nesting is possible?
46.          What is Log Shipping?
47.          Name 3 ways to get an accurate count of the number of records in a table?
48.          What does it mean to have QUOTED_IDENTIFIER ON? What are the implications of having it OFF?
49.          What is the difference between a Local and a Global temporary table?
50.          What is the STUFF function and how does it differ from the REPLACE function?
51.          What is PRIMARY KEY?
52.          What is UNIQUE KEY constraint?
53.          What is FOREIGN KEY?
54.          What is CHECK Constraint?
55.          What is NOT NULL Constraint?
56.          How to get @@ERROR and @@ROWCOUNT at the same time?
57.          What is a Scheduled Jobs or what is a Scheduled Tasks?
58.          What are the advantages of using Stored Procedures?
59.          What is a table called, if it has neither Cluster nor Non-cluster Index? What is it used for?
60.          Can SQL Servers linked to other servers like Oracle?
61.          What is BCP? When does it used?
62.          How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
63.          What is an execution plan? When would you use it? How would you view the execution plan?
64.          What is RDBMS?
65.          What are the properties of the relational tables?
66.          What is Normalization?
67.          What is De-Normalization?
68.          How is the ACID property related to the database?
69.          What are the different normalization forms?
70.          What is a Stored Procedure?
71.          What are the different types of collation sensitivity?
72.          What is dirty read?
73.          How do you check collation and compatibility level for a database?
74.          What is the difference between HAVING and WHERE clause?
75.          What is a B-Tree?
76.          What are the different Index configurations a table can have?
77.          What is a Filtered Index?
78.          What are indexed views?
79.          What is the restriction on Indexed view?
80.          What are Statistics? How can you find it in SQL Server?
81.          What is blocking?
82.          What is a deadlock?
83.          How do you identify and resolve deadlock?
84.          What is the maximum row size for a table?
85.          What are Spare columns?
86.          What is the maximum number of columns a table can have?
87.          What are included columns?
88.          What is INTERSECT operation?
89.          What is EXCEPT operation?
90.          What are GROUPING Sets?
91.          What are the row constructors inside SQL Server?
92.          What are table valued parameters?
93.          What is RAID?
94.          What is PIVOT and UN-PIVOT?
95.          What are the advantages of using Stored Procedure?
96.          What is the difference between COMMIT and ROLLBACK?
97.          What is Transaction?
98.          What is the difference between CHAR and VARCHAR?
99.          What is the difference between VARCHAR and VARCHAR (max)?
100.        What is SQL INJECTION?
101.        How do you find list of schema names and table names from the database?
102.        Why can be there only one clustered index?
103.        What is HINT?
104.        How do you delete duplicate records?
105.        How do you delete all tables?
106.        What is Aggregate function?
107.        What is the difference between Index Seek and Index Scan?
108.        Can we insert data if the clustered index is disabled?
109.        How to recompile a stored procedure at run time?
110.        Does the order of columns in UPDATE statement matters?
111.        What are the different types of System database inside SQL Server?
112.        How does SQL Server database engine work?
113.        What is the maximum number of indices per table?
114.        What is service Broker?
115.        What is CTE?
116.        What are the advantages of CTE?
117.        Can we write Sub Queries into Simple Select Using join and CTE?
118.        Can CTE be recursive? Till what level it can be nested?
119.        What does the Merge Statement do?
120.        What are the new data types introduced in SQL Server 2012?
121.        Define Hierarchyid data type?
122.        What are synonyms?
123.        What is LINQ?
124.        How do you do Error Handling in SQL server?
125.        What is RAISEERROR?
126.        What is XML?
127.        How can you find tables without Indexes?
128.        How do you find the size of index?
129.        How do you copy data from one table to another table?
130.        What are the limitations of Select Into clause?
131.        What is FileStream in SQL Server?
132.        What do you mean by TableSample?
133.        What is ROW_NUMBER()?
134.        What is ROLLUP clause?
135.        What are ranking functions?
136.        What is CDC?
137.        What is Auditing inside SQL Server?
138.        What is the difference between GETDATE() and SYSDATETIME()?
139.        How do you check if Automatic Statistic Update is enabled for a database?
140.        What are the limitations of view?
141.        What is a covered Index?
142.        When I delete data from a table, Does SQL Server reduce the size of table?
143.        How do you rebuild master database?
144.        What is standby server?
145.        How do you copy tables, schemas, and views from one sql server to another SQL Server?
146.        Where are SQL Server user names and passwords stored in?
147.        How do you disable Index?
148.        How do you enable index?
149.        What are WAIT Types?
150.        What is FILL Factor?
151.        What is Data Page?
152.        What is the use of Transaction Log file?
153.        What is the difference between view and Materialized view?
154.        What is database mirroring?
155.        What are the steps to create a Table Partition?
156.        What are the basics of Table Partitioning in SQL Server?
157.        How do you stop a log file from growing too big?
158.        How do we use DBCC commands?
159.        What is the difference between data mirroring and log shipping?
160.        What are the different backup options within SQL Server?
161.        How to add DEFAULT constraint on existing column? Write query
162.        How to add NOT NULL constraint on existing column? Write query
163.        Can we have Triggers on Temp table and Variable Table?
164.        Can we create view on TEMP table?
165.        Can we use constraints on TEMP Table?
166.        How many maximum Identity columns we can have in a single table?
167.        How to find all Triggers in database?
168.        Can we add constraint on Variable Table?
169.        How to take database online –offline?
170.        How to copy data using Bulk copy when columns data type doesn’t match?
171.        What is SP_Configure commands and SET commands?
172.        What is Dynamic SQL?
173.        How to execute Dynamic SQL?
174.        Can we use Variable table in Dynamic SQL?
175.        How to execute queries stored in a table?
176.        How can you capture the length of column when it is text, image and ntext data type?
177.        Is it possible to import data using TSQL?
178.        How can you prevent TSQL code from running on a Production server?
179.        How to disable Auto Commit in SQL Server?
180.        Can we recover deleted data?
181.        Can we create a table name as Table?
182.        How to delete Top 100 records from a table?
183.        How to delete two tables using one Drop command?
184.        How can we find the table size?
185.        How to find N highest salary?
186.        Why do we use DECLARE for cursor and Variable table?
187.        What are the different data types in SQL Server?
188.        Which is fast UNION or UNION ALL?
189.        Can we create clustered index on view?
190.        Can we create computed columns?
191.        What is Schema?
192.        Can we create multiple constraints on a single column?
193.        Can we rename table and column using ALTER command?
194.        How to rename Table and Column?
195.        How to rename Database?
196.        Can we change the Column Sequence order inside table?
197.        Truncate is DDL or DML?
198.        What is difference between Deterministic and Non Deterministic Functions?
199.        What is Synchronous and asynchronous function?
200.        Can we add Identity column after creating the table?
201.        Can we drop Identity column?
202.        How can we disable Identity column?
203.        Can Foreign key column have NULL?
204.        How to find column description of a table?
205.        How to find primary key name if not given by the user for a particular column?
206.        Can we add two columns using ALTER command?
207.        How to join two tables from different database?
208.         Can we use ORDER BY Clause in UNION?
209.        How to get row number without ROW_NUMBER function?
210.        What is Partitioned View?
211.        What is WITH CHECK OPTION on view?
212.        Can we create view from view?
213.        Can we ALTER two columns using ALTER command?
214.        Can we store Image, MP3 and binary data in SQL Server?
215.        How to clean Buffer in SQL Server?
216.        How to clear Execution Plan cache?
217.        Can I create CTE in Trigger?
218.        Can we create Variable table in Trigger?
219.        Can we use cursors in Trigger?
220.        Can we call Stored Procedure in Trigger?
221.        Can I create Triggers on TEMP table?
222.        Can we use PRINT Command in Triggers?
223.        How Triggers are fired?
224.        Can Inserted table have multiple records?
225.        Can we perform DML & DDL operation on Inserted and Deleted tables?
226.        What is the advantage of Index?
227.        What is the disadvantage of Index?
228.        How do you find why query is running slow?
229.        How to create foreign key constraints on temporary table?
230.        How can we check for existence of any object in database?
231.        What is meant by differed name resolution in SQL Server?
232.        What is the use of SP_Helptext , SP_HelpIndex stored procedure?
233.        Can we change order of triggers?
234.        How to find Organization Employee Hierarchy using SQL?
235.        How does a recursive CTE works?
236.        How to delete Duplicate records?
237.        How to find employees hired in last month?
238.        How to find all rows that contains only numeric data?
239.        How to find department with highest number of employees?
240.        How to join 3 tables?
241.        What is the real time example of RIGHT Outer Join?
242.        How to find who deleted/ dropped from Transaction log?
243.        What are different operation available on ONDELETE and ONUPDATE?
244.        What are the uses of System tables?
245.        What is the difference between UNIQUE Key and Primary Key?
246.        What is the difference between TEMP tables and Variable Tables?
247.        What are the different String Functions in SQL Server?
248.        What are the different Date functions in SQL Server?
249.        What are the different Mathematical Functions in SQL Server?

250.        What is the difference between COUNT and COUNT_BIG?