M GOYAL & CO Interview Questions and Answers

Transactions ensure data consistency and integrity in a database.

• Allows multiple operations to be treated as a single unit of work

• Ensures that all operations are either committed or rolled back as a whole

• Prevents data inconsistencies and corruption

• Provides ACID properties to the database

• Example: transferring funds between bank accounts

• Example: updating inventory levels after a purchase

Output parameters are variables used to return values from a function or stored procedure.

• Output parameters are declared with the OUTPUT keyword in SQL Server.

• They can be used to return a single value or a table of values.

• Output parameters are useful for passing data between stored procedures or functions.

• They can also be used to return status codes or error messages.

Magic tables are temporary tables created by SQL Server during DML operations.

• Magic tables are used to access the data before and after an INSERT, UPDATE, or DELETE operation.

• They are created automatically by SQL Server and cannot be modified by the user.

• The inserted and deleted tables are examples of magic tables.

• They are useful for auditing and logging changes to a database.

A recursive CTE is a Common Table Expression that references itself.

• It is used to perform recursive operations on hierarchical data.

• It consists of two parts: the anchor member and the recursive member.

• The anchor member is the base case and the recursive member is the recursive case.

• It can be used to traverse a tree structure or to calculate running totals.

• It can improve performance and simplify complex queries.

Temp tables are used to store intermediate results during query processing.

• Temp tables are created in tempdb database.

• They can be local or global.

• Local temp tables are only visible to the current session.

• Global temp tables are visible to all sessions.

• They can be created using SELECT INTO or CREATE TABLE statements.

• They can be used to store intermediate results during complex queries.

• They are automatically dropped when the session that created them ends.

• They can also be explic...read more

File groups are logical containers for database files that can be used to manage storage and backup operations.

• File groups allow for the organization of database files into logical units.

• They can be used to manage storage and backup operations.

• A database can have multiple file groups, each with its own set of files.

• File groups can be used to control the placement of data and indexes on different physical disks.

• File groups can also be used to control the backup and restore ope...read more