Most frequently Asked SQL DBA Interview Questions and Answers for freshers and experienced pdf free download
1. Number of joins and use of complex views/cursors have to be reduced.
● The use of the stored procedures and indexes have to be maximized.
● The optimized use of the complex conditional checks and computer columns have to be in place.
● Tracking of performance analysis for the query helps us in identifying the right aspects to optimize.
2. How would you use the SP_ functions to identify the blocking problems?
Blocking is the deadlock situation when two SQL connections race to obtain the control over the same set of rows in conflicting terms. This can be tracked by the status of WAIT present in the SP_LOCK procedure’s output. All the active LOCKS and the different rows that are being involved are shown in this output. The
identification of the connections involved in the specific row contention lock can be identified with sp_who and sp_who2 procedures. This way the causal agents of the blocking is identifies. KILL command issued against the specific SQL connection causing the BLOCK can resolve the issue. But the permanent solution lies in the proper design of the application code to execute in concurrence across different connections.
3. What are the different types of backups?
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.
4. What are the different levels of isolation?
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.
5. How can you start the SQL Server in the single user mode and the minimal configuration mode?
The SQLServer.exe is the executable which can be called in the command prompt with the parameters -m and -f. These are the options that will start the SQL server in the user mode and minimal configuration mode respectively.
6. How can you know that statistics should be updated?
Statistics represent the uniqueness for the indexes that are being used for selecting the records. This can make the query execution pretty efficient. The tables that we are dealing with if truncated and repopulated, there is a good chance that the indexes and statistics are out of sync and this is when we have to update the
statistics. There are also other situations like when the table has been modified and lot of rows have been added recently or like when a server has been updated with different version of software. These also give us the reason to use the UPDATE_STATISTICS, DBCC SHOW_STATISTICS etc to update it accordingly.
7. What is replication in SQL Server?
Replication refers to the moving or copying of the database elements from one system to another. This can be done in the SQL Server in one of the following methods:
● Transactional.
● Snapshop.
● Merge replication.
8. Can we initiate a external COM object from within SQL?
Yes we can use the stored procedure sp_OACreate to initiate the external COM object from the T-SQL.
9. What is a schema? How is it useful in SQL Serers?
The Schema refers to the overall structure of the database with all related information like users, access privileges, interaction information between the different tables etc. The CREATE SCHEMA is the command that can be used to create the schema in the SQL Server. This when done can be used to re deploy the same database in another system for demonstrative or test purposes. This holds intact the underlying framework over which the database has been built.
10. What is a write-ahead log?
The write-ahead log is the logging system that just updates the buffer cache of the database for the transactions and updates the logs and only then the actual changes are incorporated in the actual database. This is the reason why it is called “write ahead”. This helps in maintaining the consistency in the database. This can also be useful in getting the actual database values even in case of failures.
11. What is the use of check points in the transaction logs?
The check points are restoration points that indicate the specific state of the database. When there is some failure int he database that is occurring before the next check point, the database can be reverted back to the previous check point and thus the database would still be consistent.
12. What is a column with identity?
The column with a defined identity in turn means that there is an unique value that the system assigns to the specific column. This is similar to the AUTONumber property of the Access backend.
13. What are the different components that constitute the SQL Server architecture?
● The Logical component consists of the overall database structure definitions, users, groups, access permissions, collations etc.
● The Physical component is the one that contains the physically represented form of the database i.e the actual files. There are three types of files:
1. Primary data files: These contain the links to the other files. This file is the representation of the higher level of the database. (.mdf)
2. Secondary data files - These contain the files that contain the values and actual data as in database.
3. Log files: These represent the different types of the logging output of the SQL Server, including the transaction logs.
14. What are scrollable cursors? How are they created?
The scrollable cursors are the ones that can get the entire set of rows as single entity, within which all the rows present can be accessed in any order without the open/close of cursor done for every row access. The scrollable cursors are created with the keyword SCROLL added to the CREATE Cursor statements. The scrollable cursors are useful for the access of information from different rows but not for the delete/insert of new rows.
15. What is RAID? How does it help storage of databases?
The RAID stands for Redundant Array of Independent Disks. With its own RAID controllers, the RAID implements a fail-safe storage mechanism with its own backup mechanisms. There are different configurations of the RAID that all give us the ACID properties of storage along with other such facilities. This kind of storage will make the SQL Server database to be failsafe and stable. This can sometimes mean that the backup mechanisms and other such reliability measures can be taken off from the SQL Server level of operations.
16. How can you identify the version number of the SQL Server installed?
The global variable @@version has the build and version information for the SQL Server and the service packs.
17. What are the Deference between Primary Key and Unique Key?
An unique key cant not be referenced as foreign key. And it may allow on null.
18. What is mean by Clustered Index and Non clustered index, give syntax of creation?
create clustered index index_name on empmst(card)
19. What is Scan Table/View and Seek Table/View When its Occurs?
A Table/view SCAN occurs when no useful indexes exist. A TABLE SCAN reads all data, row by row, to find the match.
20. What are the Referential actions supported by FOREIGN KEY integrity constraint ?
UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.
1. Number of joins and use of complex views/cursors have to be reduced.
● The use of the stored procedures and indexes have to be maximized.
● The optimized use of the complex conditional checks and computer columns have to be in place.
● Tracking of performance analysis for the query helps us in identifying the right aspects to optimize.
2. How would you use the SP_ functions to identify the blocking problems?
Blocking is the deadlock situation when two SQL connections race to obtain the control over the same set of rows in conflicting terms. This can be tracked by the status of WAIT present in the SP_LOCK procedure’s output. All the active LOCKS and the different rows that are being involved are shown in this output. The
identification of the connections involved in the specific row contention lock can be identified with sp_who and sp_who2 procedures. This way the causal agents of the blocking is identifies. KILL command issued against the specific SQL connection causing the BLOCK can resolve the issue. But the permanent solution lies in the proper design of the application code to execute in concurrence across different connections.
3. What are the different types of backups?
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.
4. What are the different levels of isolation?
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.
5. How can you start the SQL Server in the single user mode and the minimal configuration mode?
The SQLServer.exe is the executable which can be called in the command prompt with the parameters -m and -f. These are the options that will start the SQL server in the user mode and minimal configuration mode respectively.
6. How can you know that statistics should be updated?
Statistics represent the uniqueness for the indexes that are being used for selecting the records. This can make the query execution pretty efficient. The tables that we are dealing with if truncated and repopulated, there is a good chance that the indexes and statistics are out of sync and this is when we have to update the
statistics. There are also other situations like when the table has been modified and lot of rows have been added recently or like when a server has been updated with different version of software. These also give us the reason to use the UPDATE_STATISTICS, DBCC SHOW_STATISTICS etc to update it accordingly.
7. What is replication in SQL Server?
Replication refers to the moving or copying of the database elements from one system to another. This can be done in the SQL Server in one of the following methods:
● Transactional.
● Snapshop.
● Merge replication.
8. Can we initiate a external COM object from within SQL?
Yes we can use the stored procedure sp_OACreate to initiate the external COM object from the T-SQL.
9. What is a schema? How is it useful in SQL Serers?
The Schema refers to the overall structure of the database with all related information like users, access privileges, interaction information between the different tables etc. The CREATE SCHEMA is the command that can be used to create the schema in the SQL Server. This when done can be used to re deploy the same database in another system for demonstrative or test purposes. This holds intact the underlying framework over which the database has been built.
10. What is a write-ahead log?
The write-ahead log is the logging system that just updates the buffer cache of the database for the transactions and updates the logs and only then the actual changes are incorporated in the actual database. This is the reason why it is called “write ahead”. This helps in maintaining the consistency in the database. This can also be useful in getting the actual database values even in case of failures.
11. What is the use of check points in the transaction logs?
The check points are restoration points that indicate the specific state of the database. When there is some failure int he database that is occurring before the next check point, the database can be reverted back to the previous check point and thus the database would still be consistent.
12. What is a column with identity?
The column with a defined identity in turn means that there is an unique value that the system assigns to the specific column. This is similar to the AUTONumber property of the Access backend.
13. What are the different components that constitute the SQL Server architecture?
● The Logical component consists of the overall database structure definitions, users, groups, access permissions, collations etc.
● The Physical component is the one that contains the physically represented form of the database i.e the actual files. There are three types of files:
1. Primary data files: These contain the links to the other files. This file is the representation of the higher level of the database. (.mdf)
2. Secondary data files - These contain the files that contain the values and actual data as in database.
3. Log files: These represent the different types of the logging output of the SQL Server, including the transaction logs.
14. What are scrollable cursors? How are they created?
The scrollable cursors are the ones that can get the entire set of rows as single entity, within which all the rows present can be accessed in any order without the open/close of cursor done for every row access. The scrollable cursors are created with the keyword SCROLL added to the CREATE Cursor statements. The scrollable cursors are useful for the access of information from different rows but not for the delete/insert of new rows.
15. What is RAID? How does it help storage of databases?
The RAID stands for Redundant Array of Independent Disks. With its own RAID controllers, the RAID implements a fail-safe storage mechanism with its own backup mechanisms. There are different configurations of the RAID that all give us the ACID properties of storage along with other such facilities. This kind of storage will make the SQL Server database to be failsafe and stable. This can sometimes mean that the backup mechanisms and other such reliability measures can be taken off from the SQL Server level of operations.
16. How can you identify the version number of the SQL Server installed?
The global variable @@version has the build and version information for the SQL Server and the service packs.
17. What are the Deference between Primary Key and Unique Key?
An unique key cant not be referenced as foreign key. And it may allow on null.
18. What is mean by Clustered Index and Non clustered index, give syntax of creation?
create clustered index index_name on empmst(card)
19. What is Scan Table/View and Seek Table/View When its Occurs?
A Table/view SCAN occurs when no useful indexes exist. A TABLE SCAN reads all data, row by row, to find the match.
20. What are the Referential actions supported by FOREIGN KEY integrity constraint ?
UPDATE and DELETE Restrict - A referential integrity rule that disallows the update or deletion of referenced data. DELETE Cascade - When a referenced row is deleted all associated dependent rows are deleted.
No comments:
Post a Comment