Interview Questions and Answers - 2

What is the recovery model? List the types of recovery model available in SQL Server?

Ans. Recovery model basically tells SQL Server what data should be kept in the transaction log file and for how long. A database can have only one recovery model.

It also tells SQL server that which backup is possible in a particular recovery model selected. There are three types of recovery model:

·         Full

·         Simple

·         Bulk-Logged

What are the types of backups available in SQL Server?

Ans. Different possible backups are:

·         Full backup

·         Differential Backup

·         Transaction Log Backup

·         Copy Only Backup

·         File and File group backup

What is Mirroring? What are the advantages of the Mirroring?

Ans. Mirroring is a high availability solution. It is designed to maintain a hot standby server which is consistent with the primary server in terms of a transaction. Transaction Log records are sent directly from principal server to secondary server which keeps a secondary server up to date with the principal server.

Advantages of Mirroring are:

·         It is more robust and efficient that Log shipping.

·         It has an automatic failover mechanism.

·         The secondary server is synced with the primary in near real time.

Which TCL Commands are available on the SQL Server?

Ans.  There are 3 TCL Commands in the SQL Server. These are as follows:

Commit: This command is used to save the transaction permanently in the database.

Rollback: This is used to roll back the changes that are done i.e. to restore the database in the last committed state.

Save Tran: This is used for saving the transaction so as to provide the convenience that the transaction can be rolled back to the point wherever required.

What is the difference between a Local and a Global temporary table?

Ans. If defined in inside a compound statement a local temporary table exists only for the duration of that statement but a global temporary table exists permanently in the database but its rows disappear when the connection is closed.