1. What is SQL?
    SQL is an acronym for Structured Query Language.

  2. What is Normalization, and explain the different levels.
    Normalization is a method for reducing redundancy in a database.  The idea is that data is grouped together in such a manner to enhance a given performance goal.

    1NF- Removes any duplicated attributes.
    2NF - Should be 1NF and each non-key should be dependent on the primary key.
    3NF - Should be 2NF and all non-key attributes that are not dependent on the primary key should be removed.

  3. What is Denormalization and when should it be applied?
    Denormalization is the reverse of normalization. It increases query performance by reducing the number of joins by consolidating data.

  4. What is the difference between an Heap table and a Clustered Table?
    HEAP table: A table in which the data is not stored in any particular order. There's no clustered index.

    CLUSTERED Table: A table with a predefined clustered index on at least one column defining the storing order of the rows within the data pages, based on a clustered index key.

  5. How many clustered indexes can be created on a table?
    One only. This because a table can be sorted in the table itself only once.

  6. What is the difference between a clustered and an non-clustered index?
    A table can have multiple non-clustered indexes, but only one clustered index.

  7. What are the database relationship types?
    One-To-One - For each instance in the first entity there is one and only one matching instance in the second entity, and vice versa.|

    One-To-Many - For each instance in the first entity, there can be more than one matching instance in the second entity.  The second entity will have one and only one matching instance in in the first entity.

    Many-To_Many - For each instance in the first entity, there can be one or more matching  instances in the second entity, and vice versa. 

  8. What is the difference between a Primary and a Unique Key?
    A Primary Key denotes a default clustered index, and does not allow nulls.
    A Unique Key denotes a non-clustered index, and allows one null.

  9. What is a Query Plan?
    A query plan is a physical breakdown of the code passed to the SQL Server optimizer. 

  10. What are Candidate, Alternate and Composite Keys?
    Candidate Key - a key that can uniquely identify a row in a table
    Alternate Key - If the table has more than one candidate keys and one becomes a primary key, the rest become alternate keys.
    Composite Key - More than one key to uniquely define a table row.

  11. What is a Transaction?
    A Transaction is a work wrapper that creates a reversible work instance as long as the transaction has not been committed.  Transactions conform to the ACID principle.

  12. What is ACID?
    ACID is the acronym for Atomicy, Consistency, Isolation and Durability:

    Atomicy: each transaction is to be 'all or nothing'. If one part of the transaction fails, the entire transaction fails.

    Consistency: Any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to the defined rules and structures of that database.

    Isolation: Ensure that the concurrent execution of transactions would be the same as if they were entered serially. Transactions are independent of each other.

    Durability: Once a transaction has been committed, it will remain committed, even after a system failure, environmental / hardware crashes, or errors. 

  13. What are the primary steps in data modeling?
    Logical - Planning, Analysis and Design
    Physical - Design,Implementation and Maintenance

  14. What is a cursor?
    A cursor is a mechanism that allows the developer to go throw a data result set row by row.

  15. What is a SPID?
    A SPID is a Service Process ID. It is simply a SQL Server 'session' created anytime an application connects to SQL Server.

  16. What are the different types of cursors?
    Static cursors: populates the result set at the time of cursor creation and the query result is cached. Can move forward and backward (scrollable). Most memory intensive of the cursor types.
    No UPDATE, INSERT, or DELETE operations are reflected in a static cursor (unless the cursor is closed and reopened).
    SQL Server static cursors are always read-only.

    Dynamic Cursors: Allows the developer to see updates, deletes and inserts while the cursor is open. So the cursor will be sensitive to data changes.

    Forward-only Cursors:The fastest of all the cursors. It does not allow for backward scrolling like static and dynamic cursors will.

  17. What is a SQL Server Deadlock?
    A deadlock is when 2 SPIDs have data locked, and neither process can release their locks until one of the SPIDs releases.
  18. Why should you avoid using cursors in general?
    A Cursor impacts the performance of the SQL Server since it uses the SQL Server instances' memory, reduce concurrency, decrease network bandwidth and lock resources.

  19. What is an execution plan, and how do you use it?
    An execution plan is the graphical road map of the SQL Server process.  It gives the developer an opportunity to analyze the performance characteristics of the query at hand.

  20. What is NOLOCK?
    NOLOCK allows SQL to read from data tables that would normally be blocked by another query or process. It can improve performance, but can result in dirty reads.
    This is only applicable with SELECT statements, as CRUD processes will result in an error.

  21. What is a 'Dirty Read'?
    A dirty read is a query that has run against a table where an active transaction is set. This means that the data returned in the query could be 'out of date' from the process holding the transaction open.

  22. What is a Transaction Isolation Level?
    This is how SQL Server controls the locking and row versioning of Transactions.

    READ COMMITTED: Stipulates that statements cannot read data that has been modified but not committed by other transactions.

    READ UNCOMMITTED: Stipulates that statements can read rows that have been modified, even if the row hasn't been committed by other transactions.

    REPEATABLE READ: Specifies that statements cannot read data that has been modified but not yet committed by other transactions, and no other transactions can modify data that has been read by the current transaction until the current transaction completes.

    SNAPSHOT: Specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction.

    SERIALIZABLE: Statements cannot read uncommitted data, no other transactions can modify the data until the current transaction completes, and other transactions cannot insert new rows with key values within the range of keys managed by the current transaction.

  23. What are the different types of locks available in SQL Server?
    Shared: used for operations that don't change data (like SELECT statements)

    Update: used when updating resources

    Exclusive: Used for CRUD processes
    Schema: Used when an operation dependent on the schema is a table is executing.

    Bulk Update: Used when bulk copying data into a table and the TABLOCK hint is specified.

    Intent (shared, update, exclusive): Used to establish a lock hierarchy.

    Key Range (shared, insert, exclusive): Protects a defined range of rows read by a query when using the SERIALIZABLE transaction isolation level.

  24. What is Lock Isolation?
    It's the process of of converting may fine-grained locks into fewer, coarse-grained locks.

  25. Define Index Depth, Density and Sensitivity.
    Index Depth: the number of levels from the index root node to the leaf nodes. If the depth is too deep, there will be performance degradation.

    Index Density: the measure if the lack of uniqueness of the data in a table. A dense column is one that has a high number of duplicates.

    Index selectivity: A measure of how many rows scanned compared to the total number of rows. High selectivity means there is a small number of rows scanned when related to total number of rows.

  26. What is the difference between an OLAP and a OLTP database?
    OLAP- Online Analytic Processing: Databases are used for transactional systems. Most of the submitted queries are for data modification.

    OLTP- Online Transaction Processing: Databases used for data warehousing systems. Most queries are data retrievals that filter, group, aggregate and join larger datasets very quickly.

  27. Name some tools / methods for monitoring SQL performance.
    Performance Monitor
    SQL Profiler
    Server-Side Trace
    Custom Scripts
    Third Party Applications

  28. How do you collect IO and time statistics for queries?
    SET STATISTICS IO on: provides a textual count of table reads.
    SET STATISTICS TIME on: provides compile and execution times.
