SQLGeeksPro

Hey there, this article is about Junior SQL Server DBA Interview Questions and Answers. This will help you learn and prepare for Interview. If you are an Interviewer then also it will help you to access a Junior DBA’s skill. You can ask these questions for 0 – 2 years of experience.

Please do not mug up the answers, always understand the concepts. That will help you to grow your technical skills. I have taken many interviews and it is easy to understand if the candidate has just read a blog without understanding the concepts. The below questions are from real Interviews hence learn well and all the best.

1. What is a Database?

Ans: A database is an collection of structured data. Electronically stored and organized in a computer system. The data can be easily accessed, managed, modified, updated and controlled.

Know more: https://en.wikipedia.org/wiki/Database

2.  What are the main differences between DBMS and RDBMS?

Ans: DBMS stands for Database Management System and RDBMS is the achroname for Relational Database Management System.

DBMS is basically a software to maintain/manage a database with ability to provide controlled access to the data. DBMS stores data as a file.

RDBMS is a an advanced version of DBMS where the data is organized in tables which are related to each other or linked together.

DBMS has low software and hardware requirements whereas RDBMS has higher hardware and software requirements

Any DBMS supports single users, while RDBMS supports multiple users.

DBMS does not support client-server architecture but RDBMS supports client-server architecture.

Examples of DBMS — MS Access, FoxPro, dBase etc.

Example of RDBMS – SQL Server, Oracle, MySQL etc.

3. What is a Transaction in SQL Server?

Ans: Transaction is a set of SQL statements/commands. i.e. Batch of SQL Statement(s) performing a single or multiple activities on a database. It can be a single SQL Statement as well.

Example:

4. What are ACID properties?

Ans: ACID stand for Atomicity, Consistency, Isolation, and Durability − commonly known as ACID properties of a transaction.

Atomicity:

Either all or none. Either the entire transaction takes place at once or doesn’t happen at all. There is nothing like partial completion of transactions.

e.g.  You are withdrawing money from ATM. The action (Transaction at the background) either has to complete or does not happen at all.

Consistency:

The database must be in consistent state before and after the transaction.

e.g. If you are transferring money from one account to another account. The total amount of both the accounts should be same before and after the transfer.

Example:
A transferring 100 dollars to B. A’s initial balance is 200 and B’s initial balance is 500.

Before the transaction:
Total of A+B = 200 + 500 = 700$

After the transaction:
Total of A+B = 100 + 6000 = 700$

Isolation: 

A transaction occurs independently. It shouldn’t interfere with the execution of another transaction. Isolation ensures the occurrence of multiple transactions concurrently without a database state leading to a state of inconsistency.

e.g. An account  A is having a balance of 500$ and two transactions started simultaneously to transfer 100$ each to account B and C.  Let’s say these transactions run concurrently and both the transactions read 500$ balance, in that case the final balance of A would be 400$ instead of 300$. So data will become inconsistent.

Isolation makes sure one of the transactions complete first followed by the second one. Hence second transaction would read the correct balance of A as 400$ then deduct 100$.

Durability:

Once a transaction completes successfully, the changes/updates it has made into the database is permanent even if there is a system failure.

e.g: Consider any of the above examples. Once the transaction completes even if the system fails/power down, the account balance will remain same.

5. What is DDL and DML statements?

Ans: DDL – Data Definition Language consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema. It is used to create and modify the structure of database objects in the database.

e.g. CREATE, DROP, ALTER, RENAME etc.

DML – Data Manipulation Language deals with the manipulation of data present in the database.

e.g. INSERT, UPDATE, DELETE etc.

6. What is the difference between Implicit, Explicit and Auto Commit transactions?

Ans: In the implicit transaction mode, SQL Server begins the transactions implicitly but it waits for the commit or rollback commands from the user.

e.g.
SET IMPLICIT_TRANSACTIONS ON
UPDATE T1
SET i=2 WHERE i=3
COMMIT;

Until you run the COMMIT statement the transaction will not complete.

In Explicit transaction mode, user must start the transaction with the BEGIN TRANSACTION statement and end with either COMMIT TRANSACTION or ROLLBACK TRANSACTION statements.

e.g.
BEGIN TRAN
UPDATE T1
SET i=2 WHERE i=3
COMMIT TRAN;


Auto Commit: This is the Default mode of SQL Server. When you run a query without specifying BEGIN TRAN/SET IMPLICIT_TRANSACTIONS, the transaction opens internally and closed automatically.

e.g.
UPDATE T1
SET i=2 WHERE i=3


7. What are system databases and User databases?

Ans: The databases which got installed during the Installation of SQL Server are called as system databases. These are basically Master, Model, Msdb, Tempdb and Resource db.

System databases are used to manage SQL Server and contain information about the SQL Server system like logins, databases, linked servers, jobs, schedules, reports, etc.

User databases are created by users/application. These databases contain the data of application.

8. Explain the use of system databases in SQL Server?

Ans:

Master Database:

Contains all the system-level information for an instance of SQL Server. The master database is the most important as it is necessary for starting the SQL Server.

This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.

Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.

Model Database: 

The model database is used as a template for other databases created in SQL Server, especially when creating user-defined databases.

The entire configuration of the model database, including database options, are copied to the newly created database.

Every time SQL Server is started, it creates tempdb from model hence if model is corrupted, SQL will not come up normally.

Msdb Database: 

The msdb database is used mainly by the SQL Server Agent, and also by other features like SQL Server Management Studio, Service Broker, and Database Mail.

SQL Server automatically maintains a complete online backup-and-restore history within tables in msdb.

Tempdb Database:

TempDB is a global resource that is accessed by all the users in the SQL Server Instance

This stores temporary user objects that you create like temporary tables (Global and Local), temporary variables, temporary stored procedures that hold intermediate results during the sorting or query processing, and cursors.

Internal objects like Work Tables, Work files. Intermediate sort results for operations such as creating or rebuilding indexes.

Version stores, which are collections of data pages that hold the data rows that support features for row versioning.

Resource database:

The Resource database is a read-only database that contains all the system objects that are included with SQL Server.

SQL Server system objects, such as sys.objects, are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

The Resource database does not contain user data or user metadata.

Continue reading Junior DBA Interview Q&A -Part2