14. Define cursor.
Answer:
A Cursor is a database object which helps in manipulating data row by row representing a result set.
15. What is sub-query? Explain properties of sub-query.
Answer:
A subquery is a SELECT statement that is nested within another T-SQL statement, executed independently in which it is nested and returns a result set.
Properties of Sub-Query:
- A subquery must be enclosed in the parenthesis.
- A subquery must be put in the right hand of the comparison operator.
- A subquery cannot contain ORDER-BY clause.
- A query can contain more than one sub-queries.
16. What is the difference between Clustered and Non-Clustered Index?
Answer:
A clustered index reorders the way records in the table are physically stored.
A Non-Clustered index creates a separate object within the table and does not reorders the way records in the table was stored.
Non-clustered index has data pointers so there can be many non-clustered indexes per table. While clustered index is distinct for every table.
17. What is database Trigger?
Answer:
A trigger is a special type of stored procedure that fires automatically in response to DML and DDL events.
DML triggers execute when data is modified through a data manipulation language (DML) event such as INSERT, UPDATE, or DELETE statements on a table. DDL triggers execute in response to Transact-SQL CREATE, ALTER, and DROP statements.
18. What are stored procedures? And what are the advantages of using them?
Answer:
A stored procedure is a set of SQL statements that performs a user defined operation. Since it is precompiled and stored in the database, it runs queries faster. It also reduces network traffic as many queries can be included in a stored procedure, round trip time to execute multiple queries from an application to database and back is avoided.
19. What are properties of a transaction?
Answer:
Properties of the transaction can be summarized as ACID Properties, i.e. Atomicity, Consistency, Isolation, Durability. These are the set of properties of database transactions that ensure accuracy, completeness, and data integrity.
1. Atomicity
A transaction may consist of many statements. The database should get reflected only when all statements are run successfully. If any of the statement fails, all the transactions must be rolled back.
2. Consistency
The database should create a new state if the transaction succeeds. If the transaction fails, the database should bounce back to its original state, i.e. the state before the transaction was started.
3. Isolation
A transaction in process must remain isolated from any other transaction.
4. Durability
Once a transaction has completed successfully, the updated rows/records must be available for all other transactions on a permanent basis.
