SQL Server DBA Interview Questions and answers for beginners and experts. List of frequently asked SQL Server DBA Interview Questions with answers by Besant Technologies. We hope these SQL Server DBA Interview Questions and answers are useful and will help you to get the best job in the networking industry. This SQL Server DBA Interview Questions and answers are prepared by SQL Server DBA Professionals based on MNC Companies expectation. Stay tuned we will update New SQL Server DBA Interview questions with Answers Frequently. If you want to learn Practical SQL Server DBA Training then please go through this SQL Server DBA Training in Chennai.
Besant Technologies supports the students by providing SQL Server DBA Interview Questions and answers for the job placements and job purposes. SQL Server DBA is the leading important course in the present situation because more job openings and the high salary pay for this SQL Server DBA and more related jobs. We provide the SQL Server DBA online training also for all students around the world through the Gangboard medium. These are top SQL Server DBA Interview Questions and answers, prepared by our institute experienced trainers.
Here is the list of most frequently asked SQL Server DBA Interview Questions and answers in technical interviews. These questions and answers are suitable for both freshers and experienced professionals at any level. The questions are for intermediate to somewhat advanced SQL Server DBA professionals, but even if you are just a beginner or fresher you should be able to understand the answers and explanations here we give.
Q1) What is fundamental unit of SQL DB?
Q2) What is size of page?
Q3) What is the size of page header?
Q4) What are contains in page header?
Index of data, free space
Q5) How many types of pages are available?
Q6) What is Extent?
Extents are basic unit where space is allocated to tables and indexes
Q7) What is the size of extent?
8 contiguous pages or 64KB
Q8) How many system databases are there?
Q9) List the system Databases?
Master
Model
MSDB
Tempdb
Resource
Distribution
Q10) What is the basic syntax for create database?
Create database <databasename>
Q11) How to create database with specific file location?
Restore Database <database name> from disk='<Backup file location + file name>’
Q12) List few methods of HA technologies in SQL?
Backup, Restore, Replication, Mirroring, Log shipping, Clustering, etc,..
Q13) What is the syntax for drop database?
Drop database <database name>
Q14) What is database backup?
Backup is copy of data/database.
Q15) How many types of backup is there?
Q16) List the backup types?
Full /Database
Differential /Incremental
Transactional Log /Log
Q17)What are the methods for backup?
Q18) What is SSMS?
SQL Server Management Studio.
Q19) What are the services are available along with SQL server?
Reporting services, Analysis Services, Management Studio, Integration services, Full text search, browser, etc..
Q20) How to create new user in SQL server?
Create user <username> for login <loginname>
Q21) How to provide access to particular access?
Use <database name>
Grant <permissionname> on <objectname> to <username>
Q22) What is SQL server monitoring?
Monitoring refers to check database status, settings which may be the owner’s name, file names, file sizes, schedules, etc.
Q23) What are main services for SQL server?
SQL Server and SQL Server Agent
Q24) What are the methods to start services?
Services.msc and SQL Server Configuration Manager
Q25) What are the methods to stop services?
Services.msc, SQL Server Configuration Manager and SSMS
Q26) Why we cannot start services via SSMS?
Due to services already stopped state, we cannot access services via SSMS.
Q27) Whether can we stop SQL services without SQL Agent stop?
Agent Service is a dependent service, so it will stop first agent and then SQL services.
Q28) What is Replication?
The source data will be copied to destination through replication agents (jobs). Object level technology
Q29) What are the terminology used for Replication?
Publisher server
Distributor (Optional)
Subscriber server
Q30) What is log shipping?
The source data will be copied to destination via Transaction Log backup jobs. Database level tech.
Q31) What are the terminology used in log shipping?
- Primary server.
- Secondary server.
- Monitor server (Optional)
Q32) What is mirroring?
The primary data will be copied to secondary via network. Database level tech.
Q33) What are the terminology used for mirroring?
- Principal server.
- Mirror server.
- Witness server(Optional)
Q34) What is clustering?
The data will be stored in shared location which is used by both primary and secondary servers based on availability of the server. Instance level tech.
Q35) What are the terminology used for clustering?
- Active node.
- Passive node
Q36) What is Always ON Availability Groups?
The primary data will be copied to secondary via network. Group of database level tech.
Q37) What are the terminology used for Always ON Availability Groups?
- Primary is source server.
- Secondary is destination server
Q38) What is the usage of reporting services?
To create and publish various kinds of reports.
Q39) What are the environments used for reports?
Q40) What is execution plan?
Helps to statistics and processor tree. It is the result of query optimizer.
Q41) What are types of query estimation plans there?
there are two types of query estimation plans are there.
Q42) How many types of execution plans formats are available?
Ans : there are three formats of execution plans are there.
Q43) hat is the command to see the execution plan by user?
Q44) what is the syntax to get top 60 records in a table?
“Select top 60 * from table”
Q45) What is syntax to insert data into a table?
Insert into table(Column1, column2,etc)
Values(‘’,’’,etc)
Q46) How to delete current day records in a table which has timestamp column?
Delete from table where datediff(day,timestamp,getdate())=0
Q47) How to clear all records including data types in a table?
Q48) What is SSIS?
SQL Server Integration services
Q49) What is the usage of SSIS?
It is used to carry out (Extraction, Transform, Load data)ETL
Q50) What is the usage of Analysis service?
To analyse huge amounts of data and apply to business decisions.
