10+ S&P Global Software Development Engineer II Interview Questions and Answers

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 be used

• Truncate operation cannot be rolled back, delete ope...read more

To build a responsive Web UI page with large images, use lazy loading, image optimization, and responsive design techniques.

• Implement lazy loading to only load images when they are in the viewport.

• Optimize images for web by compressing them and using modern image formats like WebP.

• Use responsive design techniques like CSS media queries to adjust image sizes based on screen resolution.

• Consider implementing a content delivery network (CDN) to serve images faster.

• Use placeholder...read more

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.

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 cleansing processes

• Load the transformed data into the tar...read more

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). Can be clustered or unclustered.

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 within that scope.

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.

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

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.