(To be continued)

  1. What is MVC?
    MVC is an acronym for the Model, View Controller framework:
    Model - The business object layer of the application
    View - the front-end, or customer facing layer of the application
    Controller - the business logic layer of the application

  2. What are the prime advantage of using MVC?
    Since the view is separate from the model and controller, it's easier to create unique view that utilize the same Models and Controllers. It is also easier to update the View without changing the Model or Controller.

    Separation of Concerns - As MVC denotes separate disciplines, it is easier to delegate coding responsibilities to different teams.

    Testability - MVC is better suited to TDD than ASP.NET web form development

  3. What is the MVC application Life cycle?


    1. Fill Route - MVC requests are mapped to Route Tables that specify which controller and action to invoke.
    2. Fetch Route - Depending on the URL sent, the "UlrRoutingModule" searches the route table to create the "RouteDate" object, which contains the details of which controller and action to invoke.
    3. Request Context Created - The "RouteData" object is used to create the "RequestContext" object
    4. Controller instance Created - The request object is sent to "MvcHandler" instance to create the Controler class instance. Once the Controller class object is created, the 'Execute"  method of the Controller is called.


    5. Execute the method defined in step 4.
    6. Build the View for the requesting platform.
    7. Send the resulting View data to the client.

  4. What are the different return types of a Controller action method?
    ViewResult (view) - Returns a web page
    PartialViewResult(PartialView) - Returns a partial view that is to be displayed in a different view.
    RedirectToRouteResult(RedirectToAction, RedirectToRoute) - Used to redirect to another action method
    ContentResult(Content) - Returns an HTTP content type (ie: text/plain) as a result
    JsonResult(json) - Returns a json result set
    JavascriptResult(javascript) - returns a Javascript result set
    FileResult(File) - Returns a binary file data response
    EmptyResult - Returns noting (void)

  5. What are Filters in MVC?
    FIlters are qualifiers that can be used in MVC to set pre/post behaviors on a Controller's action methods:
    Action Filters - Used to implement logic  that's executed before and after a Controller action executes.
    Authorization Filters - Used to implement authentication and authorization for controller actions.
    Result Filters - These contain logic that is executed before and after a View result is executed.
    Exception Filters - Used to handle any errors raised by either Controller actions or Controller action results.

  6. What are the Action Filters in MVC?
    Output Cache - caches the output of a controller action for a set amount of time
    Handler Error - handles errors raised when a Controller Action executes.
    Authorize - enables the developer to restrict access to a given user or role

  7. What is Routing in MVC?
    Routing is the mechanism that processes the incoming URL for an MVC web site. It is generally thought of as more descriptive and able to provide a more detailed response.

  8. What is the difference between TempData, ViewData and ViewBag?
    TempData - Used to pass data from the current request to the next request. Generally used to store 'one-time' messages such as error and validation messages.  Requires typecasting for complex types.
    ViewData - Used to pass data from the Controller to the View. Requires typecasting for complex types.
    ViewBag - Used to pass data from the Controller to the View. This is a dynamic property, so it does not require typecasting for complex types.

