.Net Interview

Interviewing candidate for a mid level requires lot of scrutiny to get right candidate.  I had always had list of areas I would like to cover the depth in the area is depends on the candidates interest and experience, but I would like always make sure to know his breadth.  Here is my notes (Crude copy paste from One Note)

1. OOPs
   1. Delegates / Extended Methods
   2. Threading (Pool / TPL / Native) 
   3. Could not differentiate Static and Dynamic poly
   4. Abstract and Interface
   5. Abstract Factory
   6. Virtual, Override
   7. Partial class
2. .Net basics
   1. Reference type, value type
   2. Call by value and call by reference
   3. Why Reflections / Why Garbage Collection & How
3. Collections
1. Stack, Queue
2. Array, Array List
3. Hash table, Dictionary
4. Build & Deployment

Debugging

1. When ASP.Net page is slow how do you respond?

Technology Area

1. ASP.Net (Single sign-on)
   • Cache, Paging
   • Session
   • Application - Global.asax
   • Tracing
   • Error handling
   • Profiling
   • AJAX
   • Configuration
   • HTTPModule & HTTPHandler
   • ASP.Net Pipeline
   • User control, Customer control
   • Authentication, Authorization
   • Member provider
   • Data Binding
2. MVC / MVVM
3. Entity Framework / ORM / LINQ
4. WCF (RIA, OData) / WPF / WF / SL (MEF, Prism)
5. Design Patterns
6. SQL (SSRS, SSIS, SSAS, TSQL)
7. SharePoint (Customization, Performance Point, Power View, Power Pivot)

Designing Skills

• Consider you are building simple Reporting solution to where you need to show data from DB to an UI page.  What are you primary question to estimate & design this app?
• Number of Reports
• Size of data
• Number of users
• Authentication
• Performance needs, Frequency of data change and Caching
• SP vs Dynamic SQL
• AJAX & Pagination

Technical Leadership

• Code Review
• Readability
• Each method one action
• Input validation
• Error Handling
• Performance & Caching
• View State
• Estimation
• Task allocation and tracking

Tools

• Fiddler
• FxCop
• Beyond Compare

Logical Thinking

• Designing lift logic
• Designing Railway platform logic
• Representing hierarchy using Array
• Thread Synchronization
• Tic-Tac -To who to create next move based on current state
• How to you store the state
• Compare two sets
• Process communication

SSAS

Great collection of useful SSAS articles.  It helped me.  It will help all.

http://ssas-wiki.com

SSIS–DFT does not loads last row from flat file

Yesterday, I had an interesting issue.  It has been reported me that an ETL always misses the last row from the flat file.  No matter what is the row is it happens for every file that the last row is missing.

It is wired issue, I started check the ETL and created a new ETL with DFT pointing the same share file.  I could able to get all the rows.  It was happening only in that particular ETL.

I copied the DFT from the error ETL to my new ETL.  The problem disappeared.  I was wondering about the magic happening here.  The DFT is same, data file is same but the last row is getting missed.

I started comparing the ETL config and connections.  oh…at last I could able to figure out the issue.  It was due the Text Qualifier setting which update on the Flat File Connection component.  Make sure it set to None, if you don’t have any special handling.

Related MSDN discussion

SQL Table Size

Many times you may want to check the size of set of tables in SQL server.  There are multiple ways to achieve the same.

DECLARE @Unit CHAR(2)
DECLARE @SlicerSize FLOAT

SET @Unit = 'GB' --MB

CREATE TABLE #DataSize
(
    RowId        INT IDENTITY,
    TableName    SYSNAME,
    DataRowCount BIGINT,
    Reserved     VARCHAR(20),
    Data         VARCHAR(20),
    IndexSize    VARCHAR(20),
    Unused       VARCHAR(20)
)

SELECT @SlicerSize = CASE WHEN @Unit = 'GB' 
                          THEN 1024.0 / 1024.0
                          ELSE 1024.0 
                     END

