Senior Database Developer

Indexing is a data structure technique to efficiently retrieve records from the database files based on some attributes on which the indexing has been done.
Indexing is defined based on its indexing attributes. Indexing can be of the following types −

Primary Index − Primary index is defined on an ordered data file. The data file is ordered on a key field. The key field is generally the primary key of the relation.

Secondary Index − Secondary index may be generated from a field which is a candidate key and has a unique value in every record, or a non-key with duplicate values.

Clustering Index − Clustering index is defined on an ordered data file. The data file is ordered on a non-key field.

Bookmarks : Bookmarks let consumers quickly return to a row. With bookmarks, consumers can access rows randomly based on the bookmark value. The bookmark column is column 0 in the rowset. The consumer sets the dwFlag field value of the binding structure to DBCOLUMNSINFO_ISBOOKMARK to indicate that the column is used as a bookmark.

ROWID : ROWID is nothing but the physical memory location on which that data/row is stored.ROWID basically returns address of row. It uniquely identifies row in database. ROWID is combination of data object number, data block in datafile, position of row and datafile in which row resides. ROWID is 16 digit hexadecimal number whose datatype is also ROWID Or UROWID


Covering index : A covering index is one which can satisfy all requested columns in a query without performing a further lookup into the clustered index.

bit It is an integer that can be 0, 1 or null.
tinyint It allows whole numbers from 0 to 255.
Smallint It allows whole numbers between -32,768 and 32,767.
Int It allows whole numbers between -2,147,483,648 and 2,147,483,647.
bigint It allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807.
float(n) It is used to specify floating precision number data from -1.79E+308 to 1.79E+308. The n parameter indicates whether the field should hold the 4 or 8 bytes. Default value of n is 53.
real It is a floating precision number data from -3.40E+38 to 3.40E+38.
money It is used to specify monetary data from -922,337,233,685,477.5808 to 922,337,203,685,477.5807.

Normalization is the process of organizing the data in the database. Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to eliminate the undesirable characteristics like Insertion, Update and Deletion Anomalies. Normalization divides the larger table into the smaller table and links them using relationship.
There are the four types of normal forms:
1NF : A relation is in 1NF if it contains an atomic value.
2NF : A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional dependent on the primary key.
3NF : A relation will be in 3NF if it is in 2NF and no transition dependency exists.
4NF : A relation will be in 4NF if it is in Boyce Codd normal form and has no multi-valued dependency.
5NF : A relation is in 5NF if it is in 4NF and not contains any join dependency and joining should be lossless.

Containers are objects that help SSIS provide structure to one or more tasks. They can help you loop through a set of tasks until a criterion has been met or group a set of tasks logically. Containers can also be nested, containing other containers. There are three types of containers in the Control Flow tab:
1. Sequence : Sequence Containers handle the flow of a subset of a package and can help you divide a package into smaller, more manageable pieces.
2. For Loop : The For Loop Container enables you to create looping in your package similar to how you would loop in nearly any programming language. In this looping style, SSIS optionally initializes an expression and continues to evaluate it until the expression evaluates to false.
3. Foreach Loop Containers : The Foreach Loop Container is a powerful looping mechanism that enables you to loop through a collection of objects. As you loop through the collection, the container assigns the value from the collection to a variable, which can later be used by tasks or connections inside or outside the container.

Character Map Transformation: The Character Map transformation allows you to do character operations on string columns. It makes common string data changes for you.
Copy Column Transformation: This transformation is used to add a copy of column to the transformation output. You can later transform the copy. Makes a copy of a single or multiple columns that will be further transformed by subsequent tasks in the package
Data Conversion Transformation: This transformation is used to convert a column data type to a new (another) column data type.
Derived Column Transformation: This transformation is used to apply expression to a data column and create a new derived column calculated from an expression.
OLEDB Command Transformation: Runs a SQL command for each input data row. Normally your SQL statement will include a parameter (denoted by the question mark)
Script Component Transformation: This transformation is used to do a custom transformation. It uses a script to transform the data and you can apply specialized business logic to your data flow.

Event Handlers in SSIS is one of the most useful, and powerful feature. At the run-time executables such as Containers, Tasks will raise events. Situations where Event handlers are used :
Truncating, or Cleaning tables before we start loading the data.
Removing unwanted files after we exported them to other location (or to SQL).
Sending Email when an error occurs.
Retrieving system information etc.

CTE stands for Common Table Expressions. It was introduced with SQL Server 2005. It is a temporary result set and typically it may be a result of complex sub-query. Unlike the temporary table, its life is limited to the current query. It is defined by using WITH statement. CTE improves readability and ease in maintenance of complex queries and sub-queries.

Temp tables : In SQL Server, temporary tables are created at run-time and you can do all the operations which you can do on a normal table. These tables are created inside the Tempdb database. Based on the scope and behavior temporary tables are of two types : Local temp table and Global temp table

Temp variable : It acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of the batch. This is also created in the tempdb database but not the memory. This also allows you to create a primary key, identity at the time of Table variable declaration but not non-clustered index.

1. In star schema, The fact tables and the dimension tables are contained. While in snowflake schema, The fact tables, dimension tables as well as sub dimension tables are contained.
2. Star schema is a top-down model. While snow flake schema is a bottom-up model.
3. Star schema uses more space. While snow flake schema uses less space.
4.Star schema takes less time for the execution of queries. While snow flake schema takes more time than star schema for the execution of queries.
5. In star schema, Normalization is not used. While in snow flake schema. Both normalization and denormalization are used.

Merge can only accept two datasets while Union All can accept more than two datasets for input. 
The second difference is that Merge requires both datasets to be sorted while Union All does not require sorted datasets

Tip 1 : The cross questioning can go intense some time, think before you speak.
Tip 2 : Be open minded and answer whatever you are thinking, in these rounds I feel it is important to have opinion.
Tip 3 : Context of questions can be switched, pay attention to the details. It is okay to ask questions in these round, like what are the projects currently the company is investing, which team you are mentoring. How all is the work environment etc.
Tip 4 : Since everybody in the interview panel is from tech background, here too you can expect some technical questions. No coding in most of the cases but some discussions over the design can surely happen.