Predict the output of these queries

-- Create FirstTable

CREATE TABLE FirstTable (in_id INT, vc_value VARCHAR(10), in_flag INT)

GO

-- Populate FirstTable

INSERT INTO FirstTable (in_id, vc_value, in_flag)

SELECT 1, 'First', 1

UNION ALL

SELECT 2, 'Second', 1

UNION ALL

SELECT 3, 'Third', 2

UNION ALL

SELECT 4, 'Fourth', 1

UNION ALL

SELECT 5, 'Fifth', 2

UNION ALL

SELECT 6, 'Sixth', 1

UNION ALL

SELECT 7, 'Seventh', 2

GO

-- Create SecondTable

CREATE TABLE SecondTable (in_id INT, vc_value VARCHAR(10), in_flag INT)

GO

-- Populate SecondTable

INSERT INTO SecondTable (in_id, vc_value, in_flag)

SELECT 1, 'First', 1

UNION ALL

SELECT 2, 'Second', 1

UNION ALL

SELECT 3, 'Third', 2

UNION ALL

SELECT 8, 'Eighth', 1

UNION ALL

SELECT 9, 'Ninth', 2

GO

Predict the output of these queries?

--Query 1

SELECT * FROM FirstTable a

LEFT JOIN SecondTable b ON a.in_id = b.in_id AND a.in_flag = 1

GO

--Query 2

SELECT * FROM FirstTable a

LEFT JOIN SecondTable b ON a.in_id = b.in_id

WHERE b.in_flag = 1

GO

DROP TABLE FirstTable

GO

DROP TABLE SecondTable

GO

Answer: Query 1 returns the rows of SecondTable where in_flag = 1 with all rows of FirstTable. Query 2 only returns rows from FirstTable and SecondTable where in_flag = 1.

Explanation: The ON clause applies before JOIN so it retrieves all the rows of SecondTable where in_flag = 1 but it does not affect FirstTable so it retrieves all the rows of FirstTable. When WHERE clause is applied, it applies to the complete result so it removes all the rows from FirstTable and SecondTable where Flag is not equal to 1, essentially keeping in_flag = 1 rows from FirstTable and SecondTable.

Predict the output

When running the code below on SQL Server 2008 and above, what will the final query (with the comment "Predict the output of this query") return?

CREATE TABLE dbo.PredictOutput

(in_pk int NOT NULL,

in_pk_value int NOT NULL,

PRIMARY KEY (in_pk)

);

go

CREATE TRIGGER TestTrig

ON dbo.PredictOutput

FOR INSERT

AS

IF (SELECT COUNT(*) FROM INSERTED WHERE in_pk_value < 0) = 1

BEGIN;

RAISERROR ('Negative values are not allowed!', 16, 1);

ROLLBACK TRAN;

END;

go

-- Insert attempt #1

INSERT INTO dbo.PredictOutput (in_pk, in_pk_value)

VALUES (1, 1);

go

-- Insert attempt #2

INSERT INTO dbo.PredictOutput (in_pk, in_pk_value)

VALUES (2, -2);

go

-- Insert attempt #3

INSERT INTO dbo.PredictOutput (in_pk, in_pk_value)

VALUES (3, -3), (4, -4);

go

-- Predict the output of this query:

SELECT COUNT(*) FROM dbo.PredictOutput;

go

DROP TRIGGER TestTrig;

go

DROP TABLE dbo.PredictOutput;

go

Answer: 3

Explanation: An insert trigger fires once per execution of a statement that may cause rows to be inserted. During that single execution, all inserted rows will be in the INSERTED pseudo-table (also called magic table). The trigger here works correctly only if a single row is inserted.

Coding triggers that works correctly only if a single row is inserted is bad practice. Even if the application currently inserts rows one at a time, it may change in the future.

In the case of this question, insert attempt #1 succeeds (as there are no rows inserted with in_pk_value below zero).

Insert attempt #2 fails (the negative value in in_pk_value causes an error message and a rollback). This aborts the batch, but the next batch (starting at the next "go") will execute normally.

Insert attempt #3 then succeeds. Both inserted rows violate the business rule, but the trigger here only causes an error message if the number of violations is exactly one.

The correct way to code this would have been to use IF EXISTS (SELECT * FROM INSERTED WHERE in_pk_value < 0). Incidentally, this would also have been more efficient.

What is the maximum amount of parameters a stored procedure can have?

Choose one of the answers below:

1. 256
2. 1024
3. 2048
4. 2100

Answer:

4. 2100

Explanation:

A stored procedure can have a maximum of 2100 parameters passed into it. However, please don't take advantage of this :)

How can you remove the filter created by a CREATE SUBCUBE statement in an MDX query?

Choose one of the answers below:

1. It is removed when the batch ends.
2. Issue the CLEAR SUBCUBE statement.
3. Issue the DROP SUBCUBE statement.
4. Issue the CLEAR FILTER statement.

Answer:

3. Issue the DROP SUBCUBE statement.

Explanation:

After you create a subcube, it is used for all subsequent batches until the DROP SUBCUBE

