Lemma Technologies 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 subset of data based on a filter predicate

• Spatial index: optim...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: DELETE FROM my_view WHERE column_name = 'value';

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 views while stored procedures cannot.

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 is dropped automatically at the end of a session or trans...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