S&P Global Software Development Engineer II Interview Questions, Process, and Tips

Clustered index physically reorders the data in the table, unclustered index does not, unique index enforces uniqueness of values.

• Clustered index physically reorders the data in the table based on the index key. Only one clustered index per table.

• Unclustered index does not reorder the data in the table. Can have multiple unclustered indexes per table.

• Unique index enforces uniqueness of values in the indexed column(s).

CDC stands for Change Data Capture in SQL Server.

• CDC is a feature in SQL Server that captures changes made to data in a table.

• It allows you to track insert, update, and delete operations on the table.

• CDC uses a separate table to store the changes made to the tracked table.

• It is useful for auditing, data replication, and data warehousing purposes.

Views are virtual tables that simplify complex queries. Indexes can be created on views to improve performance.

• Views are virtual tables created by querying one or more tables.

• They simplify complex queries by storing the query logic in the view.

• Indexes can be created on views to improve query performance.

• Indexes on views can speed up data retrieval by allowing the database to quickly locate the relevant data.

Temp table is local to a session, global table is accessible across sessions, variable table is a table variable declared in a function or stored procedure.

• Temp table is created and dropped automatically when the session ends.

• Global table is created using a double hash (##) prefix and is accessible across sessions.

• Variable table is a table variable declared in a function or stored procedure and is only accessible withi

Truncate removes all rows from a table, while delete removes specific rows. Truncate is faster but cannot be rolled back.

• Truncate is a DDL operation while delete is a DML operation

• Truncate resets the identity seed of the table, delete does not

• Truncate is faster as it does not log individual row deletions, delete logs each row deletion

• Truncate cannot be used on tables referenced by a foreign key constraint, delete can b...read more

Merge operation in SQL is used to combine two sets of data into a single result set.

• Merge operation is used to insert, update, or delete data in a target table based on the results of a join with a source table.

• It is commonly used for data synchronization between two tables.

• Syntax: MERGE INTO target_table USING source_table ON condition WHEN MATCHED THEN UPDATE SET column1 = value1 WHEN NOT MATCHED THEN INSERT (column1

Error handling in SQL involves using try-catch blocks, raising custom errors, and using error functions.

• Use TRY-CATCH blocks to handle errors gracefully

• Raise custom errors using RAISEERROR function

• Use error functions like ERROR_MESSAGE(), ERROR_NUMBER(), ERROR_SEVERITY(), ERROR_STATE(), and ERROR_LINE() to retrieve error information

Plan ETL for various data sources by identifying sources, defining data extraction methods, transforming data, and loading into target systems.

• Identify all data sources and understand their structure and format

• Define data extraction methods based on the source systems (e.g. APIs, databases, files)

• Transform data as needed to match the target system's schema and requirements

• Consider data quality issues and implement data...read more

Bulk insert is a process of inserting a large amount of data into a database at once.

• Efficient way to insert large volumes of data into a database

• Reduces overhead by minimizing the number of transactions

• Often used for data migration or loading data from external sources

Synonyms are words that have similar meanings.

• Synonyms are words that can be used interchangeably in a sentence.

• They help in avoiding repetition and adding variety to the language.

• Examples include: big and large, happy and joyful, fast and quick.