statement is submitted or the session closes.

Is it possible to modify data in a table variable passed as a PARAMETER to stored procedure in SQL Server 2008?

Choose one of the answers below:

1. Yes
2. No
3. Not possible to pass a table variable as parameter to stored procedure

Answer:

2. No.

Explanation:

Table variables are READONLY when passed as a parameter to Stored Procedure. These can be used but not modified.

What does @@total_write in SQL Server 2005?

Choose one of the answers below:

1. The total number of writes (disk + cached) for the previous statement.
2. The total number of writes (disk only) for the previous statement.
3. The total number of writes (disk + cached) for the instance since last restart.
4. The total number of writes (disk only) for the instance since last restart.

Answer:

4. The total number of writes (disk only) for the instance since last restart.

Explanation:

The @@total_read function returns the number of disk writes since the SQL Server instance was last restarted.

Differentiate between web.config, app.config and machine.config files

web.config file

• web.config is used for ASP.NET Web Projects / Web Services. web.config by default has several configurations required for the web application. It is also called Application Level Configuration File and inherits setting from the machine.config file.
• web.config is parsed at runtime, so if you edit the web.config file, the web application will automatically load the changes in the config file.
• web.config file is automatically generated when new web application created.
• You can have more than one web.config file in your application. Specifically, you can have a web.config for each folder under your web application.
• The web.config file is required for ASP.NET webpages.

app.config file

• app.config is used for Windows Forms, Windows Services, Console Apps and WPF applications.
• app.config is parsed at compile time, so if you edit the app.config file, you have to restart the application. At compile time a copy of the app.config file is taken, renamed to [output].config and moved to the build folder. This copy can then be modified, and your modifications will be read each time the application/service is started.
• app.config is not added automatically to an application. You can go to the solution explorer, select 'Add new item' and add the 'Application Configuration File'.
• There is always one app.config file in a window application.
• The app.config file is optional in an application and doesn't have to be used when writing desktop applications.

machine.config file

• machine.config file is automatically installed on your system when you install Visual Studio.Net. This is also called Machine Level Configuration File. Only one machine.config file exists on a server, and is at the highest level in the configuration hierarchy.
  
• The settings of machine.config file are applied to all the web applications residing on the server.
  
• The machine.config file is overridden by the web.config file.
  
• Without the machine.config file, application can not be executed.
  

What are Object Initializers and Collection Initializers in C#?

Object Initializers

Object initializers facilitates assignment of values to any accessible fields or properties of an object at creation time, without having to explicitly invoke a constructor.

Object initializers with named types

In this example, we use auto-implemented properties to define a class.

public class Point
{
    public int iXPoint { get; set; }
    public int iYPoint { get; set; }
} 

During instantiation of the objects of the Point class, we can use:

Point oPoint = new Point();
oPoint.iXPoint = 0;
oPoint.iYPoint = 1;

Given below is a shorter technique to implement the above declaration and assignments:

// {iXPoint = 0, iYPoint = 1} is field or property assignments
Point oPoint = new Point { iXPoint = 0, iYPoint = 1 };  

In LINQ, we can use named object initializer as given below:

The following example shows the technique to use named object initializer with LINQ. The example below assumes that an object contains many fields and methods related to a product, but we are only interested in creating a sequence of objects that contain the product name and the unit price.

 var productInfos =
      from p in products
      select new { ProductName = p.ProductName, UnitPrice = p.UnitPrice };  

Collection Initializers

Collection Initializers are similar in concept to Object Initializers and allows you to create and initialize a collection in a single step. During implementation of a collection initializer, you do not have to specify multiple calls to the Add method of the class in your source code; the compiler adds the calls by itself.

List<int> numbers = new List<int> { 1, 100, 100 };

In fact, it is the short form of the following:

List<int> numbers = new List<int>();
numbers.Add(1);
numbers.Add(10);
numbers.Add(100); 

Note: To be able to use a Collection Initializer on an object, the object must satisfy these two requirements:

• It must implement the IEnumerable interface.
• It must have a public Add() method.

What are implicit type var and Anonymous Types?

Implicit type var

In Visual C#, variables that are declared in the method scope can have an implicit type var. You can use the modifier var to instruct the compiler to infer and assign the type, as given below:

var iRoll = 23;      // int iRoll = 23;
var sMessage = "Hello"; // string sMessage = "Hello"; 

Arrays can also be declared with implicit typing as given below:

var iNum = new[] { 1, 2, 3, 4 }; // int[]
var sStr = new[] { "hello", null, "world" }; // string[]
var sJagged = new[] { a, new[] { 5, 6, 7, 8 } }; // single-dimension jagged array

The following restrictions apply to implicitly-typed variable declarations:

• var can only be used when a local variable is declared and initialized in the same statement; the variable cannot be initialized to null literal, because it does not have a type – like lambda expressions or method groups. However, it can be initialized with an expression that happens to have the value null, as long as the expression has a type.
• var cannot be used on fields in class scope.
• Variables declared by using var cannot be used in their own initialization expression.
  In other words, var v = v++; will result in a compile-time error.
