MSBI Interview questions & answers Part 2

1. Tell me about yourself?
2. Tell me the current project you are working? and type of your current project?
3. How much you are efficient in sql server,ssis and ssrs?
4. What is the difference between delete and truncate?
5. What is the difference between function and stored procedure?
6. What is the difference between temp table and table variable?
7. What is the difference between control flow and data flow?
8. What is merge join?
9. What are the tasks you used in data flow?
10. Types of joins?
11. What is – DML, DDL, DCL and TCL?

Notes:

• Function Cannot perform permanent environmental changes to SQL Server (i.e. no INSERT or UPDATE statements allowed).
• A Function can be used inline in SQL Statements but stored procedure cannot.
• We can't use transactions in UDF.
• SP can have input\output parameter.Function can have only input parameter.
• We can use exception handling using Try-catch block in SP.We can't use Try-Catch block in UDF.
• Temporary tables cannot be used in User defined function.Stored procedure can use Temporary tables.
• User defined functions cannot execute Dynamic SQL.Stored procedure can execute Dynamic SQL.

• Temporary Tables are same as real tables so you can do things like CREATE INDEXes, etc We can create non – clustered index on temporary table. We cannot create a non-clustered index on table variable.If we have large amount of data and performing queries on it then temporary table may be better option than table variable since we can increase the performance of a query.Table variables can have indexes by using PRIMARY KEY or UNIQUE constraints.
• Table variables don't participate in transactions, logging or locking.
• You can create a temp table using SELECT INTO, which can be quicker to write 
• Both table variables and temp tables are stored in tempdb.
• Global Temp Tables (##tmp) are another type of temp table available to all sessions and users.
• We can pass the table variables as a parameter of stored procedures or functions.
• Scope of temporary table is session.Scope of table variable is batch.

• In SSIS packages, the control flow defines the tasks used in performing data management operations; it determines the order in which these tasks are executed and the conditions of their execution.

• In SSIS packages, the data flow is a special control flow task used specifically in data movement operations and data transformations.

• INNER JOIN : This join returns rows when there is at least one match in both the tables.
• OUTER JOIN : There are three different Outer Join methods.
• LEFT OUTER JOIN : This join returns all the rows from the left table with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
• RIGHT OUTER JOIN : This join returns all the rows from the right table with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
• FULL OUTER JOIN : This join combines left outer join and right outer join. It returns row from either table when the conditions are met and returns null value when there is no match.
• CROSS JOIN : This join is a Cartesian join that does not necessitate any condition to join. The resultset contains records that are multiplication of record number from both the tables.

• DML : Data Manipulation Language. It is used to retrieve, store, modify, delete, insert and update data in database.Examples: SELECT, UPDATE, INSERT statements
• DDL : Data Definition Language. It is used to create and modify the structure of database objects in database.Examples: CREATE, ALTER, DROP statements
• DCL : Data Control Language. It is used to create roles, permissions, and referential integrity as well it is used to control access to database by securing it.Examples: GRANT, REVOKE statements
• TCL : Transactional Control Language. It is used to manage different transactions occurring within a database.Examples: COMMIT, ROLLBACK statements

• The Merge Join joins two sorted datasets using a FULL, LEFT, or INNER join. For example, you can use a LEFT join to join a table that includes product information with a table that lists the country/region in which a product was manufactured.The result is a table that lists all products and their country/region of origin.
• Requirements:The Merge Join Transformation requires sorted data for its inputs.The Merge Join transformation requires that the joined columns have matching metadata. For example, you cannot join a column that has a numeric data type with a column that has a character data type. If the data has a string data type, the length of the column in the second input must be less than or equal to the length of the column in the first input with which it is merged.