-- Add the table you want to check the size
INSERT #DataSize EXEC sp_executesql N'EXEC sp_spaceused [dbo.Emp]'
INSERT #DataSize EXEC sp_executesql N'EXEC sp_spaceused [dbo.Address]'

SELECT TableName, 
       DataRowCount / 1000 AS RowCountInKs ,
       CONVERT(DECIMAL(10,2),CONVERT(INT,REPLACE(Reserved,' KB','')) / @SlicerSize)  AS ReservedSpace ,
       CONVERT(DECIMAL(10,2),CONVERT(INT,REPLACE(Data,' KB','')) / @SlicerSize)      AS DataSize ,
       CONVERT(DECIMAL(10,2),CONVERT(INT,REPLACE(IndexSize,' KB','')) / @SlicerSize) AS IndexSize,
       @Unit                                                                         AS Unit 
       FROM #DataSize

DROP TABLE #DataSize

SSIS Programming reference

Generating SSIS using meta data is a powerful technique when you want to move large number of table from source to target.  In this post I will highlight the programming references to create the SSIS dynamically.

• Microsoft.SqlServer.Dts.Runtime.Package is the highlevel object which hold all the objects of the package.  You can create New Package object to start building you object mode.
• Connections collection holds connections. 
  Example: PackageObj.Connections.Add("OLEDB");
  Refer MSDN for list of connection types
• You can change the custom property of the connection by accessing the Property collection of the connection
  Example: ConnectionObj.Properties["Format"].SetValue(csvFile, "Delimited");
• Executable: By accessing the Executable collection you can add DFT, SQL Task or any other task to Package.
  Example: Package.Executables.Add(“STOCK:SEQUENCE”);
  For list of moniker, refer the SQLIS blog
• When you are dealing with Data Flow Task (DFT), it is complex due to the fact DFT are COM and we have a CManagedComponentWrapper to access them.
• The hierarchy of object structure is
• Executable
• TaskHost
• MainPipe
• IDTSComponentMetaData100

Package p = new Package();
Executable e = p.Executables.Add("DTS.Pipeline.1");
TaskHost thMainPipe = e as TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;

• In DFT, you can add pipeline items by using ComponentMetaDataCollection of MainPipe object

IDTSComponentMetaData100 pipeLineItem = MainPipeObj.ComponentMetaDataCollection.New();
pipeLineItem.ComponentClassID = "DTSAdapter.OleDbSource.2";

• When you are using the Source object in PipeLine, make sure it get initiated using IDTSComponentMetaData100.Instantiate & CManagedComponentWrapper.ProvideComponentProperties methods.  These methods are explicit interface implementation so make sure necessary type cast done when you call the methods.

CManagedComponentWrapper InstanceSource = IDTSComponentMetaData100Obj.Instantiate();
InstanceSource.ProvideComponentProperties();

• For Source, Destination tasks make sure the connection are associated.

public static void SetConnection(
IDTSComponentMetaData100 obj, ConnectionManager con)
{
obj.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(con);
obj.RuntimeConnectionCollection[0].ConnectionManagerID = con.ID;
}

• After the connection is set, it needs to be refreshed to get the meta data

CManagedComponentWrapperObj.AcquireConnections(null);
CManagedComponentWrapperObj.ReinitializeMetaData();
CManagedComponentWrapperObj.ReleaseConnections();

• You can attach multiple item to pipeline. 
• To link the pipeline objects, you can use Attach Path method

IDTSPath100 path = dataFlow.PathCollection.New();
path.AttachPathAndPropagateNotifications(source.OutputCollection[0], target.InputCollection[0]);

• To map the columns between pipeline items, you need to use the column collections