• Multiple implicitly-typed variables cannot be initialized in the same statement.
• If a type named var is in scope, then we will get a compile-time error if we attempt to initialize a local variable with the var keyword.

Anonymous Type

Anonymous types provide a convenient way to encapsulate a set of read-only properties into a single object without having to first explicitly define a type. The type name is generated by the compiler and is not available at the source code level. The type of the properties is inferred by the compiler. 

The following example shows an anonymous type being initialized with two properties called iAmt and sMessage:

var v = new { iAmt = 123, sMessage = "Hello" };

Anonymous types are class types that consist of one or more public read-only properties. No other kinds of class members such as methods or events are allowed.

Some rules on anonymous types:

• You must provide a name to a property that is being initialized with an expression, except in the situation that the second bullet describes.
• If you do not specify member names in the anonymous type, the compiler gives the anonymous type members the same name as the variable, field or property being used to initialize them.
• Anonymous types are limited to a local scope.

Can base/derived classes in C# be exported to COM?

COM Interop is a wonderful technology. One aspect of the common language runtime (CLR) that makes it an extremely powerful platform is that it allows seamless interactions between managed applications and unmanaged COM components.

• COM only deals with interfaces. Base/derived classes have no meaning or functionality in COM. Inheritance is not applicable either.
• In COM, interfaces can inherit from one another. However, the .NET implementation that exports the .NET interface to COM does not support inheritance. Therefore, we must replicate any interface members in a base interface to the derived interface.
• Moving members between a base and derived class will have no impact on what is visible to COM.
• The programmer can only define what is exposed to COM. The compiler will not use reflection or anything else to determine what should be exposed.
• All COM classes have a single, default interface. This is the interface that is normally used for an object. A COM class can expose other interfaces but the COM client must then query for the interface. In .NET the first COM visible interface is used as the default interface for a COM class.

The MVC Programming Model

ASP.NET MVC is a framework for building web applications using a MVC (Model View Controller) design

• The Model represents the application core (business layer).
• The View displays the data (the display layer).
• The Controller handles the input (input control).

The ASP.NET MVC framework is a lightweight, highly testable presentation framework that (as with Web Forms-based applications) is integrated with existing ASP.NET features, such as Master Pages and Membership–Based Authentication. The MVC framework is defined in the System.Web.Mvc namespace.

The MVC model also provides full control over HTML, CSS, and JavaScript.

The MVC model defines web applications with 3 logical layers:

• The business layer (Model logic)

• The display layer (View logic)

• The input control (Controller logic)

Model objects are the parts of the application that implement the logic for the application's data domain. Often, model objects retrieve data (and store data) from a database.

Views are the parts of the application that handles the display of the data (User Interface). Most often the views are created from the model data. The view in MVC in dumb. It just receives data.

Controller is the part of the application that handles user interaction. Typically controllers read data from a view, control user input, and send input data to the model.

In an MVC application, the view only displays information; the controller handles and responds to user input and interaction. The MVC pattern helps us to create applications that separate the different aspects of the application (input logic, business logic, and UI logic), while providing a loose coupling between these elements. The pattern specifies where each kind of logic should be located in the application.

The MVC separation helps you manage complex applications, because you can focus on one aspect a time. For example, you can focus on the view without depending on the business logic. It also makes it easier to test an application. The MVC separation also simplifies group development. Different developers can work on the view, the controller logic, and the business logic in parallel.

The global.asax file in ASP.NET

The global.asax file in ASP.NET as an application file, which is optional and allows us to write code that response to global application events raised by ASP.NET or by HttpModules. These events fire at various points during the lifetime of a web application, including when the application domain is first created.

The global.asax file resides in the root directory of an ASP.NET application. At run time, global.asax is parsed and compiled into a dynamically generated .NET Framework class derived from the HttpApplication base class.

The global.asax file looks very similar to a normal .aspx file. However, instead of containing HTML or ASP.NET tags, it contains event handlers that response to application events. Each ASP.NET application can have only one global.asax file. Once you place it in the appropriate website directory, ASP.NET recognizes it and uses it automatically.

The ASP.NET global.asax file can coexist with the ASP global.asa file.

Basic Application Events

S. No.	Event Handling Method	Description
1	Application_Start()	Application_Start() event occurs when the application starts, which is the first time it receives a request from any user. It doesn’t occur on subsequent requests. This event is commonly used to create or cache some initial information that will be reused later.
2	Application_End()	Application_End() event occurs when the application is shutting down, generally because the web server has restarted. You can insert cleanup code here.
3	Application_BeginRequest()	Application_BeginRequest() event occurs with each request the application receives, just before the page code is executed.
4	Application_EndRequest()	Application_EndRequest() event occurs with each request the application receives, just after the page code is executed.
5	Session_Start()	Session_Start() event occurs whenever a new user request is received and a session is started.
6	Session_End()	Session_End() event occurs when a session times out or is programmatically ended. This event is only raised if you are using in-process session state storage (the InProc mode, not the StateServer or SQLServer modes ).
7	Application_Error()	Application_Error() event occurs in response to an un-handled error.