SQL Database Administrator

filter-iconFilter interviews by

50+ SQL Database Administrator Interview Questions and Answers

Updated 19 Jan 2025

Popular Companies

search-icon

Q1. What is a database management System and what is concept of primary key and foreign key?

Ans.

A database management system (DBMS) is a software that manages and organizes databases. Primary key uniquely identifies a record, while foreign key establishes a relationship between tables.

  • A DBMS is a software that allows users to create, manage, and manipulate databases.

  • It provides tools for creating, modifying, and deleting databases, tables, and records.

  • Primary key is a unique identifier for a record in a table.

  • Foreign key establishes a relationship between two tables by ...read more

Q2. Diff composite and non composite key and then to create primary key without defining primary key what are other alternatives. Ana - Like. Unique, not null, candidate key.

Ans.

Composite key is made up of multiple columns while non-composite key is made up of a single column. Primary key can also be defined using unique, not null, or candidate key.

  • Composite key uses multiple columns to uniquely identify a row while non-composite key uses a single column

  • Primary key can be defined using unique, not null, or candidate key instead of explicitly defining it

  • Candidate key is a unique key that can be used as a primary key

SQL Database Administrator Interview Questions and Answers for Freshers

illustration image

Q3. how to create new users/ login, how to create new job, what are the job involved in log shipping and replication, how to optimize query, how to check slowness, how to add login in standby database, how to add a...

read more
Ans.

The question is about SQL Database Administration and covers various topics such as user management, job creation, query optimization, replication, troubleshooting, and backup/restore processes.

  • To create new users/login, use the CREATE LOGIN and CREATE USER statements.

  • To create a new job, use the SQL Server Agent or SQL Server Management Studio (SSMS).

  • Log shipping involves jobs like backup, copy, and restore. Replication involves jobs like snapshot, log reader, and distributi...read more

Q4. Which programming language do you prefer and why?

Ans.

I prefer SQL as it is the language of databases and allows for efficient data management.

  • SQL is specifically designed for managing and querying databases

  • It allows for efficient data retrieval and manipulation

  • Other programming languages can be used in conjunction with SQL for more complex tasks

  • Examples of other languages commonly used with SQL include Python and Java

Are these interview questions helpful?

Q5. What is Maps, Hash maps, Skeleton class OOPS concepts?

Ans.

Maps, Hash maps, and Skeleton class are OOPS concepts used in programming.

  • Maps are data structures that store key-value pairs.

  • Hash maps are a type of map that use a hash function to index the keys.

  • Skeleton class is a class that provides a basic implementation of an interface.

  • OOPS concepts are Object-Oriented Programming concepts that focus on objects and their interactions.

  • Example: HashMap in Java is a commonly used implementation of a hash map.

Q6. Recursive stored Procedure Optimising techniques Recovery and backup. How to do Loading of Jason file in sql How is Scheduling done On/off set count with curser example.

Ans.

Questions on SQL Database Administration including recursive stored procedures, optimization, recovery and backup, loading JSON files, scheduling, and cursor examples.

  • For recursive stored procedures, use common table expressions (CTEs) to simplify the code

  • Optimization techniques include indexing, query tuning, and partitioning

  • Regular backups and testing of recovery procedures are essential for data protection

  • To load JSON files in SQL, use OPENROWSET or BULK INSERT

  • Scheduling c...read more

Share interview questions and help millions of jobseekers 🌟

man-with-laptop

Q7. What is clustered and non clustered index?

Ans.

Clustered index determines the physical order of data in a table, while non-clustered index is a separate structure.

  • Clustered index sorts and stores the data rows in the table based on their key values.

  • Non-clustered index creates a separate structure that contains the key values and a pointer to the data row.

  • A table can have only one clustered index, but multiple non-clustered indexes.

  • Clustered index is faster for retrieving large amounts of data, while non-clustered index is...read more

Q8. What is the purpose of minimum memory configuration

Ans.