public void MapColumns(
IDTSComponentMetaData100 DestinationTask
, CManagedComponentWrapper InstanceDestination
, DTSUsageType dtsUsageType)
{
IDTSInput100 input = DestinationTask.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
IDTSInputColumn100 vCol = null;

if (dtsUsageType == DTSUsageType.UT_READONLY)
{
foreach (IDTSVirtualInputColumn100 vColumn in  vInput.VirtualInputColumnCollection)
{
  InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, dtsUsageType);
}

foreach (IDTSInputColumn100 col in input.InputColumnCollection)
{
IDTSExternalMetadataColumn100 exCol = input.ExternalMetadataColumnCollection[col.Name];
InstanceDestination.MapInputColumn(input.ID, col.ID, exCol.ID);
}
}
else
{
foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
vCol = InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, dtsUsageType);
IDTSExternalMetadataColumn100 exCol = input.ExternalMetadataColumnCollection[vColumn.Name];
InstanceDestination.MapInputColumn(input.ID, vCol.ID, exCol.ID);
                    }
                }
        }

• For connecting executable in Control Tasks, you can use PrecedenceConstraint class to associate the Task for sequential execution.

PrecedenceConstraint prePC = DFT.ParentContainer.PrecedenceConstraints.Add(preExec, postExec);

SQL Server storage internals

SQL Server stores table data in Pages.  A Page is a the smallest unit of data storage in MS SQL Server.  The page size is 8 KB unit, due to this, the maximum size of a row is 8 KB.

CREATE TABLE Tbl
(
    ID INT,
    Data CHAR(8000),
    Info CHAR(50)
)

The above code will throw error:

Msg 1701, Level 16, State 1, Line 1
Creating or altering table 'T' failed because the minimum row size would be 16011, including 7 bytes of internal overhead. This exceeds the maximum allowable table row size of 8060 bytes.
But when we create table with VARCHAR, TEXT, BLOB types the data get stored out side of the data Page and pointer is get stored in the data row, that the reason we can have table with multiple VARCHAR(MAX) columns.

Every heap table contains a Page called IAM which holds the pointers to all data pages.  You can find this information by querying  SYSINDEXES table.

SELECT [first],[root],[FirstIAM] 
FROM SYSINDEXES 
WHERE ID = OBJECT_ID('Tbl')

For tiny table which has less then 64 KB information the memory allocated page by page.  The moment table reaches more than 64 KB (8 Pages), the memory allocation happens Extend by Extend.  An extend is 8 consecutive pages.  So for the table which are more than 64 KB, the IAM Page consists of pointer to all the Extends of the table.

So internally for any heap table IAM is the starting point which says where the data is stored.  The similar concept applies for Clustered and non-clustered tables.

The following query fill give information about how many pages used for a given table.

SELECT dpages,reserved, [rows]
 FROM SYSINDEXES 
WHERE ID = OBJECT_ID('Tbl') 

To know the data structure of each table type check MSDN

Extend

The next level of memory unit in SQL server is Extend.  An Extend can be Mixed Extend or Uniform Extend.  When an extend consists of pages of single table then it is Uniform Extend.  If an Extend consists of pages which are belong to different tables then it is called Mixed Extend.



File

The MDF, NDF files (data files) which we create for a Database are divided into Extends and used.  The first Extend’s first page in each file is a file header Page that contains information about the file. The header page has details about the address of available free uniform extends GAM, available free mixed extends SGAM, Available pages with free spaces PFS.

• GAM – Global Allocation Map
• List of free uniform extends, which can be allocated.  When ever the Engine needs a new uniform extends, it takes up one from GAM.
• SGAM – Shared Global Allocation Map
• List of free mixed extends, which can be allocated. When ever the Engine needs a new new page, it takes up one from SGAM, scan thru the Extend to occupy the free Page. 
• PFS – Page free space
• List of pages which has some free space to hold new data row.  When a new data row needs to be inserted it checks the table’s associated pages which has free space to accommodate the new row.  If no page available with free space, it takes up new Page / Extend.

For further read check the blog, though it is very old still most of them applicable.

To summarize Database data can be stored in one or more files. Each file consists of logical unit called Extend.  Each extend consists of 8 Pages.  Each Page can hold one or more data rows.