Tuesday, April 3, 2012

.net Interview Questions and Answers


Working as a Sr. Microsoft NET developer with impressive record of achievements in designing developing, implementing, troubleshooting, maintaining multi-tier Web Based and Windows Based Applications. I have 9 years of experience as a Certified Microsoft .Net developer in analysis, design and implementation of web based and windows based client-server applications in Microsoft Technologies.

Front End/Graphical User Interface (GUI) and Middle Tier/Server Side Programming:-

·         Strong experience in developing internet, intranet, N-tier and distributed client-server applications using C#.Net, ASP.Net, ADO.Net, AJAX, JQuery, Jtable, JQGrid, VB.Net, SQL Server, Oracle and OOPS.
·         Expertise in building rich AJAX enabled web applications in .Net and developing multi-tier client-server web applications.
·         Good Knowledge on Web Services (SOAP, WSDL, UDDI), XML, XSLT, .NET Reflection.
·         Experienced in creating business components, custom controls and web services etc.
·         Expert level skills in developing XML and SOAP, HTTP based Web Services, WCF services in .Net including hands on experience in consuming web services using WSDL.
·         Strong experience in preparing deployment packages for web based and windows based applications using .Net technology.

Database Design, Modeling, Stored Procedures, Functions and Views :-

·         Good knowledge in Database development and proficient in writing database Stored Procedures, Triggers, cursors and Complex queries in MS SQL Server, MS Access, and Oracle. Extensive experience in SQL Server Database design, Database maintenance, developing T-SQL queries, DTS Packages, stored procedures, triggers, fine tuning performance, using SQL Server Reporting Services (SSRS), using SQL Server Integration services (SSIS).
·         Efficiently used the database Connectivity API’s (ADO, ADO.Net) in Client Server and Web Applications.
·         Experience on ADO.NET components such as Datasets, Data Adapter, Data Reader, Data View, Data Table and Command object to access and update database.
·         Created user-defined functions, tables, databases, indexes, stored procedures, views, triggers, cursors using T-SQL.

Other Skills:-

·         Good Experience in full Software Development Life Cycle (SDLC) from inception to peak/delivery of project / product.
·         Expert in debugging an application using the debugging tools provided by the Visual Studio.Net IDE.
·         Excellent analytical, problem solving and interpersonal skills.
·         A good team player with excellent technical, communication and interpersonal skills and capable of taking up responsibilities independently and collaboratively in teams.
·         Motivated and determined to deliver productive high quality, complete deliverables within deadlines with minimal supervision.
·         Worked on Agile Software Development methodology based on iterative and incremental development.
·         Participated in Daily Scrum, Sprint Planning and Sprint Review meetings

 





·    Responsible for secure code reviews, static code reviews and testing the vulnerability of code using different tools. And suggesting code level changes to the team.
·    Created the Sequence diagrams to depict the sequence of events for CDC.
·    Responsible for maintaining versioning/Source control for entire project using TFS 2008, Responsible for creating work items, Policies on the solution and Branching.
·    Developed complex web forms using ASP.Net server-side scripting with C#, .NET Framework 4.0, Jquery, JQGrid, JQuery UI
·    Used JavaScript to enhance the dynamics and interactive features of web page by allowing user to perform calculations, Validate form inputs, add special effects, Customize selections, Confirmation etc.
·    Developed web application using MVC3 design pattern with razor view engine.
·    Used Authorization and Action filter to handle the controller action.
·     Created new Grid control to support client side operation like sorting, filtering and searching.
·     Extensively used ADO.NET for interfacing this application with the SQL Server.
·    Worked with Custom Role based Authentication in which authentication and authorization of the application is done from the database
·     Developed user interface screens under Visual Studio.NET 2010 using ASP.NET and C#.
·     Provided support during test and UAT phase of the project
·     Created and updated technical specification documents.
·     Scheduled functional review meetings to explain functionality of the developed modules.
·     Participating in coding, code reviewing, testing, time-box delivery and deployment
·    Participated in the daily stand up SCRUM agile meetings as part of AGILE process for reporting the day to day developments of the work done
·     Created some SSRS reports for showing reports to the customers depending on input parameters.








Responsibilities:
·     Working as module lead for Empower web
·    Responsible for secure code reviews, static code reviews and testing the vulnerability of code using different tools. And suggesting code level changes to the team.
·    Created the Sequence diagrams to depict the sequence of events for Education Testing Service sections.
·    Responsible for maintaining versioning/Source control for entire project using TFS 2008, Responsible for creating work items, Policies on the solution and Branching.
·    Developed complex web forms using ASP.Net server-side scripting with C#, .NET Framework 4.0, Jquery, JQGrid, JQuery UI
·    Used JavaScript to enhance the dynamics and interactive features of web page by allowing user to perform calculations, Validate form inputs, add special effects, Customize selections, Confirmation etc.
·    Developed web application using MVC3 design pattern with razor view engine.
·    Used ASP.NET Routing to handle the incoming user request.
·    Used Authorization and Action filter to handle the controller action.
·     Used DOM for parsing XML document on client side
·     Used call back methods for asynchronous operations on the page
·     Created new Grid control to support client side operation like sorting, filtering and searching.
·     Extensively used ADO.NET for interfacing this application with the Oracle, SQL Server.
·     Used output caching to cache the pages or objects on the server for reusing them
·    Worked with Custom Role based Authentication in which authentication and authorization of the application is done from the database
·     Developed user interface screens under Visual Studio.NET 2010 using ASP.NET and C#.
·     Consumed and created WCF services and web services for some operations
·     Provided support during test and UAT phase of the project
·     Created and updated technical specification documents.
·     Scheduled functional review meetings to explain functionality of the developed modules.
·     Participating in coding, code reviewing, testing, time-box delivery and deployment and on-site training
·    Participated in the daily stand up SCRUM agile meetings as part of AGILE process for reporting the day to day developments of the work done
·     Created SSIS package and scheduled them as SQL job to run at periodic intervals.
·     Created some SSRS reports for showing reports to the customers depending on input parameters.



Description      :
abc is the official website of  xyz An end user (registered or anonymous)can use this site to browse through various verticals of his choice, to add different items to his cart, to do a checkout, to make a payment through credit cards and to get it shipped to a his home address. He can even locate a store near to his house, ship a gift to his friend on a particular date, check the status of a particular order, add items to a wish list for future purchase, get a gift card worth certain amount etc.

        Responsibilities:

  • Responsible for requirement specifications, analysis, technical documentation, architecture design and development.
  • Used MVC design patterns so that model, views and controller should communicate with each other in an effective manner.  Changing the one feature of any of them with not disturb the other functionality.
  • Worked with NUnit framework. Performed unit testing and user acceptance testing using C#.
  • Developed various T-SQL stored procedures triggers, views, and adding/changing tables for data load and transformation, and data extraction.
  • Created database maintenance planner for the performance of SQL Server, which covers Database integrity checks, update Database statistics and re-indexing.
  • Worked on SSRS, and SSIS.
  • Worked in SOAP based architecture for communication in distributed environment.
  • Activities included creating Use Cases, Flowcharts and Component Diagrams and participated in several Performance Analysis Brainstorming sessions and meetings.
  • Used datasets to populate the repeater controls and data grids in ASP.Net web forms.
  • Thread pooling was used to efficiently perform multithreading, Reflection was used to access the properties of object during runtime and Event Handling was implemented to communicate between the layers of MVC.


Description      :
MNC deals with all processes of insurance. Mainly this is divided into 4 modules according to the functional aspects like Policy module, Accounting, Admin and Claim & reporting modules. The policy module deals with all policy related issues such as customer, quotations and application submission. After successful completion of policy processing, customer will be given risked. Accounting module deals with all accounts related processes like deposits, commissions and adjustments. Claim module includes the claiming by a customer in case of loss, and processing that claim. Admin module deals with creating users and groups.

        Responsibilities:

·         Developed Web forms for registration, to update the policy installment pages and application logic in ASP. Net, C# utilizing the power of Microsoft .NET Framework.
·         Used ADO.Net in connecting to Data Access management with SQL Server.
·         Used User Controls for achieving common objective in some of web forms.
  • Involved in analysis, design and code the Accounting module using C# which deals with the clearing of checks, updating the corresponding policy in database.
  • Designed and developed the C# components for implementing business logic which is used to do the transactions on database in middle tier using ADO.NET.
  • Designed and Administered Security policies using role based security and code-based security.                
  • Deposit of the checks by the customer and creating user login and privileges.
  • Deployment of web services for online transactions using C# and exposed them through SOAP and HTTP.
  • Worked on Presentation, Business and Data Access Layers.
  • Used Try Catch Finally exception handling extensively to avoid circumstances leading to abnormal program termination.
  • Used extensively data grids in ASP.NET. Used Edit Item Templates, Footer Templates extensively for editing (modifying, saving) and adding rows.
  • Developed the front-end screens in C# .NET using Visual Studio IDE.
  • Developed web forms with C# to store the customer policy data in Database.
  • Designed, developed the required SQL stored procedures, triggers and database functions in SQL Server 2000.
  • Developed Custom User Controls To reduce the Complexity in User Interface Coding and provide better look and feel.
  • Designed the components for middle tier using C#.
  • Improved the application performance using stored procedures
·         Implemented version controlling using Visual Source Safe. 
·         Implemented Page Level Caching (i.e. Output Caching) for static pages.
·         Developed web forms with C# to store the customer policy data in Database.
·         Generated the required reports with the help of data grid, data list.

What is MVC?
MVC is a framework methodology that divides an application’s implementation into three component roles: models, views, and controllers.
“Models” in a MVC based application are the components of the application that are responsible for maintaining state. Often this state is persisted inside a database (for example: we might have a Product class that is used to represent order data from the Products table inside SQL).

“Views” in a MVC based application are the components responsible for displaying the application’s user interface. Typically this UI is created off of the model data (for example: we might create an Product “Edit” view that surfaces textboxes, dropdowns and checkboxes based on the current state of a Product object).

“Controllers” in a MVC based application are the components responsible for handling end user interaction, manipulating the model, and ultimately choosing a view to render to display UI. In a MVC application the view is only about displaying information – it is the controller that handles and responds to user input and interaction.

What are the 3 main components of an ASP.NET MVC application?
1. M - Model
2. V - View
3. C - Controller

1- In which assembly is the MVC framework defined?
System.Web.Mvc

2- Is it possible to combine ASP.NET webforms and ASP.MVC and develop a single web application?
Yes, it is possible to combine ASP.NET webforms and ASP.MVC and develop a single web application.

3- What does Model, View and Controller represent in an MVC application?
Model: Model represents the application data domain. In short the applications business logic is contained within the model.

View: Views represent the user interface, with which the end users interact. In short the all the user interface logic is contained within the UI.

Controller: Controller is the component that responds to user actions. Based on the user actions, the respective controller, work with the model, and selects a view to render that displays the user interface. The user input logic is contained within the controller.

4- What is the greatest advantage of using asp.net mvc over asp.net webforms?
It is difficult to unit test UI with webforms, where views in mvc can be very easily unit tested.

5- Which approach provides better support for test driven development - ASP.NET MVC or ASP.NET Webforms?
ASP.NET MVC

6- What is Razor View Engine?
Razor view engine is a new view engine created with ASP.Net MVC model using specially designed Razor parser to render the HTML out of dynamic server side code. It allows us to write Compact, Expressive, Clean and Fluid code with new syntaxes to include server side code in to HTML.

7- What are the advantages of ASP.NET MVC?
1. Extensive support for TDD. With asp.net MVC, views can also be very easily unit tested.
2. Complex applications can be easily managed
3. Separation of concerns. Different aspects of the application can be divided into Model, View and Controller.
4. ASP.NET MVC views are light weight, as they donot use viewstate.

8- Is it possible to unit test an MVC application without running the controllers in an ASP.NET process?
Yes, all the features in an asp.net MVC application are interface based and hence mocking is much easier. So, we don't have to run the controllers in an ASP.NET process for unit testing.

9- What is namespace of asp.net mvc?
ASP.NET MVC namespaces and classes are located in the System.Web.Mvc assembly.
System.Web.Mvc namespace
Contains classes and interfaces that support the MVC pattern for ASP.NET Web applications. This namespace includes classes that represent controllers, controller factories, action results, views, partial views, and model binders.
System.Web.Mvc.Ajax namespace
Contains classes that support Ajax scripts in an ASP.NET MVC application. The namespace includes support for Ajax scripts and Ajax option settings.
System.Web.Mvc.Async namespace
Contains classes and interfaces that support asynchronous actions in an ASP.NET MVC application
System.Web.Mvc.Html namespace
Contains classes that help render HTML controls in an MVC application. The namespace includes classes that support forms, input controls, links, partial views, and validation.

10- Is it possible to share a view across multiple controllers?
Yes, put the view into the shared folder. This will automatically make the view available across multiple controllers.

11- What is the role of a controller in an MVC application?
The controller responds to user interactions, with the application, by selecting the action method to execute and also selecting the view to render.

12- Where are the routing rules defined in an asp.net MVC application?
In Application_Start event in Global.asax

13- Name a few different return types of a controller action method?
The following are just a few return types of a controller action method. In general an action method can return an instance of a any class that derives from ActionResult class.
1. ViewResult
2. JavaScriptResult
3.RedirectResult
4.ContentResult5. JsonResult

14- What is the ‘page lifecycle’ of an ASP.NET MVC?
Following process are performed by ASP.Net MVC page:
1) App initialization
2) Routing
3) Instantiate and execute controller
4) Locate and invoke controller action
5) Instantiate and render view

15- What is the significance of NonActionAttribute?
In general, all public methods of a controller class are treated as action methods. If you want prevent this default behaviour, just decorate the public method with NonActionAttribute.

16- What is the significance of ASP.NET routing?
ASP.NET MVC uses ASP.NET routing, to map incoming browser requests to controller action methods. ASP.NET Routing makes use of route table. Route table is created when your web application first starts. The route table is present in the Global.asax file.

17- How route table is created in ASP.NET MVC?
When an MVC application first starts, the Application_Start() method is called. This method, in turn, calls the RegisterRoutes() method. The RegisterRoutes() method creates the route table.

18- What are the 3 segments of the default route, that is present in an ASP.NET MVC application?
1st Segment - Controller Name
2nd Segment - Action Method Name
3rd Segment - Parameter that is passed to the action method

Example:
http://google.com/search/label/MVC
Controller Name = search
Action Method Name = label
Parameter Id = MVC

19- ASP.NET MVC application, makes use of settings at 2 places for routing to work correctly. What are these 2 places?
1. Web.ConfigFile : ASP.NET routing has to be enabled here.
2. Global.asaxFile : The Route table is created in the application Start event handler, of the Global.asax file.
How do you avoid XSS Vulnerabilities in ASP.NET MVC?
Use thesyntax in ASP.NET MVC instead of usingin .net framework 4.0.

20- What is the adavantage of using ASP.NET routing?
In an ASP.NET web application that does not make use of routing, an incoming browser request should map to a physical file. If the file does not exist, we get page not found error.

An ASP.NET web application that does make use of routing, makes use of URLs that do not have to map to specific files in a Web site. Because the URL does not have to map to a file, you can use URLs that are descriptive of the user's action and therefore are more easily understood by users.

21- What are the 3 things that are needed to specify a route?
1. URL Pattern - You can include placeholders in a URL pattern so that variable data can be passed to the request handler without requiring a query string.
2. Handler - The handler can be a physical file such as an .aspx file or a controller class.
3. Name for the Route - Name is optional.

22- Is the following route definition a valid route definition?
{controller}{action}/{id}
No, the above definition is not a valid route definition, because there is no literal value or delimiter between the placeholders. Therefore, routing cannot determine where to separate the value for the controller placeholder from the value for the action placeholder.

23- What is the use of the following default route?
{resource}.axd/{*pathInfo}
This route definition, prevent requests for the Web resource files such as WebResource.axd or ScriptResource.axd from being passed to a controller.

24- What is the difference between adding routes, to a webforms application and to an mvc application?
To add routes to a webforms application, we use MapPageRoute() method of the RouteCollection class, where as to add routes to an MVC application we use MapRoute() method.

25- How do you handle variable number of segments in a route definition?
Use a route with a catch-all parameter. An example is shown below. * is referred to as catch-all parameter.
controller/{action}/{*parametervalues}

26- What are the 2 ways of adding constraints to a route?
1. Use regular expressions
2. Use an object that implements IRouteConstraint interface

27- Give 2 examples for scenarios when routing is not applied?
1. A Physical File is Found that Matches the URL Pattern - This default behaviour can be overriden by setting the RouteExistingFiles property of the RouteCollection object to true.
2. Routing Is Explicitly Disabled for a URL Pattern - Use the RouteCollection.Ignore() method to prevent routing from handling certain requests.

28- What is the use of action filters in an MVC application?
Action Filters allow us to add pre-action and post-action behavior to controller action methods.

29- If I have multiple filters implemented, what is the order in which these filters get executed?
1. Authorization filters
2. Action filters
3. Response filters
4. Exception filters

30- What are the different types of filters, in an asp.net mvc application?
1. Authorization filters
2. Action filters
3. Result filters
4. Exception filters

31- Give an example for Authorization filters in an asp.net mvc application?
1. RequireHttpsAttribute
2. AuthorizeAttribute

32- Which filter executes first in an asp.net mvc application?
Authorization filter

33- What are the levels at which filters can be applied in an asp.net mvc application?
1. Action Method
2. Controller
3. Application

34- Is it possible to create a custom filter?
Yes

35- What filters are executed in the end?
Exception Filters

36- Is it possible to cancel filter execution?
Yes

37- What type of filter doesOutputCacheAttribute class represents?
Result Filter

38- What are the 2 popular asp.net mvc view engines?
1. Razor
2. .aspx

39- What is difference between Viewbag and Viewdata in ASP.NET MVC?
The basic difference between ViewData and ViewBag is that in ViewData instead creating dynamic properties we use properties of Model to transport the Model data in View and in ViewBag we can create dynamic properties without using Model data.

40- What symbol would you use to denote, the start of a code block in razor views?
@

41- What symbol would you use to denote, the start of a code block in aspx views?
<%= %>

In razor syntax, what is the escape sequence character for @ symbol?
The escape sequence character for @ symbol, is another @ symbol

42- When using razor views, do you have to take any special steps to proctect your asp.net mvc application from cross site scripting (XSS) attacks?
No, by default content emitted using a @ block is automatically HTML encoded to protect from cross site scripting (XSS) attacks.

43- When using aspx view engine, to have a consistent look and feel, across all pages of the application, we can make use of asp.net master pages. What is asp.net master pages equivalent, when using razor views?
To have a consistent look and feel when using razor views, we can make use of layout pages. Layout pages, reside in the shared folder, and are named as _Layout.cshtml

44- What are sections?
Layout pages, can define sections, which can then be overriden by specific views making use of the layout. Defining and overriding sections is optional.

45- What are the file extensions for razor views?
1. .cshtml - If the programming lanugaue is C#
2. .vbhtml - If the programming lanugaue is VB

46- How do you specify comments using razor syntax?
Razor syntax makes use of @* to indicate the begining of a comment and *@ to indicate the end.

47- What is Routing?
A route is a URL pattern that is mapped to a handler. The handler can be a physical file, such as an .aspx file in a Web Forms application. Routing module is responsible for mapping incoming browser requests to particular MVC controller actions.

48.Can we share a view across multiple controllers ?
Yes, It is possible to share a view across multiple controllers by putting a view into the shared folder.

By doing like this, you can automatically make the view available across multiple controllers.











49.What is the use of a controller in an MVC applicatio ?

A controller will decide what to do and what to display in the view. It works as follows:



i) A request will be received by the controller

ii) Basing on the request parameters, it will decide the requested activities

iii) Basing on the request parameters, it will delegates the tasks to be performed

iv) Then it will delegate the next view to be shown





50.Mention some of the return types of a controller action method ?

 An action method is used to return an instance of any class which is derived from ActionResult class.

Some of the return types of a controller action method are:

i) ViewResult : It is used to return a webpage from an action method

ii) PartialViewResult : It is used to send a section of a view to be rendered inside another view.

iii) JavaScriptResult : It is used to return JavaScript code which will be executed in the user’s browser.

iv) RedirectResult : Based on a URL, It is used to redirect to another controller and action method.

v) ContentResult : It is an HTTP content type may be of text/plain. It is used to return a custom content type as a result of the action method.

vi) JsonResult : It is used to return a message which is formatted as JSON.

vii) FileResult : It is used to send binary output as the response.

viii) EmptyResult : It returns nothing as the result.





51.Explain about 'page lifecycle' of ASP.NET MVC ?

The page lifecycle of an ASP.NET MVC page is explained as follows:

i) App Initialisation

In this stage, the aplication starts up by running Global.asax’sApplication_Start() method.

In this method, you can add Route objects to the static RouteTable.Routes collection.

If you’re implementing a custom IControllerFactory, you can set this as the active controller factory by assigning it to the System.Web.Mvc.ControllerFactory.Instance property.



ii) Routing

Routing is a stand-alone component that matches incoming requests to IHttpHandlers by URL pattern.

MvcHandler is, itself, an IHttpHandler, which acts as a kind of proxy to other IHttpHandlers configured in the Routes table.



iii) Instantiate and Execute Controller

At this stage, the active IControllerFactory supplies an IController instance.



iv) Locate and invoke controller action

At this stage, the controller invokes its relevant action method, which after further processing, calls RenderView().



v) Instantiate and render view

At this stage, the IViewFactory supplies an IView, which pushes response data to the IHttpResponse object.





52.Explain about NonActionAttribute ?



It is already known that all the public methods of a controller class are basically treated as action methods.

If you dont want this default behaviour, then you can change the public method with NonActionAttribute. Then, the default behaviour changes.





53.Explain about the formation of Router Table in ASP.NET MVC ?

The Router Table is formed by following the below procedure:



In the begining stage, when the ASP.NET application starts, the method known as Application_Start() method is called.

The Application_Start() will then calls RegisterRoutes() method.

This RegisterRoutes() method will create the Router table.



54.Explain the usage of action filters in an MVC application ?

Action filter in an mvc application is used to perform some additional processing, such as providing extra data to the action method, inspecting the return value, or canceling execution of the action method.

Action filter is an attribute that implements the abstract FilterAttribute class.



55.In an MVC application, which filter executes first and which is the last ?

As there are different types of filters in an MVC application, the Authorization filter is the one which executes first and Exeption filters executes in the end.




From constructor to destructor (taking into consideration Dispose() and the concept of nondeterministic finalization), what are the events fired as part of the ASP.NET System.Web.UI.Page lifecycle. Why are they important? What interesting things can you do at each?As all of us know a request comes from Client (Browser) and sends to Server (we call it as Web server) in turn server process the request and sends response back to the client in according to the client request. But internally in the web server there is quite interesting process that happens. To get aware of that
process we should first of all know about the architecture of the IIS
It mainly consists of 3 Parts/Files
 Inetinfo.exec
 ISAPI Filer (Container for Internet Server Application Interface dlls),
 Worker Process (aspnet_wp.exe)
Inetinfo.exe
is the ASP.Net request handler that handles the requests from the client .If it's for static resources like HTML files or image files. inetinfo.exe process the request and sent to client. If the request is with extension aspx/asp, inetinfo.exe processes the request to API filter.
ISAPI filter will have several runtime modules called as ISAPI extensions. To process the request ISAPI filter takes the help of these runtime modules. The runtime module loaded for ASP page is asp.dll. And for ASP.NET page it'sASPNET_ISAPI.dll. From here the request is processed to the "worker process".
Worker Process will have several application domains. Worker process sends the request to HTTPPIPE line.(HTTP Pipeline is nonetheless collection of .net framework classes). HTTP Pipeline compiles the request into a library and makes a call to HTTP runtime
and runtime creates an instance of page class
public class File : System.Web.UI.Page
{}
ASP.Net web page is a class derived from page class, this page class resides in system.web.dll
After creating instance pf page class HTTP Runtime immediately invokes process request method of page class
Page Req = new Page();
Req.ProcessRequest();

Page Event Typical Use
PreInit
Raised after the start stage is complete and before the initialization stage begins.
Use this event for the following:
Check the IsPostBack property to determine whether this is the first time
the page is being processed. The IsCallback and IsCrossPagePostBack
properties have also been set at this time.
 Create or re-create dynamic controls.
 Set a master page dynamically.
 Set the Theme property dynamically.
 Read or set profile property values.
Init
Raised after all controls have been initialized and any skin settings have been
applied. The Init event of individual controls occurs before the Init event of the
page.
Use this event to read or initialize control properties.
InitComplete
Raised at the end of the page's initialization stage. Only one operation takes
place between the Init and InitComplete events: tracking of view state changes is
turned on. View state tracking enables controls to persist any values that are
programmatically added to the ViewState collection. Until view state tracking is
turned on, any values added to view state are lost across postbacks. Controls
typically turn on view state tracking immediately after they raise their Init event.
Use this event to make changes to view state that you want to make sure are
persisted after the next postback.
PreLoad
Raised after the page loads view state for itself and all controls, and after it
processes postback data that is included with the Request instance.
Load
The Page object calls the OnLoad method on the Page object, and then
recursively does the same for each child control until the page and all controls
are loaded. The Load event of individual controls occurs after the Load event of
the page.
Use the OnLoad event method to set properties in controls and to establish
database connections.
Control events
Use these events to handle specific control events, such as a Button control's
Click event or a TextBox control's TextChanged event.
LoadComplete
Raised at the end of the event-handling stage.
Use this event for tasks that require that all other controls on the page be
loaded.
PreRender
Raised after the Page object has created all controls that are required in order to
render the page, including child controls of composite controls. (To do this, the
Page object calls EnsureChildControls for each control and for the page.)
The Page object raises the PreRender event on the Page object, and then
recursively does the same for each child control. The PreRender event of
individual controls occurs after the PreRender event of the page.

Use the event to make final changes to the contents of the page or its controls
before the rendering stage begins.
PreRenderComplete
Raised after each data bound control whose DataSourceID property is set calls its
DataBind method. For more information, see Data Binding Events for Data-
Bound Controls later in this topic.
SaveStateComplete
Raised after view state and control state have been saved for the page and for all
controls. Any changes to the page or controls at this point affect rendering, but
the changes will not be retrieved on the next postback.
Render
This is not an event; instead, at this stage of processing, the Page object calls this
method on each control. All ASP.NET Web server controls have a Render method
that writes out the control's markup to send to the browser.
If you create a custom control, you typically override this method to output the
control's markup. However, if your custom control incorporates only standard
ASP.NET Web server controls and no custom markup, you do not need to
override the Render method. For more information, see Developing Custom
ASP.NET Server Controls.
A user control (an .ascx file) automatically incorporates rendering, so you do not
need to explicitly render the control in code.
Unload
Raised for each control and then for the page.
In controls, use this event to do final cleanup for specific controls, such as closing
control-specific database connections.
For the page itself, use this event to do final cleanup work, such as closing open
files and database connections, or finishing up logging or other request-specific
tasks.
Although both Init and Load recursively occur on each control, they happen in reverse order. The Init
event (and also the Unload event) for each child control occur before the corresponding event is raised
for its container (bottom-up). However the Load event for a container occurs before the Load events for
its child controls (top-down). Master pages behave like child controls on a page: the master page Init
event occurs before the page Init and Load events, and the master page Load event occurs after the
page Init and Load events.
What is EnableViewStateMAC?
Setting EnableViewStateMac=true is a security measure that allows ASP.NET to ensure that the viewstate for a page has not been tampered with. If on Postback, the ASP.NET framework detects that there has been a change in the value of viewstate that was sent to the browser, it raises an error -
Validation of viewstate MAC failed.

Use <%@ Page EnableViewStateMac="true"%> to set it to true (the default value, if this attribute is not specified is also true) in an aspx page.
But this has a side effect: it also prevents multiple servers from processing the same ViewState. One solution is to force every server in your farm to use the same key-- generate a hex encoded 64-bit or 128-bit <machineKey> and put that in each server's machine.config :
<!-- validation="[SHA1|MD5|3DES]" -->
<machineKey validation="SHA1"
validationKey="F3690E7A3143C185A6A8B4D81FD55DD7A69EEAA3B32A6AE813ECEEC" />
What is the difference between asp:Label and asp:Literal control?
asp:Label control

asp:Label control is used to write text on the page with different formatting options like bold, italic, underlined etc
asp:Literal control
Ideally Literal control is the rarely used control which is used to put static text on the web page. When it is rendered on the page, it is implemented just as a simple text.
Unlike asp:Label control, there is no property like BackColor, ForeColor, BorderColor, BorderStyle, BorderWidth, Height etc. of Literal control. That makes it more powerful, you can even put a pure HTML contents into it.
What’s a SESSION and APPLICATION object?
Viewstate -
Viewstate is a hidden fields in an ASP.NET page, contains state of those controls on a page whose "EnableViewstate" property is "true".
You can also explicitly add values in it, on an ASP.NET page like:
Viewstate.Add( "TotalStudents", "87" );
Viewstate should be used when you want to save a value between different roundtrips of a single page as Viewstate of a page is not accessible by another page. Because Viewstate renders with the page, it consumes bandwidth, so be careful to use it in applications to be run on low bandwidth.
Session Variable -
Session variables are usually the most commonly used. When a user visits a site, it's sessions starts and when the user become idle or leave the site, the session ends. Session variables should be used to save and retrieve user specific information required on multiple pages. Session variables consumes server memory, so if your may have a huge amount visitors, use session very carefully and instead of put large values in it try to put IDs and references
Application variables -
Application variables are shared variables among all users of a web application. Application variables behave like static variables and they are substitute of static variables as static variables are stateless in web applications. Only shared values should be persisted in Application variables, and as soon as they are not in use they should be removed explicitly.
Cache -
Cache is probably the least used state feature of ASP.NET. Cache is basically a resource specific state persistence feature, means unlike session it stick with resource instead of user, for instance: pages, controls etc. Cache should be used or frequently used pages, controls, and data structures. Data cache can be used to cache frequently used list of values e.g. list of products
Cookies -
Cookies are some values saved in browsers by the website to retrievable and use afterwards. Usually cookies are used to help dynamic websites to identify visitors and retrieve their saved preferences. Cookies are also used to facilitate auto login by persisting user id in a cookie save in user's browser. Because cookies have been saved at client side, they do not create performance issues but may create security issues as they can be hacked from browser.
What are the different types of caching?
CachingOutput Caching -
We can use Page output for those page which content are relatively static. So rather than generating the page on each user request we can cached the page using Page output caching so that it can be access from cache itself. So, Instead of pages can be generated once and then cached for subsequent request. Page output caching allows the entire content of a given page to be stored in the cache.
<%@ Page Language="C#" %>
<%@ OutputCache Duration='300' VaryByParam='none' %>
Fragment caching -
ASP.NET provides a mechanism for caching portions of pages, called page fragment caching. To cache a portion of a page, you must first encapsulate the portion of the page you want to cache into a user control. In the user control source file, add an OutputCache directive specifying the Duration and VaryByParam attributes. When that user control is loaded into a page at runtime, it is cached, and all subsequent pages that reference that same user control will retrieve it from the cache.
<!— UserControl.ascx —>
<%@ OutputCache Duration='60'
VaryByParam='none' %>
<%@ Control Language="'C#'" %>
Data Caching -
Caching of data can dramatically improve the performance of an application by reducing database contention and round-trips. Simply data caching store the required data in cache so that web server did not send request to DB server every time for each and every request which increase the web site performance.
There are three Different ways to add data or object into cache. But based upon the situation we have to access it. These methods are Cache[], Cache.add(), cache.insert(). The following table will show you the clear difference of these methods.
Is it possible to prevent a browser from caching an ASPX page?
Just call SetNoStore on the HttpCachePolicy object exposed through the Response object's Cache property, as demonstrated here:

What does AspCompat="true" mean and when should I use it?
The AspCompat attribute forces the page to execute in STA mode. The runtime throws an exception if the compatibility tag is omitted and an STA component is referenced on the page. If you convert the STA component to an assembly using Tlbimp.exe, the runtime does not detect that the component uses the STA model and does not throw an exception, but your application can suffer from poor performance.
<%@Page AspCompat=true Language = VB%>
What are the main event handlers in Global.asax?
ASP.NET provides several modules that participate in each request and expose events you can handle in Global.asax. You can customize and extend these modules as you like, or develop completely new custom modules to process information for and about HTTP requests made to your ASP.NET-based
application. Following are important events catered for in the Global.asax file.
  • Application_Init: Fires when the application initializes for the first time.
  • Application_Start: Fires the first time an application starts.
  • Session_Start: Fires the first time when a user’s session is started.
  • Application_BeginRequest: Fires each time a new request comes in.
  • Application_EndRequest: Fires when the request ends.
  • Application_AuthenticateRequest: Indicates that a request is ready to be authenticated.
  •  Application_Error: Fires when an unhandled error occurs within the application.
  •  Session_End: Fires whenever a single user Session ends or times out.
  •  Application_End: Fires when the application ends or times out (Typically used for application
  • cleanup logic).
What are different types of directives in .NET?
@Page
: Defines page-specific attributes used by the ASP.NET page parser and compiler. Can be included
only in .aspx files <%@ Page AspCompat="TRUE" language="C#" %>
@Control: Defines control-specific attributes used by the ASP.NET page parser and compiler. Can be included only in .ascx files. <%@ Control Language="VB" EnableViewState="false" %>

@Import: Explicitly imports a namespace into a page or user control. The Import directive cannot have
more than one namespace attribute. To import multiple namespaces, use multiple @Import directives.
<% @ Import Namespace="System.web" %>
@Implements: Indicates that the current page or user control implements the specified .NET framework
interface.<%@ Implements Interface="System.Web.UI.IPostBackEventHandler" %>
@Register: Associates aliases with namespaces and class names for concise notation in custom server control syntax.<%@ Register Tagprefix="Acme" Tagname="AdRotator" Src="AdRotator.ascx" %>
@Assembly: Links an assembly to the current page during compilation, making all the assembly's classes and interfaces available for use on the page. <%@ Assembly Name="MyAssembly" %><%@ Assembly Src="MySource.vb" %>
@OutputCache: Declaratively controls the output caching policies of an ASP.NET page or a user control contained in a page<%@ OutputCache Duration="#ofseconds" Location="Any | Client | Downstream |
Server | None" Shared="True | False" VaryByControl="controlname" VaryByCustom="browser |
customstring" VaryByHeader="headers" VaryByParam="parametername" %>
@Reference: Declaratively indicates that another user control or page source file should be dynamically
compiled and linked against the page in which this directive is declared.
What are ASHX files? What are HttpHandlers? Where can they be configured?
ASP.NET programming supports the creation of custom HttpHandler components, which provide a flexible and efficient way to process requests that don't return standard HTML-based pages. For example, HttpHandler components are great for situations in which you want to return simple text, XML, or binary data to the user.
The easiest way to create a custom HttpHandler component is to create a source file with an .ashx extension. You must then add a @WebHandler directive to the top of the .ashx file, along with a class definition that implements the IHttpHandler interface. Any class that implements the IHttpHandler interface must provide an implementation of the IsReusable method and the ProcessRequest method.
<%@ Assembly Name="Microsoft.SharePoint, [full assembly name]" %>
<%@ WebHandler Language="C#" Class="HelloHttpHandler" %>
using System;
using System.Web;
using Microsoft.SharePoint;
public class HelloHttpHandler : IHttpHandler {
public bool IsReusable {
get { return false; }
}
public void ProcessRequest(HttpContext context) {
SPSite siteColl = SPContext.Current.Site;
SPWeb site = SPContext.Current.Web;
context.Response.ContentType = "text/plain"
context.Response.Write("Hello HttpHandler from the site " +
site.Title +
" at " +
site.Url);
}
}