Minimum memory configuration is used to allocate the minimum amount of memory required for SQL Server to run.

  • Minimum memory configuration is used to ensure that SQL Server has enough memory to function properly.

  • It is important to set the minimum memory configuration to a value that is appropriate for the workload of the server.

  • If the minimum memory configuration is set too low, SQL Server may experience performance issues.

  • For example, if a server has 16GB of RAM and the minim...read more

SQL Database Administrator Jobs

SQL DBA 4-9 years
Capgemini
3.7
Pune
Mssql DBA 3-6 years
Cognizant
3.7
Hyderabad / Secunderabad
Mssql DBA 6-11 years
Cognizant
3.7
Hyderabad / Secunderabad

Q9. Diff in clustered index and non clustered index with example

Ans.

Clustered index determines physical order of data while non-clustered index is a separate structure.

  • Clustered index sorts and stores data in the table based on the key values

  • Non-clustered index creates a separate structure to store the index data

  • Clustered index can only be created on one column while non-clustered index can be created on multiple columns

  • Example: Clustered index on a customer table can be created on the customer ID column while a non-clustered index can be cre...read more

Q10. How do you determine which issue to prioritize when faced with multiple problems?

Ans.

I prioritize issues based on impact, urgency, and complexity.

  • Assess the impact of each issue on the system or users

  • Consider the urgency of resolving each issue

  • Evaluate the complexity of fixing each issue

  • Prioritize critical issues that have high impact, urgency, and low complexity

  • Create a priority list based on these factors

Q11. What are joins in SQL, and what are the different types?

Ans.

Joins in SQL are used to combine rows from two or more tables based on a related column between them.

  • Types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN.

  • INNER JOIN returns rows when there is at least one match in both tables.

  • LEFT JOIN returns all rows from the left table and the matched rows from the right table.

  • RIGHT JOIN returns all rows from the right table and the matched rows from the left table.

  • FULL JOIN returns rows when there is a match in one of t...read more

Q12. What is the method to find the third highest salary in a dataset?

Ans.

Use the SQL query with ORDER BY and LIMIT to find the third highest salary.

  • Use the SQL query: SELECT DISTINCT Salary FROM Employees ORDER BY Salary DESC LIMIT 2, 1

  • The above query will return the third highest salary from the 'Employees' table

  • Make sure to replace 'Employees' and 'Salary' with the appropriate table and column names

Q13. 1. replication types and jobs 2. Transitional replication how it's work and which job run and what's is important

Ans.

Replication types and jobs, and how transactional replication works

  • Replication types include snapshot, transactional, and merge

  • Snapshot replication copies the entire database at a specific point in time

  • Transactional replication copies changes as they occur

  • Merge replication combines changes from multiple sources

  • Transactional replication uses a distributor to send changes to subscribers

  • Important jobs for transactional replication include the Log Reader Agent and Distribution Ag...read more

Q14. Define SQL and its version and how to install sql server

Ans.

SQL is a relational database management system used to manage and store data.

  • SQL stands for Structured Query Language

  • Versions include SQL Server 2019, 2017, 2016, etc.

  • To install SQL Server, download the installation file from Microsoft's website

  • Run the installation file and follow the prompts to complete the installation process

Q15. What are the system databases?

Ans.

System databases are the default databases created during SQL Server installation.

  • System databases include master, model, msdb, and tempdb.

  • Master database stores system-level information.

  • Model database is used as a template for new databases.

  • Msdb database is used for SQL Server Agent and maintenance plans.

  • Tempdb database is used for temporary storage of data.

  • System databases cannot be deleted or detached.

  • Additional system databases may be created by third-party software.

  • Syste...read more

Q16. How do you manage multiple clients simultaneously?

Ans.

I prioritize tasks, set clear expectations, utilize time management tools, and communicate effectively.

  • Prioritize tasks based on urgency and importance

  • Set clear expectations with clients regarding response times and availability

  • Utilize time management tools such as calendars and task lists

  • Communicate effectively with clients to provide updates and manage expectations

