Interview Questions

SQL Server DBA Interview Questions and Answers

Interview Questions December 6, 2018
SQL Server DBA Interview Questions

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 – Chennai & Bangalore Branch Locations

Besant Technologies - Velachery Branch

Plot No. 119, No.8, 11th Main road, Vijaya nagar,

Velachery, Chennai - 600 042

Tamil Nadu, India

Landmark - Reliance Digital Opposite Street

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - Tambaram Branch

No.2A, 1st Floor, Duraisami Reddy Street,

West Tambaram, Chennai - 600 045

Tamil Nadu, India

Landmark - Near By Passport Seva

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - OMR Branch

No. 5/318, 2nd Floor, Sri Sowdeswari Nagar,

OMR, Okkiyam Thoraipakkam, Chennai - 600 097

Tamil Nadu, India

Landmark - Behind Okkiyampet Bus Stop,

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - Porur Branch

No. 180/84, 1st Floor, Karnataka Bank Building,

Trunk Road, Porur, Chennai - 600 116

Tamil Nadu, India

Landmark - Opposite to Gopalakrishna Theatre

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - Anna Nagar Branch

Plot No:1371, 28th street kambar colony,

I Block, Anna Nagar, Chennai - 600 040

Tamil Nadu, India

Landmark - Behind Reliance Fresh

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - T.Nagar Branch

Old No:146/2- New No: 48,

Habibullah Road,T.Nagar, Chennai - 600 017

Tamil Nadu, India

Landmark - Opposite to SGS Sabha

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - Thiruvanmiyur Branch

22/67, 1st Floor, North mada street, Kamaraj Nagar

Thiruvanmiyur, Chennai 600041

Tamil Nadu, India

Landmark - Above Thiruvanmiyur ICICI Bank

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - Siruseri Branch

No. 4/76, Ambedkar Street, OMR Road, Egatoor, Navallur,

Siruseri, Chennai 600130

Tamil Nadu, India

Landmark - Near Navallur Toll Gate, Next to Yamaha Showroom

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - Maraimalai Nagar Branch

No.37, Ground Floor, Thiruvalluvar Salai,

Maraimalai Nagar,Chennai 603209

Tamil Nadu, India

Landmark - Near to Maraimalai Nagar Arch

  +91-8099 770 770

  info@besanttechnologies.com

Besant Technologies - BTM Layout Branch

No 2, Ground floor, 29th Main Road,

Kuvempu Nagar,BTM Layout 2nd Coming from Silkboard,

AXA company signal, Stage, Bangalore - 560 076

Karnataka, India

Landmark - Next to OI Play School

  +91-8767 260 270

  info@besanttechnologies.com

Besant Technologies - Marathahalli Branch

No. 43/2, 2nd Floor, VMR Arcade,

Varthur Main Road, Silver Springs Layout,

Munnekollal, Marathahalli, Bengaluru - 560037

Karnataka, India

Landmark - Near Kundalahalli Gate Signal

  +91-8767 260 270

  info@besanttechnologies.com

Besant Technologies - Rajaji Nagar Branch

No. 309/43, JRS Ecstasy, First Floor,

59th Cross, 3rd Block, Bashyam Circle,

Rajaji Nagar, Bangalore - 560 010

Karnataka, India

Landmark - Near Bashyam Circle

  +91-8767 260 270

  info@besanttechnologies.com

Besant Technologies - Jaya Nagar Branch

No. 2nd Floor,1575,11th Main Road,

4th T-Block, Pattabhirama Nagar,

Jaya Nagar, Bangalore - 560 041

Karnataka, India

Landmark - Opposite to Shanthi Nursing Home

  +91-8767 260 270

  info@besanttechnologies.com

Besant Technologies - Kalyan Nagar Branch

No.513, 4th Cross Rd

2nd Block, HRBR Layout,

Kalyan Nagar, Bangalore - 560 043

Karnataka, India

Landmark - Opposite to kalayan nagar Axis Bank

  +91-8767 260 270

  info@besanttechnologies.com

Besant Technologies - Electronic City Branch

No. 7, 3rd Floor, Ganga Enclave,

Neeladri Road, Karuna Nagar, Doddathoguru Village,

Electronics City Phase 1, Electronic City,

Bangalore - 560100, karnataka, India

Landmark - Adjacent to HDFC Bank and State Bank Of India

  +91-8767 260 270

  info@besanttechnologies.com

Besant Technologies - Indira Nagar Branch

No.54, 1st Floor,

5th Main kodihalli,

Bengaluru, Karnataka 560008, India

Landmark - Behind Leela Palace Hotel,

  +91-8767 260 270

  info@besanttechnologies.com

Besant Technologies - HSR Layout Branch

Plot No. 2799 & 2800, 27th Main,

1st Sector, HSR Layout,

Bengaluru, Karnataka 560102, India

  +91-8767 260 270

  info@besanttechnologies.com

Besant Technologies - Hebbal Branch

No.29, 2nd Floor, SN Complex,

14th Main Road, E-Block Extention, Sahakara Nagar,

Bengaluru, Karnataka -560092, India

  +91-8767 260 270

  info@besanttechnologies.com

Scroll Up