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?