Q17. What are joins in SQL?

Ans.

Joins in SQL are used to combine data from two or more tables based on a related column.

  • Joins are used to retrieve data from multiple tables in a single query.

  • There are different types of joins such as inner join, left join, right join, and full outer join.

  • The join condition is specified using the ON keyword and the related columns.

  • Example: SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

  • Joins can be used to combine data from more than two tables as we...read more

Frequently asked in,

Q18. What is Synchronisation in JAVA?

Ans.

Synchronization in Java is a process of controlling access to shared resources by multiple threads.

  • Synchronization is achieved using synchronized keyword or locks.

  • It prevents race conditions and ensures thread safety.

  • Example: synchronized methods, synchronized blocks, ReentrantLock.

  • Synchronization can cause performance issues if not used properly.

Q19. What are SQL editions?

Ans.

SQL editions are different versions of SQL Server with varying features and capabilities.

  • SQL Server Express - free, limited to 10GB database size

  • SQL Server Standard - basic features for small to medium businesses

  • SQL Server Enterprise - advanced features for large businesses

  • SQL Server Developer - full-featured version for development and testing

  • SQL Server Web - designed for web applications

  • SQL Server Business Intelligence - specialized for data analysis and reporting

Q20. 1. Dr dill steps on log shipping 2. Performance tuning

Ans.

Log shipping and performance tuning are important aspects of SQL Database Administration.

  • Log shipping is a process of automating the backup of transaction log files on a primary database server and then restoring them onto a secondary server.

  • Performance tuning involves identifying and resolving performance issues in a database system, such as slow queries or inefficient indexing.

  • Techniques for performance tuning include query optimization, index tuning, and server configurati...read more

Q21. What is Database Indexing and types of Indexing

Ans.

Database indexing is a way to improve query performance by creating a copy of a subset of data sorted in a specific order.

  • Indexing is used to speed up data retrieval operations

  • Types of indexing include clustered, non-clustered, unique, and full-text

  • Clustered index determines the physical order of data in a table

  • Non-clustered index creates a separate structure to store the index data

  • Unique index ensures that each value in the indexed column is unique

  • Full-text index is used to ...read more

Q22. What is default fill factor?

Ans.

Default fill factor is the percentage of space reserved for future data growth in an index.

  • It is set to 0 by default, which means the index will be filled to 100% capacity.

  • A fill factor of 80% means that 20% of the index space will be left empty for future growth.

  • It can be set at the server, database, or index level.

  • It can affect performance and disk space usage.

Q23. Data migration from SQL to azure which is best

Ans.

The best method for data migration from SQL to Azure depends on the specific requirements and constraints of the project.

  • Consider the size and complexity of the database to determine the most suitable migration approach.

  • Options include using Azure Database Migration Service, Azure Data Factory, or manual migration scripts.

  • Evaluate factors such as downtime tolerance, data consistency, and cost when choosing the migration method.

  • Test the migration process thoroughly before exec...read more

Q24. What is different between truncate and delete

Ans.

Truncate is faster and cannot be rolled back, while delete is slower and can be rolled back.

  • Truncate is a DDL command, while delete is a DML command.

  • Truncate removes all rows from a table, while delete removes specific rows based on a condition.

  • Truncate resets identity column values, while delete does not.

  • Truncate cannot be used with a WHERE clause, while delete can.

  • Truncate is faster than delete as it does not log individual row deletions.

  • Delete can be rolled back using a tr...read more

Q25. What all processes occurres when a db restarts.

Ans.

When a database restarts, various processes occur such as recovery, rollback, and reapplying transactions.

  • Recovery process ensures that the database is in a consistent state by rolling back uncommitted transactions and redoing committed transactions.

  • Rollback process undoes any changes made by transactions that were not committed before the restart.

  • Reapplying transactions involves re-executing transactions that were committed but not yet reflected in the database.

  • System checks...read more

