SQL SERVER INTERVIEW QUESTION & ANSWERS
| 56. |
Why Should we Use @@ERROR? |
| |
We may be tempted to use the TRY...CATCH construct everywhere we can. Using @@ERROR can be cumbersome; however, it is supported in all previous versions of SQL Server. If our code must execute on versions of SQL Server prior to SQL Server 2005, we must avoid using TRY...CATCH, and use @@ERROR for error handling instead.
|
| 57. |
Define Properties of Transactions. |
| |
- Atomicity: A transaction behaves as an atomic operation: it is either committed or rolled back as a unit.
- Consistency: All database structures must be consistent. Tables and indexes must remain in sync.
- Isolation: Transactions are isolated from one another with locks. The degree of isolation is determined by the transaction isolation level.
- Durability: Transactions must persist in the event of a system failure. Transaction logging in SQL Server supports this requirement.
|
| 58. |
Explain Autocommit Transaction. |
| |
By default, SQL Server considers each statement that makes a modification a separate transaction. Either the statement completes entirely, or not at all. For example, an UPDATE statement will either update all the records in its search criteria, or none of them. If an error is encountered in the middle of the update process, any changes that have been made must be rolled back. It is important to understand the behavior of batches running in Autocommit mode. When runtime errors are encountered, the statement that generated the runtime error is rolled back.
|
| 59. |
Explain Explicit Transactions. |
| |
SQL Server allows you to group statements into a single transaction. BEGIN TRANSACTION indicates the beginning of a transaction. COMMIT TRANSACTION means that all changes since the BEGIN TRANSACTION statement should be committed to the database. ROLLBACK TRANSACTION allows any modifications made to be undone by the database engine
|
| 60. |
Tell me the operations that are not allowed in a stored procedure. |
| |
Creating or altering any of the following objects:
- Aggregate
- Default
- Function
- Procedure
- Rule
- Schema
- Trigger
- View
- The USE statement
- SET PARSEONLY or SHOWPLAN variants
|
