ELLKAY SQL Developer Interview Questions and Answers

SSIS Architecture is a platform for data integration and workflow applications.

• SSIS stands for SQL Server Integration Services

• It includes a data flow engine, control flow engine, and event handling

• SSIS packages can be created using SQL Server Data Tools (SSDT)

• SSIS packages can be scheduled and executed using SQL Server Agent

Transactions in SSIS can be implemented using the TransactionOption property in Control Flow tasks.

• Set the TransactionOption property to Required on the Control Flow tasks that need to be included in the transaction.

• Use the TransactionOption property to specify the level of transaction support needed for each task.

• Transactions can be managed using the Distributed Transaction Coordinator (DTC) service.

Use the INSERT INTO statement with multiple value sets separated by commas

• Use the INSERT INTO statement followed by the table name

• List the column names in parentheses after the table name

• Use the VALUES keyword followed by multiple value sets in parentheses, separated by commas

• Example: INSERT INTO table_name (column1, column2) VALUES (value1, value2), (value3, value4)

Update query using joins in SQL

• Use UPDATE statement with JOIN clause to update data in multiple tables

• Specify the tables to be updated and joined using ON clause

• Set the columns to be updated in SET clause

Query to display the highest salary in a table

• Use the SELECT statement to retrieve data

• Use the MAX() function to find the highest salary

• Specify the column name for salary in the query

Types of indexes include clustered, non-clustered, unique, composite, and filtered indexes.

• Clustered indexes physically reorder the data in the table based on the index key.

• Non-clustered indexes create a separate structure for the index, pointing back to the original table data.

• Unique indexes ensure that no two rows have the same values in the indexed columns.

• Composite indexes are created on multiple columns to improve...read more