After you deploy your .ashx file within a directory nested within the \LAYOUTS directory, it is accessible
to any site in the farm by using a site-relative path.
http://MyWebServer/sites/Sales/_layouts/Litware/HelloHttpHandler.ashx
What is needed to configure a new extension for use in ASP.NET? For example, what if I wanted my
system to serve ASPX files with a *.jsp extension?
It is possible to configure new extension for use in ASP.Net. This as to be configured in IIS actually in
order for IIS to route your pages to the proper ISAPI
Follow this: http://blogs.msdn.com/gduthie/archive/2007/03/14/custom-file-extensions-in-asp-net-2-
0.aspx
What events fire when binding data to a data grid? What are they good for?
ItemCreated: The ItemCreated event is fired when an item in a DataGrid control is created. This means
that at the time the event is fired, the DataGrid does not yet know about the data that will be bound to
it. So, if the logic of your method depends on this data being available to the control, you’re better off
using the ItemDataBound event. Other than that, the ItemCreate event differentiates itself in one other
way from the ItemDataBound event: the ItemCreated event is raised when data is bound to the control
and during round-trips (postbacks). These qualities make the event especially well-suited to add custom
attributes to a DataRow (such as onmouseover or other javascript events) or to control the appearance
in ways that do not depend on the data within the DataRow (such as making every 10th row a different
color).
ItemDataBound: The ItemDataBound event is fired after after an item in a DataGrid control is bound.
This means that (unlike the ItemCreated event) you can add special formatting to a DataRow that is
dependent upon the data contained within that row. Since ItemDataBound is fired after the
ItemCreated event, it is within this event that you are presented with the final opportunity to access the
data before it is rendered to the client. These qualities make the event well-suited for changing the
appearance of a row or cell based on the data within that row or cell (such as highlighting outliers or
other important information).Example:
Assume we have the following DataGrid declared on our .aspx page:
<asp:DataGrid ID="MainDataGrid"
runat="server"
AutoGenerateColumns="true"
OnItemDataBound="MainDataGrid_ItemDataBound"
OnItemCreated="MainDataGrid_ItemCreated" />
On the code behind page then, we can create the following two methods to handle adding
titles to header row, to specify more descriptive headers, and to change the row background
color based on an employee’s salary:

protected void MainDataGrid_ItemCreated(object sender, DataGridItemEventArgs e)
{
//If the item is in the header
if (e.Item.ItemType == ListItemType.Header)
{
//Iterate through each cell
foreach(TableCell item in e.Item.Cells)
{
//Add the title attribute — we could just as easily
//add a javascript onmouseover event here
item.Attributes.Add("title", item.Text);
}
//Since the header values are set before we have access
//to the data, we can modify the third column header to
//be a bit more descriptive
e.Item.Cells[2].Text = "Salary (in US$)";
}
}
protected void MainDataGrid_ItemDataBound(object sender, DataGridItemEventArgs e)
{
//Since DataGrid differentiates between Items and AlternatingItems, you sometimes
have to check
//for one *or* the other
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.Alternating
Item)
{
//Here we will modify the row color based on the salary
//We can only do this within ItemDataBound since it relies
//on the data being available from the data source
if (Convert.ToInt32(e.Item.Cells[2].Text) < 10000)
{
e.Item.BackColor = System.Drawing.Color.LightPink;
}
else if (Convert.ToInt32(e.Item.Cells[2].Text) < 1000000)
{
e.Item.BackColor = System.Drawing.Color.LightBlue;
}
else
{
e.Item.BackColor = System.Drawing.Color.LightGreen;
}
}
}

ASP.NET Code Block Types
Interaction with the page from these blocks is limited, since the code is executed during the render
phase of the page life cycle
<% Trace.Warn("Embedded Code Block", "Hello World"); %>
Let’s have a look at the different types of syntax we can use for code blocks in ASP.NET pages. There are
really four types of code blocks, and the first one is different from the others:
 <%$ %>
 <%# %>
 <% %>
 <%= %>
ASP.NET Expression Syntax
First of all we have ASP.NET expressions which look like <%$ AppSettings:Key %>
<asp:Label runat="server" Text="<%$ AppSettings:Key %>" />
ASP.NET Data-Binding syntax
The next code construct is the data-binding syntax: <%# Eval("Value") %> which is used to bind to
properties to data on demand.
Statement and Expression/Evaluated Code Blocks
Display some values
<%
string message = "Hello World!";
Response.Write(message);
%>
These are delimited by <%= and %> and the content of this code block becomes the parameter to the
HtmlTextWrite.Write() method. Therefore, the code inside this type of code block should be an
expression, and not a statement.
<%= String.Format("The title of this page is: {0}", this.Title ?? "n/a") %>
What method do you use to explicitly kill a user s session?
Session.Abandon
Which two properties are on every validation control?
1. ControlToValidate
2. ErrorMessage
What are the validation controls in asp.net?
There are 5 validation controls in asp.net
1. RequiredFieldValidator
2. RangeValidator
3. RegularExpressionValidator
4. CompareValidator
5. CustomValidator

ValidationSummary is not a validation control but a control that displays summary of all error occurs
while validating the page.
How to load a user control dynamically in runtime?
Control c = (Control)Page.LoadControl("~/usercontrol/MyUserControl.ascx");
Page.Form.Controls.Add(c);
How to get the authentication mode from web.config file programmatically at runtime?
System.Web.Configuration.AuthenticationSection section =
(AuthenticationSection)WebConfigurationManager.GetSection("system.web/authentication");
Label1.Text = section.Mode.ToString();
What’s the difference between Response.Write() and Response.Output.Write()?
Response.Output.Write() allows you to write formatted output.
What’s a bubbled event?
When you have a complex control, like DataGrid, writing an event processing routine for each object
(cell, button, row, etc.) is quite tedious. The controls can bubble up their event handlers, allowing the
main DataGrid event handler to take care of its constituents.
ASP.NET Compilation Tool (Aspnet_compiler.exe)
The ASP.NET Compilation tool (Aspnet_compiler.exe) enables you to compile an ASP.NET Web
application, either in place or for deployment to a target location such as a production server. In-place
compilation helps application performance because end users do not encounter a delay on the first
request to the application while the application is compiled.
Compilation for deployment can be performed in one of two ways: one that removes all source files,
such as code-behind and markup files, or one that retains the markup files.
What is different between WebUserControl and in WebCustomControl?
Web user controls :- Web User Control is Easier to create and another thing is that its support is limited
for users who use a visual design tool one good thing is that its contains static layout one more thing a
separate copy is required for each application.
Web custom controls: - Web Custom Control is typical to create and good for dynamic layout and
another thing is that it has full tool support for user and a single copy of control is required because it is
placed in Global Assembly cache.
What is smart navigation?
Enable smart navigation by using the Page.SmartNavigation property. When you set the
Page.SmartNavigation property to true, the following smart navigation features are enabled:

 The scroll position of a Web page is maintained after postback.
 The element focus on a Web page is maintained during navigation.
 Only the most recent Web page state is retained in the Web browser history folder.
 The flicker effect that may occur on a Web page during navigation is minimized.
Note: Smart navigation is deprecated in Microsoft ASP.NET 2.0 and is no longer supported by Microsoft
Product Support Services
How many types of cookies are there in ASP.NET ?
 In-memory cookies: An in-memory cookie goes away when the user shuts the browser down.
 Persistent cookies: A persistent cookie resides on the hard drive of the user and is retrieved
when the user comes back to the Web page.
If you create a cookie without specifying an expiration date, you are creating an in-memory cookie,
which lives for that browser session only. The following illustrates the script that would be used for an
in-memory cookie:
Response.Cookies("SiteArea") = "TechNet"
The following illustrates the script used to create a persistent cookie:
Response.Cookies("SiteArea") = "TechNet"
Response.Cookies("SiteArea").Expires = "August 15, 2000"
TODO List
Explain how PostBacks work, on both the client-side and server-side. How do I chain my own JavaScript?
How does ViewState work and why is it either useful or evil?
What is the OO relationship between an ASPX page and its CS/VB code behind file in ASP.NET 1.1? In
2.0?
What happens from the point an HTTP request is received on a TCP/IP port up until the Page fires the
On_Load event?
How does IIS communicate at runtime with ASP.NET? Where is ASP.NET at runtime in IIS5? IIS6?
What is an assembly binding redirect? Where are the places an administrator or developer can affect
how assembly binding policy is applied?
Compare and contrast LoadLibrary(), CoCreateInstance(), CreateObject() and Assembly.Load().

What is MVC?
MVC is a framework methodology that divides an application’s implementation into three component
roles: models, views, and controllers.
“Models” in a MVC based application are the components of the application that are responsible for
maintaining state. Often this state is persisted inside a database (for example: we might have a Product
class that is used to represent order data from the Products table inside SQL).
“Views” in a MVC based application are the components responsible for displaying the application’s
user interface. Typically this UI is created off of the model data (for example: we might create an
Product “Edit” view that surfaces textboxes, dropdowns and checkboxes based on the current state of a
Product object).
“Controllers” in a MVC based application are the components responsible for handling end user
interaction, manipulating the model, and ultimately choosing a view to render to display UI. In a MVC
application the view is only about displaying information – it is the controller that handles and responds
to user input and interaction.
Which are the advantages of using MVC Framework?
MVC is one of the most used architecture pattern in ASP.NET and this is one of those ASP.NET interview
question to test that do you really understand the importance of model view controller.
It provides a clean separation of concerns between UI and model.
1. UI can be unit test thus automating UI testing.
2. Better reuse of views and model. You can have multiple views which can point to the same
model and also vice versa.
3. Code is better organized.
What is Razor View Engine?
Razor view engine is a new view engine created with ASP.Net MVC model using specially designed Razor
parser to render the HTML out of dynamic server side code. It allows us to write Compact, Expressive,
Clean and Fluid code with new syntaxes to include server side code in to HTML.
What is namespace of asp.net MVC?
ASP.NET MVC namespaces and classes are located in the System.Web.Mvc assembly.
System.Web.Mvc namespace
Contains classes and interfaces that support the MVC pattern for ASP.NET Web applications. This
namespace includes classes that represent controllers, controller factories, action results, views, partial
views, and model binders.
System.Web.Mvc.Ajax namespace
Contains classes that support Ajax scripts in an ASP.NET MVC application. The namespace includes
support for Ajax scripts and Ajax option settings.
System.Web.Mvc.Async namespace
Contains classes and interfaces that support asynchronous actions in an ASP.NET MVC application

System.Web.Mvc.Html namespace
Contains classes that help render HTML controls in an MVC application. The namespace includes classes
that support forms, input controls, links, partial views, and validation.
How to identify AJAX request with C# in MVC.NET?
The solution is in depended from MVC.NET framework and universal across server-side technologies.
Most modern AJAX applications utilize XmlHTTPRequest to send async request to the server. Such
requests will have distinct request header:
X-Requested-With = XMLHTTPREQUEST
MVC.NET provides helper function to check for Ajax requests which internally inspects X-Requested-
With request header to set IsAjax flag.
HelperPage.IsAjax Property
Gets a value that indicates whether Ajax is being used during the request of the Web page.
Namespace: System.Web.WebPages
Assembly: System.Web.WebPages.dll
However, same can be achieved by checking requests header directly:
Request["X-Requested-With"] == “XmlHttpRequest”
What is Repository Pattern in ASP.NET MVC?
Repository pattern is useful for decoupling entity operations form presentation, which allows easy
mocking and unit testing.
“The Repository will delegate to the appropriate infrastructure services to get the job done.
Encapsulating in the mechanisms of storage, retrieval and query is the most basic feature of a Repository
implementation”
“Most common queries should also be hard coded to the Repositories as methods.”

Which MVC.NET to implement repository pattern Controller would have 2 constructors on
parameterless for framework to call, and the second one which takes repository as an input:
class myController: Controller
{
private IMyRepository repository;
// overloaded constructor
public myController(IMyRepository repository)
{
this.repository = repository;
}
// default constructor for framework to call
public myController()
{
//concreate implementation
myController(new someRepository());
}
...
public ActionResult Load()
{
// loading data from repository
var myData = repository.Load();
}
}
What is difference between MVC (Model-View-Controller) and MVP(Model-View-Presenter)?
The main difference between the two is how the manager (controller/presenter) sits in the overall
architecture.
All requests go first to the Controller
MVC pattern puts the controller as the main ‘guy’ in charge for running the show. All application request
comes through straight to the controller, and it will decide what to do with the request.
Giving this level of authority to the controller isn’t an easy task in most cases. Users interaction in an
application happen most of the time on the View.
Thus to adopt MVC pattern in a web application, for example, the url need to become a way of
instantiating a specific controller, rather than ‘simply’ finding the right View (webform/ html page) to
render out. Every requests need to trigger the instantiation of a controller which will eventually produce
a response to the user.

This is the reason why it’s alot more difficult to implement pure MVC using Asp.Net Webform. The Url
routing system in Asp.Net webform by default is tied in to the server filesystem or IIS virtual directory
structure. Each of these aspx files are essentially Views which will always get called and instantiated first
before any other classes in the project. (Of course I’m overgeneralizing here. Classes like IHttpModule,
IHttpHandler and Global.asax would be instantiated first before the aspx web form pages).
MVP (Supervising Controller) on the other hand, doesn’t mind for the View to take on a bigger role.
View is the first object instantiated in the execution pipeline, which then responsible for passing any
events that happens on itself to the Presenter.
The presenter then fetch the Models, and pass it back to the view for rendering.
What is the ‘page lifecycle’ of an ASP.NET MVC?
Following process are performed by ASP.Net MVC page:
1) App initialization
2) Routing
3) Instantiate and execute controller
4) Locate and invoke controller action
5) Instantiate and render view
How to call javascript function on the change of Dropdown List in ASP.NET MVC?
Create a java-script function:
<script type="text/javascript">
function selectedIndexChanged() {
}
</script>
Call the function:
<%:Html.DropDownListFor(x => x.SelectedProduct,
new SelectList(Model.Products, "Value", "Text"),
"Please Select a product", new { id = "dropDown1",
onchange="selectedIndexChanged()" })%>
How route table is created in ASP.NET MVC?
When an MVC application first starts, the Application_Start() method is called. This method, in turn, calls
the RegisterRoutes() method. The RegisterRoutes() method creates the route table.
How do you avoid XSS Vulnerabilities in ASP.NET MVC?
Use the syntax in ASP.NET MVC instead of using in .net framework 4.0.

Explain how to access Viewstate values of this page in the next page?
PreviousPage property is set to the page property of the nest page to access the viewstate value of the
page in the next page.
Page poster = this.PreviousPage;
Once that is done, a control can be found from the previous page and its state can be read.
Label posterLabel = poster.findControl("myLabel");
string lbl = posterLabel.Text;
What is difference between Viewbag and Viewdata in ASP.NET MVC?
The basic difference between ViewData and ViewBag is that in ViewData instead creating dynamic
properties we use properties of Model to transport the Model data in View and in ViewBag we can
create dynamic properties without using Model data.
What is Routing?
A route is a URL pattern that is mapped to a handler. The handler can be a physical file, such as an .aspx
file in a Web Forms application. Routing module is responsible for mapping incoming browser requests
to particular MVC controller actions.
Is it possible to combine ASP.NET webforms and ASP.MVC and develop a single web application?
Yes, it is possible to combine ASP.NET webforms and ASP.MVC and develop a single web application.
Is it possible to unit test an MVC application without running the controllers in an ASP.NET process?
Yes, all the features in an asp.net MVC application are interface based and hence mocking is much
easier. So, we don't have to run the controllers in an ASP.NET process for unit testing.
Is it possible to share a view across multiple controllers?
Yes, put the view into the shared folder. This will automatically make the view available across multiple
controllers.
What is the role of a controller in an MVC application?
The controller responds to user interactions, with the application, by selecting the action method to
execute and also selecting the view to render.
Where are the routing rules defined in an asp.net MVC application?
In Application_Start event in Global.asax

Name a few different return types of a controller action method?
The following are just a few return types of a controller action method. In general an action method can
return an instance of a any class that derives from ActionResult class.
1. ViewResult
2. JavaScriptResult
3. RedirectResult
4. ContentResult
5. JsonResult
What is the significance of NonActionAttribute?
In general, all public methods of a controller class are treated as action methods. If you want prevent
this default behavior, just decorate the public method with NonActionAttribute.
What is the significance of ASP.NET routing?
ASP.NET MVC uses ASP.NET routing, to map incoming browser requests to controller action methods.
ASP.NET Routing makes use of route table. Route table is created when your web application first starts.
The route table is present in the Global.asax file.
What are the 3 segments of the default route, that is present in an ASP.NET MVC application?
1st Segment - Controller Name
2nd Segment - Action Method Name
3rd Segment - Parameter that is passed to the action method
Example: http://dotnetcodes.com/Article/id/5
Controller Name = Article
Action Method Name = id
Parameter Id = 5
ASP.NET MVC application makes use of settings at 2 places for routing to work correctly. What are these
2 places?
1. Web.Config File: ASP.NET routing has to be enabled here.
2. Global.asax File: The Route table is created in the application Start event handler, of the
Global.asax file.
What is the advantage of using ASP.NET routing?
In an ASP.NET web application that does not make use of routing, an incoming browser request should
map to a physical file. If the file does not exist, we get page not found error.
An ASP.NET web application that does make use of routing makes use of URLs that do not have to map
to specific files in a Web site. Because the URL does not have to map to a file, you can use URLs that are
descriptive of the user's action and therefore are more easily understood by users.

What are the 3 things that are needed to specify a route?
1. URL Pattern - You can include placeholders in a URL pattern so that variable data can be passed
to the request handler without requiring a query string.
2. Handler - The handler can be a physical file such as an .aspx file or a controller class.
3. Name for the Route - Name is optional.
Is the following route definition a valid route definition?
{controller}{action}/{id}
No, the above definition is not a valid route definition, because there is no literal value or delimiter
between the placeholders. Therefore, routing cannot determine where to separate the value for the
controller placeholder from the value for the action placeholder.
What is the use of the following default route?
{resource}.axd/{*pathInfo}
This route definition, prevent requests for the Web resource files such as WebResource.axd or
ScriptResource.axd from being passed to a controller.
What is the difference between adding routes, to a webforms application and to an MVC application?
To add routes to a webforms application, we use MapPageRoute() method of the RouteCollection class,
where as to add routes to an MVC application we use MapRoute() method.
How do you handle variable number of segments in a route definition?
Use a route with a catch-all parameter. An example is shown below. * is referred to as catch-all
parameter.
controller/{action}/{*parametervalues}
What are the 2 ways of adding constraints to a route?
1. Use regular expressions
2. Use an object that implements IRouteConstraint interface
Give 2 examples for scenarios when routing is not applied?
1. A Physical File is found that Matches the URL Pattern - This default behavior can be overridden
by setting the RouteExistingFiles property of the RouteCollection object to true.
2. Routing Is Explicitly Disabled for a URL Pattern - Use the RouteCollection.Ignore() method to
prevent routing from handling certain requests.
What is the use of action filters in an MVC application?
Action Filters allow us to add pre-action and post-action behavior to controller action methods.

If I have multiple filters implanted, what is the order in which these filters get executed?

  •  Authorization filters
  • Action filters
  • Response filters
  •  Exception filters
Give an example for Authorization filters in an asp.net MVC application?
  • RequireHttpsAttribute
  • AuthorizeAttribute
Which filter executes first in an asp.net MVC application?
Authorization filter
What are the levels at which filters can be applied in an asp.net MVC application?

  • Action Method
  • Controller
  • Application
Is it possible to create a custom filter?
Yes
What filters are executed in the end?
Exception Filters
Is it possible to cancel filter execution?
Yes
What type of filter does OutputCacheAttribute class represents?
Result Filter
What are the 2 popular asp.net MVC view engines?
1. Razor
2. .aspx
What symbol would you use to denote, the start of a code block in razor views?
@
What symbol would you use to denote, the start of a code block in aspx views?
<%= %>
In razor syntax, what is the escape sequence character for @ symbol?

The escape sequence character for @ symbol, is another @ symbol
When using razor views, do you have to take any special steps to protect your asp.net MVC application
from cross site scripting (XSS) attacks?
No, by default content emitted using a @ block is automatically HTML encoded to protect from cross
site scripting (XSS) attacks.
When using aspx view engine, to have a consistent look and feel, across all pages of the application, we
can make use of asp.net master pages. What is asp.net master pages equivalent, when using razor
views?
To have a consistent look and feel when using razor views, we can make use of layout pages. Layout
pages, reside in the shared folder, and are named as _Layout.cshtml
What are sections?
Layout pages, can define sections, which can then be overridden by specific views making use of the
layout. Defining and overriding sections is optional.
What are the file extensions for razor views?

  •  .cshtml - If the programming language is C#
  •  .vbhtml - If the programming language is VB
How do you specify comments using razor syntax?
Razor syntax makes use of @* to indicate the beginning of a comment and *@ to indicate the end. An
example is shown below.
@* This is a Comment *@

Design Pattern
Command
Encapsulate a request as an object, thereby letting you parameterize clients with different requests,
queue or log requests, and support undoable operations.
/*the Command interface*/
public interface Command {
void execute();
}
/*the Invoker class*/
import java.util.List;
import java.util.ArrayList;
public class Switch {
private List<Command> history = new ArrayList<Command>();
public Switch() {
}
public void storeAndExecute(Command cmd) {
this.history.add(cmd); // optional
cmd.execute();
}
}
/*the Receiver class*/
public class Light {
public Light() {
}
public void turnOn() {
System.out.println("The light is on");
}
public void turnOff() {
System.out.println("The light is off");
}

}
/*the Command for turning on the light - ConcreteCommand #1*/
public class FlipUpCommand implements Command {
private Light theLight;
public FlipUpCommand(Light light) {
this.theLight = light;
}
public void execute(){
theLight.turnOn();
}
}
/*the Command for turning off the light - ConcreteCommand #2*/
public class FlipDownCommand implements Command {
private Light theLight;
public FlipDownCommand(Light light) {
this.theLight = light;
}
public void execute() {
theLight.turnOff();
}
}
/*The test class or client*/
public class PressSwitch {
public static void main(String[] args){
Light lamp = new Light();
Command switchUp = new FlipUpCommand(lamp);
Command switchDown = new FlipDownCommand(lamp);

Switch s = new Switch();
try {
if (args[0].equalsIgnoreCase("ON")) {
s.storeAndExecute(switchUp);
System.exit(0);
}
if (args[0].equalsIgnoreCase("OFF")) {
s.storeAndExecute(switchDown);
System.exit(0);
}
System.out.println("Argument \"ON\" or \"OFF\" is required.");
} catch (Exception e) {
System.out.println("Argument's required.");
}
}
}
Iterator
Provide a way to access the elements of an aggregate object sequentially without exposing its
underlying representation.
Null object
Avoid null references by providing a default object.
/* Null Object Pattern implementation:
*/
using System;
// Animal interface is the key to compatibility for Animal implementations
below.
interface IAnimal
{
void MakeSound();
}
// Dog is a real animal.
class Dog : IAnimal
{
public void MakeSound()
{
Console.WriteLine("Woof!");
}
}

// The Null Case: this NullAnimal class should be instantiated and used in
place of C# null keyword.
class NullAnimal : IAnimal
{
public void MakeSound()
{
// Purposefully provides no behaviour.
}
}
/* =========================
* Simplistic usage example in a Main entry point.
*/
static class Program
{
static void Main()
{
IAnimal dog = new Dog();
dog.MakeSound(); // outputs "Woof!"
/* Instead of using C# null, use a NullAnimal instance.
* This example is simplistic but conveys the idea that if a
NullAnimal instance is used then the program
* will never experience a .NET System.NullReferenceException at
runtime, unlike if C# null was used.
*/
IAnimal unknown = new NullAnimal(); //<< replaces: IAnimal unknown =
null;
unknown.MakeSound(); // outputs nothing, but does not throw a runtime
exception
}
}
Observer or Publish/subscribe
Define a one-to-many dependency between objects where a state change in one object results in all its
dependents being notified and updated automatically.
// Observer pattern -- Structural example
using System;
using System.Collections.Generic;
namespace DoFactory.GangOfFour.Observer.Structural

{
/// <summary>
/// MainApp startup class for Structural
/// Observer Design Pattern.
/// </summary>
class MainApp
{
/// <summary>
/// Entry point into console application.
/// </summary>
static void Main()
{
// Configure Observer pattern
ConcreteSubject s = new ConcreteSubject();
s.Attach(new ConcreteObserver(s, "X"));
s.Attach(new ConcreteObserver(s, "Y"));
s.Attach(new ConcreteObserver(s, "Z"));
// Change subject and notify observers
s.SubjectState = "ABC";
s.Notify();
// Wait for user
Console.ReadKey();
}
}
/// <summary>
/// The 'Subject' abstract class
/// </summary>
abstract class Subject
{
private List<Observer> _observers = new List<Observer>();
public void Attach(Observer observer)
{
_observers.Add(observer);
}
public void Detach(Observer observer)
{
_observers.Remove(observer);
}
public void Notify()
{
foreach (Observer o in _observers)
{
o.Update();
}
}
}
/// <summary>
/// The 'ConcreteSubject' class
/// </summary>
class ConcreteSubject : Subject
{
private string _subjectState;
// Gets or sets subject state
public string SubjectState
{
get { return _subjectState; }
set { _subjectState = value; }

}
}
/// <summary>
/// The 'Observer' abstract class
/// </summary>
abstract class Observer
{
public abstract void Update();
}
/// <summary>
/// The 'ConcreteObserver' class
/// </summary>
class ConcreteObserver : Observer
{
private string _name;
private string _observerState;
private ConcreteSubject _subject;
// Constructor
public ConcreteObserver(
ConcreteSubject subject, string name)
{
this._subject = subject;
this._name = name;
}
public override void Update()
{
_observerState = _subject.SubjectState;
Console.WriteLine("Observer {0}'s new state is {1}",
_name, _observerState);

}
// Gets or sets subject
public ConcreteSubject Subject
{
get { return _subject; }
set { _subject = value; }
}
}
}
Visitor
Represent an operation to be performed on the elements of an object structure. Visitor lets you define a
new operation without changing the classes of the elements on which it operates.
/ Visitor pattern -- Real World example
using System;
using System.Collections.Generic;
namespace DoFactory.GangOfFour.Visitor.RealWorld
{
/// <summary>
/// MainApp startup class for Real-World
/// Visitor Design Pattern.
/// </summary>
class MainApp
{
/// <summary>
/// Entry point into console application.
/// </summary>
static void Main()
{
// Setup employee collection
Employees e = new Employees();
e.Attach(new Clerk());

e.Attach(new Director());
e.Attach(new President());
// Employees are 'visited'
e.Accept(new IncomeVisitor());
e.Accept(new VacationVisitor());
// Wait for user
Console.ReadKey();
}
}
/// <summary>
/// The 'Visitor' interface
/// </summary>
interface IVisitor
{
void Visit(Element element);
}
/// <summary>
/// A 'ConcreteVisitor' class
/// </summary>
class IncomeVisitor : IVisitor
{
public void Visit(Element element)
{
Employee employee = element as Employee;
// Provide 10% pay raise
employee.Income *= 1.10;
Console.WriteLine("{0} {1}'s new income: {2:C}",
employee.GetType().Name, employee.Name,

employee.Income);
}
}
/// <summary>
/// A 'ConcreteVisitor' class
/// </summary>
class VacationVisitor : IVisitor
{
public void Visit(Element element)
{
Employee employee = element as Employee;
// Provide 3 extra vacation days
Console.WriteLine("{0} {1}'s new vacation days: {2}",
employee.GetType().Name, employee.Name,
employee.VacationDays);
}
}
/// <summary>
/// The 'Element' abstract class
/// </summary>
abstract class Element
{
public abstract void Accept(IVisitor visitor);
}
/// <summary>
/// The 'ConcreteElement' class
/// </summary>
class Employee : Element
{

private string _name;
private double _income;
private int _vacationDays;
// Constructor
public Employee(string name, double income,
int vacationDays)
{
this._name = name;
this._income = income;
this._vacationDays = vacationDays;
}
// Gets or sets the name
public string Name
{
get { return _name; }
set { _name = value; }
}
// Gets or sets income
public double Income
{
get { return _income; }
set { _income = value; }
}
// Gets or sets number of vacation days
public int VacationDays
{
get { return _vacationDays; }
set { _vacationDays = value; }
}

public override void Accept(IVisitor visitor)
{
visitor.Visit(this);
}
}
/// <summary>
/// The 'ObjectStructure' class
/// </summary>
class Employees
{
private List<Employee> _employees = new List<Employee>();
public void Attach(Employee employee)
{
_employees.Add(employee);
}
public void Detach(Employee employee)
{
_employees.Remove(employee);
}
public void Accept(IVisitor visitor)
{
foreach (Employee e in _employees)
{
e.Accept(visitor);
}
Console.WriteLine();
}
}

// Three employee types
class Clerk : Employee
{
// Constructor
public Clerk()
: base("Hank", 25000.0, 14)
{
}
}
class Director : Employee
{
// Constructor
public Director()
: base("Elly", 35000.0, 16)
{
}
}
class President : Employee
{
// Constructor
public President()
: base("Dick", 45000.0, 21)
{
}
}
}

Singleton
Ensure a class has only one instance, and provide a global point of access to it.
/// <summary>
/// MainApp startup class for Structural
/// Singleton Design Pattern.
/// </summary>
class MainApp
{
/// <summary>
/// Entry point into console application.
/// </summary>
static void Main()
{
// Constructor is protected -- cannot use new
Singleton s1 = Singleton.Instance();
Singleton s2 = Singleton.Instance();
// Test for same instance
if (s1 == s2)
{
Console.WriteLine("Objects are the same instance");
}
// Wait for user
Console.ReadKey();
}
}
/// <summary>
/// The 'Singleton' class
/// </summary>
class Singleton
{
private static Object _Lock = new Object();
private static Singleton _instance;
// Constructor is 'protected'
protected Singleton()
{
}
public static Singleton Instance()
{
// Uses lazy initialization.
// Note: this not thread safe.
if (_instance == null)
{
lock (_Lock)
{
if(_instance == null)
_instance = new Singleton();
}
}
return _instance;
}
}

Adapter or Wrapper or Translator
Convert the interface of a class into another interface clients expect. An adapter lets classes work
together that could not otherwise because of incompatible interfaces. The enterprise integration
pattern equivalent is the Translator.
using System;
namespace DoFactory.GangOfFour.Adapter.Structural
{
/// <summary>
/// MainApp startup class for Structural
/// Adapter Design Pattern.
/// </summary>
class MainApp
{
/// <summary>
/// Entry point into console application.
/// </summary>
static void Main()
{
// Create adapter and place a request
Target target = new Adapter();
target.Request();
// Wait for user
Console.ReadKey();
}
}
/// <summary>
/// The 'Target' class
/// </summary>
class Target
{
public virtual void Request()

{
Console.WriteLine("Called Target Request()");
}
}
/// <summary>
/// The 'Adapter' class
/// </summary>
class Adapter : Target
{
private Adaptee _adaptee = new Adaptee();
public override void Request()
{
// Possibly do some other work
// and then call SpecificRequest
_adaptee.SpecificRequest();
}
}
/// <summary>
/// The 'Adaptee' class
/// </summary>
class Adaptee
{
public void SpecificRequest()
{
Console.WriteLine("Called SpecificRequest()");
}
}
}
Flyweight
Use sharing to support large numbers of similar objects efficiently.

Proxy
Provide a surrogate or placeholder for another object to control access to it.
interface Image {
void displayImage();
}
// on System A
class RealImage implements Image {
private String filename;
public RealImage(String filename) {
this.filename = filename;
loadImageFromDisk();
}
private void loadImageFromDisk() {
System.out.println("Loading " + filename);
}
public void displayImage() {
System.out.println("Displaying " + filename);
}
}
//on System B
class ProxyImage implements Image {
private String filename;
private RealImage image;
public ProxyImage(String filename) {
this.filename = filename;
}
public void displayImage() {
if (image == null) {
image = new RealImage(filename);
}
image.displayImage();
}
}
class ProxyExample {

public static void main(String[] args) {
Image image1 = new ProxyImage("HiRes_10MB_Photo1");
Image image2 = new ProxyImage("HiRes_10MB_Photo2");
image1.displayImage(); // loading necessary
image1.displayImage(); // loading unnecessary
image2.displayImage(); // loading necessary
image2.displayImage(); // loading unnecessary
image1.displayImage(); // loading unnecessary
}
}
Inversion of Control
Everybody has probably seen (or written) code like this before:
public class EmailService
{
public void SendMessage() { ... }
}
public class NotificationSystem
{
private EmailService svc;
public NotificationSystem()
{
svc = new EmailService();
}
public void InterestingEventHappened()
{
svc.SendMessage();
}
}
Above NoticicationSystem has a dependency on EmailService.
The inversion of control (IoC) pattern is abstract; it says that one should move dependency creation out
of the consumer class, but it doesn’t talk about exactly how to achieve that. In the following sections,
we’ll explore two popular ways to apply the inversion of control pattern to achieve this responsibility
shift: service locator and dependency injection.
public interface IMessagingService
{
void SendMessage();
}
public class EmailService : IMessagingService
{
public void SendMessage() { ... }
}

public class NotificationSystem
{
private IMessagingService svc;
public NotificationSystem()
{
svc = new EmailService();
}
public void InterestingEventHappened()
{
svc.SendMessage();
}
}
Design Pattern: Service Locator
Now if you re-write NotificationSystem in terms of the strongly-typed service locator, it might look like
this:
Strongly typed service locator
public interface IServiceLocator
{
object GetService(Type serviceType);
TService GetService<TService>();
}
public static class ServiceLocatorExtensions
{
public static TService GetService<TService>(this IServiceLocator locator)
{
return (TService)locator.GetService(typeof(TService));
}
}
public class NotificationSystem
{
private IMessagingService svc;
public NotificationSystem(IServiceLocator locator)
{
svc = locator.GetService<IMessagingService>();
}
public void InterestingEventHappened()
{
svc.SendMessage();
}
}
Design Pattern: Dependency Injection
The dependency injection (DI) pattern is another form of the inversion of control pattern, where in there
is no intermediary object like the service locator. Instead, components are written in a way that allows
their dependencies to be stated explicitly, usually by way of constructor parameters or property setters.
Constructor Injection







.NET basics Key words Your answer


IL code Partially compiled code



JIT(just in time) IL code to machine language



Why is ithalf compiled ? So that at run time we can detect the machine configuration , operating system give out optimized machine compiled code.



CLR Heart of the engine , GC , compilation , CAS(Code access security) , CV ( Code verification)



CTS Common data type for different .net languages so that integration is seamless



CLS Sepcification / Guidliness of the source code



CAS It helps assign the .NET code permission of what they are capable of doing.



where do we use CAS When we consume third party components which are not safe



What  has happed for CAS .NET 4.0 ?




