Cognizant Business Intelligence Associate Interview Questions and Answers for Experienced

Query to display duplicate records without using group by

• Use self-join to compare records

• Select records where the values match but the primary key is different

• Example: SELECT a.* FROM table a, table b WHERE a.value = b.value AND a.id <> b.id

The 'FOR XML' command is used in SQL Server to generate XML output from a query result.

• It allows transforming the result set of a query into XML format.

• It can be used to generate XML documents for data exchange or integration purposes.

• The 'FOR XML' command supports different modes like RAW, AUTO, EXPLICIT, and PATH.

• It can be combined with other SQL Server features like joins, aggregations, and subqueries.

• Example: SELEC

stuff() replaces a specified number of characters with another string, while substring() extracts a portion of a string.

• stuff() is used to replace a specified number of characters in a string with another string.

• substring() is used to extract a portion of a string based on the starting and ending index.

• stuff() can be used to mask sensitive data in a string, such as credit card numbers.

• substring() can be used to extract...read more

Transaction property in SSIS ensures all tasks in a package are completed or rolled back as a single unit.

• Transaction property is used to ensure data consistency in a package

• If a task fails, all previous tasks are rolled back

• If all tasks complete successfully, they are committed as a single unit

• Transaction property can be set at package or container level

• Supported isolation levels are Serializable, Repeatable Read, Rea

The connection string can be passed on runtime using various methods.

• One way is to use a configuration file and read the connection string from it.

• Another way is to pass the connection string as a parameter to the application.

• The connection string can also be stored in a database and retrieved at runtime.

• Using environment variables is also a common method to pass connection strings.

• Connection strings can be encrypted a...read more

Query optimization can be done by analyzing query execution plans, indexing, partitioning, and rewriting queries.

• Analyze query execution plans to identify bottlenecks and optimize them

• Create indexes on frequently queried columns to improve performance

• Partition large tables to distribute data and improve query performance

• Rewrite queries to use efficient join and filter conditions

• Use query hints to force specific executi...read more