Q26. what is backup? Explain different types of normalization?

Ans.

Backup is the process of creating a copy of data to protect against data loss.

  • Different types of backups include full, differential, and incremental backups.

  • Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity.

  • There are different levels of normalization, including first normal form (1NF), second normal form (2NF), and third normal form (3NF).

  • Normalization helps to prevent data inconsistencies and anomalies.

  • Example: A cu...read more

Q27. How to troubleshoot high CPU and Memory

Ans.

To troubleshoot high CPU and Memory, monitor performance metrics, identify resource-intensive queries, optimize queries and indexes, check for memory leaks.

  • Monitor performance metrics using tools like SQL Server Profiler, Performance Monitor, or DMVs.

  • Identify resource-intensive queries using tools like SQL Server Management Studio or query execution plans.

  • Optimize queries and indexes by rewriting queries, adding missing indexes, or removing unused indexes.

  • Check for memory lea...read more

Q28. What is spark architecture and partitions

Ans.

Spark architecture refers to the structure of Apache Spark framework, while partitions are units of data distribution for parallel processing.

  • Spark architecture includes components like Driver, Executor, and Cluster Manager

  • Partitions are units of data distribution for parallel processing in Spark RDDs

  • Increasing the number of partitions can improve parallelism and performance

  • Example: In Spark, data is divided into partitions which are processed in parallel by different executo...read more

Q29. What is replication deadlock performance tuning

Ans.

Replication deadlock performance tuning involves optimizing the performance of replication processes to prevent deadlocks.

  • Identify and resolve any blocking or deadlock issues in replication processes

  • Optimize the configuration settings for replication to improve performance

  • Monitor replication performance regularly to identify and address any bottlenecks

  • Consider implementing parallel replication to improve efficiency

  • Use tools like SQL Server Profiler to analyze and troubleshoot...read more

Q30. What is Login and User ?

Ans.

Login is the process of gaining access to a system, while User is an account that can be used to access a system.

  • Login is the initial step to access a system or database

  • User is an account that is created to access a system or database

  • Login is used to authenticate a user's identity

  • User is used to define the permissions and access levels for a user

  • A user can have multiple logins, but a login can only be associated with one user

Q31. What is DAG, DNN and VNN

Ans.

DAG stands for Directed Acyclic Graph, DNN stands for Deep Neural Network, and VNN stands for Virtual Neural Network.

  • DAG is a graph with directed edges and no cycles, commonly used in data processing.

  • DNN is a type of artificial neural network with multiple hidden layers, used in deep learning.

  • VNN is a virtual neural network, a concept that may refer to a neural network implemented in a virtual environment.

Q32. Difference between C/C++/JAVA

Ans.

C/C++ are low-level languages, while JAVA is high-level. C/C++ are compiled, while JAVA is interpreted.

  • C/C++ are used for system programming and embedded systems.

  • JAVA is used for web development and enterprise applications.

  • C/C++ have manual memory management, while JAVA has automatic garbage collection.

  • C/C++ have pointers, while JAVA does not.

  • C/C++ code is platform-dependent, while JAVA code is platform-independent.

  • Examples: C/C++ - operating systems, device drivers, JAVA - w...read more

Q33. Difference between view and stored procedure

Ans.

Views are virtual tables that display data from one or more tables, while stored procedures are precompiled SQL statements that can accept parameters and perform actions.

  • Views are read-only and cannot accept parameters, while stored procedures can be used to perform complex logic and can accept input parameters.

  • Views are used for simplifying complex queries and providing a layer of abstraction, while stored procedures are used for encapsulating business logic and improving pe...read more

Q34. What is CSS and HTML?

Ans.

CSS and HTML are languages used for creating and styling web pages.

  • HTML (Hypertext Markup Language) is used for creating the structure of a web page.

  • CSS (Cascading Style Sheets) is used for styling the elements on a web page.

  • HTML uses tags to define elements like headings, paragraphs, and images.

  • CSS allows for customization of colors, fonts, layouts, and more.

  • Both HTML and CSS are essential for web development.