How does the .NET code compile ? write  the code (c#,vb.net,c++.net)-- compiles to  a IL code -- CLR gives it to JIT -- JIT compiles to the machine specific language



Managed code x`



Assembly Its  a unit deployment ( EXE , DLL)



DLL and EXE EXE - Self starting , DLL - You have to link



Manifest it describes more about the assembly ( Meta data):-Versioning, References , dependent objects , Security , Scope



how do you do versioning Assemblyinfo.cs / right click properties -- assembly information



What are strongly typed and weakly typed references ? They help you to identify the publisher. Strong typed means we identify the DLL with a public key token while weak references are just simple references and identified only by class names









What is DLL Hell ? versioning done here



what is ref and out ?




Strong names They ensure that the class name is unique  to avoid confusion 



Delay signgin This is meant to protect DLL identity from your internal team developers , public key is give to the developer byextracting using SN.EXE -p , when you ready to go for  production you will onject the private key in the DLL SN.EXE -R



sn.exe




Value and Reference types Values types int , double , bool etc , Reference types objects and strings. Value types are allocated on stack and reference types are allocated on heap. Value types are stored on different memory locations while reference types point to the same memory location



Stack , heap  They are memry types .Stack has value types , heap has reference types



 boxing , unboxing Box - Convert from value to reference , unboxing - reference type to value



Does unboxing and boxing bring down performance ? yes they do bring down performance because data has to jump between heap and stack memory types.



How can we avoid Boxing and unboxing ? You can not avoid it just minimize it



Garbage collector garbage collection is continous back ground thread which keep running and claims unused managed memmory.



How does the GC run Its run in background



Does garbage collector clean unmanaged code ? No



Do I need to write clean up code for managed objects ? No, GC is smart to figure out unused objects and clean them up



So what do I do for unmanaged ? You need to write the cleanup in destructor



What kind of problems do we have with destructors from GC pewrspecticve ? lot of objects created in gen1



What are gen0 ,1 and 2? Age of the objects.Gen 0 has newly created objects , gen 1 has objects which are bit older  , while gen 2  has objects which is more older than gen 1.This is for GC optimization , garbage collector checks Gen 0 frequently rather than Gen 1 and Gen 2.



Finalize a.k.a destructor It’s a desctructor for cleaning up unmanaged objects.Decreases performance because of  objects move more to Generation 1. The time GC sees a destructor in a class it moves them to Gen 1 



how to ensure that lot of objects are not created in Gen 1 with class having destructor ? Implement Idisposable -- Expose a Dispose function -- Call GC.SuppressFinalize



What is Dispose function ? Its  method exposed for clean up for client,Idisposable , Dispose , GC.SuppressFinalize



Finalize and Dispose Finalize is a destructor and dispose is function which is implemented via Idisposable.Finalize is nondeterministic since we dont call it. It is up to the GC to call it. However dispose gives more control to the developer to perform cleanup. Since we can call it explicitly.



can we force garbage collector Yes , GC.Collect









What are namespaces ? They help us to logically group classes.









OOP




Why do we need  object oriented programming ? 1.No thinking in terms of real world 2. Reusability 3. Extensibility  4 Simplicity and maintainability



Important principles APIE (OOPs ( Encapsulation , inheritance , Polymorohish and Abstraction))



How is the reusability problem solved in OOPs ? Its solved by using classes and creating objects wherever necessary



What is a class and what is aobject  Class -> Blue print , template , Objects -> Bring life in Class , they make them live Create a simple windows screen , which has supplier name , supplier code , supplier date , button calculate payment date , supplier date + 1 month


Encapsulation Hide the complexity and make your object simple for external world.



How do we implement encapsulation Encapsulation is implemented by using access modifiers



Can namespace span across DLL yes , namespace are logical grouping of classes they can span



Abstraction Show only what  is necessary and its more a thought process and abstraction is implemented by encapsulation



Abstraction and Abstract classes Abstraction is OOPs concept and Abstract classes is half defined classes



difference  between Abstraction and Encapsulation Abtsraction complement Encapsulation , Encapsulation implements abstraction



Private , public , protected etc Private - Only with in Class , Public - Seen everywhere , Protected - With in same class and inherited classes , Internal - Within same class and same project and Protected internal is combination of protected + internal i.e. with in same class , iherited class and same project file.



What are constructor and desctructor ? Constructor(same name as class name with no return type) - initialization purpose , while destructor(~) is for cleaning purpose.



Inheritance parent child which constructor fires first Parent Constructor fires first and then child.



What is multiple interfaces are implemented  It will qualify with interface name



How to prevent inheriting futher use the sealed keyword



What is Static classes ? Static object has ony instance of the object created through out your application.



How to prevent object creation Create the constructor as private



Where do we use the same ? In Singleton pattern



Inheritance(:) Depicts parent child relationship between classes



Polymprphism objects will act different in differently under different situation



Method overloading You can have same method name with different  number / types of parameters and depending on different parameters and types the appropriate functions will be invoked.



What are the different ways of overloading types , order , ref m out … do it



What is virtual  and override keyword ? Virtual keyword gives permission to its child classes to override the function / method. The child classes will then use the override keyword to override the same.



Whats the difference between overloading and overriding ? Overloading we have same method / function names with different signatures ( input types) while overriding is a child parent concept where we override functionalities of the parent class.



Dynamic polymorphism Method  overriding  and Virtual. You point your parent class object to child class objects to fire the necessary implementation on runtime. Even interfaces can be used to implement dynamic polymorphism



Static Ploymorphism Method Overloading , we call same method with different input parameters



What is a interface Its looks like a class but has no implementation. It has only empty definition of methods , functions, events , indexer.



What is the use of interface ? to Establish a standard contract/standardization  ,   to decouple classes, dynamic polymorphism( runtime bhevior) ,version , multiple inheritance



Point to be remembered for interfaces Interface keyword is used to define interfaces.
Interface can not have field variables.
Interfaces can not have implemented functions and methods.
All methods in a interface in child class needs to be implemented.
Interface can be used to implement run time polymorphism.
You can implement multiple interfaces.
All interface methods are public




can we define public , private acess modifiers in interface ? No , by default everything is public.



Do implemented classes need to implement all the methods of a interface ? Yes



Can we create a object of interface or abstract class  No



partial classes splits a class in to 2 plhysical files. But when the compiler runs he compiles them in to one class.



use of partial class designer based code.



Abstract and virtual Abstract no implementation and virtual has implementation



Multiple constructor with parameters




what is a abstract class ? Half defined parent class   / base class  and child classes provide concrete implementation, like a generalization and specialization



What are abstract method and non-abstract method ? Abstract method will not have any implementation while non-abstract methd will have implementation they are normal .NET methods



Point to be remembered for Abstract classes An abstract class cannot be a sealed class.
An abstract method cannot be private. You can have protected and internal if you want to.
The access modifier of the abstract  method should be same in both the abstract class and its derived class. If you declare an abstract method as protected, it should be protected in its derived class. Otherwise, the compiler will raise an error.
An abstract method cannot have the modifier virtual. Because an abstract method is implicitly virtual.
An abstract member cannot be static.




Abstract classes and interfaces Interface is a contract / standardization while abstract class is a half defined class for increasing reusability. Interface is forced implementation while abstract class is reusability via inheritance.



Can we have static methods in Abstract class ? Yes



I have a interface IMyInterface which is implemented by class1 and class2 , I want to add some more functions and methods to the interface , what should I do ? Create a new interface with additional methods or inherits from the interface and create a interface with methods and apply them. This ensure that the current contracts do not get disrupted.



Multiple code level inheritance




versioning




operator overloading Operator overloading is a concept of plymorphism where you can redefine operators like + , - , * etc with additional functionalitiues. For instance we can redefine the + functionalities to add numbers like 1+1,



Delegates It’s a abstract strong typed pointer to a function



create and invoke a delegate Declare , Create , point and invoke.



Use of delegate Delegates helps for call backs. So if you have huge task running  in multithreaded manner and you would like to get messages from that task you will use delegates.



Delegate and interfaces Delegate is at method level while interfaces are  at class and object level.



Multicast delegate(+= , -=) It helps them to point to multiple functions and execute them sequentially.



Use of Multicast delegate For boardcasting to multiple classes



What is Unicast ? When one delegate point to one function its called as unicast.



Events  Events encapsulates(hides) delegates. They are useful for boradcasting , publisher and subscriber



Difference between delegates and events  Events use delegates internally. Delegates are pointer to function while events encapsulates ( hiudes) delegate function. In event clients can only subscribe while in delegates the client can subscribe as well as call all the functions of the delegates.



Asynchronous delegates Begininvoke and endinvoke , calls the methods of delegates asynch , for parallel execution.



covariance and contravariance(.net 4.0) This helps to point delegates in a very generic fashion



Shadowing It replaces the complete element of the parent class. Like method becomes a variable



Shadowing and overriding in shadowing the complete elements is replaced and in overriding you only replace the implementaton while the type remains same.



Shadowing practice Should avoid Shadowing as far as possible.



Property indexer




1.1 collection differences




Difference between Array and Arraylist Array - Fixed , ArrayList - resizable , Array has performance improve ment as no boxing and unboxing takes place while in array list it does , Arraylist can not store mutiple data types while array has fixed data types



What is a generic list Strognyl typed list and improves performance by avoiding boxing and unboxing



Generic confined the type public class cls<T> where T : String



Hashtable Stores the value as hashed key , good for comparison , but bad in terms inserting valies



Stacks and Queues First in first out and last in first out



Are there any performance problems with normal .NET collection ? yes boxing unboxing happens









Regex and validation Regex is nothing pattern matching and then validating the data using the pattern



GAC To share the assembly across multiple  application in the same computer (C:\WINDOWS\assembly)



how do we register in GAC ? Give strong name to DLL by click on the signing tab -- second you use thr GACUTIL -I to register the assembly in GAC



If you have different versions ? Use Bindingredirect , Oldversion and newversion



satellite assembly It’s a  compiled DLL which has images , text files any resource files. In other words no can tamper with your images , text files , icons etc.



Delay signing




Generics Its to increase reusability by seperating logic and data type and its strong typed.



What are generic collections / Where have you used generics Generic collections enable simple .NET collections to be generic enabled. So a normall List can be attached with any data type and we can avoid casting



Does generic improve performance Yes , type casting is avoided



Threading Threading is used to do pararellel execution.



How do we use threading Create the thread object , pass the method name using thread start and call the start function.



can we set thread priority ThreadPriority



threading can we stop and suspend yes by using thread.start , suspend , stop



Background and foreground thread




readonly and constant




What is reflection ? BY using System.reflection you can read meta data like properties , methods and also if needed you can invoked the methods and functions dynamically.



In what scenarios we would use reflection ? technological tools like VS IDE , code reviews tools like Fxcop  , unit testing NUNIT etc



hod o you do reflection Import system.rflection , get the type of t he object using gettype and then you can browser through functio an dproperties , to invoke use the invokemember



What is Serialization ? Helps  to store in memory object in file and dessrialization means read the value from file and bring it in memory back.



what is indexer  They are smart arrays



ASP.NET and Webservices




Objects in ASP.NET request , response , context , session and application



Autoeventwireup




Autoeventwireup When the request is send  to the server from a UI element like button , drop down etc the postback happens



IIS , PORTS and Website




Application pool concept Application pool helps you to isolate one website from the other website. So if that website the other websites affected. It acts a logical boundayr.



HttpHandler and httpModules They are means by which you can inject  pre/post-processing logic before the request reaches the page /gif/html



HttpHandler  HttpHandler is extension based processor.



.ASHX Its nothing but Httphandler below , they helps us to display data which does need asp.net pages and thus they improve performance



HttpModule HttpModule is a event based processor



Create a HttpModule Create a class ,implement IhttpModule, attach events with your methods and register the class in web.config in httpmodule tag



Create a HttpHandler Create a class implement IhttpHandler  , define implementation for processrequest,  register the class in web.config with file extensions in httphandler tag



ASP.NET application and page life cycle  environment created(request , response , context) - http module event ( begin request etc) - httphandlers ( page extensions) - page event (S/ILVER)-http module (end request)



What logic should be written in what events ? init (add controls dynamically), load(write logic),validate(fire your valdation),event(ui element events),pre-render(changes before HTML is generated) , render ( changes before HTML is sent to the browser) and Unload ( clean up)



What is the difference between render and pre-render ? Pre-render event happens just before the HTML is generated while render event happens when the HTML is sent to the browser.



What is a ISAPI ?




UI controls ( buttong , text box , drop down ..




why do you need Session ? HTTP is a statless protocol and in order to remember states during postback we need session variables



how do we do Session management in ASP.NET ? inproc (session object), outproc ( sql server and state server)



how do we implement Inproc session management in Asp.Net ? by using session object of ASP.NET



Are session variables are local to a user ? Yes



What is a viewstate ? Its meant for session management and it uses hidden fields internally.Viewstate is a way to manage session using client browser , it uses hidden fields internally.



What is difference between viewstate and session variables ? Viewstate value is accessed only in the current page while session variables can be accessed in different pages also.



What are the problems with viewstate Securuity



What is web farm ? Many times our current load demand increases. We can then create Web farm  which has two or more servers to meet the load.On the front end there is load balancer who routes the load to the appropriate server on load basis.



What is a loadbalancer? Loadbalancer helps to decide which server the request should be routed. There are many load balancers one of the is Windows NLB ( network load balancer) in windows  server



What is web garden ? You would like your IIS processes to run on different processors of the machine rather than loading only one processor.



How to implement web garden ? In the process model tag ( web.config  or machine.config) provide the CPU mask value in decimal quivalent of the binary.



Why do we need a outproc session ? Outproc sessions are needed in web farm environment



How can we do out proc session step 1 - create session tables using ASPNET_REGSQL , Step 2 : Set <sessionState mode="SQLServer"  with connectionstring point to the database where session tables where created.



What are server side session management and client side  management ?




Whats the life of session ? Session time out or when the user closes the browser



state server aware used for outproc session



How can we cache a ASP.NET page ? By using the <%@ OutputCache Duration="20" Location="Client"  VaryByParam="*" %>



What are the various ways by which you can cache your page ? Param , Headers , Custom and UserControl.



How can we cache the whole page and  sections of the page should be dynamic ? By using ASP:Substituion ( post cache substituion)



Cache whole opage  outputcache directive at page level



Whole page dynamic and sections of the page is cached Outputcache directive at usercontrol levele.



Whole page achced and sections of the page is dynamic Substituion control.



How can we cache as per location location = server , downstream , client



What is a Application and Cache object ? They help to create global objects in Asp.Net application. 



What is the difference between cache and application object ? In cache we can define dependency while application objects we can not define depedency.



What is cache dependency ? you can define 3 kins of dependency file , time and key. If these dependencies change then the cache object becomes null.



If cache dependencies changes does it reload the depedency ? No Cachdepedency does not have automation , we need to write t he code of refresh and reload.



Absolute and sliding



what is scavenging ?



Postback When your user control sends data to the server postback event happens



Autopostback When you want to send data to server as soon as the value changes in the control. It’s a simple property which we need set on the control.



Ispostback It’s a page property tpo check if the postback has happened or not



Configuration setting in web.config in the appsettings



Server.transfer and response.redirect response.redirect has a double trip while server transfer transfers control from the server itself. Using response.redirect you can redirect cross domains while server transfer you can redirect only between pages of the same domain. Create a simple project with page1.aspx and page2.aspx , create a simple buttong and in the button click redirect from page1.aspx to page2.aspx in the button click event







Authentication Who the user is?



 Authorization What rights does the user has ?



How do you know the user logged in to ASP.NET system ? using principal and identity objects which belongs to System.Security namespace.



What are different ways of authentication and authorization Windows , Forms and Passport



What is  Forms Authentication ? It’s a ticket based authentication . Tickets are passed via URL or stored in cookie file at the user browser.



What does actually a ticket mean ? Ticket is a identification saying that you are a valid user. By using ticket you do not need to send userid and password again and again. Userid and password are sent first time , ticket is generated and later this ticket identifies your security role and rights.



What is windows authentication ? In this the users are validated from windows local users and groups.



What is passport authentication ? In this the users are validated from microsoft sites like hotmail , devhood , MSN etc , ticket is generated and that ticket can be used to do authentication . Autho in your web application.



how can we implement Windows authentication ? Create users in yours windows user group -- in web.config file define <authentication mode="Windows"/> -- <deny users="?"/> Deny anonymous users -  map page name and roles.



How can we do Forms Authentication ? Authentication mode = forms  in web.config file ,FormsAuthentication.RedirectFromLoginPage/Authenticate



What are cookies Cookies are files which are created in the end users computer to track behavior etc.



what if cookies are diabled in forms authentication ? Its passed via browser URL.



Single sign on Single sign on is a concept where you login to one website and it automatically authorizes and authenticates other websites.



How can we implement Single sign on ? Create a machine valudation key using (using RNGCryptoServiceProvider) - define the  same machine validation key in to the web.config file for all websites which will be coming in the single sign on -- use forms authentications



aspnet membership and roles It helps to expedite your development for authentication and authorization by giving ready made user and roles tables , user interfaces and API to connect to those tables.



how does ASP.Net membership roles work ?. run aspnet_regsql -- do web.config configuration ( connectionstring , providers and roles) -- call the API from your ASP.NET behind code (Membership.CreateUser,Roles.CreateRole and Membership.ValidateUser)



Debugging and Tracing in ASP.NET Tracing is a way to monitor the execution of your ASP.NET application. You can record exception details and program flow in a way that doesn't affect the program's output. dev


how can we do tracing in ASP.NET pages Trace=true dev


whats the problem with trace=true our debugging is shown to the end users , so we need to use tracelisteners dev


How can we configure tracelisteners and source ? In web.config file define your source and listeners , in your code create the trac object and start sending messages(trace.write) dev


What is the namespace ? It logically groups calsses together



Ajax Ajax is a technology by which you can do asynhcrounous processing and only the ncessary values are posted rather than posting the whole page. Performance and great user experience.



What is updatpanel Updatepanel is a component which ensures that your server objects become Ajax enabled.It logicall groups them so that only that data is posted.



what is script manager? It injects the necessary javascript to ensure your ajax update panel runs.



What is Script manager proxy ? In Ajax we can have only one script manager. But there are condition like master  pages where we would need two script manager 



Enablepartial rendering This enables asynchrounous processing



How can we consume webservices in Ajax ? In Script manager proxy  give asp:servicereference and give ASMX path and use javascript to call the objects



What is Jquery ? Jquery is javascript library to make your javascript development simpler.



Difference between datagrid , datalist and repeater Datagrid displays everything as table , datalist has tables but gives flxibility like number of columns to displayed , while repeates is highly customizable, performance goes first repeater , then list and finally grid



Gridview and datagrid Gridview is the sucessor of datagrid,Automatic handling of sorting, paging, updates, and deletes,Additional column types and design-time column operations, rich design capaibilities



Paging in datagrid



SQL Server




Database and Tables




SQL




self/equi joins




Referentrial integrity










Normalization It’s a database design technique to avoid repetitive data.



Normalization 3 normal form , 1st normal form Break is Smallest unit , 2nd  all data should depend on the primary key fully , 3rd normal form Any non key field should not depend on other non key fields of the table



Denormalization Its combining multiple table data in one table thus avoiding joins for performance imporvement ex - reporting applications



When should we use normaliztion and denorm Normalization ( integrity , avoide redudant) and denorm ( faster treival , reporting etc)



Indexes Indexes helps to make your search faster by using balance tree( B tree) concept 



Balance tree It’s a tree structure with leaf nodes having actual data , non leaf node and root node. So rather than browsing through records sequentially it zeroe's on range of records , this making your search faster



Clustered indexes The leaf node points to actual data



Non-Clustered indexes Leaf node points to a pointer and that pointer points to actual data



When will use a clustered index and not clustered index ? You will choose fields with unique values as clustered indexes while fileds which participate in search criterias you will select them as non-clustered indexes.



Self join




inner join




left jon




right join




cartesian




case statements




Select all rows from both tables SELECT     *
FROM         Customer INNER JOIN
                      CustomerPaid ON Customer.Id = CustomerPaid.Id
union
SELECT     *
FROM         Customer left JOIN
                      CustomerPaid ON Customer.Id = CustomerPaid.Id
union
SELECT     *
FROM         Customer right JOIN
                      CustomerPaid ON Customer.Id = CustomerPaid.Id




in group by if we put a column which is not in group what wuill happen Query will no fire



cross join / cartesian




Stored procedures  Stored procedures are SQL Server objects which have one or many SQL statements



What is advantage of SP over inline SQL They are precompiled and thus increases perforance ( parsing , plan and then execution)



What are triggers  ? Trigers are logic which you want to execute insert , delete and update operation on a table



What are types of triggers ? After trigger ( fires after the operation )and instead of trigger ( before the operation). Operation means insert , update delete



What are inserted and deleted ?




Functions It returns a scalar value and is called from stored procedure declare @count1 int
set @count1= dbo.getfunctioncount()
print(@count1)



Difference between function and stored procedure Function reduce redudant code in SP.Function - You can not change data while in stored procedures you can , can return only one value while  SP returns multiple values, functions can be called inside a SP but not the vice veras



How to handle errors in Stored procedures using Raiserror function , give message severity and state



Paramters in Stored procedure input , ouput , return( only one)



profiler It’s a simple listerner tool which tracks what kind of SQL statements are fired on your SQL server and using it you can do debugging and improve performance using performance tuning wizard



optimzation  Create indexes ( balance tree), SQL Plan ( Table scan or Index scan) , SQL profiler  and Data tuning advisor for getting suggestion of indexes



sql plan Describes how your SQL statements will execute.



sql server  locking set isolation level read committed etc etc in stored procedure



SQl injection It is a Form of attack on a database-driven Web site in which the attacker executes unauthorized SQL commands by taking advantage of insecure code on a system.



How can you avoid SQL injection Do proper validation,use stored procedures and avoid on the fly SQL concatenation.



SSIS SSIS helps us to do ETL ( Extraction - Transformation and Loading). We need to create a Business intelligence project define source , transformation and destination and run the DTS package.



SSRS




SSAS




Difference between delete and truncate ?




ADO.NET




What are ADO.NET components ? Connection , Command , Dataset , dataadapter , datareader(2C and 3D)



Whats the difference between Dataset and datareader ? Dataset(back and forth) - works disconnectly , CRUD , reader works connecteldy



In what scenarios we will use a dataset and datareader ? Dataset :- When you want to take group of records , modify/browse in offline mode( move back and forth)  and then send the whole changes online. Datareader :- You have huge number of records and you want browse in the forward onnly direction



How do you connect to ADO.NET connection , Command , ExecuteNonQuery/ExecuteQuery , use it and close it



how do you load a dataset connection , Command , Dataadapter , load dataset by Fill method , use it and close it



Dataview Helps to search, sort filter dataset



Command object methods Executereader , executenonquery and executescalar



how can we use stored procedure in ADO.Net  ObjCommand.Commandtype = CommandType.Storedprocedure , give the stored procedure name



Wht connection.close To ensure the SQL Server resources are released



How do you write and read using XML using ADO.Net readXML , WriteXML function  of dataset object



Problems of Locking Dirty Reads,Unrepeatable reads,Phantom reads and Lost updates



optimistic and pessimistic Pessimistic means locks are acquired when the record is fetched until the updation and optimisitic means locking is acquired when the data is updated 



how can we do optismistic locking in ADO.Net ? dataset and dataadapter  by default supports optimistic locking , timestamp,checking  old values and new values



Pessimistic locking Read committed( reads data which is committed) , Read uncommitted ( reads data which is yet not committed), repetable read ( No updates allowed)and Serializable (Exclusive lock no updates , insert and selects).



Scnearios of pessimistic and optimistic Transactional screens / data enty screen where large number of users,critical batch processes done at the end of the day or year( closing of accounts).



What is a transaction ? When you have group of statements . Process doing insert update and delete , you would want either all of them pass or either all of them fail.



how can we implement trans call begintransaction in connection object , committransaction or rollback, in stored procedure begin tran tr1 and commit tr1



Connection pooling Reuse the connection object rather than reusing from scratch.In connection string specify pooling=true



dataset is  collection of tables with relationship and recordset has only tables



How do you pass parameters t stored procedures Paramater collection of command object



how can you automatically fill command object Command builder.



Datarelation




nerge










Reporting




Where do you get Crystal reports ? It’s free available in VS



How did you create report using crystal reports used RPT file -- Define my data sources -- Finally uses the reportviewer to display the RPT file  on the UI.



how to provide inputs to crystal report By parameter field





















Remoting , Webservices and WCF




What is Appdomain ? Appdomains make logical boundary / isolation  inside a process. Process -- Appdomains(task and application).



What's the advantage of Appdomain ? If one Appdomain crashes the other Appdomain or the whole process is not closed / terminated.



What is the use of remoting ? It helps to make remote method calls to objects which are residing in different appdomain. These Appdomain can be located in a different geographical location or different networks.



How do you create a remoting object ? Create a interface -- Implement the object by creating MarshalbyRef -- Host your server object using Remoting"RegisterWellKnownServiceType" -- Create the client to call the service"Activator.GetObject".



Problems with remoting Client and server can be only in .NET language , complicated, Faster than other remoting technologies, pick and choose Channel and formatter ( customization)



Comparison of DCOM and remoting




remoting objects life time




what is a webservice ? Webservice is a technology by which you can expose your business functionality on HTTP using SOAP XML format.



How can we do webservice security specify windows , passport , forms and pass credentials through the client proxy object



UDDI and DISCO Helps to search and discover your webserbvice from the UDDI directory



What is WSDL ? It’s a meta data which shows what are the methods , properties , datatypes exposed by a service



What is SOAP ? It’s a XML protocol which is defined how your service and client communicate.



What is difference between Webservice and Remoting Webservice(Cross language , only Http) , Remoting ( only .NET platform , Any protocol)



What is  Service Oriented Archotecture ? SOA is nothing but desigining your system in self contained services which communicate via standard messages thus making your services loosely coupled. Services -- Self contained , Orchestrate , Industry Standard messages , Reliable , Aynshcrounous message , Cross plat form  describe and discover









Why WCF ? It was to satisfy  SOA principles of WS-* specification.



What is WCF ? WCF = Webservices + remoting + COM plus + MSMQ



What is Address binding Contract ? Address - where is it hosted , Binding - how is the protocol , Contract - what  is the interfcae



What is  Service contract , Operation contract and Data contract ? Service contract - Service  name , Operation contract - Your methods and functions , data contract - Custom data type , data member - property and functions of your data contract



namespace of WCF System.ServiceModel



what's the difference between WCF and Webservices ? Webservices = WCF - ( remoting( multiple protocols) + COM plus ( transactions) + MSMQ(queueing))



What are the different hosting mechanisms of WCf services ? IIS , Self hosting or WAS server



how do host in IIS ? By suing the SVC file we can host our service  on IIS



How do you do self hosting ? Create object of Service host , provide Address ( Uri) , Binding ( Wshttpbinding) , Contract ( Interface) and open the host.



When should we go for Self hosting and when to go for IIS Http go for IIS ( we getready made features like always running , process recycling,security,ssl etc), For other protocols go for self hosting



What are the important bindings/ BasicHttp ( Like webservice plain text) , WsHttp ( Like basic with encryption) , TcpBinding ( To use TCP protocol for services ) , NetNamesPipe ( With in the same machine ) , MSMQ ( WCF integration with Queuing mechanism)



BasichttpBinding and WshttpBinding basic is plain text while Wshtpp is encrypted



How to secure basichtppbinding use SSL on webservices



Ho can we do debugging and tracing in WCF In Web.config <system.diagnostics> specify  WCF trace source objects like System.ServiceModel , message logging etc. , then use the Svtraceviewer tool to see the mesagges



How can we do transaction in WCF ? 1 . Operation contract define TransactionFlow , 2. In Web.config file make your Binding as transaction flow as true 3. In client side call the services in one transaction scope with rollback and committ.



WCF Security Transport , message and transport + message



WCF Transport SSL , Specify in config file https in address tage ,  Specify security mode and install SSL in your WCF services



WCF overloading is it allowed [ServiceContract]
interface ICalculator
{
 [OperationContract(Name="AddIntegers")]
 int Add(int a,int b)
 [OperationContract(Name="AddDouble")]
 double Add(double a,double b)
}




Dynamic polymorphism




WCF Duplex




WCF message




WCF Instancing Per call , persesion and single instance



WCF concurrency Single , Multiple , Rentrant



WCF REST Helps  to expose your WCF service via simple HTTP get and post



WCF integration










LINQ and EF 4.0




What is LINQ It’s a OR mapper  technology ,2 important use 1 - BO , DAL and Mapping , 2 - General Queries which can fired on any data store.



What is a Entity class It’s a Business object which has mapping with the data store.



What is data context It’s the data connection layer to load the entity object with data



How do we declare 1 to  many and 1 to 1 relation Entity set for 1 to many and entity ref for 1 to 1



LINQ to SQL




Optimization using data load options




CRUD example using LINQ insertonsubit , deleteonsubmit , submitchanges



Call Stored procedures using LINQ Using the function attribute



LINQ locking




PLINQ PLINQ executes two linq queries parallely



LINQ transactions By using the transaction object of data context class



DBML Database markup language









WPF and Silverlight




GDI , GDI + and Directx




Simple WPF application  WPF is for creating windows application , the user interfaces can be represented using XAML and it uses directx internally.



Rendering in WPF application




Architecture of WPF




What is silverlight ? It’s a plugin which runs inside the browser. It can run on cross platform and cross browser.



Architecture of Silverlight




Animation fundamentals in Silverlight




What are different way of silverligjt layoutin.. Stack panel ( one above another ),Canvas ( we specify left and top), Grid ( If you want layouting using table structure , rows and columns)



What are the different ways of binding the silverlight UI with .Net classes and objects ? One way ( UI to BO), two way( UI <-> BO) and one time( BO to UI).



In what scenarios you will use what ? One time binding - reports , Transaction screens two way , Pure input screen one way.



How can we connect froom Silverlight to SQL Server By using WCF services.



Why not directly ? Because silverlight is a plugin which is a downsized version .NET framework and it does not have system.data



Consume WCF service in Silverlight WCF service should have client access policy and cross domain , from silverlight you need to call the data asynchrounously



Database operations using Silverlight You can not call System.data directly from silverlight , so you need to call the WCF service and WCF service will do the necessary data operations



Prism




MVVM










WWF




WWF architecture




Composite and leaf activities




Sequential and State machine work flow




Simple example of WWF




State machine work flow










Design patterns




Which design pattern have yu used ? So pick your best pattern corelate it with your project and speak about it.  Share global fdata



Design patterns Design patterns are  tried and tested solutions for recurring design problems.



What are the categories of Design patterns Creational , Structural and Bheviorial



Factory pattern© Factory centralized object creation of same heirarchy.



Abstract Factory Pattern Abstract Factory sits on the top of factory patterns for generalized obejct creation.



Builder Pattern




Prototype Pattern It does cloning of objects.



Where is prototype used ? Audit trail , differences of objects , cancel and revert



Singleton Pattern Helps to create one instance of the object.



How did you implement singleton pattern ? Create a class with private constructor and define your object as static and expose it



We can achieve the same by cache and application Its technology specifc and not a generalized solution



we can achieve the same by using static object




Adapter Pattern if you want to adapt your current method names to new method names probably third parties.



types of adapter pattern Object adapter and class adapter.



Bridge Pattern
xxxx


Composite Pattern Uniform interfaces



Decorator Pattern Adding  logic on existing logic  kind of plug and play.



usages of decorator Maintenace code addition , plug and play validation.



Facade Patetrn Simplified interfaces



Flyweight Pattern
xxx


Proxy Pattern
xxx


Mediator Pattern




Memento Pattern
xxx


Interpreter Pattern
xxx


Iterator Pattern Browse the data in a encapsulated manner



COR Pattern
xxx


Command Pattren
xxx


State Pattern
xxx


Strategy Pattern
xxx


Observer Pattern
xxx


Template Pattern Sequence process is same and you want to override the process



Visitor Pattern
xxx


DIIOCConcept Pattern  DI helps to inject depedent objects in to a class. DI injection can be implemented by Unity app block / Windsor castle



What is MVC,MVP and MVVM These are GUI architectural pattern with only one intention of seprating the UI Logic , state and synchronization in to a separate class.



How is MVP Hit come to UI first , from there he calls presenter clas , presenter call the model and present then send updates via a interface to the UI. UI --> I --> P - M



Model view controlle Sepearetes Action , View and Data by using controller class. We have template MVC template which helps to automate the same. In odld asp.net we will httphandler which was tedious



How do you implement MVC template ( 4.0)



Strongly types Model is binded view



What is html helper It helps to automate HTML code.



But how did the models Entity frameowrk / LINQ



How to you write form action / hyperlinks By poiting to the controller action.



Authentication and Authorization Windows authentication and Forms authenticaion



What are routes This helps to customize the controller and action names to more user friendly name



validation Data annotation ( attributes)



MVP and MVC First hit to the UI , First hit to the controller/MVP presenter takes care of logic , controller takes care of logic as well as statmanagement



When to use these patterns If you have complicated UI code , MVC is mostly used for ASP.NET , MVVM is used for Silvetrlight and WPF becaiuse of rich command objects, MVP is not used currently because of less automation



UML UML is a modelling language which helps us to document and design our software application.



 Use Case Diagrams Its helps us to document "What" of the system from user point of view.



What does use case have normally ? Use cases are task having scenarios and Actor executing those scenarios



How did you document your requirement  ? Step 1 :- We identified the task which where nothing but our use cases , Step 2 :- For every use case we wrote down main scenario / alternate scenario and actors responsible



What are primary actors and secondary actors ? Primary actors are the users who are the active participants and they initiate the use case, while secondary actors are those who only passively participate in the use case.



In which format did you gather your uses cases  Company Template in word document



How did you show relationship n uses cases By Extend and include



How did you translate your requirement to design ? We has already gathered the requirement using  use cases and also relationships where between them  using the include and extend, we use these use cases and identified nouns ( classes) and verbs ( they became our methods and functions) , relation ship between classes where identified by extend and include of use cases. Later for complex scenario we use Sequence diagram / Collobaration. We also had a over all architecture diagram which depicted hardware , components and logical grouping by using Component , Deployment and package. 



 Class Digrams Class diagrams shows the static structure of project. They  help us visualize the classes and relationships between them



How do you denote a class diagram in UML ? Its denoted with square divided in to 3 section , top section has class name , middle section has properties and last section has operation / function / methods



How to demote public , private and protected in class diagram (+)  Public , (-)Private  , (#) protected , internal



Structures VS classes Struct complex data type , groups value types while classes represent real world objects. Its used where you are creating large number of objects and you do not want GC collector to stress. Structure are technical objects while classes represent real world.



How do we define relationships in UML by multiplicty (1-*)



What is composition and aggregation Composition - life time of dependent objects are same , Aggregation :- Life time of dependent objects are not same and they can exist without each other. Composition is shown by filled diamond while aggregation is shown by empty diamond.



How can denote inheritance ? Empty traingle arrow key.



How can we denote abstract classes and interfaces {} - Abstract classes , <<>> - Interfaces



 Object Diagrams




 Sequence Digrams Object diagrams over a period of time.



how did you draw a sequence diagram ? The objects in rectangle on the top side -- message by arrows and object life time vertical rectangles.



How do you show synchronous and asynch messages  Synch by dark arrow and asynch by thin arrow key



Recursive message By small rectangle in a big rectangle



Conditcion Inside Square bracket



Message branching By two rectangles with condition and branching arrows from the same.



 Collaboration Diagrams It shows the same information as sequence diagram but the emphsais is more on messages than life time of the objects



 Activity Diagram Activity diagrams depicts complex flow of a system



Parallel processing , partionting and conditions Shows by dark bar , Swimlanes for partitioning , Diamond is for conditions



 State chart Diagrams




 Component Diagrams




 Deployment Diagrams




 Stereo Types Diagrams




 Package Diagram




 UML Project Flow
















Functionpoints




 Introduction










 EI Fundamentals




 EO Fundamentals




 EQ Fundamentals




 EIF Fundamentals




 ILF Fundamentals




 GSC Fundamentals




 Productivity Factor




How did you reconcile




How did you accommodate SDLC










RUP




What is Agile Agile is a principle which means accept changes



How did you do Agile Used SCRUM



What are the principles in Agile Accept changes , Interaction with customer , people , working code



What is Agile SCRUM ? Scrum is a methodology to implement agile



How does SCRUM process work Customer Prepare Product back log -- priortizes them -- development team breaks them in to sprint -- every srpint has daily stand up meet( Today , yesterday,problems) -- Every week retrospective -- After sprint give chance of changing 









When Agile and Waterfall ? Water fall - requirement is fixed , Agile -- If requirements will change or evolve.( Time to market)









How did you code review? Mostly automation and the process



Role of your current project(SDlC) I was involved in all phases of SDLC cycle.Requirement( use cases , activity) - Design(Class , ER diagrams , Sequence / Collboaration) -coding / unit testing (NUNIT)- SIT - UAT - go live(junior ( coding) helped out ,poc( proof of concept) , pm / archotect) - end to end.



simplicity your projects  The applicaton was a integrator between indian chemical companies and EICA to provide certifications for Indian chemical companies as per EICA standards It’s a internal ATM management system in ASP.NET  which has various functionalities creating Contracts , Customer report , hand bills , Scheduling etc.


Can you explain  the architecture of your current project ? It was a tiered architecture , UI / BO and DAL. With UI in ASPX pages , BO in normal .NET classes and data access layer helps to fire SQL queries on SQL Server. We had used singleton pattern and factory patterns in the project. Architecture patter ( 3 tiuer MVC MVVM)
 Describer each layer database normalization
 Each layer DI , FA
 3 more layers
 Caching and global component
 validation
 External integaration -



What  software process did you follow for your project ? We followed normal SDLC model. In requirement we wrote the use cases , design we created classes and DB diagrams , finally we coded and unitested and went live.



How did you test your project ?




How did you do requirement for your project ?




technical Design 




Acceptance




How did you do unit testing in .NET ?




Summary 




Speak abt yourself




OOPs




.net basic




asp.net




Why Gap ?




Differences ( .NET , ASP.NET and SQL Server)




HLD and LLD



I do understand HLD but most of the times we have created a overall index of LLD and then expanded in to details. The use of HLD is mostly to verify before investing in LLD that are we in the right direction , to take approval from stake holders.
HLD has 5 section
Overall all architecture diagram ( Deployment , Component diagram , package diagram)
Overall Road map ( 3 tier , MVC , MVP MVVM)
technology
Unit testing
Load test startergy
POC :- Proof of concept

LLD
Overall detail architecture diagram
Class diagrams ,
Object diagrams ,
Sequence / Collaboration ,
DB design
In depth test stratergy
Psedu-code


































 How many types of Polymorphism in C#
A: Polymorphism in C# is of 4 types
·        Function overloading
·        Function overriding
·        Operator overloading
·        Constructor overloading


 What are the differences between structure and class w.r.t c#?
Structure
Class
Structures are passed by value, not by reference
Classes are passed by reference, not by value
Structures are stored on the stack
Structures are stored on the heap
Structures cannot be inherited
Classes can be inherited.
Structures cannot inherited from structures and classes, though they can  implement interfaces
Classes can implement a single class and multiple interfaces.
Structures cannot have constructors and destructors. It can contain only fields and methods
Classes can have constructors and destructors
In Structures we cannot initialize variables. It must be initialized either through function or using object
In classes we can initialize variables
A structure by default is sealed and the concept of inheritance is not supported

1





Data Structures


//sorting in descending order
struct node
{
int value;
node* NEXT;
}
//Assume HEAD pointer denotes the first element in the //linked list
// only change the values…don’t have to change the //pointers

Sort( Node *Head)
{
node* first,second,temp;
first= Head;
while(first!=null)
{
second=first->NEXT;
while(second!=null)
{
if(first->value < second->value)
{
temp = new node();
temp->value=first->value;
first->value=second->value;
second->value=temp->value;
delete temp;
}
second=second->NEXT;
}

first=first->NEXT;
}
}

2)      singly linked list reversal
//
// iterative version
//
Node* ReverseList( Node ** List )             
{

               Node *temp1 = *List;
               Node * temp2 = NULL;
               Node * temp3 = NULL;

               while ( temp1 )
               {
                              *List = temp1; //set the head to last node                             
temp2= temp1->pNext; // save the next ptr in temp2
                              temp1->pNext = temp3; // change next to privous
                              temp3 = temp1;
                              temp1 = temp2;
               }

               return *List;
}
3)      Delete a node from doubly linked list
void deleteNode(node *n)
{
node *np = n->prev;
node *nn = n->next;
np->next = n->next;
nn->prev = n->prev;
delete n;
}

4)      Insert a node in sorted linked list
void sortedInsert(Node * head, Node* newNode)
{
Node *current = head;
                 
// traverse the list until you find item bigger the // new node value
                 //         
while (current!= NULL && current->data < newNode->data)
{
current = current->next);
}
//
// insert the new node before the big item
//
newNode->next = current->next;
current = newNode;
}

Stacks and Queues

1)      Explain how to implement two stacks in one array A[1,...,n] in such a way that neither stack overflows unless the total number of elements in both stacks together is n. The run time of PUSH and POP is O(1).
2)      Explain how to implement a queue using two stacks. Analyze the running time of the queue operations.
3)      Explain how to implement a stack using two queues. Analyze the running time of the stack operations.
4)      Write four O(1)-time procedures to insert elements into and delete elements from both ends of a d-queue constructed from an array.
5)      Implement a stack using a singly linked list L. The run time of PUSH and POP should be O(1).
6)      Implement a queue using a singly linked list L. The run time of ENQUEUE and DEQUE should be O(1).

Object Oriented Programming

Object Oriented Programming concepts can be found at: http://en.wikipedia.org/wiki/Object-oriented_programming
1)      Could you explain what a class is?
2)      What do you know about inheritance?
3)      What is the concept for abstract class or method?
4)      What is a virtual method or class?
5)      What is the difference between interface and abstract class?
6)      How to control access to class members in OOP programming? 

String Operations

1)      Reverse a string
a.      Inplace
void ReverseString (char *String)
{
char *Begin = String;
char *End = String + strlen(String) - 1;
char TempChar = '\0';

while (Begin < End)
{
TempChar = *Begin;
*Begin = *End;
*End = TempChar;
Begin++;
End--;
}
}

function reverse_string(thestring) {
    var len     = thestring.length,
        last    = len-1,
        middle  = parseInt(len/2),
        newStr  = new Array();
    for (var i = 0; i < middle; i++) {
        newStr[last-i]  = thestring[i];
        newStr[i]       = thestring[last-i];
    }
    return newStr.join('');
}


2)      words reversal in a string

static char[] ReverseAllWords(char[] in_text) 
    { 
        int lindex = 0
        int rindex = in_text.Length - 1
        if (rindex > 1
        { 
            //reverse complete phrase 
            in_text = ReverseString(in_text, 0, rindex); 

            //reverse each word in resultant reversed phrase 
            for (rindex = 0; rindex <= in_text.Length; rindex++) 
            { 
                if (rindex == in_text.Length || in_text[rindex] == ' '
                { 
                    in_text = ReverseString(in_text, lindex, rindex - 1); 
                    lindex = rindex + 1
                } 
            } 
        } 
        return in_text; 
    } 

    static char[] ReverseString(char[] intext, int lindex, int rindex) 
    { 
        char tempc; 
        while (lindex < rindex) 
        { 
            tempc = intext[lindex]; 
            intext[lindex++] = intext[rindex]; 
            intext[rindex--] = tempc; 
        } 
        return intext; 
    } 

string words = "this is a test"

// Reverse the entire string 
for(int i = 0; i < strlen(words) / 2; ++i) { 
  char temp = words[i]; 
  words[i] = words[strlen(words) - i]; 
  words[strlen(words) - i] = temp; 

// Reverse each word 
for(int i = 0; i < strlen(words); ++i) { 
  int wordstart = -1
  int wordend = -1
  if(words[i] != ' ') { 
    wordstart = i; 
    for(int j = wordstart; j < strlen(words); ++j) { 
      if(words[j] == ' ') { 
        wordend = j - 1
        break
      } 
    } 
    if(wordend == -1
      wordend = strlen(words); 
    for(int j = wordstart ; j <= (wordend - wordstart) / 2 ; ++j) { 
      char temp = words[j]; 
      words[j] = words[wordend - (j - wordstart)]; 
      words[wordend - (j - wordstart)] = temp; 
    } 
    i = wordend; 
  } 

Testing Concepts

NOTE: When defining test cases, make sure you define their priority and severity. Most of the interviewers look for organization i.e. , structured way of defining things.

Glossary of Questions:

C# and General Questions



1)      How would you deal with changes being made a week or so before the ship date?
2)      How would you deal with a bug that no one wants to fix? Both the SDE and his lead have said they won’t fix it.
3)      Write a function that counts the number of primes in the range [1-N]. Write the test cases for this function.
4)      Given a MAKEFILE (yeah a makefile), design the data structure that a parser would create and then write code that iterates over that data structure executing commands if needed.
5)      Write a function that inserts an integer into a linked list in ascending order. Write the test cases for this function.
6)      Test the save dialog in Notepad. (This was the question I enjoyed the most).
7)      Write the InStr function. Write the test cases for this function.
8)      Write a function that will return the number of days in a month (not using System.DateTime).
9)      You have 3 jars. Each jar has a label on it: white, black, or white&black. You have 3 sets of marbles: white, black, and white&black. One set is stored in one jar. The labels on the jars are guaranteed to be incorrect (i.e. white will not contain white). Which jar would you choose from to give you the best chances of identifying the which set of marbles in is in which jar.
10)   Why do you want to work for Microsoft?
11)   Write the test cases for a vending machine. (Those were the questions I was asked. I had a lot of discussions about how to handle situations. Such as a tester is focused on one part of an SDK. During triage it was determined that that portion of the SDK was not on the critical path, and the tester was needed elsewhere. But the tester continued to test that portion because it is his baby. How would you get him to stop testing that portion and work on what needs to be worked on? Other situations came up like arranging tests into the different testing buckets (functional, stress, perf, etc.).)
12)   Find anagrams
13)   Find if two rectangles intersect in a plane
14)   Find the max in a stack
15)   how to create a queue from stacks
16)   how to create a queue from stacks
17)   Find missing number in a sorted list of n distinct integers
18)   binary search and optimizations
19)   quick sort\merge sort leave selection sort
20)   Binary search
21)   Fibonacci series - Generate series and return n'th number
a.      Recursive
function fib(n) {
    return (n <= 1)?n:fib(n-1) + fib(n-2);
}
b.      Iterative
function fib(n) {
    var cache   = [];
    cache[0]    = 0;
    cache[1]    = cache[2]  = 1;
    for (var i=3; i<=n; i++) {
        cache[i] = cache[i-2] + cache[i-1];
    }
    return cache[n];
}

22)   How to traverse 4x4 matrixes spirally.
23)   Find 2nd max no in an array
24)   Find the index of a substring in a string
25)   What is the simplest way to check whether sum of 2 integers will not result in overflow.
26)   Find if element of 1 array exist in another or not.
27)   Extract only distinct elements from an array.
28)   Find missing integer in an array having values 1 to N.
29)   Remove duplicate characters from string
30)   Remove all characters in a string from another string
31)   Count no of set bit in a 32 bit no.
32)   One line expression to check whether a number is power of 2 or not.
33)   Determine duplicate value in an array of intergers from 1 to N.
34)   Find max sum of a sub-array of an array of integers.
35)   Reverse the words in a string.
36)   Reverse the order of words in a string
37)   Palindrome string
38)   Palindrome number
39)   Reverse doubly linked list
40)   Implement queue using linked list
41)   Implement stack using linked list
42)   Merge 2 linked lists with having only distinct values
43)   Find if any digit is repeated in an integer
44)   BFS
45)   DFS
46)   Binary Tree Pre-order/In-order/Post-order
a.      In-Order
//
// recursive version
//

Void PrintTree ( struct * node node )
{
        if ( node == NULL )
                       return;

        PrintTree(node->left );
        Printf(“%d”, node->data);
        PrintTree(node->right );
}

47)   Reverse circular linked list
48)   Find sum of nodes at any particular level in a tree (it is not BST)
49)   Insert node at n'th from last node in a linked list
50)   Array with values R,G,B. Arrange it like -> R...R G...G B...B
51)   Find first common ancestor of 2 nodes in a BST
52)   Sort array having 0 and 1
53)   How to shuffle deck or cards
54)   Linked list flattening
55)   Multiply no by 7 without using * and +
NewNum = Num << 3; // mulitplied by 2 ^ 3 = 8

               NewNum = NewNum - Num; // 8 – 1 = 7

56)   Factorial
57)   Find first non-repeated character in a string


1. Demonstrate or whiteboard how you would suggest using configuration files in packages.  Would you consider it a best practice to create a configuration file for each connection manager or one for the entire package?
There should be a single configuration file for each connection manager in your packages that stores their connection string information.  So if you have 6 connection managers then you have 6 config files.  You can use the same config file across all your packages that use the same connections. 
If you have a single config file that stores all your connection managers then all your packages must have contain the connection managers that are stored in that config file.  This means you may have to put connection managers in your package that you don’t even need.
2. Demonstrate or whiteboard how checkpoints work in a package.
When checkpoints are enabled on a package if the package fails it will save the point at which the package fails.  This way you can correct the problem then rerun from the point that it failed instead of rerunning the entire package.  The obvious benefit to this is if you load a million record file just before the package fails you don’t have to load it again.
3. Demonstrate or whiteboard using a loop in a package so each file in a directory with the .txt extension is loaded into a table.  Before demonstrating this tell which task/container accomplishes this and which enumerator will be used.  (Big hint on which task/container to use is that it requires and enumerator)
This would require a Foreach Loop using the Foreach File Enumerator.  Inside the Foreach Loop Editor you need to set a variable to store the directory of the files that will be looped through.  Next select the connection manager used to load the files and add an expression to the connection string property that uses the variable created in the Foreach Loop.
4. Demonstrate or whiteboard how transactions work in a package.
If transactions are enabled on your package and tasks then when the package fails it will rollback everything that occurred during the package. First make sure MSDTC (Microsoft Distributed Transaction Coordinator) is enabled in the Control Panel -> Administrative Tools -> Component Services. Transactions must be enabled not only on the package level but also on each task you want included as part of the transaction. To have the entire package in a transaction set TransactionOption at the package level to Required and each task to Supported.
5. If you have a package that runs fine in Business Intelligence Development Studio (BIDS) but fails when running from a SQL Agent Job what would be your first guess on what the problem is?
The account that runs SQL Agent Jobs likely doesn’t have the needed permissions for one of the connections in your package. Either elevate the account permissions or create a proxy account.
To create a proxy account you need to first create new credentials with the appropriate permissions. Next assign those credentials to a proxy account. When you run the job now you will select Run As the newly created proxy account.
6. What techniques would you consider to add auditing to your packages?  You’re required to log when a package fails and how many rows were extracted and loaded in your sources and destinations.
I like to create a database that is designated for package auditing. Track row counts coming from a source and which actually make it to a destination. Row counts and package execution should be all in one location and then optionally report off that database.
There are also third party tools that can accomplish this for you (Pragmatic Works BI xPress).
7. What techniques would you consider to add notification to your packages?  You’re required to send emails to essential staff members immediately after a package fails.
This could either be set in the SQL Agent when the package runs or actually inside the package you could add a Send Mail Task in the Event Handlers to notify when a package fails.
There are also third party tools that can accomplish this for you (Pragmatic Works BI xPress).
8. Demonstrate or whiteboard techniques you would use to for CDC (Change Data Capture)?  Tell how you would write a package that loads data but first detects if the data already exists, exists but has changes, or is brand new data for a destination.
If for some reason you’ve avoided using a whiteboard to show your ideas to this point then make sure you start on this question! For small amounts of data I may use the Slowly Changing Dimension.
More often than not the data is too large to use in such a slow transform. I prefer to do a lookup on the key of the target table and rows that don’t match are obviously new rows that can be inserted. If they do match it’s possible they are updates or duplicates. Determine this by using a conditional split comparing rows from the target to incoming rows. Send updates to a staging table that can then be updated in an Execute SQL Task.
Explain that putting updates in a staging table instead of updating using the OLE DB Command is much better for performance because the Execute SQL Task performs a bulk operation.
9. Explain what breakpoints are and how you would use them.
Breakpoints put pauses in your package. It’s a great tool for debugging a package because you can place a breakpoint on a task and it will pause the package based on execution events.
A reason in which I have used breakpoints is when I have a looping container and I want to see how my variables are changed by the loop. I would place a watch window on the package and type the variable name in. Set a break point on the container the stop after each iteration of the loop.





















1. What is the use of ServiceBehavior attribute in WCF ?
ServiceBehaviour attribute is used to specify the InstanceContextMode for the WCF Service class (This can be used to maintained a state of the service or a client too)

There are three instance Context Mode in the WFC

PerSession : This is used to create a new instance for a service and the same instance is used for all method for a particular client. (eg: State can be maintained per session by declaring a variable)

PerCall : This is used to create a new instance for every call from the client whether same client or different. (eg: No state can be maintained as every time a new instance of the service is created)

Single : This is used to create only one instance of the service and the same instance is used for all the client request. (eg: Global state can be maintained but this will be applicable for all clients)
2. What is a SOA Service?
SOA is Service Oriented Architecture. SOA service is the encapsulation of a high level business concept. A SOA service is composed of three parts.
1. A service class implementing the service to be provided.
2. An environment to host the service.
3. One or more endpoints to which clients will connect.

3. What is WCF?
Windows Communication Foundation (WCF) is an SDK for developing and deploying services on Windows. WCF provides a runtime environment for services, enabling you to expose CLR types as services, and to consume other services as CLR types.

WCF is part of .NET 3.0 and requires .NET 2.0, so it can only run on systems that support it.

4. Difference between WCF and Web services?
Web Services
1.It Can be accessed only over HTTP
2.It works in stateless environment

WCF
WCF is flexible because its services can be hosted in different types of applications. The following lists several common scenarios for hosting WCF services:
IIS
WAS
Self-hosting
Managed Windows Service

5. What are the various ways of hosting a WCF service?
Self hosting the service in his own application domain. This we have already covered in the first section. The service comes in to existence when you create the object of ServiceHost class and the service closes when you call the Close of the ServiceHost class.
Host in application domain or process provided by IIS Server.
Host in Application domain and process provided by WAS (Windows Activation Service) Server.

6. What is three major points in WCF?
Address --- Specifies the location of the service which will be like http://Myserver/MyService.Clients will use this location to communicate with our service.
Binding --- Specifies how the two paries will communicate in term of transport and encoding and protocols
Contract --- Specifies the interface between client and the server.It's a simple interface with some attribute.
7. What is the difference WCF and Web services?
Web services can only be invoked by HTTP (traditional webservice with .asmx). While WCF Service or a WCF component can be invoked by any protocol (like http, tcp etc.) and any transport type.

Second web services are not flexible. However, WCF Services are flexible. If you make a new version of the service then you need to just expose a new end. Therefore, services are agile and which is a very practical approach looking at the current business trends.

We develop WCF as contracts, interface, operations, and data contracts. As the developer we are more focused on the business logic services and need not worry about channel stack. WCF is a unified programming API for any kind of services so we create the service and use configuration information to set up the communication mechanism like HTTP/TCP/MSMQ etc

8. What are various ways of hosting WCF Services?
There are three major ways of hosting a WCF services

• Self-hosting the service in his own application domain. This we have already covered in the first section. The service comes in to existence when you create the object of Service Host class and the service closes when you call the Close of the Service Host class.

• Host in application domain or process provided by IIS Server.
• Host in Application domain and process provided by WAS (Windows Activation Service) Server.

9. What was the code name for WCF?
The code name of WCF was Indigo .

WCF is a unification of .NET framework communication technologies which unites the following technologies:-

NET remoting
MSMQ
Web services
COM+

10. What are the main components of WCF?
The main components of WCF are
1. Service class
2. Hosting environment
3. End point

11. How to deal with operation overloading while exposing the WCF services?
By Default overload operations (methods) are not supported in WSDL based operation. However by using Name property of OperationContract attribute, we can deal with operation overloading scenario.

[ServiceContract]
interface ICalculator
{
[OperationContract(Name = "AddInt")]
int Add(int arg1,int arg2);
[OperationContract(Name = "AddDouble")]
double Add(double arg1,double arg2);
}
Notice that both method name in the above interface is same (Add), however the Name property of the OperationContract is different. In this case client proxy will have two methods with different name AddInt and AddDouble.
The timeout property can be set for the WCF Service client call using binding tag.

<client>
<endpoint
...
binding = "wsHttpBinding"
bindingConfiguration = "LongTimeout"
...
/>
</client>
<bindings>
<wsHttpBinding>
<binding name = "LongTimeout" sendTimeout = "00:04:00"/>
</wsHttpBinding>
</bindings>

If no timeout has been specified, the default is considered as 1 minute.

12. How to configure Reliability while communicating with WCF Services?
Reliability can be configured in the client config file by adding reliableSession under binding tag.
<system.serviceModel>
<services>
<service name = "MyService">
<endpoint
address = "net.tcp://localhost:8888/MyService"
binding = "netTcpBinding"
bindingConfiguration = "ReliableCommunication"
contract = "IMyContract"
/>
</service>
</services>
<bindings>
<netTcpBinding>
<binding name = "ReliableCommunication">
<reliableSession enabled = "true"/>
</binding>
</netTcpBinding>
</bindings>
</system.serviceModel>

Reliability is supported by following bindings only
NetTcpBinding
WSHttpBinding
WSFederationHttpBinding
WSDualHttpBinding
13. What is Transport and Message Reliability?
Transport reliability (such as the one offered by TCP) offers point-to-point guaranteed delivery at the network packet level, as well as guarantees the order of the packets. Transport reliability is not resilient to dropping network connections and a variety of other communication problems.

Message reliability deals with reliability at the message level independent of how many packets are required to deliver the message. Message reliability provides for end-to-end guaranteed delivery and order of messages, regardless of how many intermediaries are involved, and how many network hops are required to deliver the message from the client to the service.

14. What are different elements of WCF Srevices Client configuration file?
WCF Services client configuration file contains endpoint, address, binding and contract. A sample client config file looks like
<system.serviceModel>
<client>
<endpoint name = "MyEndpoint"
address = "http://localhost:8000/MyService/"
binding = "wsHttpBinding"
contract = "IMyContract"
/>
</client>
</system.serviceModel>

15. What is Proxy and how to generate proxy for WCF Services?
The proxy is a CLR class that exposes a single CLR interface representing the service contract. The proxy provides the same operations as service's contract, but also has additional methods for managing the proxy life cycle and the connection to the service. The proxy completely encapsulates every aspect of the service: its location, its implementation technology and runtime platform, and the communication transport.

The proxy can be generated using Visual Studio by right clicking Reference and clicking on Add Service Reference. This brings up the Add Service Reference dialog box, where you need to supply the base address of the service (or a base address and a MEX URI) and the namespace to contain the proxy.

Proxy can also be generated by using SvcUtil.exe command-line utility. We need to provide SvcUtil with the HTTP-GET address or the metadata exchange endpoint address and, optionally, with a proxy filename. The default proxy filename is output.cs but you can also use the /out switch to indicate a different name.

SvcUtil http://localhost/MyService/MyService.svc /out:Proxy.cs

When we are hosting in IIS and selecting a port other than port 80 (such as port 88), we must provide that port number as part of the base address:

SvcUtil http://localhost:88/MyService/MyService.svc /out:Proxy.cs

16. What are contracts in WCF?
In WCF, all services expose contracts. The contract is a platform-neutral and standard way of describing what the service does.

WCF defines four types of contracts.

Service contracts
Describe which operations the client can perform on the service.
There are two types of Service Contracts.
ServiceContract - This attribute is used to define the Interface.
OperationContract - This attribute is used to define the method inside Interface.

[ServiceContract]
interface IMyContract
{
[OperationContract]
string MyMethod( );
}
class MyService : IMyContract
{
public string MyMethod( )
{
return "Hello World";
}
}

Data contracts
Define which data types are passed to and from the service. WCF defines implicit contracts for built-in types such as int and string, but we can easily define explicit opt-in data contracts for custom types.

There are two types of Data Contracts.
  1. DataContract - attribute used to define the class
  2. DataMember - attribute used to define the properties.

[DataContract]
class Contact
{
[DataMember]
public string FirstName;

[DataMember]
public string LastName;
}

If DataMember attributes are not specified for a properties in the class, that property can't be passed to-from web service.

Fault contracts
Define which errors are raised by the service, and how the service handles and propagates errors to its clients.

Message contracts
Allow the service to interact directly with messages. Message contracts can be typed or untyped, and are useful in interoperability cases and when there is an existing message format we have to comply with.

17. What is the address formats of the WCF transport schemas?
Address format of WCF transport schema always follow

[transport]://[machine or domain][:optional port] format.

for example:

HTTP Address Format

http://localhost:8888
the way to read the above url is

"Using HTTP, go to the machine called localhost, where on port 8888 someone is waiting"
When the port number is not specified, the default port is 80.

TCP Address Format

net.tcp://localhost:8888/MyService

When a port number is not specified, the default port is 808:

net.tcp://localhost/MyService

NOTE: Two HTTP and TCP addresses from the same host can share a port, even on the same machine.

  1. IPC Address Format
  2. net.pipe://localhost/MyPipe

We can only open a named pipe once per machine, and therefore it is not possible for two named pipe addresses to share a pipe name on the same machine.

MSMQ Address Format
net.msmq://localhost/private/MyService
net.msmq://localhost/MyService

18. How to define a service as REST based service in WCF?
WCF 3.5 provides explicit support for RESTful communication using a new binding named WebHttpBinding.
The below code shows how to expose a RESTful service
[ServiceContract]
interface IStock
{
[OperationContract]
[WebGet]
int GetStock(string StockId);
}

By adding the WebGetAttribute, we can define a service as REST based service that can be accessible using HTTP GET operation.

19. What is endpoint in WCF?
Every service must have Address that defines where the service resides, Contract that defines what the service does and a Binding that defines how to communicate with the service. In WCF the relationship between Address, Contract and Binding is called Endpoint.

The Endpoint is the fusion of Address, Contract and Binding.

20. What is binding and how many types of bindings are there in WCF?
A binding defines how an endpoint communicates to the world. A binding defines the transport (such as HTTP or TCP) and the encoding being used (such as text or binary). A binding can contain binding elements that specify details like the security mechanisms used to secure messages, or the message pattern used by an endpoint.

21. WCF supports nine types of bindings.

Basic binding
Offered by the BasicHttpBinding class, this is designed to expose a WCF service as a legacy ASMX web service, so that old clients can work with new services. When used by the client, this binding enables new WCF clients to work with old ASMX services.
TCP binding
Offered by the NetTcpBinding class, this uses TCP for cross-machine communication on the intranet. It supports a variety of features, including reliability, transactions, and security, and is optimized for WCF-to-WCF communication. As a result, it requires both the client and the service to use WCF.

Peer network binding
Offered by the NetPeerTcpBinding class, this uses peer networking as a transport. The peer network-enabled client and services all subscribe to the same grid and broadcast messages to it.

IPC binding
Offered by the NetNamedPipeBinding class, this uses named pipes as a transport for same-machine communication. It is the most secure binding since it cannot accept calls from outside the machine and it supports a variety of features similar to the TCP binding.

Web Service (WS) binding
Offered by the WSHttpBinding class, this uses HTTP or HTTPS for transport, and is designed to offer a variety of features such as reliability, transactions, and security over the Internet.

Federated WS binding
Offered by the WSFederationHttpBinding class, this is a specialization of the WS binding, offering support for federated security.

Duplex WS binding
Offered by the WSDualHttpBinding class, this is similar to the WS binding except it also supports bidirectional communication from the service to the client.

MSMQ binding
Offered by the NetMsmqBinding class, this uses MSMQ for transport and is designed to offer support for disconnected queued calls.

MSMQ integration binding
Offered by the MsmqIntegrationBinding class, this converts WCF messages to and from MSMQ messages, and is designed to interoperate with legacy MSMQ clients.
For WCF binding comparison, see http://www.pluralsight.com/community/blogs/aaron/archive/2007/03/22/46560.aspx

22. Where we can host WCF services?
Every WCF services must be hosted somewhere. There are three ways of hosting WCF services.

They are
1. IIS
2. Self Hosting
3. WAS (Windows Activation Service)

For more details see http://msdn.microsoft.com/en-us/library/bb332338.aspx
23. What is address in WCF and how many types of transport schemas are there in WCF?
Address is a way of letting client know that where a service is located. In WCF, every service is associated with a unique address. This contains the location of the service and transport schemas.


What is the Microsoft.NET?
.NET is a set of technologies designed to transform the internet into a full scale distributed platform. It provides new ways of connecting systems, information and devices through a collection of web services. It also provides a language independent, consistent programming model across all tiers of an application.
The goal of the .NET platform is to simplify web development by providing all of the tools and technologies that one needs to build distributed web applications.
What is the .NET Framework?
The .NET Framework is set of technologies that form an integral part of the .NET Platform. It is Microsoft's managed code programming model for building applications that have visually stunning user experiences, seamless and secure communication, and the ability to model a range of business processes.
The .NET Framework has two main components: the common language runtime (CLR) and .NET Framework class library. The CLR is the foundation of the .NET framework and provides a common set of services for projects that act as building blocks to build up applications across all tiers. It simplifies development and provides a robust and simplified environment which provides common services to build application. The .NET framework class library is a collection of reusable types and exposes features of the runtime. It contains of a set of classes that is used to access common functionality.
What is CLR?
The .NET Framework provides a runtime environment called the Common Language Runtime or CLR. The CLR can be compared to the Java Virtual Machine or JVM in Java. CLR handles the execution of code and provides useful services for the implementation of the program. In addition to executing code, CLR provides services such as memory management, thread management, security management, code verification, compilation, and other system services. It enforces rules that in turn provide a robust and secure execution environment for .NET applications.
What is CTS?
Common Type System (CTS) describes the datatypes that can be used by managed code. CTS defines how these types are declared, used and managed in the runtime. It facilitates cross-language integration, type safety, and high performance code execution. The rules defined in CTS can be used to define your own classes and values.
What is CLS?
Common Language Specification (CLS) defines the rules and standards to which languages must adhere to in order to be compatible with other .NET languages. This enables C# developers to inherit from classes defined in VB.NET or other .NET compatible languages.
What is managed code?
The .NET Framework provides a run-time environment called the Common Language Runtime, which manages the execution of code and provides services that make the development process easier. Compilers and tools expose the runtime's functionality and enable you to write code that benefits from this managed execution environment. The code that runs within the common language runtime is called managed code.
What is MSIL?
When the code is compiled, the compiler translates your code into Microsoft intermediate language (MSIL). The common language runtime includes a JIT compiler for converting this MSIL then to native code.
MSIL contains metadata that is the key to cross language interoperability. Since this metadata is standardized across all .NET languages, a program written in one language can understand the metadata and execute code, written in a different language. MSIL includes instructions for loading, storing, initializing, and calling methods on objects, as well as instructions for arithmetic and logical operations, control flow, direct memory access, exception handling, and other operations.
What is JIT?
JIT is a compiler that converts MSIL to native code. The native code consists of hardware specific instructions that can be executed by the CPU.
Rather than converting the entire MSIL (in a portable executable[PE]file) to native code, the JIT converts the MSIL as it is needed during execution. This converted native code is stored so that it is accessible for subsequent calls.
What is portable executable (PE)?PE is the file format defining the structure that all executable files (EXE) and Dynamic Link Libraries (DLL) must use to allow them to be loaded and executed by Windows. PE is derived from the Microsoft Common Object File Format (COFF). The EXE and DLL files created using the .NET Framework obey the PE/COFF formats and also add additional header and data sections to the files that are only used by the CLR.
What is an application domain?
Application domain is the boundary within which an application runs. A process can contain multiple application domains. Application domains provide an isolated environment to applications that is similar to the isolation provided by processes. An application running inside one application domain cannot directly access the code running inside another application domain. To access the code running in another application domain, an application needs to use a proxy.
How does an AppDomain get created? AppDomains are usually created by hosts. Examples of hosts are the Windows Shell, ASP.NET and IE. When you run a .NET application from the command-line, the host is the Shell. The Shell creates a new AppDomain for every application. AppDomains can also be explicitly created by .NET applications.
What is an assembly?
An assembly is a collection of one or more .exe or dll’s. An assembly is the fundamental unit for application development and deployment in the .NET Framework. An assembly contains a collection of types and resources that are built to work together and form a logical unit of functionality. An assembly provides the CLR with the information it needs to be aware of type implementations.
What are the contents of assembly?
A static assembly can consist of four elements:
         Assembly manifest - Contains the assembly metadata. An assembly manifest contains the information about the identity and version of the assembly. It also contains the information required to resolve references to types and resources.
         Type metadata - Binary information that describes a program.
         Microsoft intermediate language (MSIL) code.
         A set of resources.
What are the different types of assembly?
Assemblies can also be private or shared. A private assembly is installed in the installation directory of an application and is accessible to that application only. On the other hand, a shared assembly is shared by multiple applications. A shared assembly has a strong name and is installed in the GAC.
We also have satellite assemblies that are often used to deploy language-specific resources for an application.
What is a dynamic assembly?
A dynamic assembly is created dynamically at run time when an application requires the types within these assemblies.
What is a strong name?
You need to assign a strong name to an assembly to place it in the GAC and make it globally accessible. A strong name consists of a name that consists of an assembly's identity (text name, version number, and culture information), a public key and a digital signature generated over the assembly.  The .NET Framework provides a tool called the Strong Name Tool (Sn.exe), which allows verification and key pair and signature generation.
What is GAC? What are the steps to create an assembly and add it to the GAC?
The global assembly cache (GAC) is a machine-wide code cache that stores assemblies specifically designated to be shared by several applications on the computer. You should share assemblies by installing them into the global assembly cache only when you need to.
Steps
- Create a strong name using sn.exe tool eg: sn -k mykey.snk
- in AssemblyInfo.cs, add the strong name eg: [assembly: AssemblyKeyFile("mykey.snk")]
- recompile project, and then install it to GAC in two ways :
         drag & drop it to assembly folder (C:\WINDOWS\assembly OR C:\WINNT\assembly) (shfusion.dll tool)
         gacutil -i abc.dll
What is the caspol.exe tool used for?
The caspol tool grants and modifies permissions to code groups at the user policy, machine policy, and enterprise policy levels.
What is a garbage collector?
A garbage collector performs periodic checks on the managed heap to identify objects that are no longer required by the program and removes them from memory.
What are generations and how are they used by the garbage collector?
Generations are the division of objects on the managed heap used by the garbage collector. This mechanism allows the garbage collector to perform highly optimized garbage collection. The unreachable objects are placed in generation 0, the reachable objects are placed in generation 1, and the objects that survive the collection process are promoted to higher generations.
What is Ilasm.exe used for?
Ilasm.exe is a tool that generates PE files from MSIL code. You can run the resulting executable to determine whether the MSIL code performs as expected.
What is Ildasm.exe used for?
Ildasm.exe is a tool that takes a PE file containing the MSIL code as a parameter and creates a text file that contains managed code.
What is the ResGen.exe tool used for?
ResGen.exe is a tool that is used to convert resource files in the form of .txt or .resx files to common language runtime binary .resources files that can be compiled into satellite assemblies.


What is ASP.NET?
Microsoft ASP.NET is a server side technology that enables programmers to build dynamic Web sites, web applications, and XML Web services. It is a part of the .NET based environment and is built on the Common Language Runtime (CLR) . So programmers can write ASP.NET code using any .NET compatible language.
What are the differences between ASP.NET 1.1 and ASP.NET 2.0?
A comparison chart containing the differences between ASP.NET 1.1 and ASP.NET 2.0 can be found over
Which is the latest version of ASP.NET? What were the previous versions released?
The latest version of ASP.NET is 2.0. There have been 3 versions of ASP.NET released as of date. They are as follows :
ASP.NET 1.0 – Released on January 16, 2002.
ASP.NET 1.1 – Released on April 24, 2003.
ASP.NET 2.0 – Released on November 7, 2005.
Additionally, ASP.NET 3.5 is tentatively to be released by the end of the 2007.
Explain the Event Life cycle of ASP.NET 2.0?
The events occur in the following sequence. Its best to turn on tracing(<% @Page Trace=”true”%>) and track the flow of events :
PreInit – This event represents the entry point of the page life cycle. If you need to change the Master page or theme programmatically, then this would be the event to do so. Dynamic controls are created in this event.
Init – Each control in the control collection is initialized.
Init Complete* - Page is initialized and the process is completed.
PreLoad* - This event is called before the loading of the page is completed.
Load – This event is raised for the Page and then all child controls. The controls properties and view state can be accessed at this stage. This event indicates that the controls have been fully loaded.
LoadComplete* - This event signals indicates that the page has been loaded in the memory. It also marks the beginning of the rendering stage.
PreRender – If you need to make any final updates to the contents of the controls or the page, then use this event. It first fires for the page and then for all the controls.
PreRenderComplete* - Is called to explicitly state that the PreRender phase is completed.
SaveStateComplete* - In this event, the current state of the control is completely saved to the ViewState.
Unload – This event is typically used for closing files and database connections. At times, it is also used for logging some wrap-up tasks.
The events marked with * have been introduced in ASP.NET 2.0.
You have created an ASP.NET Application. How will you run it?
With ASP.NET 2.0, Visual Studio comes with an inbuilt ASP.NET Development Server to test your pages. It functions as a local Web server. The only limitation is that remote machines cannot access pages running on this local server. The second option is to deploy a Web application to a computer running IIS version 5 or 6 or 7.
Explain the AutoPostBack feature in ASP.NET?
AutoPostBack allows a control to automatically postback when an event is fired. For eg: If we have a Button control and want the event to be posted to the server for processing, we can set AutoPostBack = True on the button.
How do you disable AutoPostBack?
Hence the AutoPostBack can be disabled on an ASP.NET page by disabling AutoPostBack on all the controls of a page. AutoPostBack is caused by a control on the page.
What are the different code models available in ASP.NET 2.0?
There are 2 code models available in ASP.NET 2.0. One is the single-file page and the other one is the code behind page.
Which base class does the web form inherit from?
Page class in the System.Web.UI namespace.
Which are the new special folders that are introduced in ASP.NET 2.0?
There are seven new folders introduced in ASP.NET 2.0 :
\App_Browsers folder – Holds browser definitions(.brower) files which identify the browser and their capabilities.
\App_Code folder – Contains source code (.cs, .vb) files which are automatically compiled when placed in this folder. Additionally placing web service files generates a proxy class(out of .wsdl) and a typed dataset (out of .xsd).
\App_Data folder – Contains data store files like .mdf (Sql Express files), .mdb, XML files etc. This folder also stores the local db to maintain membership and role information.
\App_GlobalResources folder – Contains assembly resource files (.resx) which when placed in this folder are compiled automatically. In earlier versions, we were required to manually use the resgen.exe tool to compile resource files. These files can be accessed globally in the application.
\App_LocalResources folder – Contains assembly resource files (.resx) which can be used by a specific page or control.
\App_Themes folder – This folder contains .css and .skin files that define the appearance of web pages and controls.
 \App_WebReferences folder – Replaces the previously used Web References folder. This folder contains the .disco, .wsdl, .xsd files that get generated when accessing remote web services.
Explain the ViewState in ASP.NET?
Http is a stateless protocol. Hence the state of controls is not saved between postbacks. Viewstate is the means of storing the state of server side controls between postbacks. The information is stored in HTML hidden fields. In other words, it is a snapshot of the contents of a page.
You can disable viewstate by a control by setting the EnableViewState property to false.
What does the EnableViewState property signify?
EnableViewState saves the state of an object in a page between postbacks. Objects are saved in a Base64 encoded string. If you do not need to store the page, turn it off as it adds to the page size.
 
Explain the ASP.NET Page Directives?
Page directives configure the runtime environment that will execute the page. The complete list of directives is as follows:
@ Assembly - Links an assembly to the current page or user control declaratively. 
@ Control - Defines control-specific attributes used by the ASP.NET page parser and compiler and can be included only in .ascx files (user controls).
@ Implements - Indicates that a page or user control implements a specified .NET Framework interface declaratively. 
@ Import - Imports a namespace into a page or user control explicitly.
@ Master - Identifies a page as a master page and defines attributes used by the ASP.NET page parser and compiler and can be included only in .master files.
@ MasterType - Defines the class or virtual path used to type the Master property of a page.
 @ OutputCache - Controls the output caching policies of a page or user control declaratively.
 @ Page - Defines page-specific attributes used by the ASP.NET page parser and compiler and can be included only in .aspx files.
 @ PreviousPageType - Creates a strongly typed reference to the source page from the target of a cross-page posting.
 @ Reference - Links a page, user control, or COM control to the current page or user control declaratively.
 @ Register - Associates aliases with namespaces and classes, which allow user controls and custom server controls to be rendered when included in a requested page or user control.
This list has been taken from here.
Explain the Validation Controls used in ASP.NET 2.0?
Validation controls allows you to validate a control against a set of rules. There are 6 different validation controls used in ASP.NET 2.0.
RequiredFieldValidator – Checks if the control is not empty when the form is submitted.
CompareValidator – Compares the value of one control to another using a comparison operator (equal, less than, greater than etc).
RangeValidator – Checks whether a value falls within a given range of number, date or string.
RegularExpressionValidator – Confirms that the value of a control matches a pattern defined by a regular expression. Eg: Email validation.
CustomValidator – Calls your own custom validation logic to perform validations that cannot be handled by the built in validators.
ValidationSummary – Show a summary of errors raised by each control on the page on a specific spot or in a message box.
How do you indentify that the page is post back?
By checking the IsPostBack property. If IsPostBack is True, the page has been posted back.
What are Master Pages?
Master pages is a template that is used to create web pages with a consistent layout throughout your application. Master Pages contains content placeholders to hold page specific content. When a page is requested, the contents of a Master page are merged with the content page, thereby giving a consistent layout.
How is a Master Page different from an ASP.NET page?
The MasterPage has a @Master top directive and contains ContentPlaceHolder server controls. It is quiet similar to an ASP.NET page.
How do you attach an exisiting page to a Master page?
By using the MasterPageFile attribute in the @Page directive and removing some markup.
How do you set the title of an ASP.NET page that is attached to a Master Page?
By using the Title property of the @Page directive in the content page. Eg:
<@Page MasterPageFile="Sample.master" Title="I hold content" %>
What is a nested master page? How do you create them?
A Nested master page is a master page associated with another master page. To create a nested master page, set the MasterPageFile attribute of the @Master directive to the name of the .master file of the base master page.
What are Themes?
Themes are a collection of CSS files, .skin files, and images. They are text based style definitions and are very similar to CSS, in that they provide a common look and feel throughout the website.
What are skins?
A theme contains one or more skin files. A skin is simply a text file with a .skin extension and contains definition of styles applied to server controls in an ASP.NET page. For eg:
<asp:button runat="server" BackColor="blue" BorderColor="Gray" Font-Bold ="true" ForeColor="white"/>
Defines a skin that will be applied to all buttons throughout to give it a consistent look and feel.
What is the difference between Skins and Css files?
Css is applied to HTML controls whereas skins are applied to server controls.
What is a User Control?
User controls are reusable controls, similar to web pages. They cannot be accessed directly.
Explain briefly the steps in creating a user control?
         Create a file with .ascx extension and place the @Control directive at top of the page.
         Included the user control in a Web Forms page using a @Register directive
What is a Custom Control?
Custom controls are compiled components that run on the server and that encapsulate user-interface and other related functionality into reusable packages. They can include all the design-time features of standard ASP.NET server controls, including full support for Visual Studio design features such as the Properties window, the visual designer, and the Toolbox.
What are the differences between user and custom controls?
User controls are easier to create in comparison to custom controls, however user controls can be less convenient to use in advanced scenarios.
User controls have limited support for consumers who use a visual design tool whereas custom controls have full visual design tool support for consumers.
A separate copy of the user control is required in each application that uses it whereas only a single copy of the custom control is required, in the global assembly cache, which makes maintenance easier.
A user control cannot be added to the Toolbox in Visual Studio whereas custom controls can be added to the Toolbox in Visual Studio.
User controls are good for static layout whereas custom controls are good for dynamic layout.
Where do you store your connection string information?
The connection string can be stored in configuration files (web.config). 
What is the difference between ‘Web.config’ and ‘Machine.config’?
Web.config files are used to apply configuration settings to a particular web application whereas machine.config file is used to apply configuration settings for all the websites on a web server.
Web.config files are located in the application's root directory or inside a folder situated in a lower hierarchy. The machine.config is located in the Windows directory Microsoft.Net\Framework\Version\CONFIG.
There can be multiple web.config files in an application nested at different hierarchies. However there can be only one machine.config file on a web server.
What is the difference between Server.Transfer and Response.Redirect?
Response.Redirect involves a roundtrip to the server whereas Server.Transfer conserves server resources by avoiding the roundtrip. It just changes the focus of the webserver to a different page and transfers the page processing to a different page.
Response.Redirect can be used for both .aspx and html pages whereas Server.Transfer can be used only for .aspx pages.
Response.Redirect can be used to redirect a user to an external websites. Server.Transfer can be used only on sites running on the same server. You cannot use Server.Transfer to redirect the user to a page running on a different server.
Response.Redirect changes the url in the browser. So they can be bookmarked. Whereas Server.Transfer retains the original url in the browser. It just replaces the contents of the previous page with the new one.
What method do you use to explicitly kill a users session?
Session.Abandon().
What is a webservice?
Web Services are applications delivered as a service on the Web. Web services allow for programmatic access of business logic over the Web. Web services typically rely on XML-based protocols, messages, and interface descriptions for communication and access. Web services are designed to be used by other programs or applications rather than directly by end user. Programs invoking a Web service are called clients. SOAP over HTTP is the most commonly used protocol for invoking Web services.

Business Intelligence Interview Questions
Business intelligence (BI) is a broad category of application programs and technologies for gathering, storing, analyzing, and providing access to data to help enterprise users make better business decisions. BI applications include the activities of decision support, query and reporting, online analytical processing (OLAP), statistical analysis, forecasting, and data mining.
Dashboard is a user interface that organizes and presents information in a way that is easy to read.Its a reporting tool that consolidates aggregates and arranges measurements metrics.
A BI dashboard is a quick re-look at all the data that makes up the business intelligence. It allows a huge amount of data to be read in a single interface. Thus, the decisions are taken up faster (i.e. real time business intelligence). These details can be further used to analyze the success or failure of any project or business.


I have got these queries from someone and I would like to share my views. I dont claim my answers to be correct and hence wherever you dont agree, please let me know and share your views.
If you have any questions or comments, please post them.
Question:How you generally Approach to ur Analytics Project?
Answer: Any project should start from defining the scope of the project and the approach
should be not to deviate from the scope.
Then the project should be functionally divided into smaller modules generally done by
project managers alongwith technical and functional leads.
The functional leads then decide on majorly three things:
1. According to the defined scope of the project they start gathering requirements while
interacting with the clients.
2. They had a discussion with the technical leads and try to reach a solution.
3. Technical leads decides what schemas to create and what requirements are going to
fulfill by that schema.
Technical leads discuss all this with the developers and try to close requirements.
Simultaneously testing and deployment is planned in a phased manner.
Question: How we are going to decide which schema we are going to implement in the data warehouse?
Answer: One way is what is mentioned in Question above.
If you ask me to blindly create schemas for the warehouse without knowing any requirements,
I will simply first divide the schemas on the basis of functional areas of an Organisation
which are similar to the modules in an ERP like sales, finance, purchase, inventory,
production, HR etc.
I will broadly describe the expected analysis an organisation would like to do in every
module. I think this way you would be able to complete at least 40-50 % of the
requirements. To move ahead, study the data and business and you can create few more
schemas.
Question: What are the Challenges You Faced while making of Reports?
Answer: Making of an report has never been a difficult task. But problem comes when users
are reluctant to adopt a new system. I have experienced that if you are not able to create
the report in exactly the way they used to see, they will keep asking for the changes. Your
approach should be to first show them what they want to see and then add more information
in the report.
Question: What you will do when your Report is not Fetching Right Data?
Answer: this is the biggest problem in report creation and verification. There could be two
reasons for report not fetching the right data.
1. Mostly clients do not have correct data in their database and on top of that to correct
the results they make some changes at the report level to bring the desired result which
you may not e aware of while creating the reports. Clients try to match the data with their
existing reports and you never get the correct results. you try to discover the things and
at later stage come to know of all these problems and you are held responsible for this
delay. Hence always consult the SPOC(Single Point of Contact) and try to understand the
logic they have used to generate their reports.
2. If the database values are correct, there there could be a problem with the joins and
relations in the schema. You need to discover that analysing and digging deep into the
matter.

There are more questions which I will try to answer later.
The questions are very specific to OBIEE and I dont have much experience in that. Hence you may not agree to my answers, but wherever please post a comment and let me know too.


Question: How analytics Process Your Request When you Create your Requests.
Answer: If the Question means how does Oracle BI Analytics Server processes the user requests, the answer is- Oracle BI server converts the logical SQL submitted by the client into optimised physical SQL which is then sent to the backend database. Also in between it performs various tasks like converting the user operations like user selections to form a logical SQL, checking and verifying credentials, breaking the request into threads(as Oracle BI is a multi threaded server), processes the requests, manages the cached results, again converting the results received from the database into user presentable form etc.



Question: From where u Get the Logical Query of your Request?
Answer: The logical SQL generated by the server can be viewed in BI Answers. If I have not understood the question, Please raise your voice.



Question: Major Challenges You Faced While Creating the RPD??????
Answer: Every now and then there are problems with the database connections but the problem while creating the repository RPD files comes with complex schemas made on OLTP systems consisting of lot of joins and checking the results. Th type of join made need to be checked. By default it is inner join but sometimes the requirement demands other types of joins. There are lot of problems with the date formats also.


Question: What are Global Filter and how thery differ From Column Filter?
Answer: Column filter- simply a filter applied on a column which we can use to restrict our column values while pulling the data or in charts to see the related content.
Global filter- Not sure. I understand this filter will have impact on across the application but I really dont understand where and how it can be user. I heard of global variables but not global filters.


How to make the Delivery Profilers Work?



When we are Use SA System how Does SA Server understand that It needs to use it For Getting

the User Profile information?



Where to Configure the Scheduler?
Answer: I am not sure if Iam correct but we configure the OBIEE schedular in database.



Question: How to hide Certain Columns From a User?
Answer: Application access level security- Do not add the column in the report, Do not add the column in the presentation layer.




Question:How can we Enable Drills in a Given Column Data?
Answer: To enable Drill down for a column, it should be included in the hirarchy in OBIEE. Hyperion IR has a drill anywhere feature where dont have to define and can drill to any available column.



Question: Is Drill Down Possible without the attribute being a Part of a Hierarchical Dimension?
Answer: No


Question: How do u Conditional Format.?
Answer: while creating a chat in BI Answers, you can define the conditions and can apply colour formatting.



Question: What is Guided Navigation?
Answer: I think it is just the arrangement of hyperlinks to guide the user to navigate between the reports to do the analysis.

How is Webcat File Deployed Across Environment?



Question: How the users Created Differs From RPD/Answers/Dashboards Level?????
Answer: RPD users can do administrator tasks like adding new data source, create hirarchies, change column names where as Answers users may create new charts, edit those charts and Dashboard users may only view and analyse the dashboard or can edit dashboard by adding/removing charts objects.


Question: Online/Offline Mode how it Impact in Dev and Delpoyment????
Answer: Online Mode- You can make changes in the RPD file and push in changes which will be immediately visible to the users who are already connected. This feature we may use in production environment.
Offline mode- can be useful in test or development environment.


What is physical query ?

The table,column, and key objects provide the metadata necessary for Oracle BI Server to access the actual physical sources with SQL requests.

The actual SQL Query which is passing to DB(Data Warehouse).

How do you use a column selector to change the dimensions dynamically

Column selector permits users to dynamically change which columns to appear in results.
Try it in the 'views' in presentation services.

what is flat aggregation

There is not such term as "flat aggregation". However, there are something called "flat dimension" - dimension that has no hierarchies. I would think that when you aggregate across flat dimension you will be doing flat aggregation?


What is BI?
Business intelligence (BI) is a broad category of applications and technologies for gathering, storing, analyzing, and providing access to data to help enterprise users make better business decisions.

What is a Universe?

Universe is a symantec layer between database and the actual user useing the objects for query. It hides the typical database structure from the user.

Dimensional Modelling

This is simple layout without stocking/restocking data, also without GL, specials handling, etc...
You would have to have these dimensions:

Date (including, excluding time - based on how grainual you need to go)
date key, date, date attributes - whatever you need to analyze
20100101, 01/01/2010, Monday, 1 QT

Buyer (or visitor)
buyer key, buyer attribute - whatever you like to keep
123, John Smith, 123 Main Street, New York, NY, male, 35 y.o., married...

Products
product key, product title, price,  product attributes - whatever you have
1234, stapler, $1.00, black, (made by) USRT, 45inch

and your Sales fact table:
datekey, buyer key and product key
20100101, 123, 1234

What is the main use of business content? How data can be loaded into business content after installation?

Business content can come from various parts of your business operations. Some from the finance department and some from the sales department.

If these business content are consistent and can be used by both departments then it's all about loading the data into a data warehouse for analysis purposes.
In case data are inconsistent and can't be easily compared, then a data integration tool would help to integrate data from various data sources before it is loaded into the data warehouse

How do you make a column drillable if its not a dimension hierarchy level?

Create a Alternate Drill down   like if region is the name of dimension and the hierarchy is


Region
Country          Branch Name
Branch Name
          Sales Rep Name


Branch Name provides an alternate drill down option.
Why we can't create an aggregate on an ODS Object?
1. Operational Data Store has very low data latency. Data moved to ODS mostly on event based rather than time based ETL to Data Warehouse/Data Mart.

2. ODS is more closer to OLTP system. We don't normally prefer to store aggregated data in OLTP. So it is with ODS.

3. Unlike data warehouse where data is HISTORICAL, ODS is near real time(NRT). So data aggregation is less important is ODS as data keeps changing.




What's A Data warehouse


Answer1:
A Data warehouse is a repository of integrated information, available for queries and analysis. Data and information are extracted from heterogeneous sources as they are generated. This makes it much easier and more efficient to run queries over data that originally came from different sources". Another definition for data warehouse is: " A data warehouse is a logical collection of information gathered from many different operational databases used to create business intelligence that supports business analysis activities and decision-making tasks, primarily, a record of an enterprise's past transactional and operational information, stored in a database designed to favour efficient data analysis and reporting (especially OLAP)". Generally, data warehousing is not meant for current "live" data, although 'virtual' or 'point-to-point' data warehouses can access operational data. A 'real' data warehouse is generally preferred to a virtual DW because stored data has been validated and is set up to provide reliable results to common types of queries used in a business.

Answer2:
Data Warehouse is a repository of integrated information, available for queries and analysis. Data and information are extracted from heterogeneous sources as they are generated....This makes it much easier and more efficient to run queries over data that originally came from different sources.
Typical relational databases are designed for on-line transactional processing (OLTP) and do not meet the requirements for effective on-line analytical processing (OLAP). As a result, data warehouses are designed differently than traditional relational databases.






What is ODS?


1. ODS means Operational Data Store.
2. A collection of operation or bases data that is extracted from operation databases and standardized, cleansed, consolidated, transformed, and loaded into an enterprise data architecture. An ODS is used to support data mining of operational data, or as the store for base data that is summarized for a data warehouse. The ODS may also be used to audit the data warehouse to assure summarized and derived data is calculated properly. The ODS may further become the enterprise shared operational database, allowing operational systems that are being reengineered to use the ODS as there operation databases.






What is a dimension table?


A dimensional table is a collection of hierarchies and categories along which the user can drill down and drill up. it contains only the textual attributes.






What is a lookup table?


A lookUp table is the one which is used when updating a warehouse. When the lookup is placed on the target table (fact table / warehouse) based upon the primary key of the target, it just updates the table by allowing only new records or updated records based on the lookup condition.






Why should you put your data warehouse on a different system than your OLTP system?


Answer1:
A OLTP system is basically " data oriented " (ER model) and not " Subject oriented "(Dimensional Model) .That is why we design a separate system that will have a subject oriented OLAP system...
Moreover if a complex querry is fired on a OLTP system will cause a heavy overhead on the OLTP server that will affect the daytoday business directly.

Answer2:
The loading of a warehouse will likely consume a lot of machine resources. Additionally, users may create querries or reports that are very resource intensive because of the potentially large amount of data available. Such loads and resource needs will conflict with the needs of the OLTP systems for resources and will negatively impact those production systems.






What are Aggregate tables?


Aggregate table contains the summary of existing warehouse data which is grouped to certain levels of dimensions.Retrieving the required data from the actual table, which have millions of records will take more time and also affects the server performance.To avoid this we can aggregate the table to certain required level and can use it.This tables reduces the load in the database server and increases the performance of the query and can retrieve the result very fastly.






What is Dimensional Modelling? Why is it important ?


Dimensional Modelling is a design concept used by many data warehouse desginers to build thier datawarehouse. In this design model all the data is stored in two types of tables - Facts table and Dimension table. Fact table contains the facts/measurements of the business and the dimension table contains the context of measuremnets ie, the dimensions on which the facts are calculated.






Why is Data Modeling Important?


Data modeling is probably the most labor intensive and time consuming part of the development process. Why bother especially if you are pressed for time? A common response by practitioners who write on the subject is that you should no more build a database without a model than you should build a house without blueprints.

The goal of the data model is to make sure that the all data objects required by the database are completely and accurately represented. Because the data model uses easily understood notations and natural language , it can be reviewed and verified as correct by the end-users.

The data model is also detailed enough to be used by the database developers to use as a "blueprint" for building the physical database. The information contained in the data model will be used to define the relational tables, primary and foreign keys, stored procedures, and triggers. A poorly designed database will require more time in the long-term. Without careful planning you may create a database that omits data required to create critical reports, produces results that are incorrect or inconsistent, and is unable to accommodate changes in the user's requirements.






What is data mining?


Data mining is a process of extracting hidden trends within a datawarehouse. For example an insurance dataware house can be used to mine data for the most high risk people to insure in a certain geographial area.






What is ETL?


ETL stands for extraction, transformation and loading.

ETL provide developers with an interface for designing source-to-target mappings, ransformation and job control parameter.
· Extraction
Take data from an external source and move it to the warehouse pre-processor database.
· Transformation
Transform data task allows point-to-point generating, modifying and transforming data.
· Loading
Load data task adds records to a database table in a warehouse.






What does level of Granularity of a fact table signify?


Granularity
The first step in designing a fact table is to determine the granularity of the fact table. By granularity, we mean the lowest level of information that will be stored in the fact table. This constitutes two steps:

Determine which dimensions will be included.
Determine where along the hierarchy of each dimension the information will be kept.
The determining factors usually goes back to the requirements






What is the Difference between OLTP and OLAP?


Main Differences between OLTP and OLAP are:-

1. User and System Orientation

OLTP: customer-oriented, used for data analysis and querying by clerks, clients and IT professionals.

OLAP: market-oriented, used for data analysis by knowledge workers( managers, executives, analysis).

2. Data Contents

OLTP: manages current data, very detail-oriented.

OLAP: manages large amounts of historical data, provides facilities for summarization and aggregation, stores information at different levels of granularity to support decision making process.

3. Database Design

OLTP: adopts an entity relationship(ER) model and an application-oriented database design.

OLAP: adopts star, snowflake or fact constellation model and a subject-oriented database design.

4. View

OLTP: focuses on the current data within an enterprise or department.

OLAP: spans multiple versions of a database schema due to the evolutionary process of an organization; integrates information from many organizational locations and data stores






What is SCD1 , SCD2 , SCD3?


SCD Stands for Slowly changing dimensions.

SCD1: only maintained updated values.

Ex: a customer address modified we update existing record with new address.

SCD2: maintaining historical information and current information by using

A) Effective Date
B) Versions
C) Flags

or combination of these

SCD3: by adding new columns to target table we maintain historical information and current information.






Why are OLTP database designs not generally a good idea for a Data Warehouse?


Since in OLTP,tables are normalised and hence query response will be slow for end user and OLTP doesnot contain years of data and hence cannot be analysed.






What is BUS Schema?


BUS Schema is composed of a master suite of confirmed dimension and standardized definition if facts.






What are the various Reporting tools in the Market?


1. MS-Excel
2. Business Objects (Crystal Reports)
3. Cognos (Impromptu, Power Play)
4. Microstrategy
5. MS reporting services
6. Informatica Power Analyzer
7. Actuate
8. Hyperion (BRIO)
9. Oracle Express OLAP
10. Proclarity






What is Normalization, First Normal Form, Second Normal Form , Third Normal Form?


1.Normalization is process for assigning attributes to entities–Reducesdata redundancies–Helps eliminate data anomalies–Produces controlledredundancies to link tables

2.Normalization is the analysis offunctional dependency between attributes / data items of userviews?It reduces a complex user view to a set of small andstable subgroups of fields / relations

1NF:Repeating groups must beeliminated, Dependencies can be identified, All key attributesdefined,No repeating groups in table

2NF: The Table is already in1NF,Includes no partial dependencies–No attribute dependent on a portionof primary key, Still possible to exhibit transitivedependency,Attributes may be functionally dependent on non-keyattributes

3NF: The Table is already in 2NF, Contains no transitivedependencies






What is Fact table?


Fact Table contains the measurements or metrics or facts of business process. If your business process is "Sales" , then a measurement of this business process such as "monthly sales number" is captured in the Fact table. Fact table also contains the foriegn keys for the dimension tables.






What are conformed dimensions?


Answer1:
Conformed dimensions mean the exact same thing with every possible fact table to which they are joined Ex:Date Dimensions is connected all facts like Sales facts,Inventory facts..etc

Answer2:
Conformed dimentions are dimensions which are common to the cubes.(cubes are the schemas contains facts and dimension tables)
Consider Cube-1 contains F1,D1,D2,D3 and Cube-2 contains F2,D1,D2,D4 are the Facts and Dimensions here D1,D2 are the Conformed Dimensions






What are the Different methods of loading Dimension tables?


Conventional Load:
Before loading the data, all the Table constraints will be checked against the data.

Direct load:(Faster Loading)
All the Constraints will be disabled. Data will be loaded directly.Later the data will be checked against the table constraints and the bad data won't be indexed.






What is conformed fact?


Conformed dimensions are the dimensions which can be used across multiple Data Marts in combination with multiple facts tables accordingly






What are Data Marts?


Data Marts are designed to help manager make strategic decisions about their business.
Data Marts are subset of the corporate-wide data that is of value to a specific group of users.

There are two types of Data Marts:

1.Independent data marts – sources from data captured form OLTP system, external providers or from data generated locally within a particular department or geographic area.

2.Dependent data mart – sources directly form enterprise data warehouses.






What is a level of Granularity of a fact table?


Level of granularity means level of detail that you put into the fact table in a data warehouse. For example: Based on design you can decide to put the sales data in each transaction. Now, level of granularity would mean what detail are you willing to put for each transactional fact. Product sales with respect to each minute or you want to aggregate it upto minute and put that data.






How are the Dimension tables designed?


Most dimension tables are designed using Normalization principles upto 2NF. In some instances they are further normalized to 3NF.

Find where data for this dimension are located.

Figure out how to extract this data.

Determine how to maintain changes to this dimension (see more on this in the next section).






What are non-additive facts?


Non-Additive: Non-additive facts are facts that cannot be summed up for any of the dimensions present in the fact table.






What type of Indexing mechanism do we need to use for a typical datawarehouse?


On the fact table it is best to use bitmap indexes. Dimension tables can use bitmap and/or the other types of clustered/non-clustered, unique/non-unique indexes.

To my knowledge, SQLServer does not support bitmap indexes. Only Oracle supports bitmaps.


Bitmap indexes are more advantageous than b-tree indexes when the table has millions of rows and the key columns have low cardinality. bitmap indexes provide better performance than b tree indexes when queries often use a combination of multiple where conditions involving the OR operator, the table is read-only, or when there is low update activity on the key columns.


b-tree indexes are used for high cardinaties, usuall when we have too many distinct columns. and bitmap indexex are used for low cardinaties, usuallywhen we have repeated columns


Bitmap indexes used for Datawarehouse and B-Tree index used for OLTP. Main difference Bitmap can index null values where as B-tree can not.


When you first create a new table, there is no index created by default. In technical terms, a table without an index is called a “heap”. As you would expect, the data we will insert into the table will be returned in the same order. A non-clustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf node of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.


A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.


Clustered index is good when you know in which order you will be returning the records in most cases. You can create clustered index and after that you don’t need to use ORDER BY statement. This will be much more faster. If the order is not important for you and will not create clustered index by yourself, then primary key will be clustered index by default. There is nothing bad not to have the clustered index, it can speed up inserting rows.






What Snow Flake Schema?


Snowflake Schema, each dimension has a primary dimension table, to which one or more additional dimensions can join. The primary dimension table is the only table that can join to the fact table.










What is real time data-warehousing?


Real-time data warehousing is a combination of two things: 1) real-time activity and 2) data warehousing. Real-time activity is activity that is happening right now. The activity could be anything such as the sale of widgets. Once the activity is complete, there is data about it.

Data warehousing captures business activity data. Real-time data warehousing captures business activity data as it occurs. As soon as the business activity is complete and there is data about it, the completed activity data flows into the data warehouse and becomes available instantly. In other words, real-time data warehousing is a framework for deriving information from data as the data becomes available.






What are slowly changing dimensions?


SCD stands for Slowly changing dimensions. Slowly changing dimensions are of three types

SCD1: only maintained updated values.
Ex: a customer address modified we update existing record with new address.

SCD2: maintaining historical information and current information by using
A) Effective Date
B) Versions
C) Flags
or combination of these
scd3: by adding new columns to target table we maintain historical information and current information






What are Semi-additive and factless facts and in which scenario will you use such kinds of fact tables?


Snapshot facts are semi-additive, while we maintain aggregated facts we go for semi-additive.

EX: Average daily balance

A fact table without numeric fact columns is called factless fact table.

Ex: Promotion Facts

While maintain the promotion values of the transaction (ex: product samples) because this table doesn’t contain any measures.






Differences between star and snowflake schemas?


Star schema - all dimensions will be linked directly with a fat table.
Snow schema - dimensions maybe interlinked or may have one-to-many relationship with other tables.






What is a Star Schema?


Star schema is a type of organising the tables such that we can retrieve the result from the database easily and fastly in the warehouse environment.Usually a star schema consists of one or more dimension tables around a fact table which looks like a star,so that it got its name.






What is a general purpose scheduling tool?


The basic purpose of the scheduling tool in a DW Application is to stream line the flow of data from Source To Target at specific time or based on some condition.






What is ER Diagram?


The Entity-Relationship (ER) model was originally proposed by Peter in 1976 [Chen76] as a way to unify the network and relational database views.

Simply stated the ER model is a conceptual data model that views the real world as entities and relationships. A basic component of the model is the Entity-Relationship diagram which is used to visually represents data objects.

Since Chen wrote his paper the model has been extended and today it is commonly used for database design For the database designer, the utility of the ER model is:

it maps well to the relational model. The constructs used in the ER model can easily be transformed into relational tables. it is simple and easy to understand with a minimum of training. Therefore, the model can be used by the database designer to communicate the design to the end user.

In addition, the model can be used as a design plan by the database developer to implement a data model in a specific database management software.






Which columns go to the fact table and which columns go the dimension table?


The Primary Key columns of the Tables(Entities) go to the Dimension Tables as Foreign Keys.
The Primary Key columns of the Dimension Tables go to the Fact Tables as Foreign Keys.






What are modeling tools available in the Market?


here are a number of data modeling tools

Tool Name Company Name
Erwin Computer Associates
Embarcadero Embarcadero Technologies
Rational Rose IBM Corporation
Power Designer Sybase Corporation
Oracle Designer Oracle Corporation






Name some of modeling tools available in the Market?


These tools are used for Data/dimension modeling

1. Oracle Designer
2. ERWin (Entity Relationship for windows)
3. Informatica (Cubes/Dimensions)
4. Embarcadero
5. Power Designer Sybase






How do you load the time dimension?


Time dimensions are usually loaded by a program that loops through all possible dates that may appear in the data. It is not unusual for 100 years to be represented in a time dimension, with one row per day.






Explain the advanatages of RAID 1, 1/0, and 5. What type of RAID setup would you put your TX logs.


Transaction logs write sequentially and don't need to be read at all. The ideal is to have each on RAID 1/0 because it has much better write performance than RAID 5.

RAID 1 is also better for TX logs and costs less than 1/0 to implement. It has a tad less reliability and performance is a little worse generally speaking.

RAID 5 is best for data generally because of cost and the fact it provides great read capability.






What are the vaious ETL tools in the Market?


Various ETL tools used in market are:

1. Informatica
2. Data Stage
3. MS-SQL DTS(Integrated Services 2005)
4. Abinitio
5. SQL Loader
6. Sunopsis
7. Oracle Warehouse Bulider
8. Data Junction






What is VLDB?


Answer 1:
VLDB stands for Very Large DataBase.

It is an environment or storage space managed by a relational database management system (RDBMS) consisting of vast quantities of information.

Answer 2:
VLDB doesn’t refer to size of database or vast amount of information stored. It refers to the window of opportunity to take back up the database.

Window of opportunity refers to the time of interval and if the DBA was unable to take back up in the specified time then the database was considered as VLDB.






Data Warehousing Interview Questions and Answers









(Continued from previous part...)


Data Warehousing Basics Interview Questions and Answers (1)






What are Data Marts ?


A data mart is a focused subset of a data warehouse that deals with a single area(like different department) of data and is organized for quick analysis






What are the steps to build the datawarehouse ?


Gathering bussiness requiremnts
Identifying Sources
Identifying Facts
Defining Dimensions
Define Attribues
Redefine Dimensions & Attributes
Organise Attribute Hierarchy & Define Relationship
Assign Unique Identifiers
Additional convetions:Cardinality/Adding ratios









here are several types of cardinality defining relationships between occurrences of entities on two sides of the line of relationships.






The Link Cardinality is a 0:0 relationship and defined as one side does not need the other to exists. For example, in a person and parking space relationship, it denotes that I do not need to have a person to have a parking space and I don’t need a parking space to have a person either. It also denotes that a person can only occupy one parking space. This relation need to have one entity nominated to become the dominant table and use programs or triggers to limit the number of related records stored inside the other table in the relation.






The Sub-type Cardinality is a 1:0 relationship and defined as having one optional side only. An example would be a person and programmer relation. This is a 1:0 relation meaning that a person can be a programmer but a programmer must always be a person. The mandatory side of the relation, in the case the programmer side, is dominant in the relationship. Triggers and programs are again used in the controlling the database.






The Physical Segment Cardinality is 1:1 relationship and it is demonstrated that both sides of the relationship are mandatory. Example may be a person and DNA patters. This relationship show that a person must only have one set of DNA patterns while the DNA patters as dictated by nature can only be applied on one person.






The Possession Cardinality is a 0:M relation (zero to many) relationship on both sides. For example, a person may own no phone or maybe plenty of phones but a phone may have no owner but has a potential to be owned by a person. In database implementation, a nullable foreign key column in the phone table is used to reference the person in its table.






The Child Cardinality is a 1:M mandatory relationship and is one of the most common relationships used most databases. An example would be a person table and membership table relationship. This relationship denotes that a person can be a member or not but a person can also be a member of many organizations. The foreign key in the membership table has to be mandatory and not null.






The Characteristic Cardinality is a 0:M relationship which is mandatory on both sides. An example would be a person and name table relationship. This denotes that a person should have at least one name but may also many names. The database implantation for this cardinality involves a nullable foreign key in the name table to the person table.






The Paradox Cardinality is 1:M relationship which is mandatory to one side. An example would be a person table and citizenship table relationship. The Paradox is similar to the Physical Cardinality. A person must have a citizenship and citizenship must have a person. But in this case, a person may have multiple citizenships.













What is Difference between E-R Modeling and Dimensional Modeling.?


Basic diff is E-R modeling will have logical and physical model. Dimensional model will have only physical model.

E-R modeling is used for normalizing the OLTP database design.

Dimensional modeling is used for de-normalizing the ROLAP/MOLAP design.






Why fact table is in normal form?


Basically the fact table consists of the Index keys of the dimension/ook up tables and the measures.

so when ever we have the keys in a table .that itself implies that the table is in the normal form.






What are the advantages data mining over traditional approaches?


Data Mining is used for the estimation of future. For example, if we take a company/business organization, by using the concept of Data Mining, we can predict the future of business interms of Revenue (or) Employees (or) Cutomers (or) Orders etc.

Traditional approches use simple algorithms for estimating the future. But, it does not give accurate results when compared to Data Mining.






What are the vaious ETL tools in the Market?


Various ETL tools used in market are:
Informatica
Data Stage
Oracle Warehouse Bulider
Ab Initio
Data Junction






Data Warehousing Basics Interview Questions and Answers (2)






What is a CUBE in datawarehousing concept?


Cubes are logical representation of multidimensional data.The edge of the cube contains dimension members and the body of the cube contains data values.






What is data validation strategies for data mart validation after loading process ?


Data validation is to make sure that the loaded data is accurate and meets the business requirements.

Strategies are different methods followed to meet the validation requirements






what is the datatype of the surrgate key ?


Datatype of the surrgate key is either inteeger or numaric or number






What is degenerate dimension table?


Degenerate Dimensions : If a table contains the values, which r neither dimesion nor measures is called degenerate dimensions.Ex : invoice id,empno






What is Dimensional Modelling?


Dimensional Modelling is a design concept used by many data warehouse designers to build their datawarehouse. In this design model all the data is stored in two types of tables - Facts table and Dimension table. Fact table contains the facts/measurements of the business and the dimension table contains the context of measuremnets ie, the dimensions on which the facts are calculated.






What are the methodologies of Data Warehousing.?


Every company has methodology of their own. But to name a few SDLC Methodology, AIM methodology are stardadly used. Other methodologies are AMM, World class methodology and many more.






What is a linked cube?


Linked cube in which a sub-set of the data can be analyzed into great detail. The linking ensures that the data in the cubes remain consistent.






What is the main difference between Inmon and Kimball philosophies of data warehousing?


Both differed in the concept of building the datawarehosue..

According to Kimball ...

Kimball views data warehousing as a constituency of Data marts. Data marts are focused on delivering business objectives for departments in the organization. And the data warehouse is a conformed dimension of the data marts. Hence a unified view of the enterprise can be obtain from the dimension modeling on a local departmental level.

Inmon beliefs in creating a data warehouse on a subject-by-subject area basis. Hence the development of the data warehouse can start with data from the online store. Other subject areas can be added to the data warehouse as their needs arise. Point-of-sale (POS) data can be added later if management decides it is necessary.

i.e.,
Kimball--First DataMarts--Combined way ---Datawarehouse
Bottom up - (Ralph kimbol)


Inmon---First Datawarehouse--Later----Datamarts


Top down - (bill Inmon)









What is Data warehosuing Hierarchy?


Hierarchies
Hierarchies are logical structures that use ordered levels as a means of organizing data. A hierarchy can be used to define data aggregation. For example, in a time dimension, a hierarchy might aggregate data from the month level to the quarter level to the year level. A hierarchy can also be used to define a navigational drill path and to establish a family structure.

Within a hierarchy, each level is logically connected to the levels above and below it. Data values at lower levels aggregate into the data values at higher levels. A dimension can be composed of more than one hierarchy. For example, in the product dimension, there might be two hierarchies--one for product categories and one for product suppliers.

Dimension hierarchies also group levels from general to granular. Query tools use hierarchies to enable you to drill down into your data to view different levels of granularity. This is one of the key benefits of a data warehouse.

When designing hierarchies, you must consider the relationships in business structures. For example, a divisional multilevel sales organization.

Hierarchies impose a family structure on dimension values. For a particular level value, a value at the next higher level is its parent, and values at the next lower level are its children. These familial relationships enable analysts to access data quickly.

Levels
A level represents a position in a hierarchy. For example, a time dimension might have a hierarchy that represents data at the month, quarter, and year levels. Levels range from general to specific, with the root level as the highest or most general level. The levels in a dimension are organized into one or more hierarchies.

Level Relationships
Level relationships specify top-to-bottom ordering of levels from most general (the root) to most specific information. They define the parent-child relationship between the levels in a hierarchy.

Hierarchies are also essential components in enabling more complex rewrites. For example, the database can aggregate an existing sales revenue on a quarterly base to a yearly aggregation when the dimensional dependencies between quarter and year are known.






What is the main differnce between schema in RDBMS and schemas in DataWarehouse....?


RDBMS Schema
* Used for OLTP systems
* Traditional and old schema
* Normalized
* Difficult to understand and navigate
* Cannot solve extract and complex problems
* Poorly modelled


DWH Schema
* Used for OLAP systems
* New generation schema
* De Normalized
* Easy to understand and navigate
* Extract and complex problems can be easily solved
* Very good model






What is hybrid slowly changing dimension?


Hybrid SCDs are combination of both SCD 1 and SCD 2.

It may happen that in a table, some columns are important and we need to track changes for them i.e capture the historical data for them whereas in some columns even if the data changes, we don't care.

For such tables we implement Hybrid SCDs, where in some columns are Type 1 and some are Type 2.






What are the different architecture of datawarehouse?


There are two main things









1.what is incremintal loading?
2.what is batch processing?
3.what is crass reference table?
4.what is aggregate fact table?


Incremental loading means loading the ongoing changes in the OLTP.

Aggregate table contains the [measure] values ,aggregated /grouped/summed up to some level of hierarchy.






what is junk dimension? what is the difference between junk dimension and degenerated dimension?


Junk dimension: Grouping of Random flags and text Attributes in a dimension and moving them to a separate sub dimension.

Degenerate Dimension: Keeping the control information on Fact table ex: Consider a Dimension table with fields like order number and order line number and have 1:1 relationship with Fact table, In this case this dimension is removed and the order information will be directly stored in a Fact table in order eliminate unneccessary joins while retrieving order information..






What are the possible data marts in Retail sales.?


Product information,sales information






What is the definition of normalized and de normalized view and what are the differences between them?


Normalization is the process of removing redundancies.

Denormalization is the process of allowing redundancies.






What is meant by metadata in context of a Datawarehouse and how it is important?


Meta data is the data about data; Business Analyst or data modeler usually capture information about data - the source (where and how the data is originated), nature of data (char, varchar, nullable, existance, valid values etc) and behavior of data (how it is modified / derived and the life cycle ) in data dictionary a.k.a metadata. Metadata is also presented at the Datamart level, subsets, fact and dimensions, ODS etc. For a DW user, metadata provides vital information for analysis / DSS.






Differences between star and snowflake schemas?


Star schema
A single fact table with N number of Dimension

Snowflake schema
Any dimensions with extended dimensions are know as snowflake schema






Difference between Snow flake and Star Schema. What are situations where Snow flake Schema is better than Star Schema to use and when the opposite is true?


Star schema contains the dimesion tables mapped around one or more fact tables.
It is a denormalised model.
No need to use complicated joins.
Queries results fastly.
Snowflake schema
It is the normalised form of Star schema.
contains indepth joins ,bcas the tbales r splitted in to many pieces.We can easily do modification directly in the tables.
We hav to use comlicated joins ,since we hav more tables .
There will be some delay in processing the Query .






What is VLDB?


The perception of what constitutes a VLDB continues to grow. A one terabyte database would normally be considered to be a VLDB.






What's the data types present in bo?n what happens if we implement view in the designer n report


Three different data types: Dimensions,Measure and Detail.
View is nothing but an alias and it can be used to resolve the loops in the universe.






can a dimension table contains numeric values?


Yes.But those datatype will be char (only the values can numeric/char)






What is the difference between view and materialized view?


View - store the SQL statement in the database and let you use it as a table. Everytime you access the view, the SQL statement executes.

Materialized view - stores the results of the SQL in table form in the database. SQL statement only executes once and after that everytime you run the query, the stored result set is used. Pros include quick query results.


What is surrogate key ? where we use it expalin with examples


surrogate key is a substitution for the natural primary key.
It is just a unique identifier or number for each row that can be used for the primary key to the table. The only requirement for a surrogate primary key is that it is unique for each row in the table.

Data warehouses typically use a surrogate, (also known as artificial or identity key), key for the dimension tables primary keys. They can use Infa sequence generator, or Oracle sequence, or SQL Server Identity values for the surrogate key.

It is useful because the natural primary key (i.e. Customer Number in Customer table) can change and this makes updates more difficult.

Some tables have columns such as AIRPORT_NAME or CITY_NAME which are stated as the primary keys (according to the business users) but ,not only can these change, indexing on a numerical value is probably better and you could consider creating a surrogate key called, say, AIRPORT_ID. This would be internal to the system and as far as the client is concerned you may display only the AIRPORT_NAME.

2. Adapted from response by Vincent on Thursday, March 13, 2003
Another benefit you can get from surrogate keys (SID) is :
Tracking the SCD - Slowly Changing Dimension.

Let me give you a simple, classical example:
On the 1st of January 2002, Employee 'E1' belongs to Business Unit 'BU1' (that's what would be in your Employee Dimension). This employee has a turnover allocated to him on the Business Unit 'BU1' But on the 2nd of June the Employee 'E1' is muted from Business Unit 'BU1' to Business Unit 'BU2.' All the new turnover have to belong to the new Business Unit 'BU2' but the old one should Belong to the Business Unit 'BU1.'

If you used the natural business key 'E1' for your employee within your datawarehouse everything would be allocated to Business Unit 'BU2' even what actualy belongs to 'BU1.'

If you use surrogate keys, you could create on the 2nd of June a new record for the Employee 'E1' in your Employee Dimension with a new surrogate key.

This way, in your fact table, you have your old data (before 2nd of June) with the SID of the Employee 'E1' + 'BU1.' All new data (after 2nd of June) would take the SID of the employee 'E1' + 'BU2.'

You could consider Slowly Changing Dimension as an enlargement of your natural key: natural key of the Employee was Employee Code 'E1' but for you it becomes

Employee Code + Business Unit - 'E1' + 'BU1' or 'E1' + 'BU2.' But the difference with the natural key enlargement process, is that you might not have all part of your new key within your fact table, so you might not be able to do the join on the new enlarge key -> so you need another id.






What is aggregate table and aggregate fact table ... any examples of both?


Aggregate table contains summarised data. The materialized view are aggregated tables.

for ex in sales we have only date transaction. if we want to create a report like sales by product per year. in such cases we aggregate the date vales into week_agg, month_agg, quarter_agg, year_agg. to retrive date from this tables we use @aggrtegate function.






What is active data warehousing?


An active data warehouse provides information that enables decision-makers within an organization to manage customer relationships nimbly, efficiently and proactively. Active data warehousing is all about integrating advanced decision support with day-to-day-even minute-to-minute-decision making in a way that increases quality of those customer touches which encourages customer loyalty and thus secure an organization's bottom line. The marketplace is coming of age as we progress from first-generation "passive" decision-support systems to current- and next-generation "active" data warehouse implementations










What is the difference between Datawarehousing and BusinessIntelligence?


Data warehousing deals with all aspects of managing the development, implementation and operation of a data warehouse or data mart including meta data management, data acquisition, data cleansing, data transformation, storage management, data distribution, data archiving, operational reporting, analytical reporting, security management, backup/recovery planning, etc. Business intelligence, on the other hand, is a set of software tools that enable an organization to analyze measurable aspects of their business such as sales performance, profitability, operational efficiency, effectiveness of marketing campaigns, market penetration among certain customer groups, cost trends, anomalies and exceptions, etc. Typically, the term “business intelligence” is used to encompass OLAP, data visualization, data mining and query/reporting tools.Think of the data warehouse as the back office and business intelligence as the entire business including the back office. The business needs the back office on which to function, but the back office without a business to support, makes no sense.






What is the difference between OLAP and datawarehosue?


Datawarehouse is the place where the data is stored for analyzing
where as OLAP is the process of analyzing the data,managing aggregations,
partitioning information into cubes for indepth visualization.






What is fact less fact table? where you have used it in your project?


Factless table means only the key available in the Fact there is no mesures availalabl






Why Denormalization is promoted in Universe Designing?


In a relational data model, for normalization purposes, some lookup tables are not merged as a single table. In a dimensional data modeling(star schema), these tables would be merged as a single table called DIMENSION table for performance and slicing data.Due to this merging of tables into one large Dimension table, it comes out of complex intermediate joins. Dimension tables are directly joined to Fact tables.Though, redundancy of data occurs in DIMENSION table, size of DIMENSION table is 15% only when compared to FACT table. So only Denormalization is promoted in Universe Desinging.






What is the difference between ODS and OLTP?


ODS:- It is nothing but a collection of tables created in the Datawarehouse that maintains only current data

where as OLTP maintains the data only for transactions, these are designed for recording daily operations and transactions of a business






What is the difference between datawarehouse and BI?


Simply speaking, BI is the capability of analyzing the data of a datawarehouse in advantage of that business. A BI tool analyzes the data of a datawarehouse and to come into some business decision depending on the result of the analysis.






Is OLAP databases are called decision support system ??? true/false?


True






explain in detail about type 1, type 2(SCD), type 3 ?


Type-1
Most Recent Value
Type-2(full History)
i) Version Number
ii) Flag
iii) Date
Type-3
Current and one Previous value






What is snapshot?


You can disconnect the report from the catalog to which it is attached by saving the report with a snapshot of the data. However, you must reconnect to the catalog if you want to refresh the data.






What is the difference between datawarehouse and BI?


Simply speaking, BI is the capability of analyzing the data of a datawarehouse in advantage of that business. A BI tool analyzes the data of a datawarehouse and to come into some business decision depending on the result of the analysis.






What are non-additive facts in detail?


A fact may be measure, metric or a dollar value. Measure and metric are non additive facts.

Dollar value is additive fact. If we want to find out the amount for a particular place for a particular period of time, we can add the dollar amounts and come up with the total amount.

A non additive fact, for eg measure height(s) for 'citizens by geographical location' , when we rollup 'city' data to 'state' level data we should not add heights of the citizens rather we may want to use it to derive 'count'


Q. When starting a new DW Project what are the two paths you can take in the design of the DW?
A. The DW can be an Enterprise DW modeled using an E/R model upon which Datamarts are built. Or the DW can start out as a DataMart with a Shared Dimensional Model for follow on datamarts.

Q. What is your view on Data Profiling and Master Data Management?
A. ~

Q. I need to give access to DMVs on a QA Server Instance without giving SA rights to the server. What needs to be done?
A. You need to give Access to View Server State - Grant View Server State to or go to Server Properties and in the Permissions Dialog, navigate to the appropriate login and check the appropriate checkbox.

Q. In terms of Real Time Data Warehouse Designs, which is better - a design using MOLAP technology or a design using HOLAP technology and why?
A. ~

Q. Which type of indexing strategy is more appropriate for Data Warehouse Designs: Bitmap Indexing or B-Tree Indexing?
A. Use Bitmap Indexes when working with Oracle Databases and B-Tree Indexes when working with SQL Server Databases. SQL Server uses Bitmap Filters, but only in parallel processing.

Q. What is a Star Schema?
A. Typically, it is a Fact Table that is tied to many Dimension Tables by a PK-FK relationship using a Surrogate Key. The diagram will resemble a Star in many cases.

Q. What is a Surrogate Key, what is the purpose of the Surrogate Key, and what value does it add to the DW Design?
A. A Surrogate Key is an integer or big integer that field that makes the dimension rows unique. It is the Primary Key in the Dimension Tables and the Foreign Key in the Fact Table. It is used to tie the Fact Table to the Dimension Tables. Other than a possible smart Surrogate Key for the Date Dimension, it has no value to the DW Design.


There are two (correct) ways to build a datawarehouse:
The first is a corporate datawarehouse with a traditional E/R model as the enterprise datawarehouse that no one can (should) access and is only used to build datamarts which have star schema models (highly denormalized, with facts and dimensions).
The other way is a collection of datamarts that share a common "databus" with conformed dimensions. This one is more appropriate when the datawarehouse project is sponsored by a particular departement within the company instead of a strong corporate initiative. If done well, it can scale to enterprise levels by adding other datamart at the end of the "databus". Not to be confused with a bunch of first generation heterogeneous datamarts, which usually need to be replaced after a short while. (That's what I do, replace them)

A data warehouse MUST be built in one of those two ways following very strict design rules otherwise it WILL fail. Data dumps made by smart people who try to re-invent the wheel are NOT data warehouses (At best some people end up with bad Operational Data Stores)

The biggest effort in building a datawarehouse is spent on the ETL process. ETL means Extract, Tranform, Load, which also includes data cleansing and conforming.

Common pitfalls in a datawarehouse are
-scope creep
-two-year plans with no tangible milestones (Only to realise that "Oh sh*t did we ever go wrong there")
-weak project sponsorship plegued by internal politics (The best is a corporate commitment)









ollowing my article last week , which generated interesting responses, I thought I’d write similar thing on the data warehousing. Like before, I will be using LMH to show the complexity (Low, Medium, High). This time, I’ll add the “Purpose”, i.e. what the question is designed for.


Data warehousing skill never stands on its own. You don’t interview people just for their data warehousing knowledge. Usually you are either looking for an ETL developer (Informatica, DataStage, BODI, SSIS), a Microsoft BI developer (RS/AS/IS), a BO/Cognos (Report) Developer, a Data Warehouse Architect, a BI Solution Architect, an ETL Architect, a Data Architect or an Enterprise Architect. All these roles require data warehouse knowledge. So most likely you will be combining these questions with other questions, e.g. for an Informatica developer you will be combining them with Informatica questions.


When I am filling a role, what I am looking particularly is whether the candidate can do the job or not. Nowadays it is different from 10 years ago. We now have Google and Bing. Anything we don’t know we can quickly Google it. So the amount of knowledge is not important now. What is important (to me) is a) experience, b) problem solving and c) character. I remember about 3 years ago I was interviewing a candidate for a data architect role. The candidate was speaker in data architecture conference so we were convinced she must be very good. I asked her what the rule for the 2nd normal form was. She could not answer it. I asked the 1st and 3rd normal form and she could not answer them either. This is like bread and butter. You can’t do data modelling without knowing the normalisation rules.


But then in an interview with a bank 3 years ago I was also “blank” like her. I was an author of a data warehousing & BI book on SQL Server so they were thinking high of me with regards to SSIS, SSAS and SSRS. They asked me what those 5 tabs in SSIS BIDS. I could mention 4 but could not remember the 5th one, even though I was using SSIS almost everyday. Since then when I interviewed I did not look for the amount of knowledge, but whether the candidate can solve a problem instead. I remember one day my manager and I was interviewing for a Teradata developer role. I said to the candidate that the amount of Teradata knowledge that he had was not important. Within 5 minutes of opening the manual or Googling he will be able to get that knowledge. So I said I would not ask him any Teradata SQL or BTEQ functions. Instead I gave him 2 real world problems that we were facing in the project and asked him to give me the solutions in about 5 minutes. They way he interrogated us with question to get information about the project and finally suggested a brilliant solution really impressed us, so we offered him the job. I can completely understand that some people disagree with my approach. After that interview my boss pulled me and told me off: “You must not say that in front of the candidates Vincent. Of course the amount of Teradata knowledge they possess is important! Why do you think we hire them for?”


So in the interview questions below, which crystallise from my experience, I put both knowledge based and experience/problem solving questions. Generally I’m less interested in “theory only” questions, so I try to wrap them up in real world problem situation.


I’m a firm believer that experience is the best teacher. So at interview I always try to find out if the candidate has done it before. So I test them using every day problems. People who have done data warehousing will surely come across those problems, and understand what the solutions are. People who are new in data warehousing, or only know the DW theory from books wouldn’t have encountered those problems and would not have a clue what the answers are.


1. Question: How do you implement Slowly Changing Dimension type 2? I am not looking for the definition, but the practical implementation e.g. table structure, ETL/loading. {L}


Answer: Create the dimension table as normal, i.e. first the dim key column as an integer, then the attributes as varchar (or varchar2 if you use Oracle). Then I’d create 3 additional columns: IsCurrent flag, “Valid From” and “Valid To” (they are datetime columns). With regards to the ETL, I’d check first if the row already exists by comparing natural key. If it exists then “expire the row” and insert a new row. Set the “Valid From” date to today’s date or the current date time.


An experienced candidate (particularly DW ETL developer) will not set the “Valid From” date to the current date time, but to the time when the ETL started. This is so that all the rows in the same load will have the same Valid From, which is 1 millisecond after the expiry time of the previous version thus avoiding issue with ETL workflows that run across midnight.


Purpose: SCD 2 is the one of the first things that we learn in data warehousing. It is considered the basic/fundamental. The purpose of this question is to separate the quality candidate from the ones who’s bluffing. If the candidate can not answer this question you should worry.


2. Question: How do you index a fact table? And explain why. {H}


Answer: Index all the dim key columns, individually, non clustered (SQL Server) or bitmap (Oracle). The dim key columns are used to join to the dimension tables, so if they are indexed the join will be faster. An exceptional candidate will suggest 3 additional things: a) index the fact key separately, b) consider creating a covering index in the right order on the combination of dim keys, and c) if the fact table is partitioned the partitioning key must be included in all indexes.


Purpose: Many people know data warehousing only in theory. This question is designed to separate those who have actually built a warehouse and those who haven’t.


3. Question: In the source system, your customer record changes like this: customer1 and customer2 now becomes one company called customer99. Explain a) impact to the customer dim (SCD1), b) impact to the fact tables. {M}


Answer: In the customer dim we update the customer1 row, changing it to customer99 (remember that it is SCD1). We do soft delete on the customer2 row by updating the IsActive flag column (hard delete is not recommended). On the fact table we find the Surrogate Key for customer1 and 2 and update it with customer99’s SK.


Purpose: This is a common problem that everybody in data warehousing encounters. By asking this question we will know if the candidate has enough experience in data warehousing. If they have not come across this (probably they are new in DW), we want to know if they have the capability to deal with it or not.


4. Question: What are the differences between Kimball approach and Inmon’s? Which one is better and why? {L}


Answer: if you are looking for a junior role e.g. developer, then the expected answer is: in Kimball we do dimension modelling, i.e. fact and dim tables where as in Inmon we do CIF, i.e. EDW in normalised form and we then create a DM/DDS from the EDW. Junior candidates usually prefer Kimball, because of query performance and flexibility, or because that’s the only one they know; which is fine. But if you are interviewing for a senior role e.g. senior data architect then they need to say that the approach depends on the situation. Both Kimball & Inmon’s approaches have advantages and disadvantages. Some of the main reasons of having a normalised DW can be found


Purpose: a) to see if the candidate understands the core principles of data warehousing or they just “know the skin”, b) to find out if the candidate is open minded, i.e. the solution depends on what we are trying to achieve (there’s right or wrong answer) or if they are blindly using Kimball for every situation.


5. Question: Suppose a fact row has unknown dim keys, do you load that row or not? Can you explain the advantage/disadvantages? {M}


Answer: We need to load that row so that the total of the measure/fact is correct. To enable us to load the row, we need to either set the unknown dim key to 0 or the dim key of the newly created dim rows. We can also not load that row (so the total of the measure will be different from the source system) if the business requirement prefer it. In this case we load the fact row to a quarantine area complete with error processing, DQ indicator and audit log. On the next day, after we receive the dim row, we load the fact row. This is commonly known as Late Arriving Dimension Rows and there are many sources for further information; one of the best is Bob Becker’s articlein 2006. Others refer to this as Early Arriving Fact Row, which Ralph Kimball explained in 2004.


Purpose: again this is a common problem that we encounter in regular basis in data warehousing. With this question we want to see if the candidate’s experience level is up to the expectation or not.


6. Question: Please tell me your experience on your last 3 data warehouse projects. What were your roles in those projects? What were the issues and how did you solve them? {L}


Answer: There’s no wrong or right answer here. With this question you are looking for a) whether they have done similar things to your current project, b) whether their have done the same role as the role you are offering, c) whether they faces the same issues as your current DW project.


Purpose: Some of the reasons why we pay more to certain candidates compared to the others are: a) they have done it before they can deliver quicker than those who haven’t, b) they come from our competitors so we would know what’s happening there and we can make a better system than theirs, c) they have solved similar issues so we could “borrow their techniques”.


7. Question: What are the advantages of having a normalised DW compared to dimensional DW? What are the advantages of dimensional DW compared to normalised DW? {M}


Answer: For advantages of having a normalised DW . The advantages of dimensional DW are: a) flexibility, e.g. we can accommodate changes in the requirements with minimal changes on the data model, b) performance, e.g. you can query it faster than normalised model, c) it’s quicker and simpler to develop than normalised DW and easier to maintain.


Purpose: to see if the candidate has seen “the other side of the coin”. Many people in data warehousing only knows Kimball/dimensional. Second purpose of this question is to check if the candidate understands the benefit of dimensional modelling, which is a fundamental understanding in data warehousing.


8. Question: What is 3rd normal form? {L} Give me an example of a situation where the tables are not in 3rd NF, then make it 3rd NF. {M}


Answer: No column is transitively depended on the PK. For example, column1 is dependant on column2 and column2 is dependant on column3. In this case column3 is “transitively dependant” on column1. To make it 3rd NF we need to split it into 2 tables: table1 which has column1 & column2 and table2 which has column2 and column3.


Purpose: A lot of people talk about “3rd normal form” but they don’t know what it means. This is to test if the candidate is one of those people. If they can’t answer 3rd NF, ask 2nd NF. If they can’t answer 2nd NF, ask 1st NF.


9. Question: Tell me how to design a data warehouse, i.e. what are the steps of doing dimensional modelling? {M}


Answer: There are many ways, but it should not be too far from this order: 1. Understand the business process, 2. Declare the grain of the fact table, 3. Create the dimension tables including attributes, 4. Add the measures to the fact tables (from Kimball’s Toolkit book chapter 2). Step 3 and 4 could be reversed (add the fact first, then create the dims), but step 1 & 2 must be done in that order. Understanding the business process must always be the first, and declaring the grain must always be the second.


Purpose: This question is for data architect or data warehouse architect to see if they can do their job. It’s not a question for an ETL, report or cube developer.


10. Question: How do you join 2 fact tables? {H}


Answer: It’s a trap question. You don’t usually join 2 fact tables especially if they have different grain. When designing a dimensional model, you include all the necessary measures into the same fact table. If the measure you need is located on another fact table, then there’s something wrong with the design. You need to add that measure to the fact table you are working with. But what if the measure has a different grain? Then you add the lower grain measure to the higher grain fact table. What if the fact table you are working with has a lower grain? Then you need to get the business logic for allocating the measure.


But it is possible to join 2 fact tables, using the common dim keys, but the performance is usually horrible. For example: if FactTable1 has dim1key, dim2key, dimkey3 and FactTable2 has dim1key and dim2key then join them like this:



1

select f2.dim1key, f2.dim2key, f1.measure1, f2.measure2


2

from














3

( select dim1key, dim2key, sum(measure1) as measure1


4

from FactTable1














5

group by dim1key, dim2key


6

) f1














7

join FactTable2 f2








8

on f1.dim1key = f2.dim1key and f1.dim2key = f2.dim2key



So if we don’t join 2 fact tables that way, how do we do it? The answer is fact key column. It is a good practice (especially in SQL Server because of the concept of cluster index) to have fact key column to enable us to identify rows on the fact table . The performance would be better (than joining on dim keys), but you need to plan this in advance as you need to include the fact key column on the other fact table.



1

select f2.dim1key, f2.dim2key, f1.measure1, f2.measure2


2

from FactTable1 f1














3

join FactTable2 f2 on f2.fact1key = f1.factkey



I implemented this technique originally for self joining, but then expand the use to join to other fact table. But this must be use on an exception basis rather than the norm.


Purpose: not to trap the candidate of course. But to see if they have the experience dealing with a problem which doesn’t happen every day.


11. Question: How do you index a dimension table? {L}


Answer: clustered index on the dim key, and non clustered index (individual) on attribute columns which are used on the query where clause.


Purpose: this question is critical to be asked if you are looking for a Data Warehouse Architect (DWA) or a Data Architect (DA). Many DWA and DA only knows logical data model. Many of them don’t know how to index. They don’t know how different the physical tables are in Oracle compared to in Teradata. This question is not essential if you are looking for a report or ETL developer. It’s good for them to know, but it’s not essential.


12. Question: Tell me what you know about William Inmon? {L} Alternatively: Ralph Kimball.


Answer: He was the one who introduced the concept of data warehousing. Arguably Barry Devlin was the first one, but he’s not as popular as Inmon. If you ask who is Barry Devlin or who is Claudia Imhoff 99.9% of the candidates wouldn’t know. But every decent practitioner in data warehousing should know about Inmon and Kimball.


Purpose: to test of the candidate is a decent practitioner in data warehousing or not. You’ll be surprise (especially if you are interviewing a report developer) how many candidates don’t know the answer. If someone is applying for a BI architect role and he never heard about Inmon you should worry.


13. Question: What is the difference between a data mart and a data warehouse? {L}


Answer: Most candidates will answer that one is big and the other is small. Some good candidates (particularly Kimball practitioners) will say that data mart is one star. Whereas DW is a collection of all stars. An excellent candidate will say all the above answers, plus they will say that a DW could be the normalised model that store EDW, whereas DM is the dimensional model containing 1-4 stars for specific department (both relational DB and multidimensional DB).


Purpose: The question has 3 different levels of answer, so we can see how deep the candidate’s knowledge in data warehousing.


14. Question: What the purpose of having a multidimensional database?


Answer: Many candidates don’t know what a multidimensional database (MDB) is. They have heard about OLAP, but not MDB. So if the candidate looks puzzled, help them by saying “an MDB is an OLAP database”. Many will say “Oh… I see” but actually they are still puzzled so it will take a good few moments before they are back to earth again. So ask again: “What is the purpose of having an OLAP database?” The answer is performance and easier data exploration. An MDB (aka cube) is a hundred times faster than relational DB for returning an aggregate. An MDB will be very easy to navigate, drilling up and down the hierarchies and across attributes, exploring the data.


Purpose: This question is irrelevant to report or ETL developer, but a must for a cube developer and DWA/DA. Every decent cube developer (SSAS, Hyperion, Cognos) should be able to answer the question as it’s their bread and butter.


What is a Design Pattern?
Design Pattern is a re-usable, high quality solution to a given requirement, task or recurring problem. Further, it does not comprise of a complete solution that may be instantly converted to a code component, rather it provides a framework for how to solve a problem.

In 1994, the release of the book Design Patterns, Elements of Reusable Object Oriented Software made design patterns popular.

Because design patterns consist of proven reusable architectural concepts, they are reliable and they speed up software development process.

Design Patterns are in a continious phase of evolution, which means that they keep on getting better & better as they are tested against time, reliability and subjected to continious improvements. Further, design patterns have evolved towards targeting specific domains. For example, windows-based banking applications are usually based on singleton patterns, e-commerce web applications are based on the MVC (Model-View-Controller) pattern.
Design Patterns are categorized into 3 types:
Creational Patterns
Structural Patterns
Behavioral Patterns
What are Creational Design Patterns?
The Creational Design Patterns focus on how objects are created and utilized in an application. They tackle the aspects of when and how objects are created, keeping in mind whats the best way these objects should be created.

Listed below are some of the commonly known Creational Design Patterns:
>>> Abstract Factory Pattern
>>> Factory Pattern
>>> Builder Pattern
>>> Lazy Pattern
>>> Prototype Pattern
>>> Singleton Pattern
Whats the difference between Abstract Factory Pattern and Factory Pattern?
In an abstract factory design, a framework is provided for creating sub-components that inherit from a common component. In .NET, this is achieved by creating classes that implement a common interface or a set of interfaces, where the interface comprises of the generic method declarations that are passed on to the sub-components. TNote that not just interfaces, but even abstract classes can provide the platform of creating an application based on the abstract factory pattern.
Example, say a class called CentralGovernmentRules is the abstract factory class, comprised of methods like ShouldHavePolice() and ShouldHaveCourts(). There may be several sub-classes like State1Rules, State2Rules etc. created that inheriting the class CentralGovernmentRules, and thus deriving its methods as well.

Note that the term "Factory" refers to the location in the code where the code is created.
A Factory Pattern is again an Object creation pattern. Here objects are created without knowing the class of the object. Sounds strange? Well, actually this means that the object is created by a method of the class, and not by the class's constructor. So basically the Factory Pattern is used wherever sub classes are given the priviledge of instantiating a method that can create an object.
Describe the Builder Design Pattern
In a builder design pattern, an object creation process is separated from the object design construct. This is useful becuase the same method that deals with construction of the object, can be used to construct different design constructs.
What is the Lazy Design Pattern?
The approach of the Lazy Design Pattern is not to create objects until a specific requirement matches, and when it matches, object creation is triggered. A simple example of this pattern is a Job Portal application. Say you register yourself in that site thus filling up the registration table, only when the registration table is filled, the other objects are created and invoked, that prompt you to fill in other details too, which will be saved in other tables.
What is the Prototype Design Pattern?
A prototype design pattern relies on creation of clones rather than objects. Here, we avoid using the keyword 'new' to prevent overheads.
What is the Singleton Design Pattern?
The Singleton design pattern is based on the concept of restricting the instantiation of a class to one object. Say one object needs to perform the role of a coordinator between various instances of the application that depend on a common object, we may design an application using a Singleton. Usage of Singleton patterns is common in Banking, Financial and Travel based applications where the singleton object consists of the network related information.

A singleton class may be used to instantiate an object of it, only if that object does not already exist. In case the object exists, a reference to the existing object is given. A singleton object has one global point of access to it.

An ASP.NET Web Farm is also based on the Singleton pattern. In a Web Farm, the web application resides on several web servers. The session state is handled by a Singleton object in the form of the aspnet_state.exe, that interacts with the ASP.NET worker process running on each web server. Note that the worker process is the aspnet_wp.exe process. Imagine one of the web servers shutting down, the singleton object aspnet_state.exe still maintains the session state information across all web servers in the web farm.

In .NET, in order to create a singleton, a class is created with a private constructor, and a "static readonly" variable as the member that behaves as the instance.
What are Structural Design Patterns?
A structural design pattern establishes a relationship between entities. Thus making it easier for different components of an application to interact with each other. Following are some of the commonly known structural patterns: >>> Adapter Pattern - Interfaces of classes vary depending on the requirement.
>>> Bridge Pattern - Class level abstraction is separated from its implementation.
>>> Composite Pattern - Individual objects & a group of objects are treated similarly in this approach.
>>> Decorator Pattern - Functionality is assigned to an object.
>>> Facade Pattern - A common interface is created for a group of interfaces sharing a similarity.
>>> Flyweight Pattern - The concept of sharing a group of small sized objects.
>>> Proxy Pattern - When an object is complex and needs to be shared, its copies are made. These copies are called the proxy objects.
What are the different types of Proxy Patterns?
1 - Remote Proxy - A reference is given to a different object in a different memory location. This may be on a different or a same machine.
2 - Virtual Proxy - This kind of object is created only & only when really required because of its memory usage.
3 - Cache Proxy - An object that behaves as a temporary storage so that multiple applications may use it. For example, in ASP.NET when a page or a user control contains the OutputCache directive, that page/control is cached for some time on the ASP.NET web server.
What is a behavioral design pattern?
Behaviorial design patterns focus on improving the communication between different objects. Following are different types of behavioral patterns:
>>> Chain Or Responsibilities Pattern - In this pattern, objects communicate with each other depending on logical decisions made by a class.
>>> Command Pattern - In this pattern, objects encapsulate methods and the parameters passed to them.
>>> Observer Pattern - Objects are created depending on an events results, for which there are event handlers created.
What is the MVC Pattern (Model View Controller Pattern)?
The MVC Pattern (Model View Controller Pattern) is based on the concept of designing an application by dividing its functionalities into 3 layers. Its like a triad of components. The Model component contains the business logic, or the other set of re-usable classes like classes pertaining to data access, custom control classes, application configuration classes etc. The Controller component interacts with the Model whenever required. The control contains events and methods inside it, which are raised from the UI which is the View component.

Consider an ASP.NET web application. Here, all aspx, ascx, master pages represent the View.

The code behind files (like aspx.cs, master.cs, ascx.cs) represent the Controller.

The classes contained in the App_Code folder, or rather any other class project being referenced from this application represent the Model component.

Advantages: * Business logic can be easily modified, without affecting or any need to make changes in the UI.
* Any cosmetic change in the UI does not affect any other component.
What is the Gang of Four Design Pattern?
The history of all design patterns used in modern day applications derive from the Gang of Four (GoF) Pattern. Gang of Four patterns are categorized into 3 types:1 - Creational
2 - Structural
3 - Behavioral

The term "Gang of Four" (or "GoF" in acronym) is used to refer to the four authors of the book
Design Patterns: Elements of Reusable Object-Oriented Software. The authors are Erich Gamma, Ralph Johnson, Richard Helm and John Vlissides.
When should design patterns be used?
While developing software applications, sound knowledge of industry proven design patterns make the development journey easy and successful. Whenever a requirement is recurring, a suitable design pattern should be identified. Usage of optimal design patterns enhance performance of the application. Though there are some caveats. Make sure that there are no overheads imposed on a simple requirement, which means that design patterns should not be unnecessarily be used.
How many design patterns can be created in .NET?
As many as one can think. Design patterns are not technology specific, rather their foundation relies on the concept of reusability, object creation and communication. Design patterns can be created in any language.
Describe the Ajax Design Pattern.
In an Ajax Design Pattern, partial postbacks are triggered asyncronously to a web server for getting live data. A web application would not flicker here, and the web site user would not even come to know that a request is being sent to the web server for live data.

Such a design pattern is used in applications like Stock Market Websites to get live quotes, News Websites for live news, Sports websites for live scores etc. 


What is SQL Server 2005 Analysis Services (SSAS)?

SSAS gives the business data an integrated view. This integrated view is provided by combining online analytical processing (OLAP) and data mining functionality. SSAS supports OLAP and allows data collected from various sources to be managed in an efficient way. Analysis services, specifically for data mining, allow use of a wide array of data mining algorithms that allows creation, designing of data mining models.

What is SQL Server 2005 Analysis Services (SSAS)? - Jan 10, 2010 at 18:50 PM by Vidya Sagar

What is SQL Server 2005 Analysis Services (SSAS)?

SQL Server 2005 Analysis Services is a service that provides to view business data by unifying and integrating. It is the foundation for traditional reporting, OLAP analysis, data mining and Key Performance Indicator scorecards.

What are the new features with SQL Server 2005 Analysis Services (SSAS)?


It offers interoperability with Microsoft office 2007.


It eases data mining by offering better data mining algorithms and enables better predictive analysis.


Provides a faster query time and data refresh rates.


Improved tools – the business intelligence development studio integrated into visual studio allows to add data mining into the development tool box.


New wizards and designers for all major objects.


Provides a new Data Source View (DSV) object, which provides an abstraction layer on top of the data source specifying which tables from the source are available.


Unified Dimensional Model: - This model defines the entities used in the business, the business logic used to implement, metrics and calculations. This model is used by different analytical applications, spreadsheets etc for verification of the reports data.

Data Source View: - The UML using the data source view is mapped to a wide array of back end data sources. This provides a comprehensive picture of the business irrespective of the location of data. With the new Data source view designer, a very user friendly interface is provided for navigation and exploring the data.

New aggregation functions: - Previous analysis services aggregate functions like SUM, COUNT, DISTINCT etc were not suitable for all business needs. For instance, financial organizations cannot use aggregate functions like SUM or COUNT or finding out the first and the last record. New aggregate functions like FIRST CHILD, LAST CHILD, FIRST NON-EMPTY and LAST NON-EMPTY functions are introduced.

Querying tools : - An enhanced query and browsing tool allows drag and drop dimensions and measures to the viewing pane of a cube. MDX queries and data mining extensions (DMX) can also be written. The new tool is easier and automatically alerts for any syntax errors.

SQL Server 2005 Analysis Services (SSAS) - new features with SQL Server 2005 Analysis Services (SSAS) - Jan 10, 2010 at 18:50 PM by Vidya Sagar

What are the new features with SQL Server 2005 Analysis Services (SSAS)?


Interoperability with 2007 office system: This feature enables optimized interoperability, delivery of powerful and affordable business insight for information user.


MS SQL Server Data Mining Add-ins for MS Office 2007: With this release, data mining became easier. It has add-ins for data mining which enables enterprises for making predictive analysis which are accessible to wider audience base by empowering harness the sophisticated data mining algorithms to end users.


MS SQL Server 2005 Analysis Services Performance Guide: This feature provides instant access to accurate information. It enables the end users to obtain answers to complex questions, surprisingly, at the speed of thought for OLAP.


The following are the new tools of SQL Server 2005 Analysis Services:

Unified Dimensional Model: The entities, business logic used for implementation, metrics and calculations are defined in this model. This model is utilized for different analytical applications, spreadsheets for reports data verification.

Data Source View: The data source view us used by UML for mapping to a wide array of back end data sources. This model provides the picture of the business irrespective of the data location.

New aggregate functions: The functions like FIRST CHILD, LAST CHIL, FIRST NON-EMPYT AND NON-EMPTY functions are available. These functions can be used in addition to basic aggregate functions, as these are not suitable for every business need.

Querying tools: Dimensions and measures can be dragged and dropped to viewing pane of a cube using query and browsing tool. Writing data mining extensions, MDX queries are the additional features. Syntax errors are automatically alerts with this tool

What are SQL Server Analysis Services cubes?

SQL SAS cube is a data structure for fast analysis of data. Using cubes to arrange data is a solution to overcome the limitations in relational databases. RDBMSs are not suited well for display of large amounts of data and instantaneous analysis. Cubes can be thought as an extension for spreadsheet. A company’s financial data by product, time-period, city etc., are additional dimensions for business data. SQL SAS cubes are used to represent this type of data.

Explain the purpose of synchronization feature provided in Analysis Services 2005.

Synchronization feature is used to copy database from one source server to a destination server. While the synchronization is in progress, users can still browse the cubes. Once the synchronization is complete, the user is redirected to the new Synchronized database

Can you explain how to deploy an SSIS package?

A SSIS package can be deployed using the Deploy SSIS Packages page. The package and its dependencies can be either deployed in a specified folder in the file system or in an instance of SQL server. The package needs to be indicated for any validation after installation. The next page of the wizard needs to be displayed. Skip to the Finish the Package Installation Wizard page.

SQL Server 2005 Analysis Services (SSAS) - how to deploy an SSIS package - Jan 10, 2010 at 18:50 PM by Vidya Sagar

Can you explain how to deploy an SSIS package?

1. Create a batch command file

@echo off
set DESTSERVER=yourserver
set DESTLOCATION=path
set SOURCE=%1
set DESTNAME=%~2
dtutil /FILE %SOURCE% /DestServer %DESTSERVER% /COPY
SQL;%DESTLOCATION%\%DESTNAME

2. Add a menu to the External Tools menu

Click on “Tools”, followed by “External Tools…” . Then click “Add”. This will add a menu option under the menu “Tools”.
Type Deploy to MSDB in Title text box.
Type the full path to the batch command file created in step 1 in the Command text box.
Type $(ItemPath) $(ItemFileName in Arguments text box.

Add a space between the two arguments. A menu bar displays under Tools menu.

3. Add an custom icon for the deploy menu item in the toolbar

Right click the toolbar and click ‘Customise…’
In the ‘command’ tab, select the left list box ‘Tools’
Select ‘External Command 1’ and drag it to the toolbar
Click on ‘Rearrange Commands…’, and check the option ‘Toolbar’ and select the toolbar into which the menu item to be dropped. It is suggested to select ‘Standard tool bar’.
Select ‘External Command 1’ in the list box and click on ‘Modify Selection’. And click on ‘Edit Button editor’.

By clicking on newly added button icon, it will deploy the active SSIS package to server.

What is the new error handling technique in SQL Server 2005?


SQL Server 2005 introduces a new exception handling paradigm using TRY / CATCH blocks in T-SQL. The errors are well trapped in the try block and the execution is transferred to the catch block. The circumstances like using cursors… the cursor is allocated for opening in the TRY block at the time of occurring an error is thrown to CATCH block to determine whether it is open. If it is open, it should be close and deallocate.

SQL Server 2005 still supports the @@ERROR function, but TRY / CATCH is a much better option.

What exactly is SQL Server 2005 Service Broker?

Servive brokers allow build applications in which independent components work together to accomplish a task. They help build scalable, secure database applications. The brokers provide a message based communication and can be used for applications within a single database. It helps reducing development time by providing an enriched environment.

Explain the Service Broker components.


The following are the components of Service Broker:

Message: The basic unit of information that is transferred in a Service Broker application. These are received by Service Broker, the way they are sent and are guaranteed only for sending and receiving one at a time.

Conversation: After sending a message to Service Broker, a conversation, which is created for a particular task and deleted soon after the ask has been completed. It is the primary information exchange construct in Service Broker.

Queue: All messages are stored in a queue till the processing is completed. It is a special type of table which can be viewed only with SELECT statement on the name of the queue and INSERT, DELETE, UPDATE statements can not be issued. These messages on a queue will certainly persists even a server restarts, thus guarantees the non-loss of the message.

Service: Reading a messages and processing them is the action of service component. It can be a stored procedure or an ad hoc, or a different program which connects to the database server. Every services need to be associated with a queue.

Conversation Groups: The message processing is serializes and correlates the messages by using conversation groups. Each conversation is a member in a conversation group. The core concept is, some of the messages are related to other messages, and the conversation group puts them together in an ordered fashion.

 

What is SQL Server Integration Services (SSIS)?
  • SQL Server Integration Services (SSIS) is component of SQL Server 2005 and later versions. SSIS is an enterprise scale ETL (Extraction, Transformation and Load) tool which allows you to develop data integration and workflow solutions. Apart from data integration, SSIS can be used to define workflows to automate updating multi-dimensional cubes and automating maintenance tasks for SQL Server databases.
How does SSIS differ from DTS?
  • SSIS is a successor to DTS (Data Transformation Services) and has been completely re-written from scratch to overcome the limitations of DTS which was available in SQL Server 2000 and earlier versions. A significant improvement is the segregation of the control/work flow from the data flow and the ability to use a buffer/memory oriented architecture for data flows and transformations which improve performance.
What is the Control Flow?
  • When you start working with SSIS, you first create a package which is nothing but a collection of tasks or package components.  The control flow allows you to order the workflow, so you can ensure tasks/components get executed in the appropriate order.
What is the Data Flow Engine?
  • The Data Flow Engine, also called the SSIS pipeline engine, is responsible for managing the flow of data from the source to the destination and performing transformations (lookups, data cleansing etc.).  Data flow uses memory oriented architecture, called buffers, during the data flow and transformations which allows it to execute extremely fast. This means the SSIS pipeline engine pulls data from the source, stores it in buffers (in-memory), does the requested transformations in the buffers and writes to the destination. The benefit is that it provides the fastest transformation as it happens in memory and we don't need to stage the data for transformations in most cases.
What is a Transformation?
  •  A transformation simply means bringing in the data in a desired format. For example you are pulling data from the source and want to ensure only distinct records are written to the destination, so duplicates are  removed.  Anther example is if you have master/reference data and want to pull only related data from the source and hence you need some sort of lookup. There are around 30 transformation tasks available and this can be extended further with custom built tasks if needed.
What is a Task?
  • A task is very much like a method of any programming language which represents or carries out an individual unit of work. There are broadly two categories of tasks in SSIS, Control Flow tasks and Database Maintenance tasks. All Control Flow tasks are operational in nature except Data Flow tasks. Although there are around 30 control flow tasks which you can use in your package you can also develop your own custom tasks with your choice of .NET programming language.
What is a Precedence Constraint and what types of Precedence Constraint are there?
  • SSIS allows you to place as many as tasks you want to be placed in control flow. You can connect all these tasks using connectors called Precedence Constraints. Precedence Constraints allow you to define the logical sequence of tasks in the order they should be executed. You can also specify a condition to be evaluated before the next task in the flow is executed.
  • These are the types of precedence constraints and the condition could be either a constraint, an expression or both 
    • Success (next task will be executed only when the last task completed successfully) or
    • Failure (next task will be executed only when the last task failed) or
    • Complete (next task will be executed no matter the last task was completed or failed).
What is a container and how many types of containers are there?
  • A container is a logical grouping of tasks which allows you to manage the scope of the tasks together.
  • These are the types of containers in SSIS:
    • Sequence Container - Used for grouping logically related tasks together
    • For Loop Container - Used when you want to have repeating flow in package
    • For Each Loop Container - Used for enumerating each object in a collection; for example a record set or a list of files.
  • Apart from the above mentioned containers, there is one more container called the Task Host Container which is not visible from the IDE, but every task is contained in it (the default container for all the tasks).
What are variables and what is variable scope?
  • A variable is used to store values. There are basically two types of variables, System Variable (like ErrorCode, ErrorDescription, PackageName etc) whose values you can use but cannot change and User Variable which you create, assign values and read as needed. A variable can hold a value of the data type you have chosen when you defined the variable.
  • Variables can have a different scope depending on where it was defined. For example you can have package level variables which are accessible to all the tasks in the package and there could also be container level variables which are accessible only to those tasks that are within the container.
What are SSIS Connection Managers?
  • When we talk of integrating data, we are actually pulling data from different sources and writing it to a destination. But how do you get connected to the source and destination systems? This is where the connection managers come into the picture. Connection manager represent a connection to a system which includes data provider information, the server name, database name, authentication mechanism, etc.
What is the RetainSameConnection property and what is its impact?
  • Whenever a task uses a connection manager to connect to source or destination database, a connection is opened and closed with the execution of that task. Sometimes you might need to open a connection, execute multiple tasks and close it at the end of the execution. This is where RetainSameConnection property of the connection manager might help you. When you set this property to TRUE, the connection will be opened on first time it is used and remain open until execution of the package completes.
What are a source and destination adapters?
  • A source adaptor basically indicates a source in Data Flow to pull data from. The source adapter uses a connection manager to connect to a source and along with it you can also specify the query method and query to pull data from the source.
  • Similar to a source adaptor, the destination adapter indicates a destination in the Data Flow to write data to. Again like the source adapter, the destination adapter also uses a connection manager to connect to a target system and along with that you also specify the target table and writing mode, i.e. write one row at a time or do a bulk insert as well as several other properties.
  • Please note, the source and destination adapters can both use the same connection manager if you are reading and writing to the same database.
What is the Data Path and how is it different from a Precedence Constraint?
  • Data Path is used in a Data Flow task to connect to different components of a Data Flow and show transition of the data from one component to another. A data path contains the meta information of the data flowing through it, such as the columns, data type, size, etc. When we talk about differences between the data path and precedence constraint; the data path is used in the data flow, which shows the flow of data. Whereas the precedence constraint is used in control flow, which shows control flow or transition from one task to another task.
What is a Data Viewer utility and what it is used for?
  • The data viewer utility is used in Business Intelligence Development Studio during development or when troubleshooting an SSIS Package. The data viewer utility is placed on a data path to see what data is flowing through that specific data path during execution. The data viewer utility displays rows from a single buffer at a time, so you can click on the next or previous icons to go forward and backward to display data.
  • What is an SSIS breakpoint? How do you configure it? How do you disable or delete it?
  • A breakpoint allows you to pause the execution of the package in Business Intelligence Development Studio during development or when troubleshooting an SSIS Package. You can right click on the task in control flow, click on Edit Breakpoint menu and from the Set Breakpoint window, you specify when you want execution to be halted/paused. For example OnPreExecute, OnPostExecute, OnError events, etc. To toggle a breakpoint, delete all breakpoints and disable all breakpoints go to the Debug menu and click on the respective menu item. You can event specify different conditions to hit the breakpoint as well.
What is SSIS event logging?
  • Like any other modern programming language, SSIS also raises different events during package execution life cycle. You can enable or write these events to trace the execution of your SSIS package and its tasks. You can also can write your custom message as a custom log. You can enable event logging at the package level as well as at the tasks level. You can also choose any specific event of a task or a package to be logged. This is essential when you are troubleshooting your package and trying to understand a performance problem or root cause of a failure.
.
What are the different SSIS log providers?
  • There are several places where you can log execution data generated by an SSIS event log:
    • SSIS log provider for Text files
    • SSIS log provider for Windows Event Log
    • SSIS log provider for XML files
    • SSIS log provider for SQL Profiler
    • SSIS log provider for SQL Server, which writes the data to the msdb..sysdtslog90 or msdb..sysssislog table depending on the SQL Server version.
How do you enable SSIS event logging?
  • SSIS provides a granular level of control in deciding what to log and where to log. To enable event logging for an SSIS Package, right click in the control flow area of the package and click on Logging. In the Configure SSIS Logs window you will notice all the tasks of the package are listed on the left side of the tree view. You can specifically choose which tasks you want to enable logging. On the right side you will notice two tabs; on the Providers and Logs tab you specify where you want to write the logs, you can write it to one or more log providers together. On the Details tab you can specify what events do you want to log for the selected task.
  • Please note, enabling event logging is immensely helpful when you are troubleshooting a package, but also incurs additional overhead on SSIS in order to log the events and information. Hence you should only enabling event logging when needed and only choose events which you want to log. Avoid logging all the events unnecessarily.
What is the LoggingMode property?
  • SSIS packages and all of the associated tasks or components have a property called LoggingMode. This property accepts three possible values: Enabled - to enable logging of that component, Disabled - to disable logging of that component and UseParentSetting - to use parent's setting of that component to decide whether or not to log the data.

What is the transaction support feature in SSIS?

  • When you execute a package, every task of the package executes in its own transaction. What if you want to execute two or more tasks in a single transaction? This is where the transaction support feature helps. You can group all your logically related tasks in single group.  Next you can set the transaction property appropriately to enable a transaction so that all the tasks of the package run in a single transaction. This way you can ensure either all of the tasks complete successfully or if any of them fails, the transaction gets roll-backed too.

What properties do you need to configure in order to use the transaction feature in SSIS?

  • Suppose you want to execute 5 tasks in a single transaction, in this case you can place all 5 tasks in a Sequence Container and set the TransactionOption and IsolationLevel properties appropriately.
    • The TransactionOption property expects one of these three values:
      • Supported - The container/task does not create a separate transaction, but if the parent object has already initiated a transaction then participate in it
      • Required - The container/task creates a new transaction irrespective of any transaction initiated by the parent object
      • NotSupported - The container/task neither creates a transaction nor participates in any transaction initiated by the parent object
  • Isolation level dictates how two more transaction maintains consistency and concurrency when they are running in parallel.

When I enabled transactions in an SSIS package, it failed with this exception: "The Transaction Manager is not available. The DTC transaction failed to start."  What caused this exception and how can it be fixed?

  • SSIS uses the MS DTC (Microsoft Distributed Transaction Coordinator) Windows Service for transaction support.  As such, you need to ensure this service is running on the machine where you are actually executing the SSIS packages or the package execution will fail with the exception message as indicated in this question.

What is event handling in SSIS?

  • Like many other programming languages, SSIS and its components raise different events during the execution of the code. You can write an even handler to capture the event and handle it in a few different ways. For example consider you have a data flow task and before execution of this data flow task you want to make some environmental changes such as creating a table to write data into, deleting/truncating a table you want to write, etc. Along the same lines, after execution of the data flow task you want to cleanup some staging tables. In this circumstance you can write an event handler for the OnPreExcute event of the data flow task which gets executed before the actual execution of the data flow. Similar to that you can also write an event handler for OnPostExecute event of the data flow task which gets executed after the execution of the actual data flow task.  Please note, not all the tasks raise the same events as others. There might be some specific events related to a specific task that you can use with one object and not with others.

How do you write an event handler?

  • First, open your SSIS package in Business Intelligence Development Studio (BIDS) and click on the Event Handlers tab.  Next, select the executable/task from the left side combo-box and then select the event you want to write the handler in the right side combo box. Finally, click on the hyperlink to create the event handler. So far you have only created the event handler, you have not specified any sort of action. For that simply drag the required task from the toolbox on the event handler designer surface and configure it appropriately.

What is the DisableEventHandlers property used for?

  • Consider you have a task or package with several event handlers, but for some reason you do not want event handlers to be called. One simple solution is to delete all of the event handlers, but that would not be viable if you want to use them in the future. This is where you can use the DisableEventHandlers property.  You can set this property to TRUE and all event handlers will be disabled. Please note with this property you simply disable the event handlers and you are not actually removing them.  This means you can set this value to FALSE and the event handlers will once again be executed.

What is SSIS validation?

  • SSIS validates the package and all of it's tasks to ensure it has been configured correctly.  With a given set of configurations and values, all the tasks and package will execute successfully. In other words, during the validation process, SSIS checks if the source and destination locations are accessible and the meta data about the source and destination tables are stored with the package are correct, so that the task will not fail if executed. The validation process reports warnings and errors depending on the validation failure detected. For example, if the source/destination tables/columns get changed/dropped it will show as error.  Whereas if you are accessing more columns than used to write to the destination object this will be flagged as a warning.

Define design time validation versus run time validation.

  • Design time validation is performed when you are opening your package in BIDS whereas run time validation is performed when you are actually executing the package.

Define early validation (package level validation) versus late validation (component level validation).

  • When a package is executed, the package goes through the validation process.  All of the components/tasks of package are validated before actually starting the package execution. This is called early validation or package level validation. During execution of a package, SSIS validates the component/task again before executing that particular component/task.  This is called late validation or component level validation.

What is DelayValidation and what is the significance?

  • As I said before, during early validation all of the components of the package are validated along with the package itself. If any of the component/task fails to validate, SSIS will not start the package execution. In most cases this is fine, but what if the second task is dependent on the first task?  For example, say you are creating a table in the first task and referring to the same table in the second task? When early validation starts, it will not be able to validate the second task as the dependent table has not been created yet.  Keep in mind that early validation is performed before the package execution starts. So what should we do in this case?  How can we ensure the package is executed successfully and the logically flow of the package is correct?  This is where you can use the DelayValidation property.  In the above scenario you should set the DelayValidation property of the second task to TRUE in which case early validation i.e. package level validation is skipped for that task and that task would only be validated during late validation i.e. component level validation. Please note using the DelayValidation property you can only skip early validation for that specific task, there is no way to skip late or component level validation.
Next Steps

What are the different components in the SSIS architecture?

  • The SSIS architecture comprises of four main components:
    • The SSIS runtime engine manages the workflow of the package
    • The data flow pipeline engine manages the flow of data from source to destination and in-memory transformations
    • The SSIS object model is used for programmatically creating, managing and monitoring SSIS packages
    • The SSIS windows service allows managing and monitoring packages

How is SSIS runtime engine different from the SSIS dataflow pipeline engine?

  • The SSIS Runtime Engine manages the workflow of the packages during runtime, which means its role is to execute the tasks in a defined sequence.  As you know, you can define the sequence using precedence constraints. This engine is also responsible for providing support for event logging, breakpoints in the BIDS designer, package configuration, transactions and connections. The SSIS Runtime engine has been designed to support concurrent/parallel execution of tasks in the package.
  • The Dataflow Pipeline Engine is responsible for executing the data flow tasks of the package. It creates a dataflow pipeline by allocating in-memory structure for storing data in-transit. This means, the engine pulls data from source, stores it in memory, executes the required transformation in the data stored in memory and finally loads the data to the destination. Like the SSIS runtime engine, the Dataflow pipeline has been designed to do its work in parallel by creating multiple threads and enabling them to run multiple execution trees/units in parallel.

How is a synchronous (non-blocking) transformation different from an asynchronous (blocking) transformation in SQL Server Integration Services?

  • A transformation changes the data in the required format before loading it to the destination or passing the data down the path. The transformation can be categorized in Synchronous and Asynchronous transformation.
  • A transformation is called synchronous when it processes each incoming row (modify the data in required format in place only so that the layout of the result-set remains same) and passes them down the hierarchy/path. It means, output rows are synchronous with the input rows (1:1 relationship between input and output rows) and hence it uses the same allocated buffer set/memory and does not require additional memory. Please note, these kinds of transformations have lower memory requirements as they work on a row-by-row basis (and hence run quite faster) and do not block the data flow in the pipeline. Some of the examples are : Lookup, Derived Columns, Data Conversion, Copy column, Multicast, Row count transformations, etc.
  • A transformation is called Asynchronous when it requires all incoming rows to be stored locally in the memory before it can start producing output rows. For example, with an Aggregate Transformation, it requires all the rows to be loaded and stored in memory before it can aggregate and produce the output rows. This way you can see input rows are not in sync with output rows and more memory is required to store the whole set of data (no memory reuse) for both the data input and output. These kind of transformations have higher memory requirements (and there are high chances of buffer spooling to disk if insufficient memory is available) and generally runs slower. The asynchronous transformations are also called "blocking transformations" because of its nature of blocking the output rows unless all input rows are read into memory.

What is the difference between a partially blocking transformation versus a fully blocking transformation in SQL Server Integration Services?

  • Asynchronous transformations, as discussed in last question, can be further divided in two categories depending on their blocking behavior:
    • Partially Blocking Transformations do not block the output until a full read of the inputs occur.  However, they require new buffers/memory to be allocated to store the newly created result-set because the output from these kind of transformations differs from the input set. For example, Merge Join transformation joins two sorted inputs and produces a merged output. In this case if you notice, the data flow pipeline engine creates two input sets of memory, but the merged output from the transformation requires another set of output buffers as structure of the output rows which are different from the input rows. It means the memory requirement for this type of transformations is higher than synchronous transformations where the transformation is completed in place.
    • Full Blocking Transformations, apart from requiring an additional set of output buffers, also blocks the output completely unless the whole input set is read. For example, the Sort Transformation requires all input rows to be available before it can start sorting and pass down the rows to the output path. These kind of transformations are most expensive and should be used only as needed. For example, if you can get sorted data from the source system, use that logic instead of using a Sort transformation to sort the data in transit/memory.

What is an SSIS execution tree and how can I analyze the execution trees of a data flow task?

  • The work to be done in the data flow task is divided into multiple chunks, which are called execution units, by the dataflow pipeline engine.  Each represents a group of transformations. The individual execution unit is called an execution tree, which can be executed by separate thread along with other execution trees in a parallel manner. The memory structure is also called a data buffer, which gets created by the data flow pipeline engine and has the scope of each individual execution tree. An execution tree normally starts at either the source or an asynchronous transformation and ends at the first asynchronous transformation or a destination. During execution of the execution tree, the source reads the data, then stores the data to a buffer, executes the transformation in the buffer and passes the buffer to the next execution tree in the path by passing the pointers to the buffers. To learn more about it
  • To see how many execution trees are getting created and how many rows are getting stored in each buffer for a individual data flow task, you can enable logging of these events of data flow task: PipelineExecutionTrees, PipelineComponentTime, PipelineInitialization, BufferSizeTunning, etc

How can an SSIS package be scheduled to execute at a defined time or at a defined interval per day?

  • You can configure a SQL Server Agent Job with a job step type of SQL Server Integration Services Package, the job invokes the dtexec command line utility internally to execute the package. You can run the job (and in turn the SSIS package) on demand or you can create a schedule for a one time need or on a reoccurring basis. Refer to this tip to learn more about it.

What is an SSIS Proxy account and why would you create it?

  • When we try to execute an SSIS package from a SQL Server Agent Job it fails with the message "Non-SysAdmins have been denied permission to run DTS Execution job steps without a proxy account". This error message is generated if the account under which SQL Server Agent Service is running and the job owner is not a sysadmin on the instance or the job step is not set to run under a proxy account associated with the SSIS subsystem. Refer to this tip to learn more about it.

How can you configure your SSIS package to run in 32-bit mode on 64-bit machine when using some data providers which are not available on the 64-bit platform?

  • In order to run an SSIS package in 32-bit mode the SSIS project property Run64BitRuntime needs to be set to "False".  The default configuration for this property is "True".  This configuration is an instruction to load the 32-bit runtime environment rather than 64-bit, and your packages will still run without any additional changes. The property can be found under SSIS Project Property Pages -> Configuration Properties -> Debugging.
Next Steps

isted below are some SQL Server Integration Services (SSIS) best practices:
  1. Keep it simple.
Avoid using components unnecessarily. For example:
    • Step 1. Declare the variable varServerDate.
    • Step 2. Use ExecuteSQLTask in the control flow to execute a SQL query to get the server date-time and store it in the variable
    • Step 3. Use the dataflow task and insert/update database with the server date-time from the variable varServerDate.
This sequence is advisable only in cases where the time difference from step 2 to step 3 really matters. If that doesn't really matter, then just use the getdate() command at step 3, as shown below:
--create table #table1 (Lap_Id int, LAP_Date datetime)
Insert into #table1 (Lap_Id, LAP_Date) values (1, getdate())
  1. Calling a child package multiple times from a parent with different parameter values.
When a child package is executed from a master package, the parameters that are passed from the master need to be configured in the child package. For this, you can use the ‘Parent Package Configuration’ option in the child package. But, for using the ‘Parent Package Configuration’, you need to specify the name of the ‘Parent Package Variable’ that is passed to the child package. If you want to call the same child package multiple times (each time with a different parameter value), declare the parent package variables (with the same name as given in the child package) with a scope limited to ‘Execute Package Tasks’.
SSIS allows declaring variables with the same name but the scope limited to different tasks – all inside the same package!
  1. SQL job with many atomic steps.
For the SQL job that calls the SSIS packages, make multiple steps, each doing small tasks, rather than a single step doing all the tasks. In the first case, the transaction log grows too big, and if a rollback happens, it may take the full processing space of the server.
  1. Avoid unnecessary typecasts.
Avoid unnecessary type casts. For example, the flat file connection manager, by default, uses the string [DT_STR] data type for all the columns. In case you want to use the actual data types, you have to manually change it. Better to change it at the source-level itself to avoid unnecessary type castings.
  1. Transactions.
Usually, the ETL processes handle large volumes of data. In such a scenario, do not attempt a transaction on the whole package logic. However, SSIS supports transaction, and it is advisable to use transactions where the atomicity of the transaction is taken care of.
For example, consider a scenario where a source record is to be spitted into 25 records at the target - where either all the 25 records reach the destination or zero. In this scenario, using a transaction, we can ensure either all the 25 records reach the destination or zero.
  1. Distributed transaction spanning multiple tasks.
The control flow of an SSIS package threads together various control tasks. It is possible to set a transaction that can span into multiple tasks using the same connection. To enable this, the “retainsameconnection” property of the Connection Manager should be set to “True”.
  1. Limit the package names to a maximum of 100 characters.
When an SSIS package with a package name exceeding 100 chars is deployed into SQL Server, it trims the package name to 100 chars, which may cause an execution failure. So, limit the package names to a maximum of 100 characters.
  1. Select * from…
Make sure that you are not passing any unnecessary columns from the source to the downstream. With the OLEDB connection manager source, using the ‘Table or View’ data access mode is equivalent to ‘SELECT * FROM <TABLE_NAME>’, which will fetch all the columns. Use ‘SQL command’ to fetch only the required columns, and pass that to the downstream. At each down-stream component, filter out the unnecessary columns.
  1. Sorting.
Sorting in SSIS is a time consuming operation. At the places where we know that data is coming from database tables, it’s better to perform the sorting operation at the database query itself.
  1. Excel Source and 64-bit runtime.
The Excel Source or Excel Connection manager works only with the 32 bit runtime. When a package using the Excel Source is enabled for 64-bit runtime (by default, it is enabled), it will fail on the production server using the 64-bit runtime. Go to the solution property pages\debugging and set Run64BitRuntime to False.
  1. On failure of a component, stop/continue the execution with the next component.
When a component fails, the property failParentonFailure can be effectively used either to stop the package execution or continue with the next component - exception - stop/continue with the next component in a sequence container. The value of the constraint connecting the components in the sequence should be set to "Completion", and the failParentonFailure property should be set to False (default).
  1. Protection.
To avoid most of the package deployment error from one system to another system, set the package protection level to ‘DontSaveSenstive’.
  1. Copy pasting the Script component.
Once you copy-paste a script component and execute the package, it may fail. Just open the script editor of the pasted script component, save the script, and execute the package – it will work.
  1. Configuration filter – Use as a filter.
It is a best practice to use the package name as the configuration filter for all the configuration items that are specific to a package. It is especially useful when there are so many packages with package-specific configuration items. For the configuration items that are general to many packages, use a generic name.
  1. Optimal use of configuration records.
Avoid the same configuration item recorded under different filter/object names. For example, if two packages are using the same connection string, you need only one configuration record. To enable this, use the same name for the connection manager in both the packages. Also, use a generic configuration filter. This is quite convenient at the time of porting from one environment to another (e.g.: from UAT to production).

Top 10 SQL Server Integration Services Best Practices

How many of you have heard the myth that Microsoft® SQL Server® Integration Services (SSIS) does not scale? The first question we would ask in return is: “Does your system need to scale beyond 4.5 million sales transaction rows per second?” SQL Server Integration Services is a high performance Extract-Transform-Load (ETL) platform that scales to the most extreme environments. And as documented in SSIS ETL world record performance, SQL Server Integration Services can process at the scale of 4.5 million sales transaction rows per second.

SSIS is an in-memory pipeline, so ensure that all transformations occur in memory.
The purpose of having Integration Services within SQL Server features is to provide a flexible, robust pipeline that can efficiently perform row-by-row calculations and parse data all in memory.

While the extract and load phases of the pipeline will touch disk (read and write respectively), the transformation itself should process in memory. If transformations spill to disk (for example with large sort operations), you will see a big performance degradation. Construct your packages to partition and filter data so that all transformations fit in memory.

A great way to check if your packages are staying within memory is to review the SSIS performance counter Buffers spooled, which has an initial value of 0; above 0 is an indication that the engine has started swapping to disk. For more information,


Plan for capacity by understanding resource utilization.
SQL Server Integration Services is designed to process large amounts of data row by row in memory with high speed. Because of this, it is important to understand resource utilization, i.e., the CPU, memory, I/O, and network utilization of your packages.

CPU Bound
Seek to understand how much CPU is being used by Integration Services and how much CPU is being used overall by SQL Server while Integration Services is running. This latter point is especially important if you have SQL Server and SSIS on the same box, because if there is a resource contention between these two, it is SQL Server that will typically win – resulting in disk spilling from Integration Services, which slows transformation speed.

The perfmon counter that is of primary interest to you is
Process / % Processor Time (Total). Measure this counter for both sqlservr.exe and dtexec.exe. If SSIS is not able to drive close to 100% CPU load, this may be indicative of:
  • Application contention: For example, SQL Server is taking on more processor resources, making them unavailable to SSIS.
  • Hardware contention: A common scenario is that you have suboptimal disk I/O or not enough memory to handle the amount of data being processed.
  • Design limitation: The design of your SSIS package is not making use of parallelism, and/or the package uses too many single-threaded tasks.

Network Bound
SSIS moves data as fast as your network is able to handle it. Because of this, it is important to understand your network topology and ensure that the path between your source and target have both low latency and high throughput.

The following Network perfmon counters can help you tune your topology:
  • Network Interface / Current Bandwidth: This counter provides an estimate of current bandwidth.
  • Network Interface / Bytes Total / sec: The rate at which bytes are sent and received over each network adapter.
  • Network Interface / Transfers/sec: Tells how many network transfers per second are occurring. If it is approaching 40,000 IOPs, then get another NIC card and use teaming between the NIC cards.
These counters enable you to analyze how close you are to the maximum bandwidth of the system. Understanding this will allow you to plan capacity appropriately whether by using gigabit network adapters, increasing the number of NIC cards per server, or creating separate network addresses specifically for ETL traffic.

I/O Bound
If you ensure that Integration Services is minimally writing to disk, SSIS will only hit the disk when it reads from the source and writes to the target. But if your I/O is slow, reading and especially writing can create a bottleneck.

Because tuning I/O is outside the scope of this technical note, please refer to Predeployment I/O Best Practices.  Remember that an I/O system is not only specified by its size ( “I need 10 TB”) – but also by its sustainable speed (“I want 20,000 IOPs”).

Memory bound
A very important question that you need to answer when using Integration Services is: “How much memory does my package use?”

The key counters for Integration Services and SQL Server are:
  • Process / Private Bytes (DTEXEC.exe) – The amount of memory currently in use by Integration Services. This memory cannot be shared with other processes.
  • Process / Working Set (DTEXEC.exe) – The total amount of allocated memory by Integration Services.
  • SQL Server: Memory Manager / Total Server Memory: The total amount of memory allocated by SQL Server. Because SQL Server has another way to allocate memory using the AWE API, this counter is the best indicator of total memory used by SQL Server. To understand SQL Server memory allocations better,
  • Memory / Page Reads / sec – Represents to total memory pressure on the system. If this consistently goes above 500, the system is under memory pressure.


Baseline source system extract speed.
Understand your source system and how fast you extract from it. After all, Integration Services cannot be tuned beyond the speed of your source – i.e., you cannot transform data faster than you can read it.
Measure the speed of the source system by creating a very simple package reading data from your source with the a destination of “Row Count”:
Execute the package from the command line (DTEXEC) and measure the time it took for it to complete its task. Use the Integration Services log output to get an accurate calculation of the time. You want to calculate rows per second:
Rows / sec = Row Count / TimeData Flow
Based on this value, you now know the maximum number of rows per second you can read from the source – this is also the roof on how fast you can transform your data. To increase this Rows / sec calculation, you can do the following:
  • Improve drivers and driver configurations: Make sure you are using the most up-to-date driver configurations for your network, data source, and disk I/O. Often the default network drivers on your server are not configured optimally for the network stack, which results in performance degradations when there are a high number of throughput requests. Note that for 64-bit systems, at design time you may be loading 32-bit drivers; ensure that at run time you are using 64-bit drivers.
     
  • Start multiple connections: To overcome limitations of drivers, you can try to start multiple connections to your data source. As long as the source can handle many concurrent connections, you may see an increase in throughput if you start several extracts at once. If concurrency is causing locking or blocking issues, consider partitioning the source and having your packages read from different partitions to more evenly distribute the load.
     
  • Use multiple NIC cards: If the network is your bottleneck and you’ve already ensured that you’re using gigabit network cards and routers, then a potential solution is to use multiple NIC cards per server. Note that you will have to be careful when you configure multiple NIC environments; otherwise you will have network conflicts.


Optimize the SQL data source, lookup transformations, and destination.
When you execute SQL statements within Integration Services (as noted in the above Data access mode dialog box), whether to read a source, to perform a look transformation, or to change tables, some standard optimizations significantly help performance:
  •  Use the NOLOCK or TABLOCK hints to remove locking overhead.
  • To optimize memory usage, SELECT only the columns you actually need. If you SELECT all columns from a table (e.g., SELECT * FROM) you will needlessly use memory and bandwidth to store and retrieve columns that do not get used. .
     
  • If possible, perform your datetime conversions at your source or target databases, as it is more expensive to perform within Integration Services..
     
  • In SQL Server 2008 Integration Services, there is a new feature of the shared lookup cache. When using parallel pipelines (see points #8 and #10 below), it provides a high-speed, shared cache. .
     
  • If Integration Services and SQL Server run on the same server, use the SQL Server destination instead of the OLE DB destination to improve performance..
     
  • Commit size 0 is fastest on heap bulk targets, because only one transaction is committed. If you cannot use 0, use the highest possible value of commit size to reduce the overhead of multiple-batch writing.  Commit size = 0 is a bad idea if inserting into a Btree – because all incoming rows must be sorted at once into the target Btree—and if your memory is limited, you are likely to spill.  Batchsize = 0 is ideal for inserting into a heap. For an indexed destination, I recommend testing between 100,000 and 1,000,000 as batch size.
     
  • Use a commit size of <5000 to avoid lock escalation when inserting; note that in SQL Server 2008 you can now enable/disable lock escalation at the object level, but use this wisely.
     
  • Heap inserts are typically faster than using a clustered index. This means that you may want to drop indexes and rebuild if you are changing a large part of the destination table; you will want to test your inserts both by keeping indexes in place and by dropping all indexes and rebuilding to validate..
     
  • Use partitions and partition SWITCH command; i.e., load a work table that contains a single partition and SWITCH it in to the main table after you build the indexes and put the constraints on..
     
  • Another great reference from the SQL Performance team is Getting Optimal Performance with Integration Services Lookups.


Tune your network.
A key network property is the packet size of your connection. By default this value is set to 4,096 bytes. This means a new network package must be assemble for every 4 KB of data. As noted in SqlConnection.PacketSize Property in the .NET Framework Class Library, increasing the packet size will improve performance because fewer network read and write operations are required to transfer a large data set.
If your system is transactional in nature, with many small data size read/writes, lowering the value will improve performance.

Since Integration Services is all about moving large amounts of data, you want to minimize the network overhead. This means that the value 32K (32767) is the fastest option. While it is possible to configure the network packet size on a server level using sp_configure, you should not do this. The database administrator may have reasons to use a different server setting than 32K. Instead, override the server settings in the connection manager as illustrated below.
Another network tuning technique is to use network affinity at the operating system level. At high throughputs, you can sometimes improve performance this way.

For the network itself, you may want to work with your network specialists to enable jumbo frames to increase the default payload of 1,500 bytes to 9,000 bytes. By enabling jumbo frames, you will further decrease the amount of network operation required to move large data sets.


Use data types – yes, back to data types! –wisely.
Of all the points on this top 10 list, this is perhaps the most obvious. Yet, it is such an important point that it needs to be made separately. Follow these guidelines:
  •  Make data types as narrow as possible so you will allocate less memory for your transformation.
     
  • Do not perform excessive casting of data types – it will only degrade performance. Match your data types to the source or destination and explicitly specify the necessary data type casting..
     
  • Watch precision issues when using the money, float, and decimal types. Also, be aware the money is faster than decimal, and money has fewer precision considerations than float.


Change the design.
There are some things that Integration Services does well – and other tasks where using another tool is more efficient. Your tool choice should be based on what is most efficient and on a true understanding of the problem. To help with that choice, consider the following points:
  •  Do not sort within Integration Services unless it is absolutely necessary. In order to perform a sort, Integration Services allocates the memory space of the entire data set that needs to be transformed. If possible, presort the data before it goes into the pipeline. If you must sort data, try your best to sort only small data sets in the pipeline. Instead of using Integration Services for sorting, use an SQL statement with ORDER BY to sort large data sets in the database – mark the output as sorted by changing the Integration Services pipeline metadata on the data source.
     
  • There are times where using Transact-SQL will be faster than processing the data in SSIS. As a general rule, any and all set-based operations will perform faster in Transact-SQL because the problem can be transformed into a relational (domain and tuple) algebra formulation that SQL Server is optimized to resolve. Also, the SQL Server optimizer will automatically apply high parallelism and memory management to the set-based operation – an operation you may have to perform yourself if you are using Integration Services. Typical set-based operations include:
     
    • Set-based UPDATE statements - which are far more efficient than row-by-row OLE DB calls.
       
    • Aggregation calculations such as GROUP BY and SUM. These are typically also calculated faster using Transact-SQL instead of in-memory calculations by a pipeline.
       
  • Delta detection is the technique where you change existing rows in the target table instead of reloading the table. To perform delta detection, you can use a change detection mechanism such as the new SQL Server 2008 Change Data Capture (CDC) functionality. If such functionality is not available, you need to do the delta detection by comparing the source input with the target table. This can be a very costly operation requiring the maintenance of special indexes and checksums just for this purpose. Often, it is fastest to just reload the target table. A rule of thumb is that if the target table has changed by >10%, it is often faster to simply reload than to perform the logic of delta detection. 


Partition the problem.
One of the main tenets of scalable computing is to partition problems into smaller, more manageable chunks. This allows you to more easily handle the size of the problem and make use of running parallel processes in order to solve the problem faster.

For ETL designs, you will want to partition your source data into smaller chunks of equal size. This latter point is important because if you have chunks of different sizes, you will end up waiting for one process to complete its task. For example, looking at the graph below, you will notice that for the four processes executed on partitions of equal size, the four processes will finish processing January 2008 at the same time and then together continue to process February 2008. But for the partitions of different sizes, the first three processes will finish processing but wait for the fourth process, which is taking a much longer time. The total run time will be dominated by the largest chunk.

To create ranges of equal-sized partitions, use time period and/or dimensions (such as geography) as your mechanism to partition. If your primary key is an incremental value such as an IDENTITY or another increasing value, you can use a modulo function. If you do not have any good partition columns, create a hash of the value of the rows and partition based on the hash value. For more information on hashing and partitioning, refer to the white paper; while the paper is about distinct count within Analysis Services, the technique of hash partitioning is treated in depth too.

Some other partitioning tips:
  • Use partitioning on your target table. This way you will be able to run multiple versions of the same package, in parallel, that insert data into different partitions of the same table. When using partitioning, the SWITCH statement is your friend. It not only increases parallel load speeds, but also allows you to efficiently transfer data. Please refer to the SQL Server Books Online article for more information.
     
  • As implied above, you should design your package to take a parameter specifying which partition it should work on. This way, you can have multiple executions of the same package, all with different parameter and partition values, so you can take advantage of parallelism to complete the task faster.
     
  • From the command line, you can run multiple executions by using the “START” command.


Minimize logged operations.
When you insert data into your target SQL Server database, use minimally logged operations if possible. When data is inserted into the database in fully logged mode, the log will grow quickly because each row entering the table also goes into the log.

Therefore, when designing Integration Services packages, consider the following:
  • Try to perform your data flows in bulk mode instead of row by row. By doing this in bulk mode, you will minimize the number of entries that are added to the log file. This reduction will improve the underlying disk I/O for other inserts and will minimize the bottleneck created by writing to the log.
     
  • If you need to perform delete operations, organize your data in a way so that you can TRUNCATE the table instead of running a DELETE. The latter will place an entry for each row deleted into the log. But the former will simply remove all of the data in the table with a small log entry representing the fact that the TRUNCATE occurred. In contrast with popular belief, a TRUNCATE statement can participate in a transaction.
     
  • Use the SWITCH statement and partitioning. If partitions need to be moved around, you can use the SWITCH statement (to switch in a new partition or switch out the oldest partition), which is a minimally logged statement.
     
  • Be careful when using DML statements; if you mix in DML statements within your INSERT statements, minimum logging is suppressed.


Schedule and distribute it correctly.
After your problem has been chunked into manageable sizes, you must consider where and when these chunks should be executed. The goal is to avoid one long running task dominating the total time of the ETL flow.

A good way to handle execution is to create a priority queue for your package and then execute multiple instances of the same package (with different partition parameter values). The queue can simply be a SQL Server table. Each package should include a simple loop in the control flow:
  1.  Pick a relevant chunk from the queue:
    1. Relevant” means that is has not already been processed and that all chunks it depends on have already run.
    2. If no item is returned from the queue, exit the package.
  2. Perform the work required on the chunk.
  3. Mark the chunk as “done” in the queue.
  4. Return to the start of loop.
Picking an item from the queue and marking it as “done” (step 1 and 3 above) can be implemented as stored procedure, for example.

The queue acts as a central control and coordination mechanism, determining the order of execution and ensuring that no two packages work on the same chunk of data. Once you have the queue in place, you can simply start multiple copies of DTEXEC to increase parallelism.
Here are some SSIS related Interview Questions with answers. hope they help.

1) What is the control flow
2) what is a data flow
3) how do you do error handling in SSIS
4) how do you do logging in ssis
5) how do you deploy ssis packages.
6) how do you schedule ssis packages to run on the fly
7) how do you run stored procedure and get data
8) A scenario: Want to insert a text file into database table, but during the upload want to change a column called as months - January, Feb, etc to a code, - 1,2,3.. .This code can be read from another database table called months. After the conversion of the data , upload the file. If there are any errors, write to error table. Then for all errors, read errors from database, create a file, and mail it to the supervisor.
How would you accomplish this task in SSIS?
9)what are variables and what is variable scope ?
Answers
For Q 1 and 2:
In SSIS a workflow is called a control-flow. A control-flow links together our modular data-flows as a series of operations in order to achieve a desired result.

A control flow consists of one or more tasks and containers that execute when the package runs. To control order or define the conditions for running the next task or container in the package control flow, you use precedence constraints to connect the tasks and containers in a package. A subset of tasks and containers can also be grouped and run repeatedly as a unit within the package control flow.

SQL Server 2005 Integration Services (SSIS) provides three different types of control flow elements: containers that provide structures in packages, tasks that provide functionality, and precedence constraints that connect the executables, containers, and tasks into an ordered control flow.

A data flow consists of the sources and destinations that extract and load data, the transformations that modify and extend data, and the paths that link sources, transformations, and destinations. Before you can add a data flow to a package, the package control flow must include a Data Flow task. The Data Flow task is the executable within the SSIS package that creates, orders, and runs the data flow. A separate instance of the data flow engine is opened for each Data Flow task in a package.

SQL Server 2005 Integration Services (SSIS) provides three different types of data flow components: sources, transformations, and destinations. Sources extract data from data stores such as tables and views in relational databases, files, and Analysis Services databases. Transformations modify, summarize, and clean data. Destinations load data into data stores or create in-memory datasets.
Q3:
When a data flow component applies a transformation to column data, extracts data from sources, or loads data into destinations, errors can occur. Errors frequently occur because of unexpected data values.

For example, a data conversion fails because a column contains a string instead of a number, an insertion into a database column fails because the data is a date and the column has a numeric data type, or an expression fails to evaluate because a column value is zero, resulting in a mathematical operation that is not valid.

Errors typically fall into one the following categories:

-Data conversion errors, which occur if a conversion results in loss of significant digits, the loss of insignificant digits, and the truncation of strings. Data conversion errors also occur if the requested conversion is not supported.
-Expression evaluation errors, which occur if expressions that are evaluated at run time perform invalid operations or become syntactically incorrect because of missing or incorrect data values.
-Lookup errors, which occur if a lookup operation fails to locate a match in the lookup table.

Many data flow components support error outputs, which let you control how the component handles row-level errors in both incoming and outgoing data. You specify how the component behaves when truncation or an error occurs by setting options on individual columns in the input or output.

For example, you can specify that the component should fail if customer name data is truncated, but ignore errors on another column that contains less important data.

Q 4:
SSIS includes logging features that write log entries when run-time events occur and can also write custom messages.

Integration Services supports a diverse set of log providers, and gives you the ability to create custom log providers. The Integration Services log providers can write log entries to text files, SQL Server Profiler, SQL Server, Windows Event Log, or XML files.

Logs are associated with packages and are configured at the package level. Each task or container in a package can log information to any package log. The tasks and containers in a package can be enabled for logging even if the package itself is not.

To customize the logging of an event or custom message, Integration Services provides a schema of commonly logged information to include in log entries. The Integration Services log schema defines the information that you can log. You can select elements from the log schema for each log entry.

To enable logging in a package
1. In Business Intelligence Development Studio, open the Integration Services project that contains the package you want.
2. On the SSIS menu, click Logging.
3. Select a log provider in the Provider type list, and then click Add.
Q 5 :

SQL Server 2005 Integration Services (SSIS) makes it simple to deploy packages to any computer.
There are two steps in the package deployment process:
-The first step is to build the Integration Services project to create a package deployment utility.
-The second step is to copy the deployment folder that was created when you built the Integration Services project to the target computer, and then run the Package Installation Wizard to install the packages.
Q 9 :

Variables store values that a SSIS package and its containers, tasks, and event handlers can use at run time. The scripts in the Script task and the Script component can also use variables. The precedence constraints that sequence tasks and containers into a workflow can use variables when their constraint definitions include expressions.

Integration Services supports two types of variables: user-defined variables and system variables. User-defined variables are defined by package developers, and system variables are defined by Integration Services. You can create as many user-defined variables as a package requires, but you cannot create additional system variables.

Scope :

A variable is created within the scope of a package or within the scope of a container, task, or event handler in the package. Because the package container is at the top of the container hierarchy, variables with package scope function like global variables and can be used by all containers in the package. Similarly, variables defined within the scope of a container such as a For Loop container can be used by all tasks or containers within the For Loop container.


Question 1 - True or False - Using a checkpoint file in SSIS is just like issuing the CHECKPOINT command against the relational engine. It commits all of the data to the database.
False. SSIS provides a Checkpoint capability which allows a package to restart at the point of failure.

Question 2 - Can you explain the what the Import\Export tool does and the basic steps in the wizard?
The Import\Export tool is accessible via BIDS or executing the dtswizard command.
The tool identifies a data source and a destination to move data either within 1 database, between instances or even from a database to a file (or vice versa).


Question 3 - What are the command line tools to execute SQL Server Integration Services packages?
DTSEXECUI - When this command line tool is run a user interface is loaded in order to configure each of the applicable parameters to execute an SSIS package.
DTEXEC - This is a pure command line tool where all of the needed switches must be passed into the command for successful execution of the SSIS package.


Question 4 - Can you explain the SQL Server Integration Services functionality in Management Studio?
You have the ability to do the following:
Login to the SQL Server Integration Services instance
View the SSIS log
View the packages that are currently running on that instance
Browse the packages stored in MSDB or the file system
Import or export packages
Delete packages
Run packages

Question 5 - Can you name some of the core SSIS components in the Business Intelligence Development Studio you work with on a regular basis when building an SSIS package?
Connection Managers
Control Flow
Data Flow
Event Handlers
Variables window
Toolbox window
Output window
Logging
Package Configurations

Question Difficulty = Moderate

Question 1 - True or False: SSIS has a default means to log all records updated, deleted or inserted on a per table basis.
False, but a custom solution can be built to meet these needs.

Question 2 - What is a breakpoint in SSIS? How is it setup? How do you disable it?
A breakpoint is a stopping point in the code. The breakpoint can give the Developer\DBA an opportunity to review the status of the data, variables and the overall status of the SSIS package.
10 unique conditions exist for each breakpoint.
Breakpoints are setup in BIDS. In BIDS, navigate to the control flow interface. Right click on the object where you want to set the breakpoint and select the 'Edit Breakpoints...' option.


Question 3 - Can you name 5 or more of the native SSIS connection managers?
OLEDB connection - Used to connect to any data source requiring an OLEDB connection (i.e., SQL Server 2000)
Flat file connection - Used to make a connection to a single file in the File System. Required for reading information from a File System flat file
ADO.Net connection - Uses the .Net Provider to make a connection to SQL Server 2005 or other connection exposed through managed code (like C#) in a custom task
Analysis Services connection - Used to make a connection to an Analysis Services database or project. Required for the Analysis Services DDL Task and Analysis Services Processing Task
File connection - Used to reference a file or folder. The options are to either use or create a file or folder
Excel
FTP
HTTP
MSMQ
SMO
SMTP
SQLMobile
WMI


Question 4 - How do you eliminate quotes from being uploaded from a flat file to SQL Server?
In the SSIS package on the Flat File Connection Manager Editor, enter quotes into the Text qualifier field then preview the data to ensure the quotes are not included.
Additional information: How to strip out double quotes from an import file in SQL Server Integration Services
Question 5 - Can you name 5 or more of the main SSIS tool box widgets and their functionality?
For Loop Container
Foreach Loop Container
Sequence Container
ActiveX Script Task
Analysis Services Execute DDL Task
Analysis Services Processing Task
Bulk Insert Task
Data Flow Task
Data Mining Query Task
Execute DTS 2000 Package Task
Execute Package Task
Execute Process Task
Execute SQL Task
etc.

Question Difficulty = Difficult

Question 1 - Can you explain one approach to deploy an SSIS package?
One option is to build a deployment manifest file in BIDS, then copy the directory to the applicable SQL Server then work through the steps of the package installation wizard
A second option is using the dtutil utility to copy, paste, rename, delete an SSIS Package
A third option is to login to SQL Server Integration Services via SQL Server Management Studio then navigate to the 'Stored Packages' folder then right click on the one of the children folders or an SSIS package to access the 'Import Packages...' or 'Export Packages...'option.
A fourth option in BIDS is to navigate to File | Save Copy of Package and complete the interface.



Question 2 - Can you explain how to setup a checkpoint file in SSIS?
The following items need to be configured on the properties tab for SSIS package:
CheckpointFileName - Specify the full path to the Checkpoint file that the package uses to save the value of package variables and log completed tasks. Rather than using a hard-coded path as shown above, it's a good idea to use an expression that concatenates a path defined in a package variable and the package name.
CheckpointUsage - Determines if/how checkpoints are used. Choose from these options: Never (default), IfExists, or Always. Never indicates that you are not using Checkpoints. IfExists is the typical setting and implements the restart at the point of failure behavior. If a Checkpoint file is found it is used to restore package variable values and restart at the point of failure. If a Checkpoint file is not found the package starts execution with the first task. The Always choice raises an error if the Checkpoint file does not exist.
SaveCheckpoints - Choose from these options: True or False (default). You must select True to implement the Checkpoint behavior.

Question 3 - Can you explain different options for dynamic configurations in SSIS?
Use an XML file
Use custom variables
Use a database per environment with the variables
Use a centralized database with all variables

Question 4 - How do you upgrade an SSIS Package?
Depending on the complexity of the package, one or two techniques are typically used:
Recode the package based on the functionality in SQL Server DTS
Use the Migrate DTS 2000 Package wizard in BIDS then recode any portion of the package that is not accurate


Question 5 - Can you name five of the Perfmon counters for SSIS and the value they provide?
SQLServer:SSIS Service
SSIS Package Instances - Total number of simultaneous SSIS Packages running
SQLServer:SSIS Pipeline
BLOB bytes read - Total bytes read from binary large objects during the monitoring period.
BLOB bytes written - Total bytes written to binary large objects during the monitoring period.
BLOB files in use - Number of binary large objects files used during the data flow task during the monitoring period.
Buffer memory - The amount of physical or virtual memory used by the data flow task during the monitoring period.
Buffers in use - The number of buffers in use during the data flow task during the monitoring period.
Buffers spooled - The number of buffers written to disk during the data flow task during the monitoring period.
Flat buffer memory - The total number of blocks of memory in use by the data flow task during the monitoring period.
Flat buffers in use - The number of blocks of memory in use by the data flow task at a point in time.
Private buffer memory - The total amount of physical or virtual memory used by data transformation tasks in the data flow engine during the monitoring period.
Private buffers in use - The number of blocks of memory in use by the transformations in the data flow task at a point in time.
Rows read - Total number of input rows in use by the data flow task at a point in time.
Rows written - Total number of output rows in use by the data flow task at a point in time.



You want to include an image in a report. How do you display the Image Properties dialog box?
When you drag an image item from the Toolbox window to the Report Designer, the Image Properties dialog box automatically opens.


You want to configure an amount to display the value in a currency format. Which property do you use?
To configure an amount to display a value in a currency format, select the report item, and then set the format property to C or c.


What are data regions?
Data regions are report items that display repeated rows of summarized information from datasets.


You want to generate a report that is formatted as a chart. Can you use the Report Wizard to create such a report?
No, the Report Wizard lets you create only tabular and matrix reports. you must create the chart report directly by using the Report Designer.


You want to use BIDS to deploy a report to a different server than the one you chose in the Report Wizard. How can you change the server URL?
You can right-click the project in Solution Explorer and then change the Target-Server URL property.


Which rendering formats are affected by the PageSize properties?
Because only the Adobe PDf file, Word, and Image rendering extensions use physical page breaks, they are the only formats that are affected by the PageSize properties.


Can you use a stored procedure to provide data to an SSRS report?
Yes, you can use a stored procedure to provide data to an SSRS report by configuring the dataset to use a stored procedure command type. However, your stored procedure should return only a single result set. If it returns multiple result sets, only the first one is used for the report dataset.


You want to use a perspective in an MDX query. How do you select the perspective?
Use the Cube Selector in the MDX Query Designer to select a perspective.


Can you use data mining models in SSRS?
Yes, you can use the DMX Designer to create data mining queries for SSRS reports. However, do not forget to flatten the result set returned by the DMX query.


You want your report to display a hyperlink that will take users to your intranet. How do you configure such a hyperlink?
Create a text box item, set the action to Go To URL, and then configure the URL.


You want a report to display Sales by Category, SubCategory, and Product. You want users to see only summarized information initially but to be able to display the details as necessary. How would you create the report?
Group the Sales information by Category, SubCategory, and Product. Hide the SubCategory group and set the visibility to toggle based on the Category item. Hide the Product category group and set the visibility to toggle based on the SubCategory item.


You want to create an Excel interactive report from SSRS. In SSRS, can you create the same interactive experience in Excel that you would have on the Web?
No, you cannot create the same experience with SSRS. you can, however, use Excel to create such an experience.


What is the main difference between a Matrix report item and a Table report item?
The main difference between a Matrix and a Table report item is in the initial template. Actually, both report items are just templates for the Tablix data region.


When you do not use report caching, is it better to use parameters to filter information in the query or to use filters in the dataset?
From a performance perspective, it is better to use parameters because they let SSRS pull filtered data from the data source. In contrast, when you use filters, the queries retrieve all data and then filter the information in an additional step.


How do you configure a running aggregate in SSRS?
You can use the RunningValue function to configure a running aggregate.


What is the main purpose of a report parameter?
The main purpose of a report parameter is to add interactivity to your reports, letting users change the report behavior based on options they select.


What is the main purpose of a query parameter?
The main purpose of a query parameter is to filter data in the data source.


You want your users to select a parameter from a list of values in a list box. How should you configure the parameter?
You should create a data source that contains the possible values and then bind the data source to the parameter.


What is the main benefit of using embedded code in a report?
The main benefit of using embedded code in a report is that the code you write at the report level can be reused in any expression in the report.


What programming language would you use to create embedded functions in SSRS?
An SSRS report supports only visual Basic .nET embedded code.


How do you reference an embedded function in a report expression?
Use the Code prefix and the name of the function to reference an embedded function in a report expression.


Which of the following are valid options for deploying a report? (Choose all that apply.)
a. With BIDS
b. With the Computer Management console
c. With the .nET START command
d. With SSMS
e. With Report Manager
The correct answers are a and e, BIDS and Report Manager.


Why should you not overwrite a shared data source in production?
You should not overwrite a production-shared data source because the administrator has probably already changed some connection string properties.


Can you edit a report that an end user created by using Report Builder in BIDS?
Yes, if an end user created a report by using Report Builder in BIDS and saved the report definition file, you can open the file in BIDS and edit it.


How can you manage reports from your application if the report server is deployed in SharePoint integrated mode?
Use the ReportService2006 endpoint of the SSRS Web service if your report server is deployed in SharePoint integrated mode.


In which processing mode of a report viewer control can you use the full functionality of your report server?
You should use the remote processing mode to use the full functionality of your report server.


What types of roles are available in SSRS 2008, and what are their purposes?
Item-level roles and system-level roles are the two types of roles available in SSRS 2008. An item-level role is a collection of tasks related to operations on an object of the report object hierarchy of SSRS 2008. A system-level role is a collection of tasks related to operations on server objects outside the report object hierarchy of SSRS 2008.


Can a user or group belong to more than one item-level or system-level role?
Yes, in SSRS 2008, a user or group can have more than one association to a system-level or an item-level role.


When storing the credentials of a data source in the server, are those credentials safe?
Yes, the data source credentials are safe because Reporting Services encrypts them and stores them in the ReportServer SQL Server database.


What happens if you do not specify a parameter value in a subscription and the parameter does not have a default value?
If you do not specify a parameter value in a subscription and the parameter does not have a default value, the execution of the report will fail.


You want to create a subscription to a report. However, when you right-click the Subscription subfolder of the report, you notice that the new Subscription option is dimmed. What is wrong?
When the new Subscription option is dimmed, the report probably does not use stored credentials for accessing the data. SSRS needs these credentials stored in its own ReportServer database to execute a report on a schedule.


What can you do if your query with properties for a data-driven subscription does not provide values for all of the subscription properties?
If your query with properties for a data-driven subscription does not provide values for all of the subscription properties, you can use text and default values instead. These values are then used for parameters of all subscriptions you get from the query.


What mechanisms do you identify to reduce the overhead of Reporting Services data sources?
Snapshots and cached reports can help reduce the processing pressure on data sources and improve report response time.


Can you always create a cache of a report?
No, you can create a cache of a report only when certain requirements, such as having credentials stored in the Report Server, are met.


Can you edit the .rdl code associated with a linked report?
No, because a linked report has no .rdl code of its own. It refers to the .rdl code of the base report.


Which of the tools and utilities described in this lesson can change or create the virtual directories for the report server and Report Manager after installation?
Only Reporting Services Configuration Manager can enable and name the virtual directories for the report server and Report Manager.


What is the file name extension for an encryption key backup?
Encryption key backups have an .snk file name extension.


What are the three SSRS command-line utilities and their primary functions?
RSConfig.exe is used to define the connection properties from the SSRS instance to the Report Server database; RSKeyMgmt.exe performs encryption key operations and scale-out deployment setup; RS.exe runs Report Server Script files that can perform report deployment and management operations.


If you want to disable one of the rendering options in the Save As drop-down list when viewing a report through Report Manager, where do you do that?
The visibility property for any of the rendering devices can be changed by modifying the RSReportServer.config file and locating the <ExtensionName="RenderDevice"/> tag for the specific device.
What are some of the Reporting Services best practices that you follow?

Top of Form

Report Property options:
-----------------------------------
* SnapToGrid - For finer control of object sizes, set the SnapToGrid property to False. Once done, you can resize rows, columns, textboxes, etc. to more exact dimensions.
* InteractiveSize – To achieve one long scrollable web page, change height property (expanc InteractiveSize) to 0. Note: With reports that are very long, this can adversely affect report rendering times. Experiment, use judgement, and obtain feedback from users. Educate your users as to the pros and cons of this.
* PageSize – The default is 8.5in, 11in. This is a standard portrait letter size page size. For landscape printing, swap the width and height (to 11in, 8.5in). Coordinate these changes with the InteractiveSize. (i.e. If you want to print landscape, but see one long scrollable page in the web browser before printing, set the InteractiveSize to a width of 11in, and a height of 0.)
* Description – With Reporting Services (RS) in Native mode (not Sharepoint Integration mode), text you write in this property is displayed in the Report Manager and the WSS2 Reporting Services report viewer web part. For RS in Sharepoint Integration mode the entry has no affect. (For Sharepoint Integrated installations, you can add a field to the reporting document library and put the description there.)
Table properties:
-----------------------------------
* DataSetName – Got have one, even if you are doing something creative like displaying some sort of header table. Almost every report I create has a dataset called ‘header’. I use this with a table to display the report logo graphic and some identifying fields (i.e. report runtime, username, report title, etc). This dataset is just a placeholder without any real content. Sometimes I use select statements like “Select getdate()” or “Select 1”.
* NoRows – Depending on data selection criteria and/or parameters chosen by users, your report may yield no results (no rows). (Or your backend server might be down…yikes!) This option allows you to display a custom message to users in the event there is no data to display. (i.e. “The parameters you chose contains no data... please change your parameter choices and try rerunning the report”.)
* FixedHeader – Set to “True”, this is the same as the “Header should remain visible while scrolling” checkbox in the Table Properties pop up window. It’s really a slick feature.
Textbox properties within a table:
-----------------------------------
* BackgroundColor – Background color of the textbox
* BorderStyle – I prefer “Solid” most of the time
* Color – Means font color
* Format – Used for various number, percentage, date, text formatting. For Dates without time use ‘d’. For integers, use ‘N0’. For fixed decimals use N and then a number. ‘N4’ is a number with 4 decimals. Use ‘P1’ for a percentage with 1 decimal place. Check BOL for more formatting codes.
* Visibility – Another favorite, but deserves it’s own tip. Another day...
* CanGrow – ‘True’ most of the time, but I like to use ‘False’ if I don’t want lines to wrap. In that event, I add the field to the Tooltip in the properties window of the textbox.
In the Properties section:










Reporting Services runs as a middle-tier server as part of the existing server architecture.

SQL Server 2000 should be installed for the database server, and Internet Information Services 6.0 as a Web server.

The report server engine takes in report definitions, locates the corresponding data, and produces the reports.

Interaction with the engine can be done through the Web-based Report Manager, which also lets you manage refresh schedules and notifications.

End users view the report in a Web browser, and can export it to PDF, XML, or Excel. You can expand your current Server or avail the reporting service database on another server. Depending on report characteristics and application logic, it is sometimes better to have a copy of data separate to improve the performance.

You can also use continuous replication for reporting. With this there wouldn’t be any interference with the OLTP environment.

The locking problems can be solved by using ‘nolock’ and the query performance can be improved using ‘dirty read’ when a copy of the data is not available. This can be accomplished only if the database design and application logic permit dirty reads. You can expand your current Server or avail the reporting service database on another server. Depending on report characteristics and application logic, it is sometimes better to have a copy of data separate to improve the performance.

You can also use continuous replication for reporting. With this there wouldn’t be any interference with the OLTP environment.

The locking problems can be solved by using ‘nolock’ and the query performance can be improved using ‘dirty read’ when a copy of the data is not available. This can be accomplished only if the database design and application logic permit dirty reads. You can expand your current Server or avail the reporting service database on another server. Depending on report characteristics and application logic, it is sometimes better to have a copy of data separate to improve the performance.

You can also use continuous replication for reporting. With this there wouldn’t be any interference with the OLTP environment.

The locking problems can be solved by using ‘nolock’ and the query performance can be improved using ‘dirty read’ when a copy of the data is not available. This can be accomplished only if the database design and application logic permit dirty reads. You can expand your current Server or avail the reporting service database on another server. Depending on report characteristics and application logic, it is sometimes better to have a copy of data separate to improve the performance.

You can also use continuous replication for reporting. With this there wouldn’t be any interference with the OLTP environment.

The locking problems can be solved by using ‘nolock’ and the query performance can be improved using ‘dirty read’ when a copy of the data is not available. This can be accomplished only if the database design and application logic permit dirty reads. You can expand your current Server or avail the reporting service database on another server. Depending on report characteristics and application logic, it is sometimes better to have a copy of data separate to improve the performance.

You can also use continuous replication for reporting. With this there wouldn’t be any interference with the OLTP environment.

The locking problems can be solved by using ‘nolock’ and the query performance can be improved using ‘dirty read’ when a copy of the data is not available. This can be accomplished only if the database design and application logic permit dirty reads. You can expand your current Server or avail the reporting service database on another server. Depending on report characteristics and application logic, it is sometimes better to have a copy of data separate to improve the performance.

You can also use continuous replication for reporting. With this there wouldn’t be any interference with the OLTP environment.

The locking problems can be solved by using ‘nolock’ and the query performance can be improved using ‘dirty read’ when a copy of the data is not available. This can be accomplished only if the database design and application logic permit dirty reads. What are the ways to tune Reporting Services?What are the ways to tune Reporting Services?What are the ways to tune Reporting Services?Following are a few ways you can tune up Reporting Services:

You can expand your current Server or avail the reporting service database on another server. Depending on report characteristics and application logic, it is sometimes better to have a copy of data separate to improve the performance.

You can also use continuous replication for reporting. With this there wouldn’t be any interference with the OLTP environment.

The locking problems can be solved by using ‘nolock’ and the query performance can be improved using ‘dirty read’ when a copy of the data is not available. This can be accomplished only if the database design and application logic permit dirty reads. Bottom of Form