Microsoft SQL Server (DBA) basic interview Questions with Answers
Here are basic
Microsoft SQL Server interview questions with answers:
1. What is a primary
key in SQL Server?
Answer: A primary key is a unique
identifier for a row in a SQL Server table. It ensures that each row in the
table is uniquely identifiable and helps in maintaining data integrity.
2. What are the
different data types in SQL Server?
Answer: SQL Server supports various
data types such as int, varchar, char, float, date, datetime, bit, etc., for
storing different types of data such as integers, strings, dates, and more.
3. What is a clustered
index in SQL Server?
Answer: A clustered index in SQL
Server determines the physical order of data in a table. When a table has a
clustered index, the rows in the table are stored on disk in the same order as
the index, which can improve query performance.
4. What is a stored
procedure in SQL Server?
Answer: A stored procedure in SQL
Server is a pre-compiled set of SQL statements that are stored in the database
and can be executed with a single call. It is used for encapsulating complex
logic or tasks, and it can improve performance and security.
5. What is a foreign
key in SQL Server?
Answer: A foreign key in SQL Server
is a column or a set of columns that refers to the primary key of another
table. It establishes a relationship between two tables and ensures data
integrity by enforcing referential integrity rules.
6. What is a trigger
in SQL Server?
Answer: A trigger in SQL Server is a
special type of stored procedure that is automatically executed in response to
an event such as an INSERT, UPDATE, DELETE, or ALTER operation on a table.
Triggers are used for enforcing business rules or performing automated actions.
7. What is
normalization in SQL Server?
Answer: Normalization is the process
of organizing data in a relational database to eliminate redundancy and improve
data integrity. There are different normal forms (e.g., 1NF, 2NF, 3NF) that
define the rules for structuring tables and relationships in a relational
database.
8. What is a view in
SQL Server?
Answer: A view in SQL Server is a
virtual table that is defined by a query and does not store any data on its
own. It is used for simplifying complex queries, providing security by
restricting access to certain columns or rows, and encapsulating logic.
9. What is the
difference between UNION and UNION ALL in SQL Server?
Answer: UNION and UNION ALL are used
to combine the results of two or more SELECT statements in SQL Server. The main
difference is that UNION removes duplicate rows from the result set, while
UNION ALL includes all rows, including duplicates.
10. What is the difference
between DELETE and TRUNCATE in SQL Server?
Answer: DELETE and TRUNCATE are used
to delete data from a table in SQL Server. The main differences are that DELETE
is a logged operation, while TRUNCATE is minimally logged and more efficient
for deleting all data from a table. Also, DELETE can be used with a WHERE
clause to delete specific rows, while TRUNCATE deletes all rows.
11. What is an index in
SQL Server?
Answer: An index in SQL Server is a
database object that provides a fast and efficient way to look up data in a
table. It is used to speed up query performance by allowing the database engine
to quickly locate and retrieve data based on the values in one or more columns.
12. What is the
difference between CHAR and VARCHAR in SQL Server?
Answer: CHAR and VARCHAR are both
used to store character data in SQL Server, but the main difference is that
CHAR has a fixed length, while VARCHAR has a variable length. This means that
CHAR will always occupy the same amount of storage, even if the data stored in
it is shorter than the maximum length, whereas VARCHAR will only occupy the
storage needed for the actual data.
13. What is the purpose
of the SQL Server transaction log?
Answer: The SQL Server transaction
log is used to record all changes made to the database, including INSERT,
UPDATE, and DELETE operations, as well as changes to the database schema. It is
used for recovering the database in case of a failure and for providing
transactional consistency and durability.
14. What is a cursor in
SQL Server?
Answer: A cursor in SQL Server is a
database object that allows you to retrieve and manipulate rows from a result
set one at a time. It is typically used when you need to perform row-by-row
processing or when you need to update or delete rows in a result set.
15. What is the
difference between a heap and a clustered index in SQL Server?
Answer: A heap is a table in SQL
Server that does not have any clustered index, and the data is stored in an
unordered manner. On the other hand, a clustered index determines the physical
order of data in a table, and the data is stored on disk in the same order as
the index.
16. What is the purpose
of the SQL Server query optimizer?
Answer: The SQL Server query
optimizer is responsible for generating the most efficient execution plan for a
given SQL query. It analyzes the query and the available indexes, statistics,
and other information to determine the best way to retrieve and manipulate the
data, with the goal of optimizing query performance.
17. What is a temp
table in SQL Server?
Answer: A temp table in SQL Server is
a temporary table that is created and used within a single session or
connection. It is typically used for storing intermediate results during
complex queries or for temporary data storage that is not required to persist
beyond the current session.
18. What is the
difference between a scalar function and a table-valued function in SQL Server?
Answer: A scalar function in SQL
Server returns a single value, such as an integer or a string, whereas a
table-valued function returns a table as a result set. Scalar functions are
used for performing calculations on individual values, while table-valued
functions are used for returning sets of data that can be used in joins or
other operations.
19. What is the purpose
of the SQL Server transaction log backup?
Answer: The SQL Server transaction
log backup is used for capturing changes made to the transaction log since the
last full or differential backup. It is used for recovering the database to a
specific point in time and for maintaining a backup chain that allows for
point-in-time recovery in case of a failure.
20. What is a stored
procedure in SQL Server?
Answer: A stored procedure in SQL
Server is a pre-compiled, reusable set of SQL statements that are stored on the
server and can be executed with a single call. Stored procedures are used for
encapsulating business logic, improving performance, and enhancing security.
21. What is the
difference between INNER JOIN and OUTER JOIN in SQL Server?
Answer: INNER JOIN and OUTER JOIN are
two types of join operations in SQL Server:
· INNER JOIN: Returns only the rows that have matching values in both the
joined tables. It filters out the rows that do not have matching values in both
tables.
· OUTER JOIN: Returns all the rows from one table (called the
"outer" table) and the matching rows from the other table (called the
"inner" table). If there is no matching row in the inner table, NULL
values are returned.
22. What is a trigger
in SQL Server?
Answer: A trigger in SQL Server is a
special type of stored procedure that is automatically executed in response to
a specific event, such as an INSERT, UPDATE, DELETE, or TRUNCATE statement.
Triggers are used for enforcing data integrity, auditing changes, and automating
business logic.
23. What is the purpose
of the SQL Server Agent?
Answer: The SQL Server Agent is a
built-in job scheduling and automation tool in SQL Server that allows you to
automate tasks, such as backup and restore operations, database maintenance,
and data extraction. It provides a way to schedule and manage tasks that are
performed automatically by the SQL Server.
24. What is a view in
SQL Server?
Answer: A view in SQL Server is a
virtual table that is defined by a SELECT statement and stored on the server.
It does not contain any data of its own but provides a way to display the data
from one or more tables in a customized format. Views are used for simplifying
complex queries, providing data security, and encapsulating business logic.
25. What is the purpose
of the SQL Server Log Shipping?
Answer: SQL Server Log Shipping is a
disaster recovery solution that involves automatically sending transaction log
backups from a primary (source) server to one or more secondary (destination)
servers. The transaction log backups are then restored on the secondary servers
to keep them in sync with the primary server. Log Shipping is used for creating
a standby server for high availability and for offsite data protection.
26. What is the
difference between UNION and UNION ALL in SQL Server?
Answer: UNION and UNION ALL are two
set operations in SQL Server used for combining the results of two or more
SELECT statements:
· UNION: Returns only distinct rows from the combined result set, removing
duplicate rows.
· UNION ALL: Returns all rows from the combined result set, including
duplicate rows.
27. What is the purpose
of the SQL Server Index?
Answer: An index in SQL Server is a
database object that provides a fast and efficient way to look up and retrieve
data from a table based on the values in one or more columns. Indexes are used
for improving query performance, reducing the amount of data that needs to be
read from disk, and speeding up data retrieval.
28. What is the
difference between a primary key and a foreign key in SQL Server?
Answer: A primary key in SQL Server
is a unique identifier for a row in a table, used to uniquely identify each row
in the table. A foreign key, on the other hand, is a column or a set of columns
in a table that refers to the primary key of another table, establishing a
relationship between the two tables. In other words, a primary key is used to
uniquely identify rows in a table, while a foreign key is used to establish
relationships between tables.
29. What is a deadlock
in SQL Server?
Answer: A deadlock in SQL Server is a
situation where two or more transactions are blocked indefinitely, each waiting
for a resource that is locked by another transaction in the deadlock cycle.
This results in a stalemate, where none of the transaction can proceed, and the
system becomes unresponsive. Deadlocks can occur when transactions are
competing for shared resources, such as tables, row, or page-level locks, and
can lead to a performance degradation or even system failure if not resolved
timely.
Post a Comment