Q35. Difference between index and statistics

Ans.

Index is used to speed up data retrieval while statistics are used by the query optimizer to create an efficient query plan.

  • Indexes are created on columns to speed up data retrieval

  • Statistics are used by the query optimizer to create an efficient query plan

  • Indexes are used to enforce uniqueness and primary key constraints

  • Statistics are used to estimate the number of rows returned by a query

  • Indexes can be clustered or non-clustered

  • Statistics can be manually updated using the U...read more

Q36. How to configure replication

Ans.

Replication can be configured by setting up a publication, creating a subscription, and initializing the subscription.

  • Set up a publication by selecting the database objects to replicate and defining the publication properties.

  • Create a subscription by specifying the subscriber server, database, and synchronization schedule.

  • Initialize the subscription by generating a snapshot of the publication and applying it to the subscriber.

Q37. What is service account

Ans.

A service account is a special account used by a service or application to access resources on a network.

  • Service accounts are used to run services or applications with the necessary permissions and access rights.

  • Service accounts are often used in database administration to allow the database service to interact with the operating system.

  • Service accounts can be configured with specific permissions and restrictions to limit access to resources.

  • Examples of service accounts inclu...read more

Q38. What is DBCC ? Explain

Ans.

DBCC stands for Database Console Commands. It is a set of commands used to perform maintenance tasks on a SQL Server database.

  • DBCC CHECKDB - used to check the logical and physical integrity of all objects in a database

  • DBCC SHRINKDATABASE - used to shrink the size of a database

  • DBCC SQLPERF - used to display performance statistics for SQL Server

  • DBCC TRACEON - used to enable a specific trace flag for troubleshooting purposes

Q39. Recovery of corrupt System Database

Ans.

Recovery of corrupt System Database involves restoring from backups, running repair utilities, and potentially rebuilding the database.

  • Check for recent backups and restore the database from a known good backup

  • Run repair utilities like DBCC CHECKDB to identify and fix corruption

  • If necessary, rebuild the database by exporting data, dropping the corrupt database, and recreating it

Q40. What is rdd, trsnsformations

Ans.

RDD (Resilient Distributed Dataset) is a fundamental data structure in Apache Spark for distributed data processing. Transformations are operations that create a new RDD from an existing one.

  • RDD is a fault-tolerant collection of elements that can be operated on in parallel.

  • Transformations are functions that produce a new RDD by applying a computation on the existing RDD.

  • Examples of transformations include map, filter, reduceByKey, etc.

Q41. Sql dmvs used in performance tuning

Ans.

SQL DMVs are dynamic management views that provide information about the current state of a SQL Server instance for performance tuning.

  • DMVs can be used to identify performance bottlenecks, monitor resource usage, and troubleshoot issues.

  • Examples of commonly used DMVs include sys.dm_exec_query_stats, sys.dm_os_wait_stats, and sys.dm_io_virtual_file_stats.

Q42. The Architecture of SQL Server

Ans.

The architecture of SQL Server involves components like storage engine, query processor, and buffer manager.

  • SQL Server architecture consists of multiple components such as storage engine, query processor, and buffer manager.

  • The storage engine manages data storage, retrieval, and modification.

  • The query processor processes SQL queries and generates execution plans.

  • The buffer manager is responsible for managing data cache in memory.

  • SQL Server also includes components like securi...read more

Q43. What is SQL?

Ans.

SQL is a programming language used to manage and manipulate relational databases.

  • SQL stands for Structured Query Language

  • It is used to create, modify, and query databases

  • Commonly used in web development and data analysis

  • Examples of popular SQL databases include MySQL, Oracle, and Microsoft SQL Server

Q44. What is architecture of SQL

Ans.

