1. Question: What is Microsoft SQL Server (MS SQL)?
- Answer: Microsoft
SQL Server is a relational database management system developed by Microsoft.
It is widely used for storing, retrieving, and managing data.
2. Question: Explain the difference between a clustered and
a non-clustered index in MS SQL Server.
- Answer: A
clustered index determines the physical order of data rows in a table and is
used for sorting and searching. A non-clustered index, on the other hand,
creates a separate structure for indexing without affecting the physical order
of data.
3. Question: What is the purpose of the T-SQL language in MS
SQL Server?
- Answer:
Transact-SQL (T-SQL) is a set of programming extensions to SQL used in MS SQL
Server. It includes additional features like procedural programming, local
variables, and error handling.
4. Question: How can you optimize a query in MS SQL Server?
- Answer: Query
optimization involves creating appropriate indexes, avoiding unnecessary joins,
using proper data types, and optimizing complex queries. Execution plans and
statistics can be analyzed to identify performance bottlenecks.
5. Question: What is the role of the SQL Server Agent in MS
SQL Server?
- Answer: SQL
Server Agent is a component in MS SQL Server that automates tasks, schedules
jobs, and manages alerts. It is used for tasks like database backups, data
integrity checks, and running scheduled jobs.
6. Question: Explain the concept of a stored procedure in MS
SQL Server.
- Answer: A stored
procedure is a precompiled collection of one or more SQL statements stored in
the database. It can accept parameters, return values, and be executed using a
single call, improving performance and code reusability.
7. Question: How does MS SQL Server handle transactions, and
what is the purpose of the `BEGIN TRANSACTION` statement?
- Answer: MS SQL
Server supports transactions using the `BEGIN TRANSACTION`, `COMMIT`, and
`ROLLBACK` statements. The `BEGIN TRANSACTION` statement marks the beginning of
a transaction, and `COMMIT` or `ROLLBACK` ends it.
8. Question: What is the purpose of the `sp_who` and
`sp_who2` stored procedures in MS SQL Server?
- Answer: `sp_who`
and `sp_who2` are stored procedures in MS SQL Server used to display
information about current user connections, sessions, and processes.
9. Question: Explain the concept of SQL Server Replication.
- Answer: SQL
Server Replication is a set of technologies for copying and distributing data
and database objects from one database to another. It ensures data consistency
across distributed systems.
10. Question: How can you monitor and optimize SQL Server
performance using SQL Server Profiler?
- Answer: SQL
Server Profiler is a tool used to monitor and analyze SQL Server events. It
captures events like query execution, stored procedure calls, and errors,
helping in performance analysis and optimization.
11. Question: What is the purpose of the SQL Server
Management Studio (SSMS)?
- Answer: SQL
Server Management Studio is a graphical tool for managing and interacting with
MS SQL Server. It provides a user interface for database development,
administration, and maintenance.
12. Question: Explain the concept of SQL Server Index
Fragmentation.
- Answer: Index
fragmentation occurs when data pages in an index are not in sequential order.
It can lead to decreased query performance. Fragmentation can be reduced using
index maintenance operations like rebuilding or reorganizing.
13. Question: What are the different types of backups
supported by MS SQL Server?
- Answer: MS SQL
Server supports full backups, differential backups, and transaction log
backups. Full backups include all data, differential backups capture changes
since the last full backup, and transaction log backups capture changes since
the last log backup.
14. Question: What is the purpose of the `NOLOCK` hint in MS
SQL Server queries?
- Answer: The
`NOLOCK` hint is used to allow a SELECT statement to read uncommitted data from
another transaction. It can improve query performance but may result in reading
uncommitted or "dirty" data.
15. Question: How does MS SQL Server handle deadlocks, and
what is a deadlock graph?
- Answer: MS SQL
Server uses a deadlock detection mechanism to identify and resolve deadlocks by
choosing a victim to roll back. A deadlock graph is a visual representation of
the deadlock, showing the involved processes and resources.
16. Question: Explain the purpose of the SQL Server CHECK
constraint.
- Answer: A CHECK
constraint in MS SQL Server is used to limit the values that can be placed in a
column. It ensures that data in the column satisfies a specific condition.
17. Question: What is SQL Server AlwaysOn Availability
Groups, and how does it enhance high availability?
- Answer: SQL
Server AlwaysOn Availability Groups is a high-availability and disaster
recovery feature. It allows multiple databases to be grouped together and
provides failover support, ensuring continuous availability.
18. Question: How can you secure a MS SQL Server database?
- Answer: Securing
MS SQL Server involves implementing strong authentication, configuring
firewalls, encrypting data in transit and at rest, using role-based access
control, and regularly applying security updates.
19. Question: What is the purpose of the SQL Server `MERGE`
statement?
- Answer: The
`MERGE` statement in MS SQL Server is used to perform insert, update, or delete
operations on a target table based on the results of a join with a source
table. It streamlines the process of handling changes in a data set.
20. Question: How does MS SQL Server handle the
"Tempdb" database, and why is it important?
- Answer:
"Tempdb" is a system database in MS SQL Server used to store
temporary objects, intermediate results, and temporary tables. It is crucial
for various SQL Server operations, including sorting, grouping, and joins.
Proper management of "Tempdb" is important for optimal performance.
No comments:
Post a Comment