(To be continued)

  1. What is ETL?
    ETL is an acronym for Extraction, Transformation and Loading. It the process of converting and loading data from one given source to another.

  2. What are the steps that define ETL?
    Defining the source (setting the source connection to the  source)
    Defining the target (setting the target connection to its target source)
    Creating the mapping (defining and setting the business logic)
    Create the session (defining the mapping instructions)
    Create the work flow (setting the instruction sets)

  3. What is a 3-tier data warehouse?
    Here, the data is thought of as a 3 tiered system. The 1st layer is considered the source layer.
    The 2nd layer is the integration layer, where 1st layer transformations are stored.
    The 3rd layer is the dimension layer - this is where the presentation layer is accessed.

  4. What is a snapshot?
    A snapshot is a read-only copy of the master data. 
  5. What is a materialized View?
    A materialized view is simply an aggregate table, or a database view object.

  6. What is round-robin processing?
    Round robin means that we are processing more than one source or thread. Instead of reading or processing in a linear fashion, we have divided the source data into n units and, in turn, pull one element from each source.

    Round robin also means that data is distributed evenly to n number of partitions.

  7. What is hash partitioning?
    Hash partitioning means that we create a hash key for each row in a source data set and process the data by the generated hash key.

    Hash partitioning also means that data is distributed evenly via a generated hash key for each data element.

    A hash key is essentially a small data value meant to represent a larger piece of data.

  8. What is an Operational Data Store?
    An Operational Data Store (ODS) is a repository between the staging area and the data warehouse. 

  9. What is the difference between a Full Load, an Incremental Load and an Initial Load?
    Initial Load - Data is loaded into the target source for the first time.
    Full Load - This is the equivalent of a new load process.  All existing data is erased and fresh data is loaded into the store.
    Incremental Load - Only changes to the target source are made. This is generally a scheduled process.

  10. What is a factless fact schema?
    A factless fact schema is a fact table without measures. That is, the facts represented in the table are likely computations (statistics, for example) and is more akin to a report table.

