Website Developers India Interview Questions and Answers

Indexing is a way to improve database performance by creating a separate data structure that allows faster data retrieval.

• Clustered index: sorts and stores the data rows in the table based on their key values

• Non-clustered index: creates a separate structure that includes the indexed columns and a pointer to the data row

• Full-text index: enables full-text search on character-based data types

• Filtered index: indexes a subs...read more

Query optimization techniques aim to improve the performance of database queries.

• Use indexes to speed up query execution

• Avoid using SELECT * and instead specify only required columns

• Use JOINs instead of subqueries

• Avoid using functions in WHERE clauses

• Use EXPLAIN to analyze query execution plan

Functions return a value while stored procedures do not.

• Functions are used to perform a specific task and return a value.

• Stored procedures are used to execute a set of SQL statements and do not return a value.

• Functions can be used in SQL statements while stored procedures cannot.

• Functions can be called from within stored procedures.

• Functions are deterministic while stored procedures may not be.

• Functions can be used in ...read more

Temp table and magic table are used in SQL for temporary storage and tracking changes respectively.

• Temp table is used to store intermediate results during a query execution.

• Magic table is used to track changes made to a table during an INSERT, UPDATE or DELETE operation.

• Temp table is created using CREATE TABLE statement with # or ## prefix.

• Magic table is accessed using the special INSERTED and DELETED tables.

• Temp table...read more

Yes, we can delete records from views. Views are virtual tables that display data from one or more tables.

• Views are used to simplify complex queries by providing a virtual table with a specific subset of data.

• Deleting records from a view will delete the corresponding records from the underlying table(s).

• Views can also be used to restrict access to sensitive data by only allowing certain columns to be displayed.

• Example:...read more

Constraints are limitations or rules that must be followed in software development.

• Data constraints: limit the type or amount of data that can be entered

• Domain constraints: limit the values that can be entered

• Business rules: limit the actions that can be taken

• Hardware constraints: limit the hardware that can be used

• Time constraints: limit the time available for development or execution