Our Special Offer - Get 3 Courses at 24,999/- Only. Read more
Hire Talent (HR):+91-9707 240 250

Interview Questions

SQL Server DBA Interview Questions and Answers

SQL Server DBA Interview Questions and Answers

SQL Server DBA Interview Questions and answers

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.

Best SQL Server DBA Interview Questions and answers

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.

SQL Server DBA Interview Questions and answers for the job placements

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?

Page

Q2) What is size of page?

8 Kb

Q3) What is the size of page header?

96 bytes.

Q4) What are contains in page header?

Index of data, free space

Q5) How many types of pages are available?

9

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?

4+2

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?

3

Q16) List the backup types?

Full /Database
Differential /Incremental
Transactional Log /Log

Q17)What are the methods for backup?

T-SQL & SSMS

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?

The BIDS and SSDT

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.

  • Estimated
  • Actual
Q42) How many types of execution plans formats are available?

Ans : there are three formats of execution plans are there.

  • Graphical
  • Text
  • XML
Q43) hat is the command to see the execution plan by user?

SHOWPLAN

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?

Truncate table Tablename

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.

Besant Technologies WhatsApp