(To be continued)

  1. What are the four tenets of an OOP language?
    Abstraction- refers to hiding the internal operations of a class from anything that consumes that class.
    Encapsulation- refers to the bundling of data and methods into a single object or class
    Inheritance- refers to the ability of one class to use properties of another class
    Polymorphism- refers to the ability to use different object types through the same interface.

  2. What is boxing and unboxing?
    Boxing is the ability to store a value type into an object.  
    Unboxing is the ability to take an object variable and convert it to a specific value type.

  3. What is a generic?
    A genericis a placeholder type. It allows the developer to use an object without immediately determining a typeset. Used primarily in collections.

  4. What is LINQ?
    LINQ(Language Integrated Query) is a methodology for querying data and objects.

  5. What is a Lambda Expression?
    A Lambda Expression is the shorthand syntax for writing LINQ statements. 

  6. What are delegates?
    Delegates allow the developer to set variables as function calls. It can be thought of as to a pointer to a function.

  7. What is an Abstract Class?
    An abstract class is a class that cannot be instantiated. Abstract classes can, however, be inherited.

  8. What is an Interface?
    An interfaceis a collection of abstract methods.

  9. What is the difference between overloading and overriding?
    Overloading - multiple functions of the same name, using different input parameters.
    Overriding - use of a subclass to override(replace) the method functionality of a main class.

    Overloading is a static binding, while overriding is a dynamic binding.

  10. What are the Access Modifiers?

  11. What is the difference between Public, Static and Void?
    Public - the object or method is available to anything able to consume it.
    Private - the object is available only to the class that contains it.
    Static- the object is globally accessible without instantiating a class. 
  12. What is a Constructor?
    A constructor is a function that has the same name as its containing class.

  13. What is Serialization?
    Serialization  is the act of turning an object into a stream of bytes. This stream can then be stored into another platform (like a file.)

    A class can be made serializable by declaring it as ISerialize.

  14. What is Deserialization?
    Deserialization is the conversion of a byte array into a set object.

  15. What's the difference between Read-Only variables and Constant variables?
    Constant variables are set once during compile time and cannot be changed. Read-Only variables are used only when setting a value at runtime.

  16. Can you override the methods in an interface?
    No. All of the interface methods are virtual, so they can only be overridden in the class that inherits them.

  17. What is the difference between a Struct and a Class?
    A Struct is a value-type variable, where a Class is a reference type.  Structs cannot be inherited, and are stored on the stack for speed of access.

  18. What is the difference between Stack and Heap memory?
    Stack memory is more structured and managed by the system.  Heap memory is managed by the application.

    Value types are allocated to the Stack.  Reference types are allocated to the Heap.

  19. What is a sealed modifier?
    A sealed modifier prevents the object from being inherited, 

  20. What is Jagged Array?
    A jagged  array is an array of arrays.

  21. What is Managed code?
    Managed Code is any code executed by the CLR. The code is 'managed' by the .NET Framework.

  22. What are the different types of classes in C#?
    Keyword: Partial Class - A 'shared' class allows its members to be used by multiple .cs files.
    Keyword: Sealed class - A class that cannot inherited. To access its members, you need to instantiate the class directly.
    Keyword: Abstract class - A class that cannot be instantiated. This class can only be inherited, and will contain at least one method.
    Keyword: Static class - A class that does not allow inheritance. Members of a static class are also static.

  23. What are the steps taken in C# code compilation?
    1. Compiling the source code to Managed Code
    2. Combining newly created code to Assemblies
    3. Loading the Common Language Runtime (CLR)
    4. Executing the assembly by the CLR

  24. What's the difference between a Virtual Method and an Abstract Method?
    Abstract Method - always has a default implementation. It can be overridden in its derived class, though not necessarily mandatory. It can be overridden using the keyword: override.
    Virtual Method - does not have an implementation, and resides in the abstract class. It is mandatory that the the derived class implements the abstract method.

  25. What are Events?
    Events are actions that generate some type of notification.
(To be continued)