SQL architecture refers to the design and structure of the SQL database system.

  • SQL architecture includes components such as storage engines, query processors, and transaction managers.

  • The architecture can vary depending on the specific database management system being used.

  • SQL architecture can be divided into logical and physical components.

  • Logical architecture includes the schema, tables, and relationships between them.

  • Physical architecture includes the storage and access me...read more

Q45. Recent issues and resolution

Ans.

Resolved recent issues related to database performance by optimizing queries and increasing server resources.

  • Identified slow performing queries and optimized them by adding indexes or rewriting them

  • Increased server resources such as CPU, memory, or storage to improve performance

  • Implemented query caching or stored procedures to reduce load on the database server

Q46. What is cursor ?

Ans.

A cursor is a database object used to retrieve and manipulate data row by row.

  • Used in SQL to fetch and process individual rows from a result set

  • Can be used to update or delete specific rows in a table

  • Can be declared, opened, fetched, and closed in SQL

Q47. Troubleshooting around log file

Ans.

Troubleshooting log file issues in SQL databases

  • Check for any errors or warnings in the SQL Server error log

  • Monitor the size of the log file and ensure it has enough space

  • Verify that the log file is not corrupted or inaccessible

  • Review the database recovery model to ensure it is appropriate for the workload

  • Consider shrinking the log file if it has grown too large

Q48. What is dirty read.

Ans.

Dirty read is when a transaction reads data that has been modified by another transaction but not yet committed.

  • Occurs when a transaction reads uncommitted data from another transaction

  • Can lead to inconsistent or incorrect results

  • Can be prevented by using proper isolation levels in database transactions

Q49. What is data base

Ans.

A database is a structured collection of data that is organized and stored for easy access, management, and retrieval.

  • A database is used to store and manage large amounts of data.

  • It consists of tables, rows, and columns to organize data in a structured manner.

  • SQL (Structured Query Language) is commonly used to interact with databases.

  • Examples of databases include MySQL, Oracle, and SQL Server.

Q50. Current ctc n expected ctc

Ans.

The current CTC (Cost to Company) and expected CTC (Cost to Company) are typically discussed during salary negotiations.

  • Be honest about your current salary and provide a realistic expectation for your future salary.

  • Consider factors such as your experience, skills, industry standards, and the company's budget when discussing CTC.

  • It is important to have a clear understanding of your worth and negotiate accordingly.

1
2
Next
Interview Tips & Stories
Ace your next interview with expert advice and inspiring stories

Interview experiences of popular companies

3.7
 • 10.4k Interviews
3.8
 • 8.1k Interviews
3.6
 • 7.5k Interviews
3.7
 • 5.6k Interviews
3.7
 • 4.8k Interviews
3.5
 • 3.8k Interviews
4.0
 • 2.3k Interviews
3.7
 • 847 Interviews
3.3
 • 519 Interviews
View all

Calculate your in-hand salary

Confused about how your in-hand salary is calculated? Enter your annual salary (CTC) and get your in-hand salary

Recently Viewed
LIST OF COMPANIES
Credit Bajaar
Overview
PHOTOS
InsuranceDekho
3 office photos
INTERVIEWS
Oracle
No Interviews
REVIEWS
Stream Global Services
No Reviews
SALARIES
Stream Global Services
SALARIES
Stream Global Services
JOBS
Ujjivan Small Finance Bank
No Jobs
SALARIES
Stream Global Services
No Salaries
INTERVIEWS
eClerx
No Interviews
DESIGNATION
SQL Database Administrator Interview Questions
Share an Interview
Stay ahead in your career. Get AmbitionBox app
qr-code
Helping over 1 Crore job seekers every month in choosing their right fit company
65 L+

Reviews

4 L+

Interviews

4 Cr+

Salaries

1 Cr+

Users/Month

Contribute to help millions

Made with ❤️ in India. Trademarks belong to their respective owners. All rights reserved © 2024 Info Edge (India) Ltd.

Follow us
  • Youtube
  • Instagram
  • LinkedIn
  • Facebook
  • Twitter