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

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.