SQL Server DBA Interview Questions and Answers
Q.What are the different types of backups?
Answer: The SQL server offers 4 types of backups to suit the need of the administrator.
Complete backup :The complete back up is just zipping the content of the entire database in terms of the different tables and procedures etc. This back up can server as an independent entity that can be restored in different systems with just the base SQL server installed.
Transaction log backup: This is the mechanism of backing up the transaction logs that have been maintained in the server. This way the details of the database getting updated is obtained. This cannot be a stand-alone back up mechanism. But can save a lot of time i...
SQL Server DBA Interview Questions and Answers
Q.What are the different types of backups?
Answer: The SQL server offers 4 types of backups to suit the need of the administrator.
Complete backup :The complete back up is just zipping the content of the entire database in terms of the different tables and procedures etc. This back up can server as an independent entity that can be restored in different systems with just the base SQL server installed.
Transaction log backup: This is the mechanism of backing up the transaction logs that have been maintained in the server. This way the details of the database getting updated is obtained. This cannot be a stand-alone back up mechanism. But can save a lot of time if we already have the file system related to the DB backed up on the new deployment server.
Differential backup: This is a subset of the complete backup, where only the modified datasets are backed up. This can save the time when we are just trying to maintain a backup server to main server.
File backup: This is the quickest way to take the backup of entire database. Instead of taking in the data actually stored in DB, the files are backed up and the file system thus obtained when combined with the transaction logs of the original system will render the database that we are trying to back up.
Q.What are the different levels of isolation?
Answer: The isolation represents the way of separating the database from the effects of network accesses, thereby maintaining the consistency. The different levels of isolation are:
read committed: This level of isolation uses the shared locks and the reads to the database give the constant and consistent values.
read uncommitted: No locks implemented. This is the least effective isolation level. repeatable read: There are lock over the rows and values but the updates are maintained as a separate phantom row which is the next set of values for the specific record. Values can change within a specific transaction of a SQL function.
SERIALIZABLE reads: This is the implementation of pure lock mechanism where one specific transaction is not allowed access to specific record before another one completes.
Q.What are the steps to take to improve performance of a poor performing query?
Answer: Maximum use of indexes, stored procures should be done.
Avoid excessive use of complicated joins and cursors.
Avoid using conditional operators using columns of different tables.
Make use of computed columns and rewriting the query.
Q.What is normalization? Explain different levels of normalization?
Answer: Check out the article Q100139 from Microsoft knowledge base and of course, there’s much more information available in the net. It’ll be a good idea to get a hold of any RDBMS fundamentals text book, especially the one by C. J. Date. Most of the times, it will be okay if you can explain till third normal form.
