Programming
DBMS Interview Questions and Answers for 2026
4.7 Rating 50 Questions 60 mins read110 Readers
Programming
4.7 Rating 50 Questions 60 mins read110 Readers
A Database Management System can be said to be a computerized data-keeping system. A user of this system can perform multiple types of operations on the system either to manipulate the data in the database or to manage the database structure itself. Database management systems are classified according to their structures and types.
Primary Key can be considered as the principal identifier for each row of the table, based on a specific column. A primary key implements a technical mechanism to ensure that each row has a unique, non-empty value in the primary key column. For example, if you try to add a new row with duplicate values in the primary key columns, the row will be rejected by the primary key.
Another feature of primary keys is controlled to avoid NULL values. NULL values are not allowed in primary key columns. Consider the example of Students table, studentID is the primary key. In other words, a primary key guarantee that every row in the table has unique, non-null values in the primary key columns.
You can add a primary key to a table during or after table creation if the primary key does not already exist. If columns that are part of a primary key do not have nullability defined, the Database Engine automatically configures them as NOT NULL.
SQL Server supports many ways to define a primary key when creating a table. If you want to define a primary key on only one column, you can include it as part of the column definition as shown in the following example.
CREATE TABLE Students ( studentId INT PRIMARY KEY, studentName VARCHAR(20) NOT NULL );
In the above example, the table named as Students is being created using SQL statements. In this table studentId column is defined as primary key.
The primary key data type must be numeric, integer, or fixed-width short characters. Uniqueness is important. A primary key must have unique values. This means that columns in other rows in the table do not contain the same value. These columns are preferable for a primary key.
While assigning primary key to the column we need to make sure to keep both data integrity and query performance in mind. A primary key not only guarantees the uniqueness of each row across a table, but it also helps in querying data and maintaining the database.
A PRIMARY KEY is a value that is used to uniquely identify a record or single row of a table, whereas a UNIQUE KEY is used to apply a unique value to a column or set of columns.
Here is the difference between PRIMARY KEY and UNIQUE KEY:
There is a major difference between intension and extension. Let’s discuss it one by one.
Data is collected (extracted, transformed, loaded) from heterogeneous sources and stored in databases. A data warehouse can be thought of as a central repository that receives data from transactional systems and other relational databases. Large business data can be correlated to provide better insight into business performance. A data warehouse is the core of business intelligence, a data analysis and reporting system.
This database is maintained separately from the standard production database. These are two separate systems, the second system is optimized for fast and accurate updates of real-time data, while the first is primarily suitable for offline operation and provides long-term data view over the time.
In general, it is fair to say that locks are primarily used to ensure that only one user/session can update a particular piece of data. Two types of locks can be discussed here: shared locks (S) and exclusive locks (X). These locks can be held on tables, pages, index keys, or single rows.
There is another concept related to locks called intent (I) locks. There are intent shared (IS) locks and intent exclusive (IX) locks. These locks allow finer-grained concurrency control. Technically we don't need them. S and X locks are sufficient, but they help optimize queries.
This is a regular feature in DBMS interview questions for freshers, be ready to tackle it.
The indexes are used to speed up the query process. Without them, the DBMS would have to do a full table scan, which would be very time-consuming.
A clustered index points to the physical storage of data. We can basically ask the DBMS to sort the rows by columns and physically store them in that order. As we can see, only one clustered index can be created per table. A clustered index provides fast sequential scans, so data can be retrieved very quickly. We can either create a custom clustered index or let the DBMS automatically create one clustered index using the primary key.
However, non-clustered indexes have nothing to do with physical storage. These indexes are sorted on columns and stored outside the table. We can think of these indexes as reference tables with two columns. One column is one ordered form of the table columns, and the other is the physical address (row address) in memory. For these indexes, when looking up a record, we can first find that index in the lookup table, then go to physical storage and get all the records associated with that row address.
In summary, non-clustered indexes are slower than clustered indexes because they involve an extra search step. Additional storage space is also required as these lookup tables must be stored. Another difference is that we can have one clustered index per table, while we can have as many non-clustered indexes as we want.
With respect to inter-dependencies, there are two types of sub-queries. We know that one inner query depends on the value of the outer query. This type of query is called a “correlated” query, and the other inner and outer queries are independent and called a "non-correlated" query.
We can easily say that, correlated sub-queries are very slow because the inner sub-query must be executed once for each row of the outer query.
Partitioning is the process of dividing a very large table into several smaller manageable pieces. Benefits of partitioning include faster queries, faster data loading, and faster purging of old data. The benefits of partitioning are limited by your choice of partition key and granularity.
There are two ways to split a table: horizontally and vertically. Vertical partitioning places different columns in different partitions, while horizontal partitioning places subsets of rows in different partitions based on the partition key. For example, company sales data can be split horizontally based on sales date.
Row based database stores the data on disk row-by-row, whereas columnar database stores the data column by column. Each method has its own advantages. The row-based is very fast and efficient for the operations on rows and column based is fast and efficient for the operations on columns, for example aggregating large volumes of data for a subset of columns.
The operations that need the complete row are writing operations for example INSERT, DELETE, UPDATE, etc. The operations that need columns are typically read operations for example SELECT, GROUP BY, JOIN, etc. From the above we can say that columnar database is good for analytical operations and row database is good for transaction processing.
Both OLTP and OLAP are online processing systems. OLTP stands for Online Transaction Processing and it is a system for managing transaction-oriented applications. OLAP stands for Online Analytical Processing and it is a system for managing analytical queries.
The main difference between the two systems is that OLTP is a write-intensive system and OLAP is a read-intensive system. This difference has significant implications for implementation. For example, it is very important to employ good concurrency control in an OLTP system, but for read-intensive operations this is not a big issue. Another difference between the two systems is that OLTP queries are very simple and return a relatively small number of records, whereas OLAP queries are very complex with many complex joins and aggregations.
Another difference is that OLTP systems, due to their real-time nature, often follow a distributed architecture to avoid single points of failure, whereas OLAP systems use a centralized architecture.
Besides, in most DBMS, OLTP is a row-based database and OLAP is a column-based database.
Isolation is the third characteristic of the ACID property. With this property, our goal is to make all transactions completely separate from each other (serializable). However, some applications do not require complete isolation. Therefore, we define some isolation levels that are less strict than full isolation. Generally, there are five isolation levels defined.
Snapshot: This level of isolation is different from the other isolation levels discussed so far. Others were based on locks and blocks. It does not use locks. With this isolation level, when a transaction modifies a row (that is, inserts, updates, and deletes), the committed version of the modified row is copied to the temporary database (tempdb) and versioned. This is also known as row versioning. Then, when another session tries to read the modified object, tempdb returns the committed version of the object to that operation.
If talking about snapshot isolation sounds fundamentally different than other isolation levels, then it is. Snapshot isolation is based on an optimistic model, while other isolation levels are based on a pessimistic concurrency control model. The optimistic model assumes conflicts are rare, chooses not to prevent conflicts, and deals with conflicts when they do occur.
A must-know for anyone heading into the technical round, this is a frequently asked Database interview question.
One of the most common DBMS viva questions, don't miss this one. Here is how you can explain these terms -
