SQL server interviews questions and answer, this will help you to prepare for SQL Server interviews. Sharing the frequently asked interview questions related sql server to crack the interviews.
How many sql server version do you support ?
Answer -> Sql server version like sql server 2008, 2012,2014 and 2016. Latest is SQL Server 2017 which runs on Linux operating system
What is the difference between sql server 2008 and 2012 ?
Answer could be for DBA, Audit feature is only in Enterprise edition and developer edition for SQL Server 2008 and In SQL Server 2012, Audit feature is available in all editions
List of SQL server edition ?
SQL Server Enterprise edition,
SQL Server Standard Edition
SQL Server express edition
SQL Server developer edition
What is RDMS ?
Answer -> RDMS stands for Relational Database Management System. It is use to maintain data in term of tables and indices. Each table maintain relationship with related table based on data. Keys are maintain in the tables to relate with other table.
Type of backup in SQL Server ?
Answer -> SQL Server has full , differential, copy only, file group and transaction log backup.
Full backup : Records all the changes in database and it is master copy while restoring database in case any failure.
Differential : Records the change done after full backup, you can restore differential backup only after full backup restore. Generally schedule to reduce the restore time in case failure.
Copy Only backup : To avoid log sequence issue, you can use this option to take backup
File group backup : Using this option, you can take backup of specific file group level.
Transaction log : It use to capture all transaction details, generally used to recover database to specific point time recovery.
How many type of backup do you have in your environment ?
Answer -> We have full backup which runs on weekly and differential backup daily every 4 hours. Some critical databases runs transaction log every hour.
What is index ?
Answer -> Index are used to improve query performance, it helps to you search data in the tables based on specific column. Index can be created on single column or multiple columns based on your query filter.
How many type of indexes in sql server ?
Answer -> Clustered index and Non clustered index
What is recovery model ? and list of recovery model in sql server ?
Answer -> Recovery model will tell you, how you are capturing data in transaction log file. Recovery model will tell whether data can be recoverable or not in recovery of database.
Full, bulk logged and simple are the recovery model available in sql server.
Full recovery model will record transaction log details and release space after successfully transaction log backup.
Bulk logged recovery model is used to handle bulk transactions like data is loading in tables and generating huge transaction log, you can switch database to bulk logged recovery to avoid huge transaction log.
Simple recovery model is used for non critical database here transaction log are not important in case of failure data can bee loaded again from different source. Reporting server is typical example of simple recovery model.
What is port for SQL Server ?
Answer -> Default port number of SQL Server 1433
How to check whether port is enabled or not ?
Answer – > You can use telnet command to check port status, open command prompt and type telnet servername portnumber
What is common performance issue in sql server ?
CPU bottleneck issue
Missing indexes issues
What are the tools available to troubleshoot performance issue ?
SQL Server profiler
Dynamic Management Views
SQL Server traces
Database tuning advisor in sql server
I will add some more questions soon, You can share your comments and questions if you have any ….. Enjoy learning !!!!