Interview Questions for SQL DBA
I have added some of the asked questions related to SQL Servers. Please mention your comments and suggestions to improve the same and help other to clear the interview related to SQL Support.
1. What are difference between Delete and Truncate command?
Ans: Delete command removes the rows from a table based on the condition we put in the where clause. Truncate command removes all the rows the table immediately.
DELETE:
- Delete is slower as it writes the entries in transaction log.
- Delete removes one row at a time from the table and records an entry of the deleted rows in the transaction log.
- The identity column is not reset while we use Delete command.
- Delete can be used with/without the where clause.
- Delete statement can be rolled back.
- Delete statement is a DML statement.
- Truncate is faster and uses fewer system resources and transaction log.
- Truncate removes the data by deallocating the space and only deallocation of space is written in the transaction log.
- Truncate removes all rows from the table and doesn't use the where clause.
- The identity column is reset to the seed value while we use Truncate command.
- Truncate statement can not be rolled back.
- Truncate is a DDL statement.
2. What are the various types of Recovery models available in Ms SQL Servers?
Ans: There are three types of Recovery models available with SQL Server 2005. They are:
- Full Recovery Model
- Bulk-logged Recovery Model
- Simple Recovery Model
3. What are the different types of Backups available in SQL Server?
Ans: The various types of Backups are:
- Full Backup
- Differential Backup
- Transaction Log Backup
4. How to move a database from Server A to Server B with least administrative access?
Ans: There are various methods to copy the database from one server to another. But using the system stored procedures sp_detachdb and sp_attachdb you can copy the database with least administrative access. The following are the syntax to follow up:
1)-- To remove the server from the database
sp_detachdb 'databasename';
GO
2) Copy the database files (.mdf & .ldf) from Server A to Server B.
3) -- To attach the database to the server B, use the following command.
sp_attachdb '.mdf file location of the database';
GO
5. How to copy the user-login details from one database to another database?
6. How to reduce the size of the tempdb?
Ans: Sometimes, due to badly written queries, the size of the tempdb database in your SQL Server may increased a lot. There are various ways to reduce the size of the tempdb. I am addressing one of the way to do it.
Restart your SQL Server and execute the following statements:
-- To bring the server into Single User mode
sp_dboption 'tempdb', 'Single User', 'TRUE';
GO
USE tempdb
GO
-- The following statement reduce the size of the data file
dbcc shrinkfile (tempdev, 'target size in MB');
GO
-- The following statement reduce the size of the log file
dbcc shrinkfile (templog, 'target size in MB');
GO
-- The following statement change the database from Single User mode to Multi User mode sp_dboption 'tempdb', 'Single User', 'FALSE';
GO
7. You are administering a database Customers on SQL Server. You have scheduled a backup plan for the database Customers. The plan is as follows: Full backup on Monday morning at 5 am Differential backup on rest of the day at 11pm Transactional Log backup after every hour. Your database server crashed on Wednesday morning at 11:15am due to a hardware failure. How do you bring your database server online ASAP?
Ans: As describe in the above scenario, the database server failed at 11:15am on a Wednesday. So you have one full backup, which was taken on Monday morning, two differential backups taken on Monday and Tuesday at 11pm, and transactional log backup every hour up to 11am on Wednesday. First you have to replace the hardware which failed, the restart your server. Than take a backup of the transactional log. This log contains the details of all the transactions after the last transaction backup. This is also known as Tail log backup. Now we have all the restore available to backup our server up to the time of failure i.e. 11:15am on Wednesday.
Follow the steps mentioned below to restore the database:
1. Restore the full backup of the database.
2. Restore the differential log backup of Tuesday.
3. Restore all the transaction log backup( first at 12am, then 1am, then 2am....up to 11am)
4. Restore the tail backup which you took after rebooting your server, after 11:15am.
Now the Customers database is restored up to the time of failure.
8. How to start the SQL Server in Single User Mode?
Ans: We can bring a SQL Server in Single User mode by using the query analyzer and by using Management Studio.
Using Query Analyzer:
--Use the following system stored procedure, we can change a database to Single User mode. sp_dboption 'databasename', 'Single User', 'TRUE';
GO
Using Management Studio: check the msdn article on this at; http://msdn.microsoft.com/en-us/library/ms345598.aspx
9. How many indexes can be created on a table?
Ans: We can create one Clustered index and 249 non-clustered index on a table.
10.Difference between Primary Key and Unique Key?
Ans: Primary Keys are the unique identifiers for each row. They must contain unique values and cannot be null. Due to their importance in relational databases, Primary keys are the most fundamental of all keys and constraints. A table can have only one Primary key. Primay Key, by default, creates Clustered index.
As compared to Primary Key, Unique Keys are also the unique identifiers for each row. Unique key contains maximum one Null value. Unique key creates non-clustered index.
11. How to migrate a database?
12. What are the different type of authentication mode available in SQL Server?
Ans: There are two types of authentication mode available in SQL Server. They are:
- Windows Authentication
- Mixed Mode Authentication
In Mixed mode authentication, the user required an windows account to login to the system and a sql login account to login to the database.
For more information, please visit the MSDN website: http://msdn.microsoft.com/en-us/library/aa905171.aspx
13. How to check the database performance?
14. What is the difference between Physical Database and Logical Database?
15. Difference Between Clustered and Non-clustered index?
Ans: A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.
16. On which port, SQL Server listen to?
Ans: SQL Server listen on TCP port 1433.
17. Where is the sql server users and password information stored in SQL Server?
Ans: In SQL Server 2000, its stored in sysxlogin table and in SQL Server 2005, its stored on sys.syslogins table
18. What is Database Mirroring?
Ans: Database Mirroring is a disaster recovery method used in SQL Server. In Database Mirroring, we create a mirror of a existing database, which contain the exact copy of the database. In case of a disaster, the mirrored database can be use to serve the request.
19. What is Database Isolation Level?
20. What is Statistics in SQL Server?
