MS SQL DBA Interview Questions and Answers

Today, where enormous amounts of data are generated daily, data is essential for making decisions about corporate operations. This post will review the most common MS SQL DBA interview questions. These questions were gathered following answers from experts in the field. Learn about how to create an SQL table here.

Explain database.

An ordered set of structured data that can be digitally stored, maintained, and retrieved from a local or distant computer system is called a database. Databases are constructed using a set design and modeling methodology and can be enormous and sophisticated. Large databases are housed on computer clusters or cloud storage, whereas smaller databases can be kept on a file system.

A relational database: what is it?

A relational database is an assemblage of data that are known to be related to one another. It is made up of columns and rows. In a database, every row represents a record, and each column contains data properties. A primary key is a special identification that is used to uniquely identify each row in a table. Additionally, foreign keys can be used to tie rows in one table to those in another. RDBMS vs. NoSQL might be useful to you to learn about this.

How do you set up an SQL Server database?

An ordered file of data is called a database. It is an assemblage of objects, code functions, tables, procedures, and schemas. A variety of query languages are available for accessing and modifying data. An object that holds data in a tabular (columns and rows) form is called a table in SQL Server. The SQL command CREATE DATABASE can be used to establish a new database. 

Syntax: CREATE DATABASE DatabaseName

Example: CREATE DATABASE Student

Alternatively, you can use the SQL Server Management Studio to build a database. Next, click right-click on Databases, choose New Database, and proceed with the wizard’s instructions. 

What is SQL Server’s database engine?

Several SQL Server components, including the SQL Server Database Engine and SQL Server Agent, are operated as services. Usually, these services are launched along with the operating system. Certain services are not started by default; therefore, this relies on what is stated during setup. An executable application that runs in the background on a system is called a service. Core operating system functions like Web serving, event logging, and file serving are typically provided via services. On a desktop computer, services can operate without displaying a user interface. Learning about the view and materialized view in SQL will be useful to you.

Which SQL clauses are frequently used with SELECT queries?

Here are a few common SQL clauses that go along with a SELECT query:

WHERE clause: In SQL, records that must meet particular requirements are filtered using the WHERE clause.

ORDER BY clause: Depending on the given field or fields, the ORDER BY clause in SQL is used to sort data in either ascending (ASC) or descending (DESC) order.

GROUP BY clause: Summarized database results can be obtained by using the GROUP BY clause in conjunction with aggregation techniques to group entries with identical data.

In SQL, records are filtered using the GROUP BY clause in conjunction with the HAVING clause. Since the WHERE clause is unable to filter aggregated records, it differs from WHERE.

What is a cursor? Tips for Using a Cursor

Declare a cursor after declaring any variables. The cursor definition must always be used in conjunction with a ‘SELECT’ statement.

Point the pointer over the result set to begin. The OPEN statement needs to be run to retrieve rows from the result set.

Use the FETCH command to fetch and move to the next row in the result set.

Use the CLOSE command to make the cursor inactive.

Lastly, eliminate the cursor definition and release the associated resources by using the DEALLOCATE command.

What distinguishes OLAP and OLTP from one another?

Online transaction processing is referred to as OLTP, whereas online analytical processing is known as OLAP. Whereas OLAP is an online database query response system, OLTP is an online database modification system.

Describe Left/Outer Join

This join method is used to obtain all rows or records from the left and the rows that match from the right. Learn more about joins in SQL here.

SELECT *

FROM Table_A A

LEFT JOIN Table_B B

ON A.col = B.col;

Explain Full or Outer Join with an example

The full or outer join function is used to obtain the records that match in either the right or left table.

SELECT *

FROM Table_A A

FULL JOIN Table_B B

ON A.col = B.col;

Describe the modes of a transaction in SQL Server.

A transaction represents one task. Changes made to the data during a transaction are committed and kept in the database indefinitely. Should a transactional error arise, the data modifications will be promptly reversed.

The modes of transaction are as follows:

• Autocommit transactions
• Explicit transactions
• Implicit transactions
• Batch-scoped transactions

A Stored Procedure: What Is It?

It’s a function composed of several statements that can be repeatedly stored and used. Procedures that have been stored can be retrieved and used as needed. The SQL Server database stores stored procedures as “named objects.” The parameters’ values determine the set of results produced by the stored method.

What is an extended stored procedure used for?

Generally speaking, stored procedures are code blocks that are accessible by straightforward application calls. Extended stored procedures, which use external functions written in C or C++, are also helpful in enhancing the capabilities of SQL servers. It enables the server to receive result sets and parameters from various data sources.

How can a primary key and a unique key be distinguished from one another?

A table’s main key provides a unique identity for each record in the table. It should not have NULL values, but it should have unique values. In addition, the unique key guarantees that each value in a column is distinct. Put simply, a column’s unique key prevents duplicate data entry, except for NULL values. Furthermore, a table may contain several unique keys in addition to its single primary key. Explore more about primary keys and foreign keys in SQL. 

What connection exists between a primary key and a foreign key?

A field (s) in a table that connects the main key to a different table is called a foreign key. The primary purpose of a foreign key is to keep two tables connected if one fails. A table with a foreign key is referred to as a child table, and a table with a main key is referred to as the parent table. Just as a primary key links with another table’s unique key, so too may a foreign key link with one.

Referential Integrity: What Is It?

Maintaining consistency in SQL databases is the goal of referential integrity. It is accomplished by enforcing relationships between data in tables through a set of rules. Foreign key constraints are typically used to enable the enforcement of referential integrity. Additionally, check restrictions with user-defined functions and triggers can be used to enforce them.

What does “ER Diagram” represent to you?

The Entity-Relationship diagram is the name given to the ER diagram. The logical relationships between the tables of a database are represented visually in this diagram. Table structures with the names of the columns and the corresponding data types, primary and foreign keys in use, and table linkages are shown in an ER diagram.

Useful Source: SQL Server DBA Course Syllabus

By updated, shared, and exclusive locks, what do you mean?

Shared locks: They permit the reading of only a page or row. It limits concurrent transactions’ ability to modify data.

Exclusive locks: They enable the use of DML statements like INSERT, UPDATE, and DELETE by exclusive transactions to change a page or record.

Updated locks: This helps prevent deadlocks. On a resource that already has an updated lock, you can apply a shared lock.

Describe COALESCE in SQL Server and list a few of its characteristics.

This expression only returns the first value that is not NULL after evaluating each argument in a list. Take into consideration the following, for example:

SELECT COALESCE (NULL, 14, 15);

The COALESCE expression now returns the first value, 14, which comes after a NULL value. Features of the expression COALESCE:

• The same data type is required.
• It may be the case expression’s syntactic quick cut.

Explain database mirroring and list its advantages.

A single database can be kept in two different places via database mirroring. The database is replicated across various SQL Server database engine server instances. It works with databases that use the complete recovery paradigm.

The following are some advantages of database mirroring:

• By using automatic failover and high-safety mode, it improves database availability.
• Regardless of the setting—high-performance mode or operating mode—it improves data protection.
• During updates, it makes the production database more available, which will drastically cut down on downtime.

Useful Link: Types of SQL Server Indexes

Conclusion

The majority of commonly asked MS SQL DBA interview questions and answers have been given in this blog. We hope that this blog has made it easy to read and understand the basic concepts of SQL servers. Join our MS SQL Server DBA training in Chennai at SLA.